wee problem with Insert satement

[sqlkid2k]

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 table. 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 table.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&quot & "','"&
Request("cname&quot & "' , '" _

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

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

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

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

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

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

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

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 anticipations
sqlkid2k

 

[Transcend]

Try putting a response.write strsql in your asp page and testing the string it outputs directly on your db

Transcend

 

[sqlkid2k]

Hi there,
I tried the same also very early. I even tried all the best possible ways to sort out that nasty problem. For instance, i allready did these things......

Insert into values( value2, value3................) , i got
the error. (value1 is identity) OR

Insert into( fields 2, fields3,.)values(values2,values3) i also got the eror. Or at last

Insert into( fields 1(identity field) ,fields2,...........) values ( "" ,values 2, values3,........ ) again got the error.

I was aware that the last statment is totally wrong. But it was a wild guess to sort out the same thing.

sqlkid2k

 

[cheerio]

You cannot insert a value into an auto-increment/identity field - the database engine takes care of that. That field should not feature at all in the Insert Statement.

Ken

 

[sqlkid2k]

Hi ken,
I know the same. So, what is the bottom line for me ? how can i tackle out that nasty problem ?


sqlkid

 

[SQLSister]

Question. Are the asp page and your development machine calling the same backend database? IF not maybe the field was set for identity in one but not in the other, but it was still required and wanted a value?

Another thing to try is to move all the processing to SQL Server and do this is a stored procedure. Then call the Stored procedure in the ASP code. This is a faster way to process in general, so you might consider it.

 

[sqlkid2k]

Hi there,

Sqlsister, Answer to your first Question is Yes. Both tables and files are exactly same. I also have an sqlclient on the same system where IIS is. After checking the thing i export the tables and data through SQL 2000 Enterprise Manager and Import / Export options. So, there is Question of missing anything in tables.

As far as the second part is concerned, then to be very honest, i am not getting the things clearly. Does you mean to say that, first i make the stored Procedure and then call the stored procedure in ASP ? Or something else.

Thanks for response
sqlkid

 

[cheerio]

At the end of the day there are two simple questions:

1 Is your ASP code generating valid SQL that does the job
2 Is that code getting through correctly to the database engine

As Transcend said above, collect the SQL string by writing it out to a page. Paste it into a tool like Query Analyzer and execute it directly against the database.

If the SQL executes without problem in Query Analyzer then look at SQLSister's suggestion that you might be executing against the wrong table/database. You then need to check your database connection string (which may be buried in an INCLUDE file).

Problem at present is we cannot see where in the chain the logic is broken.

Ken

 

[SQLSister]

yes sqlkid2k, that is exactly was I was suggesting. but cheerio has a good suggestion about getting the exact string the asp page is creating and then pasting it into query analyzer to see what happens. You could also paste it here and we might see the problem too. Another thing to consider is permissions, although this isn't the error I would expect to get if it were a permissions problem. But often when there is a problem where something works fine on development but not when it goes to production or testing it's because the developers have full rights and the web user does not.

 

[sqlkid2k]

Hi there,
Thanks to all for your suggestions.

Ken, mine answer is Yes, for both of your Questions. As i said earlier to Transcend, that i did it the thing very earlier. It's running fine. Also, i am not excuting the code against the wrong table. My database connection is fine.I checked it 3rice.

Ken,I am glad that you open a very intresting Question in my mind, which wasn't striking at that time. The Question is :
Does MSSQL ate Primary/Identity key when we export/import tables ?

I was asking b'coz, if it not then i was definetly getting error when i tried to insert the values directly.. LIke..

Insert into tbl (field1,field2,....) values(value1,value2.....)

But that wasn't the case. I guess that problem is here..

SQlSiter, I am still getting the same error after making the Procedure and calling back to my ASP code.

Thanks
sqlkid

 

[SQLSister]

SQL will not necessarilty move all the constraints, indexes etc. What method was used to move the tables to production?I have never had it not move an identity field except as an identity. IF you take a tool like SQLCompare, it will check specifically the differences between your production and development servers and you can see if there is a problem with this and other tables.