Solved

"Type Mismatch" Error Retrieving SQL Record Set

Posted on 2004-03-24
17
890 Views
Last Modified: 2008-02-01
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("BioWeb2Server.Connector")
                        Set BioWebMatch = BioWebServerConnector.Match
                        
                        Set rs = Server.CreateObject("ADODB.Recordset")
                        rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
                        rs.AddNew
                        rs("UserID") = Request.Form("User_Logon")
                        rs("VerifyFeature") = BioWebMatch.Decode(Request.Form("VerifyFeature"))
                        rs("SupportedDevice") = Request.Form("SupportedDevice")
                        rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
                        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=Not4U2C;"
                              Set Conn = Server.CreateObject("ADODB.Connection")
                              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
0
Comment
Question by:dbabbitt
  • 8
  • 3
  • 2
  • +3
17 Comments
 

Author Comment

by:dbabbitt
ID: 10668899
Possibly my problem is that I am getting a Null back or something because of some security access setting being wrong somewhere - but why does it go ahead and add a row to the Biometric_Transactions table?
0
 
LVL 28

Expert Comment

by:sybe
ID: 10668977
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")
0
 
LVL 8

Accepted Solution

by:
william_jwd earned 500 total points
ID: 10669031
You have not assigned rs to the executed sql,

set rs = Conn.Execute(MySql)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 10669042
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=Not4U2C;"
                         Set Conn = Server.CreateObject("ADODB.Connection")
                         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
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10669049
Shouldn't ID be an autonumber fields?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 10669055
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=Not4U2C;"
                         Set Conn = Server.CreateObject("ADODB.Connection")
                         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

0
 
LVL 31

Expert Comment

by:alorentz
ID: 10669293
Also, this will never give you an ID number:

Set rs = Server.CreateObject("ADODB.Recordset")
                    rs.Open "Biometric_Transactions", cn, adOpenKeyset, adLockPessimistic, adCmdTable
                    rs.AddNew
                    rs("UserID") = Request.Form("User_Logon")
                    rs("VerifyFeature") = BioWebMatch.Decode(Request.Form("VerifyFeature"))
                    rs("SupportedDevice") = Request.Form("SupportedDevice")
                    rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
                    rs("Approved") = 0
                    rs.Update
                    ID = rs("ID")  '<---------------------you need to requery for this to work!!! Because I assume ID is autonumber
                    rs.Close

Try this instead:


Set rs = Server.CreateObject("ADODB.Recordset")
                    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.Form("VerifyFeature"))
                    rs("SupportedDevice") = Request.Form("SupportedDevice")
                    rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
                    rs("Approved") = 0
                    rs.Update
                    rs.requery  '<----------Requery run SQL again and get the new ID
                    ID = rs("ID")  '<---------------------you need to requery for this to work!!! Because I assume ID is autonumber
                    rs.Close

That should give you the ID that was just Added.  No more code necessary.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10669397
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.Recordset")
                    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.Form("VerifyFeature"))
                    rs("SupportedDevice") = Request.Form("SupportedDevice")
                    rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
                    rs("Approved") = 0
                    rs.Update
                    rs.requery  '<----------Requery run SQL again and get the new ID
                    rs.movefirst
                    ID = rs("ID")  '<---------------------you need to requery for this to work!!! Because I assume ID is autonumber
                    Response.write "ID: " & ID
                    rs.Close
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 31

Expert Comment

by:alorentz
ID: 10669407
Actually I know that works, I just tested and grabs each new ID, with only that code....good luck!
0
 
LVL 15

Expert Comment

by:deighc
ID: 10669441
@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.Recordset")
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.Form("VerifyFeature"))
rs("SupportedDevice") = Request.Form("SupportedDevice")
rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
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 need to requery for this to work!!! Because I assume ID is autonumber
rs.Close
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10669549
@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?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10669572
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.Recordset")
                    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.Form("VerifyFeature"))
                    rs("SupportedDevice") = Request.Form("SupportedDevice")
                    rs("RemoteAddress") = Request.ServerVariables("REMOTE_ADDR")
                    rs("Approved") = 0
                    rs.Update
                    rs.requery  '<----------Requery run SQL again and get the new ID
                    rs.movefirst
                    ID = rs("ID")  '<---------------------you need to requery for this to work!!! Because I assume ID is autonumber
                    Response.write "ID: " & ID
                    rs.Close
0
 
LVL 15

Expert Comment

by:deighc
ID: 10669609
> 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.
0
 

Author Comment

by:dbabbitt
ID: 10670622
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.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10670890
Heh, heh all that for nothing@
0
 

Author Comment

by:dbabbitt
ID: 10673397
rs.Open "SELECT * FROM Biometric_Transactions ORDER BY ID DESC", cn, 2, 3 gets a read-only error.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10673450
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now