Dynamic Query in SQL7 called from VB6/ADO

[Paul Moss]

Hello,
I am trying to call a Stored Procedure in SQL7, from a VB6 program using
ADO 2.5, which does the following: It build up a dynamic query from passed
parameters and then executes it using the EXECUTE (querystring) syntax. It
first tries an exact match on the names, then if not successful tries to
match using the LIKE operator, and then if not successful tries a match
on the Soundex of the names. This works in all three cases when executed
in Query Analyser but when I try to call it from VB6 code, it only works
for the exact match and doesn't return anything from the results of the LIKE
or Soundex matches. Furthermore the RETURN VALUE is always returned empty.

The SP is as follows:

CREATE Proc Searchit (@Firstname Varchar(30), @Surname Varchar(30), @Gender
Char(1),
@County Int, @DOD Datetime ) AS

Declare @TSQLCmd Varchar(1024)
Declare @SearchArg Varchar(1024)

SET @SearchArg = "WHERE"

IF LEN(@Firstname) > 0
SET @SearchArg = @SearchArg + " AND Firstname = '" + @Firstname + "'"
IF LEN(@Surname) > 0
SET @SearchArg = @SearchArg + " AND Surname = '" + @Surname + "'"
IF LEN(@Gender) > 0
SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
IF @County > 0
SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = " + CAST(@County
as varchar(4)))
IF @DOD > 'Dec 30, 1899'
SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
+ "'"
SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth, DateofDeath,
AgeatDeath FROM Deceased " + @SearchArg
EXECUTE (@TSQLCmd)
IF @@ROWCOUNT > 0
RETURN 1
ELSE
BEGIN
SET @SearchArg = "WHERE"
IF LEN(@Firstname) > 0
SET @SearchArg = @SearchArg + " AND Firstname LIKE '" + @Firstname
+ "%'"
IF LEN(@Surname) > 0
SET @SearchArg = @SearchArg + " AND Surname LIKE '" + @Surname +
"%'"
IF LEN(@Gender) > 0
SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
IF @County > 0
SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = " +
CAST(@County as varchar(4)))
IF @DOD > 'Dec 30, 1899'
SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
+ "'"
SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
EXECUTE (@TSQLCmd)
IF @@ROWCOUNT > 0
RETURN 2
ELSE
BEGIN
SET @SearchArg = "WHERE"
IF LEN(@Firstname) > 0
SET @SearchArg = @SearchArg + " AND Firstname_Soundex = SOUNDEX('"
+ @Firstname + "')"
IF LEN(@Surname) > 0
SET @SearchArg = @SearchArg + " AND Surname_Soundex = SOUNDEX('"
+ @Surname + "')"
IF LEN(@Gender) > 0
SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender +
"'"
IF @County > 0
SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence
= " + CAST(@County as varchar(4)))
IF @DOD > 'Dec 30, 1899'
SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
+ "'"
SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
EXECUTE (@TSQLCmd)
IF @@ROWCOUNT > 0
RETURN 3
END
END

The Code to call it as follows:

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim Gender As String
Dim CountyCode As Long

If rs.State = adStateOpen Then rs.Close

If optSex(0).Value = True Then
Gender = "M"
End If
If optSex(1).Value = True Then
Gender = "F"
End If

If cboCounty.ListIndex = -1 Then
CountyCode = 0
Else
CountyCode = cboCounty.ItemData(cboCounty.ListIndex)
End If

If IsDate(txtDOD) Then
txtDOD = Format(txtDOD, "dd/mm/yyyy")
Else
txtDOD = Format(0, "dd/mm/yyyy")
End If

With cmd
.ActiveConnection = ConnectString
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,
, 0)
.Parameters.Append .CreateParameter("@Firstname", adVarChar, adParamInput,
30, txtForeName)
.Parameters.Append .CreateParameter("@Surname", adVarChar, adParamInput,
30, txtSurName)
.Parameters.Append .CreateParameter("@Gender", adChar, adParamInput, 1,
Gender)
.Parameters.Append .CreateParameter("@County", adInteger, adParamInput,
, CountyCode)
.Parameters.Append .CreateParameter("@DOD", adDate, adParamInput, , txtDOD)
.CommandType = adCmdStoredProc
.CommandText = "Searchit"
Set rs = .Execute

Debug.Print "Return = " & .Parameters("@RETURN_VALUE")

End With


If rs.EOF And rs.BOF Then
MsgBox "Nothing found"
Exit Sub
End If

Do While Not rs.EOF
Debug.Print rs.Fields(0).Value
Debug.Print rs.Fields(1).Value
Debug.Print rs.Fields(2).Value
Debug.Print rs.Fields(3).Value
Debug.Print rs.Fields(4).Value
rs.MoveNext
Loop


Please has anybody any idea what I have done wrong, many thanks

Paul

[jy]

"Paul Moss" <pbmoss@hotmail.com> wrote:
>
>
>Hello,
> I am trying to call a Stored Procedure in SQL7, from a VB6 program using
>ADO 2.5, which does the following: It build up a dynamic query from passed
>parameters and then executes it using the EXECUTE (querystring) syntax.
It
>first tries an exact match on the names, then if not successful tries to
>match using the LIKE operator, and then if not successful tries a match
>on the Soundex of the names. This works in all three cases when executed
>in Query Analyser but when I try to call it from VB6 code, it only works
>for the exact match and doesn't return anything from the results of the
LIKE
>or Soundex matches. Furthermore the RETURN VALUE is always returned empty.
>
>The SP is as follows:
>
>CREATE Proc Searchit (@Firstname Varchar(30), @Surname Varchar(30), @Gender
>Char(1),
> @County Int, @DOD Datetime ) AS
>
>Declare @TSQLCmd Varchar(1024)
>Declare @SearchArg Varchar(1024)
>
>SET @SearchArg = "WHERE"
>
>IF LEN(@Firstname) > 0
> SET @SearchArg = @SearchArg + " AND Firstname = '" + @Firstname + "'"
>IF LEN(@Surname) > 0
> SET @SearchArg = @SearchArg + " AND Surname = '" + @Surname + "'"
>IF LEN(@Gender) > 0
> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
>IF @County > 0
> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = " + CAST(@County
>as varchar(4)))
>IF @DOD > 'Dec 30, 1899'
> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>+ "'"
>SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
>SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth, DateofDeath,
>AgeatDeath FROM Deceased " + @SearchArg
>EXECUTE (@TSQLCmd)
>IF @@ROWCOUNT > 0
> RETURN 1
>ELSE
> BEGIN
> SET @SearchArg = "WHERE"
> IF LEN(@Firstname) > 0
> SET @SearchArg = @SearchArg + " AND Firstname LIKE '" + @Firstname
>+ "%'"
> IF LEN(@Surname) > 0
> SET @SearchArg = @SearchArg + " AND Surname LIKE '" + @Surname +
>"%'"
> IF LEN(@Gender) > 0
> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
> IF @County > 0
> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = "
+
>CAST(@County as varchar(4)))
> IF @DOD > 'Dec 30, 1899'
> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>+ "'"
> SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
> SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
>DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
> EXECUTE (@TSQLCmd)
> IF @@ROWCOUNT > 0
> RETURN 2
> ELSE
> BEGIN
> SET @SearchArg = "WHERE"
> IF LEN(@Firstname) > 0
> SET @SearchArg = @SearchArg + " AND Firstname_Soundex = SOUNDEX('"
>+ @Firstname + "')"
> IF LEN(@Surname) > 0
> SET @SearchArg = @SearchArg + " AND Surname_Soundex = SOUNDEX('"
>+ @Surname + "')"
> IF LEN(@Gender) > 0
> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender +
>"'"
> IF @County > 0
> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence
>= " + CAST(@County as varchar(4)))
> IF @DOD > 'Dec 30, 1899'
> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>+ "'"
> SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
> SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
>DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
> EXECUTE (@TSQLCmd)
> IF @@ROWCOUNT > 0
> RETURN 3
> END
> END
>
>The Code to call it as follows:
>
>Dim cmd As New ADODB.Command
>Dim rs As New ADODB.Recordset
>Dim Gender As String
>Dim CountyCode As Long
>
>If rs.State = adStateOpen Then rs.Close
>
>If optSex(0).Value = True Then
> Gender = "M"
>End If
>If optSex(1).Value = True Then
> Gender = "F"
>End If
>
>If cboCounty.ListIndex = -1 Then
> CountyCode = 0
>Else
> CountyCode = cboCounty.ItemData(cboCounty.ListIndex)
>End If
>
>If IsDate(txtDOD) Then
> txtDOD = Format(txtDOD, "dd/mm/yyyy")
>Else
> txtDOD = Format(0, "dd/mm/yyyy")
>End If
>
>With cmd
> .ActiveConnection = ConnectString
> .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,
>, 0)
> .Parameters.Append .CreateParameter("@Firstname", adVarChar, adParamInput,
>30, txtForeName)
> .Parameters.Append .CreateParameter("@Surname", adVarChar, adParamInput,
>30, txtSurName)
> .Parameters.Append .CreateParameter("@Gender", adChar, adParamInput, 1,
>Gender)
> .Parameters.Append .CreateParameter("@County", adInteger, adParamInput,
>, CountyCode)
> .Parameters.Append .CreateParameter("@DOD", adDate, adParamInput, , txtDOD)
> .CommandType = adCmdStoredProc
> .CommandText = "Searchit"
> Set rs = .Execute
>
> Debug.Print "Return = " & .Parameters("@RETURN_VALUE")
>
>End With
>
>
>If rs.EOF And rs.BOF Then
> MsgBox "Nothing found"
> Exit Sub
>End If
>
>Do While Not rs.EOF
> Debug.Print rs.Fields(0).Value
> Debug.Print rs.Fields(1).Value
> Debug.Print rs.Fields(2).Value
> Debug.Print rs.Fields(3).Value
> Debug.Print rs.Fields(4).Value
> rs.MoveNext
>Loop
>
>
>Please has anybody any idea what I have done wrong, many thanks
>
> Paul
>
>
>
>
you may find that including the line 'set nocount on' at the beginning of
your stored procedure will cure your problem. this stops sql server reporting
the number of rows affected by each sql statement (without affecting @@rowcount),
which seems to confuse the sqloledb provider (i think the odbc provider -
msdasql - is immune to this). to access the command object's parameters,
you must first consume/move off the last recordset (the only one in this
case), so the order of your code will have to change:

1. execute command to create recordset
2. scroll through the recordset as necessary
3. once finished with it, move off it completely (by calling rs.nextrecordset
- this will also close the recordset)
4. finally, you should now be able to access the command's parameter collection.

jy

ps apologies for the excessive use of parentheses :-)

[Paul Moss]

jy,
many thanks, yes that has solved the problem, I didn't appreciate about
moving on to the next recordset. Thanks again

Paul



"jy" <jy_mead@hotmail.com> wrote:
>
>"Paul Moss" <pbmoss@hotmail.com> wrote:
>>
>>
>>Hello,
>> I am trying to call a Stored Procedure in SQL7, from a VB6 program using
>>ADO 2.5, which does the following: It build up a dynamic query from passed
>>parameters and then executes it using the EXECUTE (querystring) syntax.
>It
>>first tries an exact match on the names, then if not successful tries to
>>match using the LIKE operator, and then if not successful tries a match
>>on the Soundex of the names. This works in all three cases when executed
>>in Query Analyser but when I try to call it from VB6 code, it only works
>>for the exact match and doesn't return anything from the results of the
>LIKE
>>or Soundex matches. Furthermore the RETURN VALUE is always returned empty.
>>
>>The SP is as follows:
>>
>>CREATE Proc Searchit (@Firstname Varchar(30), @Surname Varchar(30), @Gender
>>Char(1),
>> @County Int, @DOD Datetime ) AS
>>
>>Declare @TSQLCmd Varchar(1024)
>>Declare @SearchArg Varchar(1024)
>>
>>SET @SearchArg = "WHERE"
>>
>>IF LEN(@Firstname) > 0
>> SET @SearchArg = @SearchArg + " AND Firstname = '" + @Firstname + "'"
>>IF LEN(@Surname) > 0
>> SET @SearchArg = @SearchArg + " AND Surname = '" + @Surname + "'"
>>IF LEN(@Gender) > 0
>> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
>>IF @County > 0
>> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = " + CAST(@County
>>as varchar(4)))
>>IF @DOD > 'Dec 30, 1899'
>> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>>+ "'"
>>SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
>>SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth, DateofDeath,
>>AgeatDeath FROM Deceased " + @SearchArg
>>EXECUTE (@TSQLCmd)
>>IF @@ROWCOUNT > 0
>> RETURN 1
>>ELSE
>> BEGIN
>> SET @SearchArg = "WHERE"
>> IF LEN(@Firstname) > 0
>> SET @SearchArg = @SearchArg + " AND Firstname LIKE '" + @Firstname
>>+ "%'"
>> IF LEN(@Surname) > 0
>> SET @SearchArg = @SearchArg + " AND Surname LIKE '" + @Surname
+
>>"%'"
>> IF LEN(@Gender) > 0
>> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender + "'"
>> IF @County > 0
>> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence = "
>+
>>CAST(@County as varchar(4)))
>> IF @DOD > 'Dec 30, 1899'
>> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>>+ "'"
>> SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
>> SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
>>DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
>> EXECUTE (@TSQLCmd)
>> IF @@ROWCOUNT > 0
>> RETURN 2
>> ELSE
>> BEGIN
>> SET @SearchArg = "WHERE"
>> IF LEN(@Firstname) > 0
>> SET @SearchArg = @SearchArg + " AND Firstname_Soundex = SOUNDEX('"
>>+ @Firstname + "')"
>> IF LEN(@Surname) > 0
>> SET @SearchArg = @SearchArg + " AND Surname_Soundex = SOUNDEX('"
>>+ @Surname + "')"
>> IF LEN(@Gender) > 0
>> SET @SearchArg = @SearchArg + " AND Gender = '" + @Gender
+
>>"'"
>> IF @County > 0
>> SET @SearchArg = @SearchArg + RTRIM(" AND CountyofResidence
>>= " + CAST(@County as varchar(4)))
>> IF @DOD > 'Dec 30, 1899'
>> SET @SearchArg = @SearchArg + " AND DateofDeath = '" + CONVERT(Char(12),@DOD,103)
>>+ "'"
>> SET @SearchArg = REPLACE(@SearchArg, "WHERE AND", "WHERE")
>> SET @TSQLCmd = "SELECT Firstname, Middlename, Surname, DateofBirth,
>>DateofDeath, AgeatDeath FROM Deceased " + @SearchArg
>> EXECUTE (@TSQLCmd)
>> IF @@ROWCOUNT > 0
>> RETURN 3
>> END
>> END
>>
>>The Code to call it as follows:
>>
>>Dim cmd As New ADODB.Command
>>Dim rs As New ADODB.Recordset
>>Dim Gender As String
>>Dim CountyCode As Long
>>
>>If rs.State = adStateOpen Then rs.Close
>>
>>If optSex(0).Value = True Then
>> Gender = "M"
>>End If
>>If optSex(1).Value = True Then
>> Gender = "F"
>>End If
>>
>>If cboCounty.ListIndex = -1 Then
>> CountyCode = 0
>>Else
>> CountyCode = cboCounty.ItemData(cboCounty.ListIndex)
>>End If
>>
>>If IsDate(txtDOD) Then
>> txtDOD = Format(txtDOD, "dd/mm/yyyy")
>>Else
>> txtDOD = Format(0, "dd/mm/yyyy")
>>End If
>>
>>With cmd
>> .ActiveConnection = ConnectString
>> .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,
>>, 0)
>> .Parameters.Append .CreateParameter("@Firstname", adVarChar, adParamInput,
>>30, txtForeName)
>> .Parameters.Append .CreateParameter("@Surname", adVarChar, adParamInput,
>>30, txtSurName)
>> .Parameters.Append .CreateParameter("@Gender", adChar, adParamInput,
1,
>>Gender)
>> .Parameters.Append .CreateParameter("@County", adInteger, adParamInput,
>>, CountyCode)
>> .Parameters.Append .CreateParameter("@DOD", adDate, adParamInput, , txtDOD)
>> .CommandType = adCmdStoredProc
>> .CommandText = "Searchit"
>> Set rs = .Execute
>>
>> Debug.Print "Return = " & .Parameters("@RETURN_VALUE")
>>
>>End With
>>
>>
>>If rs.EOF And rs.BOF Then
>> MsgBox "Nothing found"
>> Exit Sub
>>End If
>>
>>Do While Not rs.EOF
>> Debug.Print rs.Fields(0).Value
>> Debug.Print rs.Fields(1).Value
>> Debug.Print rs.Fields(2).Value
>> Debug.Print rs.Fields(3).Value
>> Debug.Print rs.Fields(4).Value
>> rs.MoveNext
>>Loop
>>
>>
>>Please has anybody any idea what I have done wrong, many thanks
>>
>> Paul
>>
>>
>>
>>
>you may find that including the line 'set nocount on' at the beginning of
>your stored procedure will cure your problem. this stops sql server reporting
>the number of rows affected by each sql statement (without affecting @@rowcount),
>which seems to confuse the sqloledb provider (i think the odbc provider
-
>msdasql - is immune to this). to access the command object's parameters,
>you must first consume/move off the last recordset (the only one in this
>case), so the order of your code will have to change:
>
>1. execute command to create recordset
>2. scroll through the recordset as necessary
>3. once finished with it, move off it completely (by calling rs.nextrecordset
>- this will also close the recordset)
>4. finally, you should now be able to access the command's parameter collection.
>
>jy
>
>ps apologies for the excessive use of parentheses :-)