November 4, 2017 at 2:22 pm
This working code lets the user enter something in a cell, click a button in Excel and exports the data into a SQL Server database. The problem is that I would like to do multiple updates. The code works when I have one line of code doing only one update. But it does not work when I add more updates. Any suggestions?
Set Cn = New ADODB.ConnectionCn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"Dim SQL As ADODB.CommandSet SQL = New ADODB.CommandSQL.CommandText = "UPDATE [dbMyDatabase].[dbo].[tbl_ExcelTest] SET [Value] =(?) WHERE [id] = 1"SQL.Parameters.Append SQL.CreateParameter("Value", adVarChar, adParamInput, 50, Value)SQL.ActiveConnection = CnSQL.ExecuteCn.CloseSet Cn = Nothing
November 5, 2017 at 12:53 am
leavesandmusic - Saturday, November 4, 2017 2:22 PMThis working code lets the user enter something in a cell, click a button in Excel and exports the data into a SQL Server database. The problem is that I would like to do multiple updates. The code works when I have one line of code doing only one update. But it does not work when I add more updates. Any suggestions?
Set Cn = New ADODB.ConnectionCn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"Dim SQL As ADODB.CommandSet SQL = New ADODB.CommandSQL.CommandText = "UPDATE [dbMyDatabase].[dbo].[tbl_ExcelTest] SET [Value] =(?) WHERE [id] = 1"SQL.Parameters.Append SQL.CreateParameter("Value", adVarChar, adParamInput, 50, Value)SQL.ActiveConnection = CnSQL.ExecuteCn.CloseSet Cn = Nothing
Suggest you write a t-sql stored procedure with all the parameters needed and then call it from the Excel VBA code.
😎
November 10, 2017 at 9:14 am
Thank you for your suggestion, but I cannot use a stored procedure in this instance.
Can you please provide an example of working code that uses my logic.
For example, "UPDATE [dbMyDatabase].[dbo].[tbl_ExcelTest] SET [Value] =(?) WHERE [id] = 1".
This works, but it needs to be done for multiple values.
November 10, 2017 at 12:55 pm
leavesandmusic - Friday, November 10, 2017 9:14 AMThank you for your suggestion, but I cannot use a stored procedure in this instance.
Can you please provide an example of working code that uses my logic.
For example, "UPDATE [dbMyDatabase].[dbo].[tbl_ExcelTest] SET [Value] =(?) WHERE [id] = 1".
This works, but it needs to be done for multiple values.
Two things: 1.) WHY can't you use a stored procedure? and 2.) If you update the same id with multiple values, you'll just end up with the Value column being equal to the last one that gets supplied. Or do you have a list of ID and Value combinations?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 11, 2017 at 2:03 pm
Okay, my code works. But here is the issue: how can I assign a value in each cell within Excel?
Dim Name As StringDim Name1 As StringName = "test1"Name1 = "test2"To something like this?
Dim Name As VariantDim Name1 As VariantName = Cells(3,4).ValueName1 = Cells(3,5).ValueI have tried this method but it does not seem to work.
Dim Cn As ADODB.ConnectionDim
Server_Name As StringDim
Database_Name As StringDim
SQLStr As StringDim
Dim Name As String
Dim Name1 As StringName = "test1"
Name1 = "test2"
Server_Name = "."
Database_Name = "MyDatabase"
Set Cn = New ADODB.ConnectionCn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";
"Dim SQL As ADODB.CommandSet SQL = New ADODB.CommandSQL.CommandText = "UPDATE [MyDatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 1;
"SQL.CommandText = SQL.CommandText + "UPDATE [Mydatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 2;"
SQL.Parameters.Append SQL.CreateParameter("name", adVarChar, adParamInput, 50, Name)
SQL.Parameters.Append SQL.CreateParameter("name1", adVarChar, adParamInput, 50, Name1)
SQL.ActiveConnection = CnSQL.Execute
Cn.Close
Set Cn = Nothing
End SubNovember 12, 2017 at 3:01 am
Okay, my code works. But here is the issue: how can I assign a value in each cell within Excel?
Use the Excel object model, define a range and iterate through it to retrieve the values.
😎
November 12, 2017 at 3:13 pm
Thank you for your help Eirikur. Any idea why your suggestion does not work for me?
Dim Cn As ADODB.ConnectionDim
Server_Name As StringDim
Database_Name As StringDim
SQLStr As StringDim
Dim Name As Object
Set Name = Cells(1,2).Value
Dim Name1 As Object
Set Name = Cells(1,3).Value
Server_Name = "."
Database_Name = "MyDatabase"
Set Cn = New ADODB.ConnectionCn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";
"Dim SQL As ADODB.CommandSet SQL = New ADODB.CommandSQL.CommandText = "UPDATE [MyDatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 1;
"SQL.CommandText = SQL.CommandText + "UPDATE [Mydatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 2;"
SQL.Parameters.Append SQL.CreateParameter("name", adVarChar, adParamInput, 50, Name)
SQL.Parameters.Append SQL.CreateParameter("name1", adVarChar, adParamInput, 50, Name1)
SQL.ActiveConnection = CnSQL.Execute
Cn.Close
Set Cn = Nothing
End Sub
November 14, 2017 at 6:41 am
leavesandmusic - Sunday, November 12, 2017 3:13 PMThank you for your help Eirikur. Any idea why your suggestion does not work for me?Dim Cn As ADODB.ConnectionDim
Server_Name As StringDim
Database_Name As StringDim
SQLStr As StringDimDim Name As Object
Set Name = Cells(1,2).ValueDim Name1 As Object
Set Name = Cells(1,3).ValueServer_Name = "."
Database_Name = "MyDatabase"
Set Cn = New ADODB.ConnectionCn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";
"Dim SQL As ADODB.CommandSet SQL = New ADODB.CommandSQL.CommandText = "UPDATE [MyDatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 1;
"SQL.CommandText = SQL.CommandText + "UPDATE [Mydatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 2;"
SQL.Parameters.Append SQL.CreateParameter("name", adVarChar, adParamInput, 50, Name)
SQL.Parameters.Append SQL.CreateParameter("name1", adVarChar, adParamInput, 50, Name1)
SQL.ActiveConnection = CnSQL.Execute
Cn.Close
Set Cn = Nothing
End Sub
See where I highlighted your variable Name in bold red? That should be Name1, not just Name. Also, you are defining both Name and Name1 as objects, when they should be some kind of scalar value. If the contents of both cells are strings, as it would appear from how you are defining parameters, then you should probably define Name and Name1 as String.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2017 at 8:31 am
Thanks again for the tip!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply