Problems with SQL Statement

[james]

Hi, I am using the following code to find a particular client in a database
table and if the record exists
display the data in one of the rows in a textbox. It works just fine as
long as the actual record (in this case CLIENTID) exists, it will then show
the test in the cell that I want in a textbox.
BUT, if the record does not exist, i get an Out of Range Error. How can I
check in my SQL statement if the CLIENTID does not exist to exit the
routine?

' client is from another table and is used to find the additional data I am
looking for
Dim Noteonn As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & mypath() & "\Data\BONDNOTE.mdb;Mode=ReadWrite")

Noteonn.Open()

Notesql = New OleDbCommand("SELECT * FROM Record WHERE [CLIENTID]='" &
client & "'", Noteonn)


Dim Noteda As New OleDbDataAdapter(Notesql)

Dim Noteds As New DataSet("Record")



Noteda.Fill(Noteds, "Record")

DataGrid1.SetDataBinding(Noteds, "Record")

Dim colcount As Integer

txtNote.Text = DataGrid1.Item(0, 5)

Noteonn.Close()



Any suggestons would be appreciated.

james

 

[Ken Tucker [MVP]]

Hi,

Try something like this.


Noteda.Fill(Noteds, "Record")

if Noteds.Tables("Record").rows.count > 0 then

DataGrid1.SetDataBinding(Noteds, "Record")

Dim colcount As Integer

txtNote.Text = DataGrid1.Item(0, 5)

end if

Ken
-----------------------------

"james" <jjames700ReMoVeMe at earthlink dot net> wrote in message
Hi, I am using the following code to find a particular client in a database
table and if the record exists
display the data in one of the rows in a textbox. It works just fine as
long as the actual record (in this case CLIENTID) exists, it will then show
the test in the cell that I want in a textbox.
BUT, if the record does not exist, i get an Out of Range Error. How can I
check in my SQL statement if the CLIENTID does not exist to exit the
routine?

' client is from another table and is used to find the additional data I am
looking for
Dim Noteonn As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & mypath() & "\Data\BONDNOTE.mdb;Mode=ReadWrite")

Noteonn.Open()

Notesql = New OleDbCommand("SELECT * FROM Record WHERE [CLIENTID]='" &
client & "'", Noteonn)


Dim Noteda As New OleDbDataAdapter(Notesql)

Dim Noteds As New DataSet("Record")



Noteda.Fill(Noteds, "Record")

DataGrid1.SetDataBinding(Noteds, "Record")

Dim colcount As Integer

txtNote.Text = DataGrid1.Item(0, 5)

Noteonn.Close()



Any suggestons would be appreciated.

james

 

[james]

Ken, I have been banging my head trying to see what the problem was and you
solved it in a few moments! Thanks a lot. I really appreciate it.

james