how to insert data from gridview into database

Question

5.00/5 (1 vote)

See more:

SQL-Server-2012

Hi i need to select data from table [A] and insert to table [M]

C#

protected void Button3_Click1(object sender, EventArgs e)
        {
            if (RadioButton2.Checked)
            {
                SqlConnection con = new SqlConnection(MyConnectionString);
                // con.Open(); // don't need the Open, the Fill will open and close the connection automatically
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM ADMS_Machining where datetime='" + TextBox1.Text + "'", con);
                mytable = new DataTable();
                da.Fill(mytable);
                GridView2.DataSource = mytable;
                GridView2.DataBind();


            }
            else
            {

                SqlConnection con = new SqlConnection(MyConnectionString);
                // con.Open(); // don't need the Open, the Fill will open and close the connection automatically
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Machining_Master where datetime='" + TextBox1.Text + "'", con);
                mytable = new DataTable();
                da.Fill(mytable);
                GridView2.DataSource = mytable;
                GridView2.DataBind();

            }


        }

        
        
        protected void Button4_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            String strConnString, strSQL;

            strConnString = "Server=kane-pc;UID=sa;PASSWORD=1234;Database=Machining;Max Pool Size=400;Connect Timeout=600;";

        strSQL = "INSERT INTO Machining_Master (DailyMean,Process_Mean,Long_Term_Stdev,CPK_ESTIMATE,DAILY_MINIMUM,DAILY_MAXIMUM,Model)" + 
                                 "VALUES" + "(@DailyMean, @Process_Mean, @Long_Term_Stdev,@CPK_ESTIMATE,@DAILY_MINIMUM,@DAILY_MAXIMUM,@Model";
             
 
//here code

          
       conn.ConnectionString = conn;
            conn.Open();
           cmd.Connection = conn;
            cmd.CommandText = strSQL;
           
           

    }

Posted 8-Jan-15 20:04pm

Member 11363027

Add a Solution

Comments

Thanks7872 9-Jan-15 2:14am

Your question description,Title and code don't match at all. I don't see any code related to Gridview for saving data.

Richard Deeming 9-Jan-15 8:20am

Your DataAdapter code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

2 solutions

Solution 2

Hi,

There are 2 methods by which you can able to insert bulk data to database table.

1.) Using datatable and tabletype parameter / UDT (User defined tables)
2.) Using XML.

Logic:

If you want to insert a bulk of data into a table at a time from your code behind using a datatable or dataset, then you need to follow above 2 methods. For that you need to collect the data you want to insert into a table into a datatable and then you can pass the datatable into a stored procedure as tabletype parameter / UDT or XML format and your job will be done in the stored procedure.

Please follow the links for option 1.
1.) bulk-insert-into-table-using-user-defined-table-type
2.) bulk-insert-using-sql-type-user-defined

Please follow the XML format below.
1.) importing-and-processing-data-from-xml-files-into-sql-server-tables

The only thing you have to pass is your data as datatable format to the stored procedure. Use any one of these method and your job will be done efficiently.

Thanks
Sisir Patro

Posted 9-Jan-15 1:59am

Sisir Patro

Updated 9-Jan-15 2:00am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Oshtri Deka · Accepted Answer · 2015-01-08T22:04:00

First of all name your controls (and variables) properly. Button1, Button2,...LabelN is non-descriptive and less readable. Make an effort and give proper names like updateButton, btnSelectData, nameLabel etc.

Second make a habit of closing your connections and embed all non queries witnin SqlTransaction.

Third to answer your question, you can use dataAdapter to update data as well.

C#

string insertCommandText = "your insert (or update) command goes here";
SqlConnection conn = new SqlConnection("someConnectionString");
SqlCommand insertCmd = null;
SqlTransaction trans = null;

try
{
    //Open connection, setup transaction and command
    conn.Open();
    trans = conn.BeginTransaction();
    insertCmd = new SqlCommand(insertCommandText, conn, trans);
    SqlDataAdapter da = new SqlDataAdapte(); 
    da.UpdateCommand = insertCmd;
    
    //get data from gridView 
    DataTable dt = yourGridView.DataSource as DataTable;
    if(dt != null)
    {
        da.Update(dt);
    }

    //commits changes to db
    trans.Commit();
}
catch(Exception ex)
{
    //rollbacks any changes
    trans.Rollback();
    //do some error handling here
}
finally
{   
    //get rid of transaction
    if(trans != null)
    {
        trans.Dispose();
    }
    if(inserCmd != null)
    {
        cmd.Dispose();
    }
    //close connection
    conn.Close();
}