Best way to search database?

[liquidspaces]

The user enters the ID in txtSearch.text. I then assigned the value of that entry to searchNum. At this point, I need to search the "ID" column in the database, and return the row where ID = searchNum. The information will then be displayed in the appropriate text boxes on the form.

My SQL statement is as follows:
Select * From Shipping Where ID = searchNum

But Im not exactly sure how to search. Ive tried a few different ideas and get errors each time. Can anybody offer a standard solution?

Thanks,
Kevin

 

[wyrd]

If you are searching for a numeric value you are probably getting errors because your search is attempting to search for a String value in the ID field;

Select * From Shipping Where ID = searchNum

Notice the searchNum. The indicate a String. Try;

SELECT * FROM Shipping WHERE ID = searchNum

If you are still getting an error it is more then likely because a) The table you specified doesnt exist, b) The column ID doesnt exist or c) Aliens came down and changed how SQL works in which case I have no clue.

 

[liquidspaces]

Im still having the same problem. I get the following error:

"An unhandled exception of type System.Data.OleDb.OleDbException occurred in system.data.dll"

Im 500% positive that the table exists, and the column ID exists. Deduction proves that it must be the aliens.

Seriously though, if I hardcode the ID number then it works beautifully. For example, "select * from shipping where ID = 728.
That works just fine.

Am I doing something wrong with my variable declarations? Right now I have:

Dim searchNum as Integer = val(txtSearch.text)

Ive tried it with and without the val, but I like it so Im leaving it in for now.

 

[wyrd]

Okay, well you still need to leave off the for when we do solve the problem otherwise thatll probably cause errors too. As for your code..

Dim searchNum as Integer = val(txtSearch.text)

Val() is old VB6 stuff so Im not sure how thats working with .NET, you should really use..

Dim searchNum as Integer = Integer.Parse(txtSearch.Text)

Try that, if it gives you an error then go into debug mode where it highlights the text for you. Move your mouse over searchNum and hold it there for a second, it should show pop up a little thing that tells you the value of searchNum.

 

[liquidspaces]

searchNum has the correct value. Its now getting caught on the following line:

OleDbDataAdapter1.Fill(DataSet71)

I cant figure out why this is causing an error, as I use the exact same command (with a different dataset and adapter) in another working part of my program.

I dont think this line is actually the problem though, because when I hardcode the value the adapter is filled properly. Must be something else, though Im not sure what.

 

[wyrd]

Paste the code of the section where its hanging up so we can take a look.

BTW whats the exact error for the .Fill?

 

[liquidspaces]

This is where its hanging up:

Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

Dim searchNum As Integer = Integer.Parse(txtSearch.Text)

DataSet71.Clear()
OleDbDataAdapter1.Fill(DataSet71)

End Sub

The exact error is:
"An unhandled exception of type System.Data.OleDb.OleDbException occurred in system.data.dll"

 

[Divil]

SELECT * FROM Shipping WHERE ID = searchNum

This looks wrong to me. Maybe you removed the string quotes for clarity, but you should be doing:

strSQL = "SELECT * FROM Shipping WHERE ID = " & searchNum.ToString()

 

[Robby]

if ID (in the table) is numeric then it should be something like this.

strSQL = "SELECT * FROM Shipping WHERE ID = " & ctype(searchNum,integer)

 

[wyrd]

Oh, yeah heh. I wasnt actually putting that into real code terms (although I should of for clarity), just copy/pasting what he was showing and giving a possible reason for the error. Of course brain dead me didnt think that he could of been doing "SELECT * FROM table WHERE id = num" instead of "SELECT * FROM table WHERE id = " + num

My mistake, Im new at helping people. I guess if I stopped assuming things itd help a bit more. :-\\

 

[liquidspaces]

Ok, this is what I have now:

Dim searchNum As Integer = Integer.Parse(txtSearch.Text)
Dim DataAdapter As New OleDb.OleDbDataAdapter("Select * From Shipping Where BILL_OF_LADING_NUM = " & CType(searchNum, Integer), OleDbConnection1)
Dim DataSet1 As New DataSet()

DataSet1.Clear()
DataAdapter.Fill(DataSet1, "Shipping")

Up until now Id always let VB create the adapter and dataset. Then I used data binding to display the information on the form. Im fairly confident that I created these the right way, but how do I bind the text boxes to specific fields?

My dataset doesnt show up in the DataBindings field of the properties box. Can anybody offer some advice?

 

[liquidspaces]

All right! I got it working, and figured Id post the working product here for future reference.

Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

Dim searchNum As Integer = Integer.Parse(txtSearch.Text)
Dim DataAdapter1 As New OleDb.OleDbDataAdapter("Select * From Shipping Where BILL_OF_LADING_NUM = " & CType(searchNum, Integer), OleDbConnection1)
Dim Command As New OleDb.OleDbCommandBuilder(DataAdapter1)
Dim DataSet99 As New DataSet()

DataAdapter1.FillSchema(DataSet99, SchemaType.Source, "Shipping")
DataSet99.Clear()
DataAdapter1.Fill(DataSet99, "Shipping")

Dim objRow As DataRow
objRow = DataSet99.Tables("Shipping").Rows.Find(searchNum)

txtName.Text = objRow.Item("SHIP_FROM")
txtAddress.Text = objRow.Item("SHIP_FROM_ADDRESS")

End Sub