sql problem with auto number field [RESOLVED]

[Supremus]

This is the start of my sql statement, but I am having problems because the first field in the db is an autonumber and I don't know how to bypass it in the sql (2nd line). I have tried just a , but that doesn't work.



SQL="INSERT INTO Players VALUES(" & _
"," & _
"'" & Request.Form("username") & "'," & _

[juliemac]

Dont refrence the field as you are inserting data, access will fill in that field for you. Try this:
<code>
Sql="insert into Players (username, password {, add other column names})values("'" & Request.Form("username") & "'," & )
</code>


Good luck, Julie (dislocated sydneysider)

[Supremus]

Thanks Julie,

I was hoping there was a way without listing all 30+ fields by just using the value.

If I put a 0 in the first field I get ...
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

If I just use a comma like this
SQL="INSERT INTO KQPlayers VALUES(" & _
"," & _
"'" & Request.Form("username") & "'," & _

to bypass it, I get
Syntax error in INSERT INTO statement.

[Supremus]

If I just use
Sql="insert into Players (username) values('" & Request.Form("username") & "'" )
I get

Error: Missing Fields
You must enter all fields to proceed.

[si_the_geek]

What happens if you specify all fields except the autonumber one? (it should be fine then)

Even tho it takes more text, specifying all fields is a good idea as it protects you against any table changes..

• If the fields you use still exist but the order has changed, it will still work as you originally inteded;
• if other fields have been added (after or between the existing fields) it will still work as intended;
• if fields you use have been removed, you will get an error from the statement, rather than accidentally putting data into the wrong fields.

[Supremus]

Thanks Si, I'll try that. I thought you were able just to list the ones you wanted changed.

Maybe it's the Insert Into command causeing my problems. Maybe it would be simpler just to add a new record?

[Supremus]

I'm getting frustrated again. I have removed the autonumber to make things simpler. I'll add it later when I can solve this problem ...

The following code works fine and adds the records and the data looks right ...

SQL="INSERT INTO KQPlayers VALUES(" & _
"'" & Request.Form("username") & "'," & _
"'" & RandPassword & "'," & _
"'" & Request.Form("email") & "'," & _
(--snip--)

But as soon as I try to add this after KQPlayers and before Values, I get a syntax error. The datafield names are correct as I cut/pasted them.

(Username, Password, Email, CurLevel, ABWin, ABLose, ABTie, DBWin, DBLose, DBTie, Exp, Money, DateCreated, DateEdited, NumLogins, LastDefeated, LastDefeatedTime, LastDefeatedBy, LastDefeatedByTime, LastTied, LastTiedTime, Salary, AttacksAccumulated, DailyAttacks, MaxUnits, Allocation, UnreadAnnouncements, TrainerAccumulated, DailyTrainer, Luck, Food, LastTimeOn)

[si_the_geek]

That's a problem with some of the field names you have used - they are reserved words (names of data types, etc), so should not be field names. The ones likely to be causing issues are:

Username, Password, Email, Money, Allocation

Ideally you should change the field names, as they may cause problems in other ways.

If that isn't possible you can use a 'temporary' fix in your SQL statements, which is to put them in square brackets, eg:

([Username], [Password], ...

I thought you were able just to list the ones you wanted changed.

You can (assuming you fill all fields that have been set as required in the table design), but you should always specify the fields, not just assume that the positions within the Values clause will map to the correct fields.

[Supremus]

Thanks a lot Si.