Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlboy
Starting Member
5 Posts |
 Posted - 2003-03-26 : 03:34:25
|
| Hi there,I have a 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 web, it prompts me error messages. This is not an single case. Every table 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 table 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. What am I doing wrong? I suspect that it might be related to the Identity fields. Or it might be that I just plain can't do this?Thanks for your anticipationssqlkid sqlboy |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-26 : 04:26:04
|
| Sounds like you have two problems.First, when you transferred your database, I think you left off the option to transfer Identity settings. Double check that the destination database actually has an identity column.Second, you just learnt a very good lesson. You should ALWAYS specify fields in your insert statement. Otherwise, if down the track you add an extra field, all of your code will break.Damian |
 |
|
|
sqlboy
Starting Member
5 Posts |
Posted - 2003-03-26 : 05:30:07
|
quote:
Sounds like you have two problems.First, when you transferred your database, I think you left off the option to transfer Identity settings. Double check that the destination database actually has an identity column.Second, you just learnt a very good lesson. You should ALWAYS specify fields in your insert statement. Otherwise, if down the track you add an extra field, all of your code will break.Damian
Hi there, Thanks for reply. Well comming to your first suggestions, does it mean that i have to do something like this .... SET IDENTITY_INSERT <tblname> ON for every table that has identity field. Also, you point out that i left off the option to transfer identity settings. May be, i left off. Would you like to put some light where it is ( identity transfer settings)?As far as your second suggestion is concerned then i am getting this error on both( on IIS as well as on web). For instance, when i did this thing....Insert into <tblname>(field2,field3....) values( value2,value3) field1 is identityThe error which 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. sqlboy |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-03-26 : 05:36:54
|
Your error is caused by the first part of my answer.I am guessing (my powers of guesswork get tested on SQL Team  ) that you used DTS to transfer your database to a live server like an ISP. Am I close ?When this happens, there is an option in DTS to transfer the Identity field. It isn't checked by default. So I think you transferred your database without any identity fields.Either redo it, or go through your tables and make your ID columns an identity again.Damian |
|
|
|
|
|
|
|
|
wee problem with insert