RESOLVED: Odd VB Error in connection with SQL Stored Procedure
Trying to figure out some basic applications of Stored Procedures with VB (VB6).
I have the following Stored Procedure in MS SQL Server:
Code:
CREATE PROCEDURE dbo.MySP
@MyState varchar(15) = 'California',
@MySort varchar (50) = 'Zip' As
Declare @MySz varchar(100)
Select @MySz = 'Select * From MyTable Where State = ' + @MyState + ' Order by ' + @MySort
Exec(@MySz)
GO
MYSp is connected to my app through a DataEnvironment object as a data command named "MyCommand" which has (in addition to the RETURN_VALUE), two parameters: (1) "MyState" and (2) "MySort" (both of Data Type "adVarChar" and Host Data Type "String(VT_BSTR)");
I have called MyProcedure from VB6 as follows:
VB Code:
Private Sub Form_Load()
Text1.Text = ""
DE.MyCommand4 "CA", "Zip"
With DE.rsMyCommand
Do While Not .EOF
If Not Text1.Text = "" Then
Text1.Text = Text1.Text + ", "
End If
Text1.Text = Text1.Text + .Fields(1).Value
.MoveNext
Loop
.Close
End With
End Sub
When I do, I get the following error:
Quote:
Run-time error '-2147217900 (80040e14)':
Invalid column name 'California'.
MyTable looks as follows:
Quote:
IDNo Name City State Zip
{39... Jose Coral Gables Florida 035180895
{08... Gretchen WLA California 900250012
{FA... Leon Jr. Miami Florida 035120088
{79... Leon Sr. LA California 900690354
{F5... Zack WLA California 900250012
Why is VB seeking to equate the MyState variable with a column in the SQL DB when the SQL statement places the @MyState variable into a conditional context as in
Code:
' . . . WHERE State = " + @MyState
????
I appreciate any help I can get. Thanks.
Re: Odd VB Error in connection with SQL Stored Procedure
@MyState is a string variable and therefore must be surrounded by quotes:
Code:
CREATE PROCEDURE dbo.MySP
@MyState varchar(15) = 'California',
@MySort varchar (50) = 'Zip' As
Declare @MySz varchar(100)
Select @MySz = 'Select * From MyTable Where State = ''' + @MyState + ''' Order by ' + @MySort
Exec(@MySz)
GO
Re: Odd VB Error in connection with SQL Stored Procedure
