none
How to update DataTable via SQL Server 2000 connection? RRS feed

  • Question

  • How to update a DataTable via a SQL Server 2000 connection?

    So,

     

    What is incorrect in the following code?

     

    Code Block

    DataRow dr1;
    string s1;


     

    dr1 = table3.NewRow();

     

    s1 = "INSERT INTO dbo.Table3 (Fname, Lname, Email)";

     

    table3Adapter.InsertCommand = new SqlCommand(s1, connection);

     

    dr1["Fname"] = txt1.Text;

    dr1["Lname"] = txt2.Text;

    dr1["Email"] = txt3.Text;

     

    table3.Rows.Add(dr1);

     

    table3Adapter.Update(table3);

     

     

    Wednesday, November 28, 2007 8:35 PM

Answers

  • Code Block

    void BasicInsertDemo()

    {

    // Basic insert demo

    // Open connection to sql and create test database and test table.

    SqlConnection conn = new SqlConnection("server=tcp:localhost;integrated security=true;database=master");

    conn.Open();

    xsql_ne(conn, "create database emailTest");

    xsql(conn, "use emailTest");

    xsql_ne(conn, "create table emailTest (FName varchar(255), Lname varchar(255), Email varchar(255))");

    // #1. Create a datatable and fill with data.

    DataTable t = new DataTable("t");

    // Add columns for our data.

    t.Columns.Add("Fname", typeof(string));

    t.Columns.Add("Lname", typeof(string));

    t.Columns.Add("Email", typeof(string));

    // Add 1 row of data to datatable (you can even add more if needed).

    object[] row = new object[3];

    row[0] = "Captain";

    row[1] = "Crunch";

    row[2] = "captain.crunch@crunchberry.com";

    t.Rows.Add(row);

    // Now do the insert.

    // Create SqlDataAdapter using our connection.

    SqlDataAdapter da = new SqlDataAdapter(null, conn);

    // Snap on an InsertCommand.

    da.InsertCommand = new SqlCommand("insert into emailTest (FName, Lname, Email) values (@p1, @p2, @p3)", conn);

    da.InsertCommand.CommandTimeout = 60; // Set this if needed for timeout control.

    // Add params. These map the column names in datatable to the parameter name @p1, @p2, @p3.

    da.InsertCommand.Parameters.Add("@p1", SqlDbType.VarChar, 255, "FName");

    da.InsertCommand.Parameters.Add("@p2", SqlDbType.VarChar, 255, "Lname");

    da.InsertCommand.Parameters.Add("@p3", SqlDbType.VarChar, 255, "Email");

    // Now fire off the data.

    da.Update(t);

    // Ok, I don't believe it, let me look at the data!

    da = new SqlDataAdapter("select * from emailTest", conn);

    DataSet ds = new DataSet();

    da.Fill(ds);

    MessageBox.Show(ds.GetXml());

    conn.Close();

    }

     

    void xsql_ne(SqlConnection conn, string sql)

    {

    try { xsql(conn, sql); }

    catch (Exception) { };

    }

    void xsql(SqlConnection conn, string sql)

    {

    using (SqlCommand cmd = conn.CreateCommand())

    {

    cmd.CommandText = sql;

    cmd.CommandType = CommandType.Text;

    cmd.ExecuteNonQuery();

    }

    }

     

     

     

    Friday, November 30, 2007 7:01 PM

All replies

  • You need to change your INSERT SQL statement to specify list of the column in a database and list of the parameters, which will be filled in from the DataTable, like

     

    INSERT INTO dbo.Table3 (Fname, Lname, Email) VALUES (@p1, @p2, @p3)


    Then you need to create collection of the parameters and map them to the columns in your DataTable as described here

     

    http://support.microsoft.com/kb/308507/en-us

    http://support.microsoft.com/kb/313028/en-us

     

    And then call Update method of the DataAdapter

     

    If you need only to insert data, you actually do not need to use DataTable and could use INSERT SQL statement directly executing it using ExecuteNonQuery method of the command. See example about it

     

    http://support.microsoft.com/kb/301075/en-us

    Thursday, November 29, 2007 11:29 AM
    Moderator
  • Thank you so much for your help.

    So,
    What is the type of p1 or p2 or p3?
    Can p1 or p2 or p3 be TextBox.Text?
    How can I set the value of TextBox.Text to a datacollumn?

    For example:
    INSERT INTO dbo.Table3 (Fname, Lname, Email) VALUES (@txt1.Text, @txt2.Text, @txt3.Text)

    Thanks for your answer.
    Friday, November 30, 2007 3:40 PM
  • Can you correct the code and write for me?
    I'll thank you so much.
    Friday, November 30, 2007 3:42 PM
  • Code Block

    void BasicInsertDemo()

    {

    // Basic insert demo

    // Open connection to sql and create test database and test table.

    SqlConnection conn = new SqlConnection("server=tcp:localhost;integrated security=true;database=master");

    conn.Open();

    xsql_ne(conn, "create database emailTest");

    xsql(conn, "use emailTest");

    xsql_ne(conn, "create table emailTest (FName varchar(255), Lname varchar(255), Email varchar(255))");

    // #1. Create a datatable and fill with data.

    DataTable t = new DataTable("t");

    // Add columns for our data.

    t.Columns.Add("Fname", typeof(string));

    t.Columns.Add("Lname", typeof(string));

    t.Columns.Add("Email", typeof(string));

    // Add 1 row of data to datatable (you can even add more if needed).

    object[] row = new object[3];

    row[0] = "Captain";

    row[1] = "Crunch";

    row[2] = "captain.crunch@crunchberry.com";

    t.Rows.Add(row);

    // Now do the insert.

    // Create SqlDataAdapter using our connection.

    SqlDataAdapter da = new SqlDataAdapter(null, conn);

    // Snap on an InsertCommand.

    da.InsertCommand = new SqlCommand("insert into emailTest (FName, Lname, Email) values (@p1, @p2, @p3)", conn);

    da.InsertCommand.CommandTimeout = 60; // Set this if needed for timeout control.

    // Add params. These map the column names in datatable to the parameter name @p1, @p2, @p3.

    da.InsertCommand.Parameters.Add("@p1", SqlDbType.VarChar, 255, "FName");

    da.InsertCommand.Parameters.Add("@p2", SqlDbType.VarChar, 255, "Lname");

    da.InsertCommand.Parameters.Add("@p3", SqlDbType.VarChar, 255, "Email");

    // Now fire off the data.

    da.Update(t);

    // Ok, I don't believe it, let me look at the data!

    da = new SqlDataAdapter("select * from emailTest", conn);

    DataSet ds = new DataSet();

    da.Fill(ds);

    MessageBox.Show(ds.GetXml());

    conn.Close();

    }

     

    void xsql_ne(SqlConnection conn, string sql)

    {

    try { xsql(conn, sql); }

    catch (Exception) { };

    }

    void xsql(SqlConnection conn, string sql)

    {

    using (SqlCommand cmd = conn.CreateCommand())

    {

    cmd.CommandText = sql;

    cmd.CommandType = CommandType.Text;

    cmd.ExecuteNonQuery();

    }

    }

     

     

     

    Friday, November 30, 2007 7:01 PM
  • Thank you so much!
    Sunday, December 2, 2007 8:11 AM