Member Avatar for unknownunknown

Hi,

I have a requirement to insert data from dataset into a table in the database. Actually i have records in the excel sheet that has to be inserted into the database. I have populated them in a dataset. Now this dataset has to be validated against some conditions. After accepting the changes in the dataset . This table in the database has no records. Records in the dataset have to be inserted to that table. i dont know how to insert all the records in the dataset at one go... Please advice.

Thanks

  • 3 Contributors
  • 2 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by arjunsasidharan

Dani AI

Generated

If you want to push all rows in one go after validating the DataSet, two safe patterns work well. Also, be careful with AcceptChanges: if you call ds.AcceptChanges() before writing, every row becomes Unchanged and nothing gets inserted. Either skip AcceptChanges until after the write, or re-mark rows as Added with For Each r In dt.Rows : r.SetAdded() : Next.

Fastest for large batches (SQL Server) is SqlBulkCopy (.NET 2.0+). Map your DataTable columns and write in a single transaction:

Dim dt As DataTable = ds.Tables("YourExcelData")

Using cn As New SqlConnection("your-connection-string")
    cn.Open()
    Using tx = cn.BeginTransaction()
        Using bc As New SqlBulkCopy(cn, SqlBulkCopyOptions.CheckConstraints, tx)
            bc.DestinationTableName = "dbo.YourTable"
            bc.ColumnMappings.Add("ExcelCol1", "DbCol1")
            bc.ColumnMappings.Add("ExcelCol2", "DbCol2")
            ' ...add mappings for all columns...
            bc.WriteToServer(dt)
        End Using
        tx.Commit()
    End Using
End Using

If you prefer the DataAdapter route suggested by and , define an explicit InsertCommand (faster and safer than relying on SqlCommandBuilder, and it does not require a primary key in the target table):

Dim dt As DataTable = ds.Tables("YourExcelData")
Using cn As New SqlConnection("your-connection-string")
    Dim da As New SqlDataAdapter("SELECT Col1,Col2 FROM dbo.YourTable WHERE 1=0", cn)
    da.InsertCommand = New SqlCommand("INSERT INTO dbo.YourTable (Col1,Col2) VALUES (@Col1,@Col2)", cn)
    da.InsertCommand.Parameters.Add("@Col1", SqlDbType.Int, 0, "Col1")
    da.InsertCommand.Parameters.Add("@Col2", SqlDbType.NVarChar, 50, "Col2")
    ' Important: do NOT call ds.AcceptChanges() before this
    da.Update(dt)
End Using

Practical tips:

  • Validate in-memory first (DataColumn.AllowDBNull/MaxLength, or dt.Select("Col1 IS NULL OR LEN(Col2)>50")).
  • Match types before insert (e.g., parse Excel strings to Int32).
  • Wrap the whole operation in a transaction so it is all-or-nothing.

Recommended Answers

All 2 Replies

Member Avatar for luisofo

Hello there: I have used this code in my application and it works. It is about populating the database with the information within a present form

Dim a As String = Me.ComboBox1.SelectedItem
Dim b As Integer = Me.txtDeel1.Text
Dim c As Integer = Me.txtDeel2.Text
Try
selectStr = "SELECT TbStukIDRegCD,TbStukIDDeel1,TbStukIDDeel2,TbStukCodeAanbieder," + _
"TbStukTijdInschrijving, TbStukCodeRectificatie, TbStukDatumVerlijden,TbStukAardStuk,TbStukDatumDoorhalingStuk," + _
"TbStukIndGeheelVerwerkt,TbStukReferentie,TbStukBedrag,TbStukCodeMeten,TbStukStukTekst FROM TbStuk"
da = New SqlDataAdapter(selectStr, conn)
da.Fill(Ds1, "TbStuk")
 
 
 
Dim TbStukRow As DataRow = Ds1.Tables("TbStuk").NewRow
''TbStukRow("TbStukDatumID") = Me.tdpdatum.Text
TbStukRow("TbStukIDRegCD") = Me.ComboBox1.SelectedItem
TbStukRow("TbStukIDDeel1") = Me.txtDeel1.Text
TbStukRow("TbStukIDDeel2") = Me.txtDeel2.Text
TbStukRow("TbStukCodeAanbieder") = Me.TextBox7.Text
TbStukRow("TbStukTijdInschrijving") = Me.TextBox6.Text
TbStukRow("TbStukCodeRectificatie") = Me.TextBox5.Text
TbStukRow("TbStukDatumVerlijden") = Me.DateTimePicker1.Text
TbStukRow("TbStukAardStuk") = Me.TextBox3.Text
TbStukRow("TbStukDatumDoorhalingStuk") = Me.DateTimePicker2.Text
TbStukRow("TbStukIndGeheelVerwerkt") = Me.TextBox1.Text
TbStukRow("TbStukReferentie") = Me.TextBox11.Text
TbStukRow("TbStukBedrag") = Me.TextBox10.Text
TbStukRow("TbStukCodeMeten") = Me.TextBox9.Text
TbStukRow("TbStukStukTekst") = Me.TextBox2.Text
Ds1.Tables("TbStuk").Rows.Add(TbStukRow)
Dim cmd As New SqlCommandBuilder(da)
da.Update(Ds1, "TbStuk")
'conn.Close()
Catch
Finally
MessageBox.Show("successfully inserted into database", "success info", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try

I do hope this can help you.

Member Avatar for arjunsasidharan

Use have to use the SqlCommandBuilder to update the table in a database through dataset

Use this it may help:

Dim object name as SqlCommandBuilder(DataAdapter's Name)
DataAdapter's Name.Update(Datasetname, "Table Name")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.