Modifying my parameterized query to select * an entire column

• leavesandmusic

  SSC-Addicted

  Points: 464

  More actions

  November 11, 2017 at 6:50 pm

  #1967651

  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).Value

  I 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 Sub
  
  

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  November 13, 2017 at 5:33 am

  #1967750

  Untested but something like this

  
SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (@my_id)"
SQL.ActiveConnection = Cn
SQL.Parameters.Append SQL.CreateParameter("@my_id", adVarChar, adParamInput, 50, Value)
RowNum = 1
While Sheets("Sheet1"),Cells(RowNum,5).Value <> ""
SQL.Parameters(1).Value = Sheets("Sheet1"),Cells(RowNum,5).Value
SQL.Execute
RowNum = RowNum + 1
Wend


  Far away is close at hand in the images of elsewhere.
  Anon.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 14, 2017 at 6:50 am

  #1967949

  leavesandmusic - Friday, November 10, 2017 11:51 AM

  Thank you, so I assume the code would look like this?

  Sub ClickButton()
  Dim SQL As ADODB.Command
  Set SQL = New ADODB.Command
  Dim A As Variant
   A = Sheets("Sheet1").Range("E1").Value            
  Dim B As Variant
   B = Sheets("Sheet1").Range("E2").Value          

  SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"
  SQL.Parameters.Append SQL.CreateParameter("my_id", adVarChar, adParamInput, 50, Value)
  SQL.ActiveConnection = Cn
  SQL.Execute
  End Sub()

  I tested the code.  It does not work. It says "object required" for this line of code: SQL.CommandText = "INSERT INTO [MyDatabase].[dbo].[MyTable] ([my_id]) VALUES (A), (B)"

  You need to translate values from the spreadsheet into strings and concatenate those strings within the query text, as opposed to thinking that VBA will magically see A and B and substitute their values in.   As you have a quoted string as your query, SQL Server will see that exact string, and that isn't going to work, even if you get past the object error.   I also fail to see why you insist on using parameters for your query when none are needed.   You can just use string concatenation to place the value of the variables A and B into the appropriate location in the string that contains the INSERT query.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• leavesandmusic

  SSC-Addicted

  Points: 464

  More actions

  November 14, 2017 at 8:31 am

  #1967985

  Thanks for the tip!