1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Data Type Mismatch in criteria expression (not the usual)

Discussion in 'C#' started by austinpickerinh, Mar 19, 2008.

  1. #1
    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
    [​IMG]heres 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 & "')"
    SEMrush
    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, Mar 19, 2008 IP
    SEMrush
  2. austinpickerinh

    austinpickerinh Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Heres how the relationships look
     

    Attached Files:

    austinpickerinh, Mar 19, 2008 IP
  3. entwickler

    entwickler Member

    Messages:
    75
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    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.
     
    entwickler, Mar 19, 2008 IP
  4. SibTiger33

    SibTiger33 Peon

    Messages:
    203
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
     
    SibTiger33, Mar 19, 2008 IP