Need help with ADODB Insert

sg48 · March 21st, 2010, 12:05 PM

I have an excel spreadsheet. I want to insert this data into a SQL Server 2008 database. The code below is inside an excel module.
I wrote a loop to insert 1 row at a time.
Issue 1 is that I get an error that says "Must declare the scalar variable "@Email". This is the first parameter that I have. I'm not sure why this is happening because I thought I had the syntax right. (Obviously not).

Issue 2. How do I enter asll rows with one query instead of writing a loop,
Right now at the end of each loop iteration I am removing all of the parameters so they can be recreated in the next iteration.- Sorry-I am new at this.)

Code:

Public Sub SaveData(ByVal wks As Worksheet, ByVal startrow As Integer, ByVal NumMessagesTotal As Integer)

Dim var1, var2, var3, var4, var5, var6, var7, var8, var9, var10, var11, var12 As String
Dim i, r As Integer
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim cmd As ADODB.Command
 
Dim strConn, strsql As String
strConn = "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=DB_4702_sfcas;Data Source=s02.winhost.com;"
Set cmd = New ADODB.Command
cmd.CommandType = adCmdText
conn.Open strConn, "xxxxx", "xxxxx"
cmd.ActiveConnection = conn
cmd.CommandText = "Insert Into tblBoostAds (Email,FName,Headline,AdText,Url,Searchterm,ProdSvce,Issue1,Issue2,Issue3,Issue4,Issue5) " & _
    "Values(@Email,@FName,@Headline,@AdText,@Url,@Searchterm,@ProdSvce,@Issue1,@Issue2,@Issue3,@Issue4,@Issue5)"
 
For r = startrow To startrow + NumMessagesTotal - 1
    var1 = wks.Cells(r, 1)
    var2 = wks.Cells(r, 2)
    var3 = wks.Cells(r, 3)
    var4 = wks.Cells(r, 4)
    var5 = wks.Cells(r, 5)
    var6 = wks.Cells(r, 6)
    var7 = wks.Cells(r, 7)
    var8 = wks.Cells(r, 8)
    var9 = wks.Cells(r, 9)
    var10 = wks.Cells(r, 10)
    var11 = wks.Cells(r, 11)
    var12 = wks.Cells(r, 12)
 
    Dim objEmail As ADODB.Parameter
    Set objEmail = cmd.CreateParameter("@Email", adVarChar, adParamInput, 25, var1)
    cmd.Parameters.Append objEmail
    
    Dim objFName As Object
    Set objFName = cmd.CreateParameter("@FName", adVarChar, adParamInput, 25, var2)
    cmd.Parameters.Append objFName
    
    
    Dim objHeadline As Object
    Set objHeadline = cmd.CreateParameter("@Headline", adVarChar, adParamInput, 50, var3)
    cmd.Parameters.Append objHeadline
    
    
    Dim objAdText As Object
    Set objAdText = cmd.CreateParameter("@AdText", adLongVarChar, adParamInput, 1000, var4)
    cmd.Parameters.Append objAdText
    
    
    
    Dim objUrl As Object
    Set objUrl = cmd.CreateParameter("@Url", adVarChar, adParamInput, 50, var5)
    cmd.Parameters.Append objUrl
    
    
    Dim objSearchterm As Object
    Set objSearchterm = cmd.CreateParameter("@Searchterm", adVarChar, adParamInput, 50, var6)
    cmd.Parameters.Append objSearchterm
    
    
    Dim objProdSvce As Object
    Set objProdSvce = cmd.CreateParameter("@ProdSvce", adVarWChar, adParamInput, 10, var7)
    cmd.Parameters.Append objProdSvce
    
    
    Dim objIssue1 As Object
    Set objIssue1 = cmd.CreateParameter("@Issue1", adLongVarChar, adParamInput, 1000, var8)
    cmd.Parameters.Append objIssue1
    
    
    Dim objIssue2 As Object
    Set objIssue2 = cmd.CreateParameter("@Issue2", adLongVarChar, adParamInput, 1000, var9)
    cmd.Parameters.Append objIssue2
    
    
    Dim objIssue3 As Object
    Set objIssue3 = cmd.CreateParameter("@Issue3", adLongVarChar, adParamInput, 1000, var10)
    cmd.Parameters.Append objIssue3
    
    
    Dim objIssue4 As Object
    Set objIssue4 = cmd.CreateParameter("@Issue4", adLongVarChar, adParamInput, 1000, var11)
    cmd.Parameters.Append objIssue4
    
    
    Dim objIssue5 As Object
    Set objIssue5 = cmd.CreateParameter("@Issue5", adLongVarChar, adParamInput, 1000, var12)
    cmd.Parameters.Append objIssue5
    'would like to remove all parameters here so can repeat
    For i = 1 To 12
        'cmd.Parameters.Delete (i)
    Next
   cmd.Execute
   
Next

conn.Close
 

End Sub