searching for value through VB

[rds747]

In VB I want to find whether an Order # exists from the db. But my lack of vb coding with sql experience is preventing me from doing this.

Here's the code:

strSQL = "Select Distinct OhNo, CmImgRec, CmName, CmAddress From OrdHead Join CusMast"
strSQL = strSQL & " On OhCus = CmCust Where OhOrd = '" & txtWorkOrder & "' "
Set Rs = New ADODB.Recordset
Rs.Open strSQL, AppSetting.objConn
If Rs.EOF And txtWorkOrder <> Rs("OhOrd" & "") Then
      MsgBox "Order doesn't exist"
End If

So I want to know whether txtWorkOrder does exist in the db and if it doesn't a msgbox is displayed.

Thanks for the help.

 

[jenlion]

Try this...

Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim ConStr As String
Dim StrSql As String

ConStr = "Provider=SQLOLEDB; Persist Security Info=True; Initial Catalog=" & DBname & ";Password=" & Password & "; User ID=" & Username & "; Data Source=" & ServerName & ";"

Conn.Open ConStr
strSQL = "Select Distinct OhNo, CmImgRec, CmName, CmAddress From OrdHead Join CusMast " & _
         "On OhCus = CmCust Where OhOrd = '" & txtWorkOrder & "' "
rs.Open strSQL, Conn, adOpenDynamic, adLockReadOnly

if rs.eof = true then
  MsgBox "Order doesn't exist"
end if

Hope it help!

 

[rds747]

Thanks for your reply.

If the end of the rs has been reached does that necessarily mean that txtworkorder wasn't found? Doesn't it just mean the query went through each record?

 

[rds747]

Tested it out and no it means that the value wasn't found. But not sure how so.

 

[jenlion]

Your return will be empty.

If you run the full statement on your SQL server (use Query Analyzer):

Select Distinct OhNo, CmImgRec, CmName, CmAddress 
From OrdHead Join CusMast On OhCus = CmCust
Where OhOrd = '1234'

If there is no OhOrd 1234, then you should get 0 rows back. VB will interpret that as EOF -- there are no records in the recordset.

 

[rds747]

Thanks for the help.

 

[jenlion]

You're welcome!