SQL Command Text and Updates With ADODB

[vbperson22]

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?

Private Sub CommandButton1_Click()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Const adParamInput = 1
Const adVarChar = 200


Server_Name = "MyServer" 
Database_Name = "MyDatabase" 

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";"

Dim SQL As ADODB.Command
Set SQL = New ADODB.Command

SQL.CommandText = "UPDATE [MyDatabase].[dbo].[Test] SET [name] =(?) WHERE [id] = 1;"
SQL.CommandText = SQL.CommandText + "UPDATE [Mydatabase].[dbo].[name] 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 = Cn
SQL.Execute

Cn.Close
Set Cn = Nothing

End Sub

[Shaggy Hiker]

Welcome to the forums. I edited your post to add [CODE][/CODE] tags, which you can do by pressing the # button and pasting the code between the resulting tags.

As to the question, you're going to have to say what "doesn't work" means. Are you getting errors? Does nothing happen? Also, it would help if you showed us how you are trying to do multiple updates.

[TysonLPrice]

I never seen stacking multiple updates like that. Seems like that would be OK but not the parameter passing.

I would make that a single called sub or function and call it once for each update, i.e. a loop.