VB.NET - how to insert multiple rows from gridview in databse

Asked By Ajay Gupta on 17-Sep-08 01:50 AM

can someone tell me how we can insert multiple rows from a gridview in Databse one by one


See this code to insert multiple rows from gridview in databse - Sagar P replied to Ajay Gupta on 17-Sep-08 02:02 AM

private void button1_Click(object sender, EventArgs e)

{

int i;

string conStr = Properties.Settings.Default.connStr;

SqlConnection con = new SqlConnection(conStr);

con.Open();

for (i = 0; i <= dataGridView1.Rows.Count - 1; i++)

{

//Here in this for loop you can access all the values of

//gridview and can store it in db by using ado.net like

string query = "insert into Table values(" + dataGridView1.Rows[i].Cells[0].Value + "," + dataGridView1.Rows[i].Cells[1].Value + ")";

SqlCommand cmd = new SqlCommand(query, con);

retval = cmd.ExecuteNonQuery();

}

con.Close();

}

Best Luck!!!!!!!!!!!!!!!
Sujit.

reply - Binny ch replied to Ajay Gupta on 17-Sep-08 02:05 AM

DataTable dt = new DataTable();
//add columns to datatable to display in anothre gridview

for (int i = 0; i < GridView1.Rows.Count; i++)
{
Boolean isInsert = false;
//cell should not be empty
if (GridView1.Rows[i].Cells[0].Text.Trim() != "")
{
isInsert = false;
}
else
{
isInsert = true;
}


if (isInsert == false)
{
//check here for other cell validations
}
//do the above logic for all cells condtions.

if (isInsert == true)
{
DataRow dr = dt.NewRow();
//add values to datarow like below
dr[0] = GridView1.Rows[i].Cells[0].Text;
dr[0] = GridView1.Rows[i].Cells[1].Text;
}

}
         //here bind the dt to antoher gridview
GridView2.DataSource = dt;
GridView2.DataBind();

use this way - Web Star replied to Ajay Gupta on 17-Sep-08 02:15 AM

protected void Grid_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        GridViewRow row = ((e.CommandSource as Control).NamingContainer as GridViewRow);

        foreach (GridViewRow gvr in GridView2.Rows)
        {
            //insert into database code write in this function
            insertRecord(Convert.ToInt32(e.Item.Cells[0].Text))  
        }

    }
Use this - Kalit Sikka replied to Ajay Gupta on 17-Sep-08 02:57 AM
Public Function PutBondInfo(ByVal data As String) As DataSet
        Try
            Dim zh As New ZipHelper
            Dim sz As String
            Dim cn As SqlConnection
            Dim da As SqlDataAdapter
            Dim ds As New DataSet
            Dim xSet As New DataSet
            Dim sSql As String

            Dim mySerializer As XmlSerializer = New
XmlSerializer(ds.GetType())

            cn = New
SqlConnection("server=HLSQL;uid=test;pwd=;database=test")
            cn.Open()
            da = New SqlDataAdapter("SELECT * FROM BondStatus", cn)
            'da.TableMappings.Add("BondStatus", "NewDataSet")

            sz = zh.Decompress(data)
            zh = Nothing

            da.FillSchema(ds, SchemaType.Mapped, "BondStatus")
            'da.MissingSchemaAction = MissingSchemaAction.AddWithKey
            da.Fill(ds, "BondStatus")

            Dim st As New MemoryStream(Encoding.UTF8.GetBytes(sz))
            ' Create the object from the xml file
            xSet = CType(mySerializer.Deserialize(st), DataSet)
            xSet.Tables(0).TableName = ds.Tables(0).TableName

            ds.Merge(xSet, False, System.Data.MissingSchemaAction.Add)

            'ds.AcceptChanges()

            Dim SqlCB As SqlCommandBuilder = New SqlCommandBuilder(da)
            da.Update(ds, "BondStatus")

            st.Close()
            da.Dispose()
            SqlCB.Dispose()
            cn.Close()

            Return ds
        Catch ex As Exception
            ex.Message().ToString()
        End Try

    End Function
use Sqlbulk copy... - Vasanthakumar D replied to Ajay Gupta on 17-Sep-08 04:14 AM

Hi,

you can use Sql bulk copy for this purpose

private void StartImport()
{
       SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
        SqlBulkCopyOptions.TableLock);
    bulkCopy.DestinationTableName = "dest table Name";

     DataTable dt = CreateDataTableFromFile();
    bulkCopy.WriteToServer(dt);
       }


private DataTable CreateDataTableFromFile()
{

       //create a datatable and fill it with the values from the grridview

        return the Datatable; 
}

how to insert multiple records - uma anand replied to Sagar P on 26-Feb-09 07:20 AM

hi...

the code below is used to insert one record....using the same code and "for" loop i want to know how to insert multiple records...please help.....i want it in visual basic

Set connect = CreateObject ("ADODB.Connection")
connect.open "DSN=OPTUMETL;Driver= Oracle in OraHome92;Server=urnts1.uhc.com;UID=OPTUMETL;PWD=OPTUMETL"
Reporter.ReportEvent 0, "Database connection", "Successfully connected to URNTS1" 
Set objRecordset = CreateObject("ADODB.Recordset")
' Stmt to execute the code
query="INSERT INTO STG_CARE.ASSESSMENTANSWER(ANSWERID, ANSWERGROUPID, ANSWERTEXT, DISPLAYSEQUENCE, ISEXCLUSIVENO, ISEXCLUSIVEYES, 
CREATEBY, CREATEDATE, UPDATEBY, UPDATEDATE, RECORDSTATUS, RECORDVERSION, RECORDSOURCE, SYNCDATE, RECORDUNIQUEID, 
PARENTRECORDUNIQUEID, AUDIT_CREAT_DT, AUDIT_UPDATE_DT, SRC_SYSTEM_ID)VALUES(95149, 95052, 'Yes (Red Flag)', 10, 0, 0, 1, TO_DATE('11/10/2005 
01:05:29 PM','DD/MM/YYYY HH12:MI:SS PM'), 1, TO_DATE('11/10/2005 01:05:29 PM','DD/MM/YYYY HH12:MI:SS PM'), 0, 1, 0, TO_DATE('01/01/1900 12:00:00 
AM','DD/MM/YYYY HH12:MI:SS PM'), 0, NULL, TO_DATE('18/08/2008 06:06:16 AM','DD/MM/YYYY HH12:MI:SS PM'), TO_DATE('18/08/2008 06:09:13 AM','DD/MM/YYYY 
HH12:MI:SS PM'), 'CARE')"
msgbox query
Set objResults = connect.Execute(query) ' Cmd used to execute the sql
'
Reporter.ReportEvent 0, "Query execution", "Executed query successfully" 
'
'objResults.Close
insert,delete,update,cancel,select with gridview using vb.net only plz help me anybody....
vanitha mca replied to Ajay Gupta on 28-Jun-09 01:57 AM
end of post
gridview records insert into table - Maydane Okiye replied to Ajay Gupta on 10-Aug-09 06:23 AM

Dear Gupta,

I am new in .NET programming and i am developing an application but I am stucking now because my experience is limited.

Problem...

I am displaying Customer order details on GridView (asp.net, Using C#).

customer order details were stored 3 tables (customer table, order table, and orderItems table).

my questions...

a customer order have more than one product, so multiply items will display on gridview.

I wanted save those rows into OrderItems table so i searched on internet and I saw your example but I dont understand what you mean "retval".


thank you so much.

this your code

for (i = 0; i <= dataGridView1.Rows.Count - 1; i++)

{

//Here in this for loop you can access all the values of

//gridview and can store it in db by using ado.net like

string query = "insert into Table values(" + dataGridView1.Rows[i].Cells[0].Value + "," + dataGridView1.Rows[i].Cells[1].Value + ")";

cmd = new MySqlDataReader(query, con);

retval = cmd.ExecuteNonQuery();

}






karan replied to Ajay Gupta on 27-Feb-11 05:29 AM
it gets into a never ending loop
when i insert the last record......