dbabbitt
asked on
"Type Mismatch" Error Retrieving SQL Record Set
Hi Guys!
How do I get rid of this "Type Mismatch" error? The first SQL call is indeed inserting a row, but for some reason it returns a null of empty ID so I have to go fetch the ID with the second SQL call. This second one is giving me the trouble:
Dim BioWebServerConnector
Dim BioWebMatch
Dim MatchResponse
Dim ID
Set BioWebServerConnector = CreateObject("BioWeb2Serve
Set BioWebMatch = BioWebServerConnector.Matc
Set rs = Server.CreateObject("ADODB
rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
ID = rs("ID")
rs.Close
Set rs = Nothing
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
Conn.Execute MySql
'I'm getting a "Type Mismatch" error here
ID = rs("ID")
Conn.Close
Set Conn = Nothing
End If
How do I get rid of this "Type Mismatch" error? Both the server and the client are running on the same laptop, with the IUSR account set up with access to the database.
Thanx
Dave
How do I get rid of this "Type Mismatch" error? The first SQL call is indeed inserting a row, but for some reason it returns a null of empty ID so I have to go fetch the ID with the second SQL call. This second one is giving me the trouble:
Dim BioWebServerConnector
Dim BioWebMatch
Dim MatchResponse
Dim ID
Set BioWebServerConnector = CreateObject("BioWeb2Serve
Set BioWebMatch = BioWebServerConnector.Matc
Set rs = Server.CreateObject("ADODB
rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
ID = rs("ID")
rs.Close
Set rs = Nothing
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
Conn.Execute MySql
'I'm getting a "Type Mismatch" error here
ID = rs("ID")
Conn.Close
Set Conn = Nothing
End If
How do I get rid of this "Type Mismatch" error? Both the server and the client are running on the same laptop, with the IUSR account set up with access to the database.
Thanx
Dave
Last Comment
I think the error is here (and maybe elsewhere too). You put a String variable in a Numeric field
rs("UserID") = Request.Form("User_Logon")
rs("UserID") = Request.Form("User_Logon")
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
check if rs.eof and rs.bof before retrieving the value
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
Conn.Execute MySql
'I'm getting a "Type Mismatch" error here
if not (rs.eof and rs.bof) then
ID = rs("ID")
end if
Conn.Close
Set Conn = Nothing
End If
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
Conn.Execute MySql
'I'm getting a "Type Mismatch" error here
if not (rs.eof and rs.bof) then
ID = rs("ID")
end if
Conn.Close
Set Conn = Nothing
End If
Shouldn't ID be an autonumber fields?
also you forgot set rs=Conn.Execute MySql
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
set rs=Conn.Execute (MySql)
if not (rs.eof and rs.bof) then
ID = rs("ID")
end if
Conn.Close
Set Conn = Nothing
End If
If (ID = NULL) OR (Len(ID) = 0) Then
Dim ConnectionString, MySql
Dim Conn
ConnectionString = "DSN=BioWeb;UID=sa;PWD=Not
Set Conn = Server.CreateObject("ADODB
Conn.Open ConnectionString
MySql = "SELECT MAX(ID) AS ID FROM Biometric_Transactions"
set rs=Conn.Execute (MySql)
if not (rs.eof and rs.bof) then
ID = rs("ID")
end if
Conn.Close
Set Conn = Nothing
End If
Also, this will never give you an ID number:
Set rs = Server.CreateObject("ADODB
rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
ID = rs("ID") '<---------------------you
rs.Close
Try this instead:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT MAX(ID) AS ID FROM Biometric_Transactions", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
ID = rs("ID") '<---------------------you
rs.Close
That should give you the ID that was just Added. No more code necessary.
Set rs = Server.CreateObject("ADODB
rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
ID = rs("ID") '<---------------------you
rs.Close
Try this instead:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT MAX(ID) AS ID FROM Biometric_Transactions", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
ID = rs("ID") '<---------------------you
rs.Close
That should give you the ID that was just Added. No more code necessary.
Whoops, that won't work....because you don't select all the fields you need to add.
This should work though:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
rs.movefirst
ID = rs("ID") '<---------------------you
Response.write "ID: " & ID
rs.Close
This should work though:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
rs.movefirst
ID = rs("ID") '<---------------------you
Response.write "ID: " & ID
rs.Close
Actually I know that works, I just tested and grabs each new ID, with only that code....good luck!
@alorentz is correct in that you have to requery the database to get the new auto-id value but he has missed one small (but important) step: when you call the requery method the recordset loses it's cursor position. So you have to save the current cursor position to a variable, requery, then set the cursor position back to what it was before the requery:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT MAX(ID) AS ID FROM Biometric_Transactions", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
cursorPos = rs.absolutePosition ' <--- save the cursor position before the requery
rs.requery '<----------Requery run SQL again and get the new ID
rs.absolutePosition = cursorPos '<---- set the cursor position back to where it was before the requery
ID = rs("ID") '<---------------------you
rs.Close
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT MAX(ID) AS ID FROM Biometric_Transactions", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
cursorPos = rs.absolutePosition ' <--- save the cursor position before the requery
rs.requery '<----------Requery run SQL again and get the new ID
rs.absolutePosition = cursorPos '<---- set the cursor position back to where it was before the requery
ID = rs("ID") '<---------------------you
rs.Close
@deighc - you are incorrect...I tested my most recent post and it works fine...noi cursor location necessary because the original query is ordering by DESC.
The query you just posted will not work, because you cannot add records to a query that does not select those records. You Select MAX(ID), but you cannot add fields to that like I have shown, that's why I used a SELECT * query and ORDER BY ID DESC. No cursor necessary, and add new works fine.
See what I mean?
The query you just posted will not work, because you cannot add records to a query that does not select those records. You Select MAX(ID), but you cannot add fields to that like I have shown, that's why I used a SELECT * query and ORDER BY ID DESC. No cursor necessary, and add new works fine.
See what I mean?
TO CLARIFY BECAUSE THERE'S BEEN A LOT OF POSTS:
This is the code that works for me to solve the question you aksed:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
rs.movefirst
ID = rs("ID") '<---------------------you
Response.write "ID: " & ID
rs.Close
This is the code that works for me to solve the question you aksed:
Set rs = Server.CreateObject("ADODB
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 '<--need this SQL for the Requery to work!
rs.AddNew
rs("UserID") = Request.Form("User_Logon")
rs("VerifyFeature") = BioWebMatch.Decode(Request
rs("SupportedDevice") = Request.Form("SupportedDev
rs("RemoteAddress") = Request.ServerVariables("R
rs("Approved") = 0
rs.Update
rs.requery '<----------Requery run SQL again and get the new ID
rs.movefirst
ID = rs("ID") '<---------------------you
Response.write "ID: " & ID
rs.Close
> The query you just posted will not work, because you cannot add records to a query that does not select those records
You're right. I cut-n-pasted the wrong section from your post. You need SELECT *.
As for using ORDER BY ID DESC, well I guess that may work but I wouldn't be 100% happy relying on it. You may have weird behaviour if lots of people of hitting the database at the same time. Even requery itself is a bit dodgy in my opinion (there's no substitute for using a stored procedure that can return the new identity field value).
But if you say it works then I, of course, I believe you.
You're right. I cut-n-pasted the wrong section from your post. You need SELECT *.
As for using ORDER BY ID DESC, well I guess that may work but I wouldn't be 100% happy relying on it. You may have weird behaviour if lots of people of hitting the database at the same time. Even requery itself is a bit dodgy in my opinion (there's no substitute for using a stored procedure that can return the new identity field value).
But if you say it works then I, of course, I believe you.
ASKER
Now I am getting an "ODBC--call failed." error. But it's a couple of lines downstream so I think I will open up a new Q.
Heh, heh all that for nothing@
ASKER
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 gets a read-only error.
Shouldn't...works fine for me all the time!
Then use this:
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, adOpenKeyset, adLockPessimistic, adCmdTable
Then use this:
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, adOpenKeyset, adLockPessimistic, adCmdTable
ASP
Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.
82K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER