connection error between sql and C#

Question

i have created that table on my sql server :

create table Empolyee(
ESSN int not null,
EFirstName varchar(20) not null ,
ELastName varchar(20) not null,
EJob varchar(20),
EBDate date,
ESalary int,

primary key(ESSN));

and in C# i try to add data to database using that code :

SqlConnection sqc = new SqlConnection("Data Source=.\\;Initial Catalog=DB;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

        da.SelectCommand = new SqlCommand("INSERT INTO Empolyee VALUES (@ESSN,@EFirstName,@ELastName,@EJob,@EBDate,@ESalary)", sqc);
        da.SelectCommand.Parameters.Add("@ESSN", SqlDbType.Int).Value = textBox1.Text;
        da.SelectCommand.Parameters.Add("@EFirstName", SqlDbType.VarChar).Value = textBox2.Text;
        da.SelectCommand.Parameters.Add("@ELastName", SqlDbType.VarChar).Value = textBox3.Text;
        da.SelectCommand.Parameters.Add("@EJob", SqlDbType.VarChar).Value = textBox4.Text;
        da.SelectCommand.Parameters.Add("@EBDate", SqlDbType.Date).Value = maskedTextBox1.Text;
        da.SelectCommand.Parameters.Add("@ESalary", SqlDbType.Int).Value = textBox5.Text;

        // open connection
        sqc.Open();
        // excute the command
        da.InsertCommand.ExecuteNonQuery();
        // close connection
        sqc.Close();

i get that error when i run : Object reference not set to an instance of an object or System.NullReferenceException: Object reference not set to an instance of an object.

When doing the Insert you don't need a DataAdapter object or DataSet try looking at using a dataReader — MethodMan
– MethodMan, Commented Dec 26, 2011 at 14:41

Yahia · Accepted Answer · 2011-12-26 14:43:08Z

2

you haven't assigned anything to da.InsertCommand - instead you have put an INSERT command into da.SelectCommand... just change all references from da.SelectCommand to da.InsertCommand in the code you show in your question:

    da.InsertCommand = new SqlCommand("INSERT INTO Empolyee VALUES (@ESSN,@EFirstName,@ELastName,@EJob,@EBDate,@ESalary)", sqc);
    da.InsertCommand.Parameters.Add("@ESSN", SqlDbType.Int).Value = textBox1.Text;
    da.InsertCommand.Parameters.Add("@EFirstName", SqlDbType.VarChar).Value = textBox2.Text;
    da.InsertCommand.Parameters.Add("@ELastName", SqlDbType.VarChar).Value = textBox3.Text;
    da.InsertCommand.Parameters.Add("@EJob", SqlDbType.VarChar).Value = textBox4.Text;
    da.InsertCommand.Parameters.Add("@EBDate", SqlDbType.Date).Value = maskedTextBox1.Text;
    da.InsertCommand.Parameters.Add("@ESalary", SqlDbType.Int).Value = textBox5.Text;

BTW you don't need any SqlDataAdapter or DataSet to execute an INSERT - it can be done with SqlConnection and SqlCommand only.

answered Dec 26, 2011 at 14:43

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R.Vector Over a year ago

OH MY GOSH , i can`t belive how come i choose selectcommand , thanks a lot , it works now

Yahia Over a year ago

@R.Vector you are welcome :-) please don't forget to upvote/mark as accepted any answer that is of help (see meta.stackexchange.com/questions/5234/…).

Wael Dalloul · Accepted Answer · 2011-12-26 14:44:06Z

you are executing the following line:

  da.InsertCommand.ExecuteNonQuery()

but you are init SelectCommand

Try to replace the following code:

da.InsertCommand= new SqlCommand("INSERT INTO Empolyee VALUES (@ESSN,@EFirstName,@ELastName,@EJob,@EBDate,@ESalary)", sqc);
        da.InsertCommand.Parameters.Add("@ESSN", SqlDbType.Int).Value = textBox1.Text;
        da.InsertCommand.Parameters.Add("@EFirstName", SqlDbType.VarChar).Value = textBox2.Text;
        da.InsertCommand.Parameters.Add("@ELastName", SqlDbType.VarChar).Value = textBox3.Text;
        da.InsertCommand.Parameters.Add("@EJob", SqlDbType.VarChar).Value = textBox4.Text;
        da.InsertCommand.Parameters.Add("@EBDate", SqlDbType.Date).Value = maskedTextBox1.Text;
        da.InsertCommand.Parameters.Add("@ESalary", SqlDbType.Int).Value = textBox5.Text;

Collectives™ on Stack Overflow

connection error between sql and C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related