Dynamic Query in SQL7 called from VB6/ADO


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Dynamic Query in SQL7 called from VB6/ADO

  1. #1
    Paul Moss Guest

    Dynamic Query in SQL7 called from VB6/ADO



    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





  2. #2
    jy Guest

    Re: Dynamic Query in SQL7 called from VB6/ADO


    "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 :-)

  3. #3
    Paul Moss Guest

    Re: Dynamic Query in SQL7 called from VB6/ADO


    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 :-)



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center