Produce error message if data searched for does not exist in a database

[KRJ1993]

Hi there, i have some code where the user enters the name of a customer and it displays their details. but if they enter a name that doesnt exist, i.e spell it wrong the whole program crashes. is there any way i can get a error message to show of they enter the wrong details, instead of the system crashing and having to restart it??
thanks!

[KRJ1993]

i should add that im using a database with all the customer details stored and being retrieved from there.

[kaliman79912]

Hello KRJ, I think you should avoid Errors all around. What you want is not an error message but a validation message. The difference is that when you display errors it makes your application look a bit unstable, whereas a validation message points to the user.
Anyway, It is very difficult for us to propose a solution if we do not know how you are accessing the data.

Put some code where you are crashing or where you are getting the input from the user or where you are fetching the data from the database. (or all of them). And remember to put the code between code tags.

[Shaggy Hiker]

You can certainly get an error message, but you probably shouldn't. It's always better to check whether an exception will occur rather than trapping them after they occur. This appears to be such a case, but we'd have to see how you are doing the search.

[KRJ1993]

'code that connects to the database
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "Data Source = C:\TheGarageDatabase.mdb"

con.ConnectionString = dbProvider & dbSource

con.Open()
'sql code that stores only the values needed for the invoice into a dataset
sql = "SELECT FullName, Registration, BookingType, BookingDate FROM Bookings WHERE Fullname = '" & txtName.Text & "'"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "TheGarageDatabase")
'fills the textboxes with the data from the database


txtName.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(0)
txtReg.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(1)
txtType.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(2)
txtDate.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(3)
con.Close()


maxRows = ds.Tables("TheGarageDatabase").Rows.Count
MsgBox(maxRows)
inc = 0

heres my code.

[techgnome]

OK... so before you do anything with the data, see if there is data... IF there aren't anyt rows THEN notify the user that there was no data, ELSE use the data. (END IF)

-tg

[KRJ1993]

Ohh i see, so would i use sql code to do that or use VB code, im new to the sql code so its confusing me slightly.
Thanks for your help though!

[CoachBarker]

something like

IF maxRows = 0 THEN
MessageBox.Show("There are no records that match those criteria.")
else 
txtName.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(0)
txtReg.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(1)
txtType.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(2)
txtDate.Text = ds.Tables("TheGarageDatabase").Rows(0).Item(3)
END IF

[robertx]

CoachBarker is spot on except for the typo with the "End If". If there are no rows returned from the query then it will crash when you try and reference "Rows(0)".

Another potential problem is that you are using inline SQL with single quotation marks. You are going to run into problems with names that contain a single quotation mark like "O'Brien". You must escape the single quotation marks when building your SQL statement.

Pass the username and password each to this function:

 Public Function EscapeQuotes(ByVal str As String) As String
	  Return str.Replace("'", "''")
  End Function

I'd also remove any trailing spaces with the Trim function or you risk returning zero rows when there are in fact matching rows.

[CoachBarker]

just typed it off the top of my head.