4

I have a table employee where id is defined as auto increment

schema:

employee(id, name, company, salary, age)

insert into employee(name, company, salary, age) 
values('John', 'ABC', 90000, 30);

This works. However, when doing the same thing via asp.net 

SqlConnection xconn = new SqlConnection();
xconn.ConnectionString = @"";  //connection details go here
xconn.Open()

String query = "insert into employee(name, company, salary, age) values(@name, @company, @salary, @age)";
SqlCommand ycmd = new SqlCommand(query, xconn);
ycmd.Parameters.Add("@name", name);
ycmd.Parameters.Add("@company", company);
ycmd.Parameters.Add("@salary", salary);
ycmd.Parameters.Add("@age", age);
ycmd.ExecuteNonQuery();

Here name, company, salary, age contain the respective values.

I get an exception

Cannot insert value NULL into column ID, column does not allow nulls

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user544079
  • 16,109
  • 42
  • 115
  • 171
  • Yes. But it is auto increment. So why would it throw an error when not specified. I did tha same via sql console and it worked fine. – user544079 Aug 14 '14 at 16:30
  • yea I missed the part about you doing in in the manager and it worked fine lol – DotN3TDev Aug 14 '14 at 16:31
  • 2
    It may not be related to the issue you currently have but always try using `using` statement for your `SqlConnection` and `SqlCommand` objects so the resources occupied by these objects are freed up appropriately. – Dennis R Aug 14 '14 at 16:34
  • 1
    a general note and I am not sure if this would cause it but you should use `Parameter.AddWithValue` as `Parameter.Add` is deprecated. Also as @DennisR stated you should use the `using` statements. – DotN3TDev Aug 14 '14 at 16:36
  • If you add the `ID` column to your c# example, what happens? – gunr2171 Aug 14 '14 at 16:37
  • 2
    Might be wrong here but the Parameter.Add() looks incorrect. Should second arg be data type. [Refer to](http://stackoverflow.com/questions/4624811/different-ways-of-passing-sqlcommand-parameters) – Tak Aug 14 '14 at 16:54
  • 1
    I'm just checking here...By auto increment you mean, IDENTITY (1,1) tight? Also, could your right click on the table and generate a CREATE script then paste that here? – Paul Aug 14 '14 at 17:34
  • I agree with @Paul we need to see the create statement for the table, there are some best practice things wrong with the code but nothing that should cause this so to further assist you we need to see the table's code. THANKS!! – DotN3TDev Aug 14 '14 at 18:04

2 Answers2

1

I don't see anything wrong with the code. I would check that your connection string in your web page points to the same server & database as that where the insert worked in query analyzer. You might also check if there is a trigger on the employee table that is updating another table and causing mischief.

Nolo Problemo
  • 201
  • 3
  • 14
  • I have also seen this where the table has an identity on one server but for some reason it didn't get set on another server causing the code to break. – HLGEM Aug 14 '14 at 18:00
  • The issue is not with the connectionstring because it is finding the database and table perfectly fine. I think seeing the create statement for the table would be the most helpful here at this point – DotN3TDev Aug 14 '14 at 18:04
1

I think your SqlCommand object is executing different SQL to what you have written. Use SQL profiler to see what is actually executed.

Also you should be using AddWithValue, or following a different calling pattern: Difference with Parameters.Add and Parameters.AddWithValue. It's most likely this that results in different effect to what you're intending with the Add() calls.

Community
  • 1
  • 1
Rory
  • 40,559
  • 52
  • 175
  • 261