dcsimg
Results 1 to 2 of 2

Thread: No results from SQL query

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    No results from SQL query

    Not sure what I did here, but I am not getting any results from the following code when run in a web environment. If I run the SQL statement in the Management Studio, I do get results, so somewhere in the code it is not getting the right info.

    Code:
    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim connection As New SqlConnection()
            connection.ConnectionString = "server=servername; database=test;User ID=publicweb;Password=userpass;Initial Catalog=DBName"
            Dim Adapter As SqlDataAdapter
            Dim ds As DataTable = New DataTable
            Image1.ImageUrl = "~/hauling/pics/" & TextBox1.Text & ".jpg"
            Adapter = New SqlDataAdapter("Select certno, primaryowner, andor, auxowner, Address, City, State, Zip, dated, equinename, yobirth, type, sex, color, face, special, brandlocation, regno FROM xfer where certno='" + TextBox1.Text + "'", connection)
            Adapter.Fill(ds)
            GridView1.DataSource = ds
            GridView1.DataBind()
            gridview2.datasource = ds
            GridView2.DataBind()
    
        End Sub
    If you need the code for the gridview, let me know. Thanks for any help..

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

    Re: No results from SQL query

    The gridview shouldn't matter. The use of a datatable named ds is pretty novel, but that's not an issue.

    There isn't any exception handling in there, so if an exception is thrown, it would either be caught there, or in a global error handler if you added one. It sounds like you are not getting any errors, it's just that you aren't getting any data. You also make it sound like the exact same code does result in data with the same input when run in VS. If those statements are true, then the problem is likely that you are not connecting to the database that you think you are connecting to. There are ways that could happen, for instance you may have been connecting to a local copy of the DB while in VS, and now are connecting to some other copy.

    Diagnosing a problem in production code can be difficult. What you might do is just add a messagebox at the end of the method that just shows the number of rows in the datatable. That number will be 0, but it would be instructive to see whether or not the messagebox was reached. If it wasn't, then there was an exception and the next question would be why you didn't see it.

    There are two items about the query that you might also consider:

    1) You are looking for a where on a field called certno, but you wrap the argument in single quotes. A field with a name like certno doesn't sound like it would be a text field. If it is a numeric field, then the single quotes shouldn't be there.

    2) In any case, you shouldn't be concatenating in text from a textbox, as that's what leads to SQL injection exploits. Instead, you should be using parameters to supply that information. This won't result in the problem you are having, though, it's just a vulnerability in the code.
    My usual boring signature: Nothing

Tags for this Thread

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