liquidspaces Posted December 2, 2002 Posted December 2, 2002 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 I'm not exactly sure how to search. I've tried a few different ideas and get errors each time. Can anybody offer a standard solution? Thanks, Kevin Quote
wyrd Posted December 2, 2002 Posted December 2, 2002 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 doesn't exist, b) The column ID doesn't exist or c) Aliens came down and changed how SQL works in which case I have no clue. Quote Gamer extraordinaire. Programmer wannabe.
liquidspaces Posted December 2, 2002 Author Posted December 2, 2002 I'm still having the same problem. I get the following error: "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" I'm 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) I've tried it with and without the val, but I like it so I'm leaving it in for now. Quote
wyrd Posted December 2, 2002 Posted December 2, 2002 Okay, well you still need to leave off the ' ' for when we do solve the problem otherwise that'll probably cause errors too. :) As for your code.. Dim searchNum as Integer = val(txtSearch.text) Val() is old VB6 stuff so I'm not sure how that's 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. Quote Gamer extraordinaire. Programmer wannabe.
liquidspaces Posted December 2, 2002 Author Posted December 2, 2002 searchNum has the correct value. It's now getting caught on the following line: OleDbDataAdapter1.Fill(DataSet71) I can't 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 don't think this line is actually the problem though, because when I hardcode the value the adapter is filled properly. Must be something else, though I'm not sure what. Quote
wyrd Posted December 2, 2002 Posted December 2, 2002 Paste the code of the section where it's hanging up so we can take a look. BTW what's the exact error for the .Fill? Quote Gamer extraordinaire. Programmer wannabe.
liquidspaces Posted December 2, 2002 Author Posted December 2, 2002 This is where it's 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" Quote
*Gurus* divil Posted December 2, 2002 *Gurus* Posted December 2, 2002 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() Quote MVP, Visual Developer - .NET Now you see why evil will always triumph - because good is dumb. My free .NET Windows Forms Controls and Articles
Moderators Robby Posted December 2, 2002 Moderators Posted December 2, 2002 if ID (in the table) is numeric then it should be something like this. strSQL = "SELECT * FROM Shipping WHERE ID = " & ctype(searchNum,integer) Quote Visit...Bassic Software
wyrd Posted December 2, 2002 Posted December 2, 2002 Oh, yeah heh. I wasn't 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 didn't think that he could of been doing "SELECT * FROM table WHERE id = num" instead of "SELECT * FROM table WHERE id = " + num My mistake, I'm new at helping people. :) I guess if I stopped assuming things it'd help a bit more. :-\\ Quote Gamer extraordinaire. Programmer wannabe.
liquidspaces Posted December 2, 2002 Author Posted December 2, 2002 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 I'd always let VB create the adapter and dataset. Then I used data binding to display the information on the form. I'm fairly confident that I created these the right way, but how do I bind the text boxes to specific fields? My dataset doesn't show up in the DataBindings field of the properties box. Can anybody offer some advice? Quote
liquidspaces Posted December 2, 2002 Author Posted December 2, 2002 (edited) All right! I got it working, and figured I'd 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 Edited December 3, 2002 by liquidspaces Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.