Item Searching in Database in not there

[timomike]

Hi! pls help me.
i already have my project. its on VB.net and MS SQL server.

but im having a hard time in trapping if the one im searching in the databse is not there..

im searching a primary key but if not there im having an error.

pls help me. heres my code for viewing.



Public Function GetByRefNum(ByVal RefNum As Integer) As DataSet
'' This is where we are inserted all the values
Dim connectionString As String = "Data Source=MLTimo;Initial Catalog=OSDSdb;Persist Security Info=True;User ID=sa; PWD=password"
Dim myConnection As SqlConnection = New SqlConnection(connectionString)

Dim myDataSet As New DataSet
Dim myDataAdapter As SqlDataAdapter

Try

Dim cmd As New SqlCommand
'Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM tblRequest Where RefNumber = " + RefNum.ToString()
cmd.CommandType = CommandType.Text
cmd.Connection = myConnection

myConnection.Open()
myDataAdapter = New SqlDataAdapter(cmd)
'' fill the dataset
myDataAdapter.Fill(myDataSet)

Catch ex As Exception
Throw ex
Finally
'' close the connection
myConnection.Close()
End Try

Return myDataSet

End Function



'the error will come here. "Index out of range something"

Dim ds As DataSet = GetByRefNum(RequestForm.TxBxRefNum.Text)
Me.LblRefNum.Text = ds.Tables(0).Rows(0).Item("RefNumber").ToString()
Me.LblType.Text = ds.Tables(0).Rows(0).Item("Type").ToString()
Me.LblRequestor.Text = ds.Tables(0).Rows(0).Item("Requestor").ToString()
Me.LblName.Text = ds.Tables(0).Rows(0).Item("Name").ToString()
Me.LblVenue.Text = ds.Tables(0).Rows(0).Item("Venue").ToString()
Me.LblStat.Text = ds.Tables(0).Rows(0).Item("Status").ToString()