Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 21st, 2010, 12:05 PM
Authorized User
 
Join Date: Jun 2003
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default Need help with ADODB Insert

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update using ADODB jmhemadri Classic ASP Databases 1 September 9th, 2008 06:07 PM
Can we Inherit ADODB venki5star Visual Studio 2005 3 March 13th, 2006 10:50 AM
ADODB.Fields EricJ ASP.NET 1.x and 2.0 Application Design 6 May 5th, 2005 07:21 AM
How to get adodb.record from adodb.recordset John Pennington Pro VB Databases 1 November 20th, 2004 06:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.