I'm having to do a small application in VB because the vendor of an ActiveX/DLL can't find a way to make it work in VFP. Any way it is just a TCP/IP listner that receives an HL7 message then parses it and then does a lookup against our patient table in a SQL database on the mpiNumber field. If it finds a match then I will check to see if there are changes to update the patient record, otherwise I need to insert the record into our system. I've hardly ever worked with VB, so this is all new to me. I have posted the code I am having a problem with below. I am using a command connection and a recordset. I have a couple messages boxes to show me what is happening. For my test, I am having a message sent to me that has the mpinumber=X741. There is a record in the patient table for X741. After the do the Set rs=cmd.Execute() command, I am checking the number of records using rs.RecordCount. This is returning -1??? I thought I could use that to get the number of records returned, maybe not. Anyway, I'm sure this is ugly to any of you VB guys, but I could sure use some help. Thanks in advance
cmd.ActiveConnection = "Provider=MSDASQL.1;UID=ctore;pwd=cqi$$;Data Source=ctore"
Dim rs As New ADODB.Recordset
cmd.CommandText = "Select * from patients where MPINumber = ?"
Set rs = cmd.Execute(, sLookUpID, adCmdText)
iRecords = rs.RecordCount
MsgBox ("Record Count = " & iRecords)
MsgBox ("Search For: " & sLookUpID)
If iRecords > 0 Then
' Check fields to see if they need to be updated
MsgBox ("Old Record")
Else
' Insert Record
MsgBox ("New Record")
End If