MS SQL INSERT INTO PROBLEM

arvin_d

hi guys,

i am hjaving a problem performing an INSERT statement to MS SQL7.0, it always keep saying i have to do a "SET IDENTITY_INSERT" to ON before i can do an INSERT.

anybody knows the fix????

please help, i need it badly!

thanks in advance.

[deleted]

Searching is fun:

http://larr.unm.edu/~owen/SQLBOL70/html/set-set_19.htm

ivpayne

Arvin,
Sounds like you're trying to specify the value of an Identity field within your statement.
Since the idea of an Identity field is to let the db generate the next value, leave it out of the statement altogether.
SET IDENTITY_INSET ON is the command you would use if you needed, for instance, to import data from an existing table.

hth

Ian

arvin_d

hi ian,

as you can see this is what i am doing, a simple insert statement, with MySQL i have no problem doing this but with MS SQL? headache!!!

$Connection = mssql_connect("FOO_SERVER","sa","") or die ("Unable to connect to server");
$DB = mssql_select_db("MyDB",$Connection);

$Query = "INSERT INTO MyTable VALUES (NULL,'$mon_year','$div','$num_emp_planned','$num_emp_attended','$emp_name','$badge_num','$div','$course_name','$comp_date')";
$Result = mssql_query($Query,$Connection);
mssql_close($Connection);

and this is the error message i recieved:

Warning: MS SQL message: An explicit value for the identity column in table 'MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON. (severity 16) in C:\Inetpub\wwwroot\Test\recordadd.php on line 7

Warning: MS SQL: Query failed in C:\Inetpub\wwwroot\Test\recordadd.php on line 7

i do not know what to do anymore, plese help

thanks

arvin_d

hi vincent,

as you can see this is what i am doing, a simple insert statement, with MySQL i have no problem doing this but with MS SQL? headache!!!

$Connection = mssql_connect("FOO_SERVER","sa","") or die ("Unable to connect to server");
$DB = mssql_select_db("MyDB",$Connection);

and this is the error message i recieved:

Warning: MS SQL: Query failed in C:\Inetpub\wwwroot\Test\recordadd.php on line 7

i do not know what to do anymore, plese help

thanks

[deleted]

According to the link I posted, you should do a special setting before you can run the query. try it out.

arvin_d

i'm not sure, but have you tried PHP with MS SQL?

had you experienced any problems with it?

ivpayne

Arvin,
lose the NULL, out of your values. i.e.
$Query = "INSERT INTO MyTable VALUES (NULL,'$mon_year','$div','$num_emp_planned','$num_emp_attended','$emp_name','$badge_num','$div','$course_name','$comp_date')"

hth

Ian

[deleted]

<ignorant mode>
Is there some rule in MS-SQL that says you can't insert NULL values?
</ignorant mode>