PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Query fails with text box-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] Query fails with text box

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Resolved [RESOLVED] Query fails with text box

    I can run this query using this form no problem
    Code:
      Private Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click
    
            SQL.ExecQuery("SELECT        PartusedID, VendorName, MachineNumber, Description, Quantity, Price, Quantity * Price AS TOTAL " &
                         "FROM            dbo.Parts " &
                          "WHERE MACHINENUMBER =13;")
    
            LOADGRID()
    but if I try to use a textbox to enter the machine number it fails
    Code:
     Private Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click
    
            SQL.ExecQuery("SELECT        PartusedID, VendorName, MachineNumber, Description, Quantity, Price, Quantity * Price AS TOTAL " &
                         "FROM            dbo.Parts " &
                          "WHERE MACHINENUMBER =txtinput.text;")
    
            LOADGRID()
        End Sub
    with this error codeSystem.InvalidCastException: 'Conversion from string "ExceQuery Error:
    The multi-par" to type 'Integer' is not valid.'
    inner exception
    System.InvalidCastException: 'Conversion from string "ExceQuery Error:
    The multi-par" to type 'Integer' is not valid.'
    how do I get around this
    Thanks

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,348

    Re: Query fails with text box

    The database system doesn't know anything about the variables and controls etc inside your program, so you need to pass the values instead of the names.

    While you could append the values into the SQL statement, that can cause a lot of problems (not just needing to get the delimiters and formatting right, but much bigger issues too). Instead it is a much better idea to use Parameters, which solve all of those issues and make your code easier to read.

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Query fails with text box

    This appears to work.
    Code:
      Private Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click
    
            SQL.ExecQuery("SELECT        PartusedID, VendorName, MachineNumber, Description, Quantity, Price, Quantity * Price AS TOTAL " &
                         "FROM            dbo.Parts " &
                          "WHERE [MACHINENUMBER] =" + txtInput.Text)
    
            LOADGRID()
        End Sub
    marking as resolved

  4. #4
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,191

    Re: [RESOLVED] Query fails with text box

    The database has no idea what a textbox even is. You should have written the query as
    Code:
    ...
    "WHERE MACHINENUMBER = " & txtinput.text & ";"
    However, that is also strongly discouraged because you are open to SQL injection (you should be using parameters!!!) and you have no idea if the user really typed in a valid number. Unless machine number is NOT always a valid integer, consider using a numeric up-down control instead of a textbox.

    Then you would end up with something that might look somewhat like this:
    VB.Net Code:
    1. Using cmd As SqlCommand = New SqlCommand("SELECT columns FROM yourTable WHERE machineNumber = @MachNum", conn)
    2.     Dim param As SqlParameter = cmd.Parameters.Add("@MachNum", SqlDbType.Int)
    3.     param.Value = myNUD.Value 'where myNUD is a numeric up-down control
    4.     Using dr As SqlDataReader = cmd.ExecuteReader()
    5.         'do what you need with the DataReader
    6.     End Using
    7. End Using

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,716

    Re: [RESOLVED] Query fails with text box

    When you send a query to the server, that's all it knows... that query. It doesn't know jack about your form, your appllication, any variables, or any text boxes on your form. In short, SQL Server (or what ever DB you're using since you're using some obscure library there) doesn't know what the heck txtInput.text is. All it knows is that MachineNumber should be a number.

    Now, what it would know about are parameters... you could set the query up to use a parameter, and then pass in the value from the textbox to the parameter...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width