Question how to insert Insert GridView Data to Database

[pactools]

hi I am desperately looking for this solution,is it possible to insert new values to your table from the datagridview?the scenario is this: i have imported data from excel and display those data on a datagrid. now after displaying those data on the datagrid there is a save button wherein if the user clicks the button the displayed data on the datagrid will all be inserted to my existing table,can anyone help me with this?thanks
Code Importing Excel Data to Grid view

Public Sub UpLoadExcel()
        Try
            Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection1").ToString()
            Dim dbConn As New OleDbConnection(strExcelConn)
            'Dim db As OleDbDataReader
            Dim strSQL As String
           strSQL = "SELECT Item_no as LI,Part_no as PartNumber,qty as OrderQty,Mfgr_pt_no as MfgrPartNumber FROM [" & txtWCON.Text & "$] where Part_sourc ='CONSG'"
            dbConn.Open()
            Dim cmd As New OleDbCommand(strSQL, dbConn)
            Dim dsExcel As New DataSet
            Dim daExcel As New OleDbDataAdapter(cmd)
            daExcel.Fill(dsExcel)
            GVCparts.DataSource = dsExcel
            GVCparts.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

web.config

 <connectionStrings>
        <add name="ExcelConnection" connectionString ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|\WO.xls;Extended Properties=Excel 8.0"/>
        <add name="ExcelConnection1" connectionString ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|\6496.xls;Extended Properties=Excel 8.0"/>
    </connectionStrings>

 

[tapaswini]

You can store the datasource set to the datagrid into a data table and then iterate through the data rows to insert each row.For each current datarow, retrieve the column values and insert those into the respective columns of the table.

Let's say you have two columns 'column1' and 'column2' in the gridview.There are 10 rows getting displayed in your gridview.Then for each row retrieve the column1 and column2 values and insert those into the tables as below.

dim dtDataTable as DataTable
dtDataTable = GVCparts.DataSource

dim column1Value as string = string.empty
dim column2Value as string = string.empty

For Each row As DataRow In dtDataTable.Rows

column1Value = row("Column1")
column2Value = row("Column2")

//*write your insert function here,pass the column values into your insert function*//

Next

 

[pactools]

Thanks thapaswini for your reply,
Can you kindly provide entire code with sample insert function, i am totally new to this subject.

 

[tapaswini]

ok..let me know one thing.you need to insert those data into access database or the sql database?

 

[pactools]

ok..let me know one thing.you need to insert those data into access database or the sql database?

I want to Insert this Gridview data into MySql Database.This is MySql Connection string: Dim connectionString As String = "Database=pjt;" & "Data Source=localhost;" & "User Id=root;Password=prasad123"

 

[Yweb81]

hi
you can insert the data shown on your gridview in the event GridView1_DataBound .it means that when your data is shown in the gridview you insert it directly in the sqql database

below is my code in a project mine .so customize it.it works in my case

Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound
        Dim con2 As New SqlConnection(GetConnectionStringI())
        Dim cmdLog As New SqlCommand()
        Dim cmdTest As New SqlCommand()
        Dim cmdupdate As New SqlCommand()
        Dim SearchResultsTable2 As New DataTable()
        Dim sqlLog, sqltest As String
        con2.Open()
        For i = 0 To GridView1.Rows.Count - 1
            sqltest = "select * from LOGBARCOD where codeArt='" & GridView1.Rows(i).Cells(1).Text & "' and codeCli='" & GridView1.Rows(i).Cells(0).Text & "' and pointure='" & GridView1.Rows(i).Cells(2).Text & "'"
            cmdTest.Connection = con2
            cmdTest.CommandType = CommandType.Text
            cmdTest.CommandText = sqltest
            cmdTest.ExecuteNonQuery()
            Dim adapter2 As New SqlDataAdapter(cmdTest)
            adapter2.Fill(SearchResultsTable2)
            Dim article, client, pointure2, barcode2 As String
            article = GridView1.Rows(i).Cells(1).Text
            client = GridView1.Rows(i).Cells(0).Text
            pointure2 = GridView1.Rows(i).Cells(2).Text
            barcode2 = GridView1.Rows(i).Cells(3).Text
            If SearchResultsTable2.Rows.Count = 0 Then
                sqlLog = "INSERT INTO LOGBARCOD(codeArt,codeCli,pointure,modif,barcode) VALUES('" & article & "','" & client & "','" & pointure2 & "','1','" & barcode2 & "')"
                cmdLog.Connection = con2
                cmdLog.CommandType = CommandType.Text
                cmdLog.CommandText = sqlLog
                cmdLog.ExecuteNonQuery()
            End If
        Next
        con2.Close()

    End Sub

 

[vforvijay]

Data Grid View

Check this link for update data from gridview to database...
.NET ASP.NET ARTICLES IN VB coding: GRID VIEW LOAD AND UPDATE SQL DATA TABLE INTO GRID VIEW USING ASP.NET