SQLServer primary key problem

Hi there,

I have an wee problem with the INSERT statement . The problem is very unusual. I would like yours help in the same. The problem is, files are running very well on my IIS but when i posted the same on net, it prompts me error messages. This is not an single case. Every database that has autoincrement fileds(identity field) will show same error but works fine on my IIS .The error which it prompt is...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name
or number of supplied values does not match table definition.

/minorities/postrec.asp, line 34

In brief, i would like to put some lights on the code and database. It's a very simple program in which user fill up their events details in the form and the vaules of that form is going to store in the database.The database contain one "AUTO-INCREMENT" (IDENTITY) field which is increment by 1 every times a new records is added. I am using SQL 2000 and ASP.

The snapshot of Insert statement is :

strsql="insert into recruitment values('"& Request("title") & "','"&
Request("cname") & "' , '" _

& request("cdesc") &"' , '"& request("country") &"','"& request("state")
&"' , '"& request("ind") &"' , '" _

& request("indother") &"' , '"& request("level") &"','"& request
("jobdesig") &"' , '"& request("jobfun") &"', '" _

& request("jobdesc") &"' , '"& request("currency") &"','"& request
("lsalary") &"' , '"& request("hsalary") &"', '" _

& request("chkperks") &"' , '"& request("rdohousing") &"','"& request
("numvac") &"' , '"& request("degree") &"', '" _

& request("workexp") &"','"& request("add_info") &"','"& request("email")
&"', '"& request("sms") &"', '" _

& request("address1") &"', '"& request("address2") &"', '"& request
("pincode") &"','"& request("contry") &"', '" _

& request("constate") &"' , '"& request("city") &"','"& request("teloff")
&"','"& request("telhome") &"','"& request("postaddress") &"')"

set recordset = connection.execute(strsql)

I am very well aware what the error message means. But, both tables and files are same. One run fines on my IIS but other show the above mentions error.

In order to sort out the problem, i also specify explcitly all the variable names with INSERT command, i am getting the below mention error on my IIS as well as on the web. The error i am getting is :

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'recnum', table 'recruitment' ; column does not allow nulls. INSERT fails.

/minorities/postrec.asp, line 35

What am I doing wrong? I suspect that it might be related to the autoincrement field. Or it might be that I just plain can't do this?

For, your convienence i am attaching all the files with this mail.

Thanks for your anticipations.
sqlboy

Your insert statement is wrong - it needs to be in the format:

"INSERT INTO tablename (fieldnames,...) VALUES (fieldvalues, ...)"

you are missing the fieldname part..

also, you cannot insert a value into a auto-incrementing field

hi there,
Thanks for response. But i tried the same thing very early. When i supplied the fields, doing something like this..

Insert into recruitment( fields2, fields3....) values(value2, value3,.... ) . Here the field1 is Identity field(autoincrement)

i am getting this error..

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'recnum', table 'recruitment' ; column does not allow nulls. INSERT fails.

sqlboy

You might want to double check that your primary key field is in fact an "identity" field. From my experience, you shouldn't be getting a "cannot insert NULL" error if this is the case.

hi there,
Well, the error which i was mentioning in my previous reply occured on both. I mean to say that on mine IIS as well as on web.

BUT, when i am not supplying the fields it's working fine on IIS as i mentioned in my problem. Now comming to your response...

Both tables are same. One works fine on IIS and other's not on web ? How is it feasible ?

Does it mean that SQL2000 not import the files correctly ? Or Is it one of the drawbacks of MSSQL2000 ?

If you say then I'll check the Primary Key of mine remote server tables.

sqlboy

Importing and Exporting SQL Server tables between databases will often result in blown away primary keys and/or identity properties. Double-check the properties on your identity field and use the syntax that Stumpy recommended. It should work.

Hi there,
Thanx for your reply numbernine. You are right, SQl not import / Export the files correctly. I found that few of tables on the web have no Primary key which they had.

It seems that MSSQL ate that key when they import / export it.

So, i guess that it is the reason of mine problem. May be, i 'll sort out rest of things by myself.

Thanks stumpy too !!!

Thanks alot for your anticipations.
sqlboy

For transferring tables using DTS - use the "Transfer Objects" method to keep all your tables, keys, etc intact.

Thanks Stumpy,
For your valuable suggestion. But, I always transfered the tables thru DTS import / Export wizard.

sqlboy

Yup, thats' what I'm talking about. After you've selected the source and destination DB's, it asks you what method you'd like to use to transfer the data. Choose the 3rd method, "Transfer Objects" to keep all meta data intact.

Hi SQLBoy,

I have seen such Problems. It definitely comes because, when you try to create Table and other Objects, using DTS, that certain indexes, default values etc. are not copied/Applied. This is the Reason why the same code that is working fine on your IIS, is not working on web. So, You can try out this:

1.) Take the Back Up of your Local Database, by using the "Back Up Database" utility of SQL.

2.) Ftp the file(.BAK) onto the web server.

3.) There create a blank database by the same name as local database.

4.) Restore the Database, using the SQL's "Restore Database Utility".

5.) This will create a Fool Proof database and then, you can manually delete the data from the table if required.

Try this out.....this might help you.

Himanshu,
Above all thanx for the reply. But the much better option was allready provided by the stumpy. Your suggestion is also valuable, no doubt et all.

Sqlboy