asked on

must declare scalar variable

I am learning to use ADO in my Excel VB code. I wrote this test code to learn. I know it is not structured properly, it is just a quick app to learn. My parameter substitution is not working. It works when my sql statement has a literal instead of a parameter, but fails with the parameter: must declare scaler variable @pLogName.
My database defines LogName field as varchar(40). I don't see my error, can you help?

Sub ExportData()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim parameter As ADODB.parameter
Dim strSQL As String

Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=QS-ROB\SQLEXPRESS;Initial Catalog=SalesHistory;UID=ExcelUser;Trusted_Connection=yes;"
strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES (@pLogName);" 'this fails: must declare scalar variable @pLogName
' strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES ('TEST');" this works as expected
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Parameters.Append cmd.CreateParameter("@pLogName", adVarChar, adParamInput, 40, "TEST")
cmd.Execute Options:=(adExecuteNoRecords + adCmdText)
conn.Close

End Sub

Ben Personick (Previously QCubed)

@pLogName is a SQL variable, and would only come into play if you were using a stored procedure which you were dumping data to, and only in that stored procedure, not in your excel code.

Excel would use Cell designations (for single cells or ranges of cells) and that would be used int he SQL statement to update SQL,

You could also use VB Variables to store some value of a cell or the reference to the cell and use that to update the SQL.

IE. If you want to use a variable that is generated via your excel VBA code you have to define it and put it in its place.

If you want to use a cell value you need to do so.

Otherwise the there is no information contained in this Object @pLogName because it's not defined in the VBCode and so thew insert statement fails.

I have defined the String that you want to populate as "_LogName", and replaced it in the code to show you what I mean. (see Below)

Also, use code tags to make your formatted code easier to follow, as below:

Sub ExportData()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim parameter As ADODB.parameter
Dim strSQL As String
Dim _LogName As String
_LogName = "Testing"


Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=QS-ROB\SQLEXPRESS;Initial Catalog=SalesHistory;UID=ExcelUser;Trusted_Connection=yes;"
strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES (_LogName);"
'strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES ('TEST');" 
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Parameters.Append cmd.CreateParameter("@pLogName", adVarChar, adParamInput, 40, "TEST")
cmd.Execute Options:=(adExecuteNoRecords + adCmdText)
conn.Close

End Sub

Open in new window

Robert Crank

ASKER

I appreciate you trying to help, but this solution fails for two reasons:
1. _LogName fails to compile, it doesn't like the _.
2. Changing the names to something that compiles fails in the database because (in the example) _LogName is contained inside a quoted string and the value "Testing" is never substituted in the Insert query.

I believe you want me to use:
strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES ('" & _LogName & "');"
am I right?

Ben Personick (Previously QCubed)

Hey Robert,

1) Ahh, I so rarely touch VB I these days I didn't realize they had that restriction. So vbLogName is probably the better variable name.

2) Thats true, since it's a SQL statement you're building inside the VBA it would need ('"&vbLogName&"');"

& yup, I believe you got the point though, you need to be sending the value of the variable to the SQL insert statement.

Sub ExportData()

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim parameter As ADODB.parameter
Dim strSQL As String
Dim vbLogName As String
vbLogName = "Testing"


Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=QS-ROB\SQLEXPRESS;Initial Catalog=SalesHistory;UID=ExcelUser;Trusted_Connection=yes;"
strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES ('"&vbLogName&"');"
'strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES ('TEST');" 
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Parameters.Append cmd.CreateParameter("@pLogName", adVarChar, adParamInput, 40, "TEST")
cmd.Execute Options:=(adExecuteNoRecords + adCmdText)
conn.Close

End Sub

Open in new window

Norie

Robert

Try replacing @pLogName with ?.

    strSQL = "INSERT INTO [SalesHistory].[dbo].[TranDetail] ([LogName]) VALUES (?);" 

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL

    cmd.Parameters(0).Value = "Test"

    cmd.Execute Options:=(adExecuteNoRecords + adCmdText)
    conn.Close

Open in new window

Robert Crank

ASKER

Norie, that worked!

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.