Data Type Mismatch in criteria expression (not the usual)

[austinpickerinh]

Hi, I am Creating a room booking system for a university using a webpage as a front end for the student to rregister for the accommodation. The problem i am having is very strange. On line 96 of the code is the line
Dconn.Execute addOccupancySQL which refers to the sql
addOccupancySQL = "INSERT INTO tblOccupancy(roomID, studentID) VALUES ('" & introomID & "','" & intstID & "')" The database relationships look like this
http://img503.imageshack.us/img503/4429/28526802nx5.th.pngheres the code:
-- Declare variables
Dim DataConnection, cmdDC, RecordSet, SQL, strError
Dim strfName, strsName, stradd1, stradd2, strtown, strcity, strpCode, strtelNo, stremail, intstID, int1styr, intOS, intDWP, intMob, intWCGF
'-- Get data from the form fields
intstID = Request.Form("txtstID")
strfName = Request.Form("txtfName")
strsName = Request.Form("txtsurname")
stradd1 = Request.Form("txtadd1")
stradd2 = Request.Form("txtadd2")
strtown = Request.Form("txttown")
strcity = Request.Form("txtcity")
strpCode = Request.Form("txtpCode")
strtelNo = Request.Form("txttelNo")
stremail = Request.Form("txtemail")
strgender = Request.Form("radiogroupgender")
int1styr = Request.Form("radiogroup1stYr")
intOS = Request.Form("radiogroupOverseas")
intDWP = Request.Form("radiogroupDWP")
intWCGF = Request.Form("radiogroupWCGF")
intMob = Request.Form("radiogroupMob")
'-- Create object and open database
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\sites\content\a\u\s\austinpickering\db\accomDB1.mdb;" & _
"Persist Security Info=False"
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

SQL = "SELECT tblapplicant.* FROM tblapplicant"

cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")

'-- Cursor Type, Lock Type

'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4


RecordSet.Open cmdDC, , 0, 3



Dim Dconn, addApplicantSQL, addCircSQL, addtoWaitlistSQL, introomID, strGFAccom
Set Dconn = Server.CreateObject("ADODB.Connection")
Dconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\sites\content\a\u\s\austinpickering\db\accomDB1.mdb;" & _
"Persist Security Info=False"


selectroomIDGFSQL =" SELECT tblroom.roomID FROM tblroom WHERE maxOccupants > currentOccupants AND gFloor = 1"

addApplicantSQL = "INSERT INTO tblapplicant(studentID, gender, firstName, surname, address1, address2, town, city, postCode, telNo, email) VALUES ('" & intstID & "','" & strgender & "','" & strfName & "','" & strsName & "','" & stradd1 & "','" & strsadd2 & "','" & strtown & "','" & strcity & "','" & strpCode & "','" & strtelNo & "','" & stremail & "')"

addCircSQL = "INSERT INTO tblCirc(studentID, disabled, wheelchair, firstYear, overseas, medical) VALUES ('" & intstID & "','" & intDWP & "','" & intWCGF & "','" & int1styr & "','" & intOS & "','" & intMob & "')"

addtoWaitlistSQL = "INSERT INTO tblWaitlist(studentID) VALUES ('" & intstID & "')"

addOccupancySQL = "INSERT INTO tblOccupancy(roomID, studentID) VALUES ('" & introomID & "','" & intstID & "')"



'add student to tbl applicant
Dconn.Execute addApplicantSQL
RecordSet.close
'if student is disabled or needs gfloor room then find a roomid with gfloor access and select that id

if intWCGF or intDWP = 1 then
Dconn.Execute addCircSQL
cmdDC.CommandText = selectroomIDGFSQL
RecordSet.Open cmdDC, , 0, 3
introomID = RecordSet.Fields("roomID")
intstID = CLng(intstID)
introomID = CLng(introomID)
Response.Write" RoomID= " & introomID & " "
Response.Write"StudentID= " & intstID & " "
Dconn.Execute addOccupancySQL
'Dconn.Execute addOccupancySQL


if int1styr or intmob or intOS = 1 then
'add values in radio buttons to tblCirc
Dconn.Execute addCircSQL
'Dconn.Execute addOccupancySQL

else
'add the student to the waitlist
Dconn.Execute addtoWaitlistSQL

Dconn.Close

Set Dconn = Nothing
end if
end if
'Forward the user to page to notify of sucess
Response.write "Your Application has been added to the database. You may be required to submit additional supporting documents, in which case you will be contacted by a member of staff"


'-- Close all connections

Set RecordSet = Nothing

DataConnection.Close
Set DataConnection = Nothing




%>

The StudentID will enter into tbl occupancy on its own so the problem is with the roomID field. I have checked that the datatype is a number in both tables it is found in the database, and i have also made sure the value im entering is numeric by using response.write to show the 2 values i am enterting and both are as they should be... I hope sombody can help me out with this 1? Thanks in advance =)

[austinpickerinh]

Heres how the relationships look

[entwickler]

there is no value assigned to introomID(at least in the pieace of code you have pasted) so I guess it is considered like variant type and this is causing the error.

[SibTiger33]

anyway in which i can see a bigger version of your relationships

i dont see anywhere in the code where the value for introomID is created