CODEWITHSUNDEEP
c#sqldatabaseado.net
Ajit Hegde
Ajit Hegde

Reputation: 552

data Is not inserted to sql database using dataadapter..?

Here is the code

string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Inventory.mdf;Integrated Security=True;User Instance=True";
        SqlConnection con = new SqlConnection(constr);
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Users", con);
            DataSet ds = new DataSet();
            con.Open();
            SqlCommandBuilder cmd = new SqlCommandBuilder(sda);
            sda.Fill(ds, "Users");
            DataRow row = ds.Tables["Users"].NewRow();
            row[0] = 2;
            row[1] = txtUsername.Text;
            row[2] = txtPassword.Text;
            ds.Tables["Users"].Rows.Add(row);
            int res=sda.Update(ds, "Users");
            con.Close();
            sda.Dispose();
            dgUser.ItemsSource = null;
            dgUser.ItemsSource = ds.Tables["Users"].DefaultView;
            ds.Dispose();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
            con.Dispose();
        }

Problem is it is executing without error but when you check in the database there will be no insertion of record... what might be the error..?

Upvotes: 0

Views: 1949

Answers (3)

tim franklin
tim franklin

Reputation: 118

try doing an insert instead of a select

    SqlDataAdapter sda = new SqlDataAdapter("insert into Users (ID, UserName, Password) VALUES (2," + 
       txtUsername.Text + ", " + txtPassword.Text + ")", con);

but inline SQL is a bad idea generally. It would be better to write a stored procedure and execute that from your code

Upvotes: 1

V4Vendetta
V4Vendetta

Reputation: 38200

Have you configured the InsertCommand for the DataAdapter ? without setting it its unlikely the adapter would know to insert your record.

you need something like 

cmd ="INSERT INTO ...."; // add param to this

sda.InsertCommand = cmd

Try updating after setting this.(Similarly do it for UpdateCommand for the Adapter if required)

Upvotes: 0

Abbas
Abbas

Reputation: 14432

To be honest, I don't see any mistake in the code. Can you try debugging with breakpoints and see if the row is correctly added to the dataset before passed on to the DB?

Upvotes: 0

Related Questions