Member Avatar for Devin

Okay heres the dilemna....

I have a Dataset("LineItems") that Stores information about so many records.

To source to this DataSet("LineItems") is not its intended ouput.

Meaning I pull this info in from one database and output to another.

My problem is i have no idea on how to write an insert statement that will insert multiple records into a SQL table. Does anyone have any pointers????

PLEASE HELP ASAP. THANKS!

  • 4 Contributors
  • 4 Replies
  • 2K Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by sierrainfo

Dani AI

Generated

@Devin you can push a whole DataTable to SQL Server without looping row-by-row. Two good options:

  • Parameterized DataAdapter + Update: safer than string SQL, easy to map columns, and runs inside one transaction.
  • SqlBulkCopy: fastest when you just need to dump rows into a table.

Example using SqlBulkCopy (great for thousands of rows). It assumes your DataSet has a table named LineItems whose columns match the destination table; add mappings if names differ.

Dim ds As DataSet = GetYourDataSet() ' contains table "LineItems"

Using conn As New SqlConnection(connString)
    conn.Open()
    Using tran = conn.BeginTransaction()
        Using bulk As New SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock Or SqlBulkCopyOptions.CheckConstraints, tran)
            bulk.DestinationTableName = "dbo.LineItems"
            bulk.ColumnMappings.Add("ColA", "ColA")
            bulk.ColumnMappings.Add("ColB", "ColB")
            bulk.ColumnMappings.Add("OrderDate", "OrderDate")
            bulk.WriteToServer(ds.Tables("LineItems"))
        End Using
        tran.Commit()
    End Using
End Using

If you prefer the DataAdapter route (clean and readable), create one INSERT with parameters, bind those parameters to DataColumn names, and call adapter.Update(ds, "LineItems") inside a transaction. It avoids the per-row ExecuteNonQuery loop @bruce2424 showed and is easier to maintain than hand-built SQL, especially when nulls and types are involved.

For @prit49: store dates as DATE/DATETIME via parameters, not as formatted strings. Let the UI worry about dd/MM/yyyy. Example:

cmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = dtPicker.Value ' store
lblDate.Text = dtPicker.Value.ToString("dd/MM/yyyy", Globalization.CultureInfo.InvariantCulture) ' display

That sidesteps regional settings and the classic 10/12 vs 12/10 confusion that trips inserts. @sierrainfo’s tip to format is fine for display, just not for persistence.

Recommended Answers

All 4 Replies

Member Avatar for prit49

in a form , I set dtpicker ( date calender ) , it is ok, i choose a date ( dd/mm/yyyy) format ( 15 /10/2008 ) , when it save in database field , it say wrong . i want my all date field will save and retrieve ( when modify ) in (dd/mm/yyyy) format. how is it possible.

Member Avatar for sierrainfo

Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "insert into product (Product_id,Product_name,Product_price) values(7,'Product7',700)"
Try
connection.Open()
adapter.InsertCommand = New SqlCommand(sql, connection)
adapter.InsertCommand.ExecuteNonQuery()
MsgBox("Row inserted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class

Member Avatar for sierrainfo

Use Format Function.
Like :

= Format(dtpicker .Value, "dd/MM/yyyy")

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.