Systax for SQL using variable

  • 19 years ago

    With TEXTBOX as variable to select items from a database using ADO (VB6 Professional SP5)


    Example:
    Dim dbs As Database, rst As Recordset
    Set dbs = OpenDatabase("Northwind.mdb")
      ' Select records from the Employees table where the
      ' Amount > txtBox1.text AND Amount < txtBox2.text
    Set rst = dbs.OpenRecordset("SELECT Amount, " _
    & "FirstName FROM Employees " _
    & "WHERE Amount > txtBox1.text AND Amount < txtBox2.text rst.MoveLast
    dbs.Close


    PLEASE give me the right syntax for such a query


    Thanks very much!

  • 19 years ago

    You Could Try


    Dim dbs As Database, rst As Recordset


    Set dbs = OpenDatabase("Northwind.mdb")


    Set rst = dbs.OpenRecordset("SELECT Amount, FirstName FROM Employees WHERE Amount > " & txtBox1.text & " AND Amount < " & txtBox2.text


    rst.MoveLast
    dbs.Close


  • 19 years ago

    Ignore the last post,


    Example:


    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL as string


    ' Build SQL statememt
    strSQL = "SELECT Amount, FirstName FROM Employees WHERE Amount > " & txtBox1.text & " AND Amount < " & txtBox2


    ' Open Database
    Set dbs = OpenDatabase("Northwind.mdb")


    ' Open Recordset with passed through SQL string
    Set rst = dbs.OpenRecordset(strSQL)


    'your code here



    ' Close Recordset
    rst.Close


    ' Close Database
    dbs.Close

  • 19 years ago

    Sorry Andy Watt:


    At the line: Set rst = dbs.OpenRecordset(strSQL) I have the following error message: run time error '3061': Too few parameters. Expected 1.


    Do you know what is wrong?


    Thanks a lot for your help!

  • 19 years ago

    Should be


    Dim dbs As Database, rst As Recordset
    Set dbs = OpenDatabase("Northwind.mdb")
     ' Select records from the Employees table where the
     ' Amount > txtBox1.text AND Amount < txtBox2.text
    Set rst = dbs.OpenRecordset("SELECT Amount, " _
    & "FirstName FROM Employees " _
    & "WHERE Amount > '" & txtBox1.text & "' AND Amount < '" & txtBox2.text & "'"
    rst.MoveLast
    dbs.Close


    should include the pass in parameter separated from SQL statement.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Better train people and risk they leave – than do nothing and risk they stay.” - Anonymous