September 5, 2001 at 9:16 am
I have established an ADO connection to a SQL Server database. However, I'm having problems selecting a record based on a dual primary key that uniquely identifies the record. Using the below code I can find a record based on either criteria individually, but upon concatination, I recieve an error 13 indicating a Type Mismatch. Any ideas why?
objRSData.MoveFirst
strCriteria = "Case_Number_IN= " & m_strCaseNumber
strCriteria = strCriteria And "Followup_Number_IN = " & m_strFolNo'
objRSData.Find strCriteria
September 5, 2001 at 9:58 am
BE sure you have the proper spacing included in your strings. Looks like you are missing a space. Try a response.write(strCriteria) and see what is returned. Be sure this is valid SQL in QA.
Steve Jones
September 5, 2001 at 3:24 pm
Steve is on the money here (not that he ever isn't!), definitely you want to response.write or debug.print (in VB) to see the SQL. Looks to me like you're missing a single quote or two. All char/varchar's need to have be surrounded with single quotes - and remember that if your string can contain a single quote you have to deal with that as well.
Slightly off topic, using the find method is not a performance winner - depending on what you're trying to accomplish it's often better to just requery.
Andy
September 5, 2001 at 4:36 pm
quote:
Steve is on the money here (not that he ever isn't!), definitely you want to response.write or debug.print (in VB) to see the SQL. Looks to me like you're missing a single quote or two. All char/varchar's need to have be surrounded with single quotes - and remember that if your string can contain a single quote you have to deal with that as well.Slightly off topic, using the find method is not a performance winner - depending on what you're trying to accomplish it's often better to just requery.
Andy
September 5, 2001 at 4:47 pm
I appreciate the help, however, I'm obviously not at the experience level you guys are. I'm writing this VB code for SQL Server database. I've gotten a little further using debug.print, but I'm not there yet. I realize that I need a couple more single quotes, but I don't see where. I guess I need a little more specific guidance. Thus, I've progressed to this point.
objRSData.MoveFirst
strCriteria = "Case_Number_IN = " & m_strCaseNumber
strCriteria = strCriteria & " And " & "Followup_Number_IN= " & m_strFolNo
objRSData.Find strCriteria
Using debug.print I can see that my strCriteria = Case_Number_IN = 123456789 And Followup_Number_IN= 0
However, I still error out when I go to objRSData.Find strCriteria. This time I am getting "3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".
My guess is that I'm not using a string literal correctly or am not using the "AND" connect correctly. Do you have an opinion? Thanks again for the guidance.
September 5, 2001 at 5:39 pm
Dont sweat the experience. Trust me, aint NOBODY on here knows it all! We're glad to help any way we can.
The right answer depends on your data types, so I did a couple different examples:
Dim strCriteria As String
Dim m_StrCaseNumber As String
Dim m_strFolNo As String
Dim m_StrCaseNumberInt As Integer
Dim m_strFolNoInt As Integer
'example one
m_StrCaseNumber = "23"
m_strFolNo = "17"
strCriteria = "Case_Number_IN = '" & m_StrCaseNumber & "'"
strCriteria = strCriteria & " And Followup_Number_IN = '" & m_strFolNo & "'"
Debug.Print "this example assumes both are strings"
Debug.Print strCriteria
'example two
m_StrCaseNumberInt = 46
m_strFolNoInt = 34
strCriteria = "Case_Number_IN = " & m_StrCaseNumberInt
strCriteria = strCriteria & " And Followup_Number_IN = " & m_strFolNoInt
Debug.Print "this example assumes both are integers"
Debug.Print strCriteria
'example three
m_StrCaseNumber = "Andy's Test"
m_strFolNoInt = 51
strCriteria = "Case_Number_IN = '" & Replace$(m_StrCaseNumber, "'", "''") & "'"
strCriteria = strCriteria & " And Followup_Number_IN = " & m_strFolNoInt
Debug.Print "this example assumes one string and one integer, string has embedded single quote"
Debug.Print strCriteria
And here are the results:
this example assumes both are strings
Case_Number_IN = '23' And Followup_Number_IN = '17'
this example assumes both are integers
Case_Number_IN = 46 And Followup_Number_IN = 34
this example assumes one string and one integer, string has embedded single quote
Case_Number_IN = 'Andy''s Test' And Followup_Number_IN = 51
See if that helps - if not, let us know!
Andy
September 6, 2001 at 2:12 pm
Thanks again for the help. I finally got exactly what I thought I wanted and needed. However, it was still not working. With further digging I found out that the Find Method does not support multi-column searches. Only a single-column name may be specified in the criteria. Thus, I'm currently looking at the Seek Method. Got to love it. Thanks again.
September 6, 2001 at 4:06 pm
I never use it, so didnt even think to check. For those interested, you can see details about this at the following:
http://support.microsoft.com/support/kb/articles/Q195/2/22.ASP
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply