No results from SQL query

[dlhall]

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.

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..

[Shaggy Hiker]

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.