Help! INSERT into Access DB is getting corrupted

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).

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 . . .

 

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!