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.

Help! INSERT into Access DB is getting corrupted

Discussion in 'C#' started by psunavy03, Mar 21, 2008.

  1. #1
    Greetings-
    This is my first post here and I'm trying to get a handle on this whole ASP thing. I've been tasked with building a DB to house training records at my squadron, and building a Web interface for it on our intranet. Luckily for me, I discovered the concept of SQL injection and realized I was building the whole thing off dynamic SQL and unsanitized user inputs. So I went back to the drawing board and therein lies my problem.

    I wrote a couple practice scripts using the ADO Command object to conduct my queries instead of concatenating the user form inputs with SQL in the ASP code. I got a SELECT statement to work and am rewriting that code, but the INSERT statement is driving me nuts. I'm working in MS Access 2002 and IIS 7.0. The table I am accessing consists of
    SSN - Long Integer, Primary Key
    L_NAME - Text(30)
    F_NAME - Text(20)
    M_INIT - Text(1)
    C_SIGN - Text(20)
    RANK - Text(6)
    PILOT - Yes/No
    CAT - Byte
    CLASS - Text(4).
    SEMrush
    The ASP Code is as follows:
     <%
    	Dim ssn
    	Dim last
    	Dim first
    	Dim middle
    	Dim rank
    	Dim callsign
    	Dim classnum
    	Dim pilot
    	Dim studcat
    	ssn=111111111
    	last="O'Brien"
    	first="Harold"
    	middle="T"
    	rank="LT"
    	callsign="Rip"
    	classnum="0408"
    	pilot=True
    	studcat=1
    	Set cmd = Server.CreateObject ("ADODB.Command")
    	set conn = Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "[database path here]"
    	cmd.CommandText = "Query1"
    	cmd.CommandType = 4
    	cmd.Parameters.Append cmd.CreateParameter("@ssn",3,1,ssn)
    	cmd.Parameters("@ssn") = ssn
    	cmd.Parameters.Append cmd.CreateParameter("@lname",202,1,Len(last))
    	cmd.Parameters("@lname") = last
    	cmd.Parameters.Append cmd.CreateParameter("@fname",202,1,Len(first))
    	cmd.Parameters("@fname") = first
    	cmd.Parameters.Append cmd.CreateParameter("@minit",129,1,1)
    	cmd.Parameters("@minit") = middle
    	cmd.Parameters.Append cmd.CreateParameter("@rank",202,1,Len(rank))
    	cmd.Parameters("@rank") = rank
    	cmd.Parameters.Append cmd.CreateParameter("@csign",202,1,Len(callsign))
    	cmd.Parameters("@csign") = callsign
    	cmd.Parameters.Append cmd.CreateParameter("@cnum",202,1,Len(classnum))
    	cmd.Parameters("@cnum") = classnum
    	cmd.Parameters.Append cmd.CreateParameter("@pilot",11,1)
    	cmd.Parameters("@pilot") = pilot
    	cmd.Parameters.Append cmd.CreateParameter("@studcat",17,1)
    	cmd.Parameters("@studcat") = studcat
    	Set cmd.ActiveConnection = conn
    	cmd.Execute
    	Set Rs=Nothing
    	Set cmd = Nothing
    	Set conn = Nothing
        response.end 
    %>
    Code (markup):
    Everything works fine except the CAT field. For some reason, it is getting corrupted and inserts a value of 255 instead of 1. I had previously had it as a text field with roman numerals, and then it had gotten corrupted into a value of -1 somehow. All the other fields insert fine.

    If I comment out the cmd.Execute and put in Response.Write(cmd.Parameters("@studcat")), it returns a value of 1, just as it should. Also, I prevously had the cmd.CommandText listing the actual SQL query. I changed that to an Append Query "Query1" in the actual MS Access DB with the syntax
    INSERT INTO STUDENT ( SSN, L_NAME, F_NAME, M_INIT, RANK, C_SIGN, CLASS, CAT )
    VALUES ([@ssn], [@lname], [@fname], [@minit], [@rank], [@csign], [@classnum], [@studcat]);
    
    Code (markup):
    Both are corrupted the same way. I'm stumped and would appreciate any insight on this issue. I know Access has issues regarding scalability and large numbers of simultaneous users, but this is one dude making one query . . .
     
    psunavy03, Mar 21, 2008 IP
    SEMrush
  2. nubsii

    nubsii Peon

    Messages:
    36
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I'm not really sure why thats happening. Did you say the -1 appeared back before you had it as type byte? cause a -1 in a byte field would be very strange.

    Here's something you could try in hopes of being slightly more explicit:


    Dim studcat As Byte

    cmd.Parameters("@studcat").Value = studcat

    Also, can you repro[duce] the bug in a more isolated scenario? 1 column table perhaps?

    I dont know much about access nor vb for that matter (eveything i do is C#+mssql), but I believe that it has two bits of odd behavior relating to the byte data type (and it varies by version) both occur when the byte has overflowed... in one scenario it'll subtract 256 from the number.. and in the other scenario it just sets the value to 255.

    Goodluck!
     
    nubsii, Apr 3, 2008 IP