Member Avatar for bghodsi

Hi folks:

Can anyone tell me what I am doing wrong in the coding below:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")


            Dim isconnecting As Boolean = True

            While isconnecting

          

            Dim str As String = _
            "INSERT INTO snippet  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
                Dim cn As New OleDbConnection(str)
                Dim cmdd As New OleDbCommand(str, cn)
                With cmdd
                    .Parameters.Add(New OleDbParameter("@snippetName", _
                     OleDbType.VarChar, 50)).Value = ds.Tables("snippetName")
                    .Parameters.Add(New OleDbParameter("@snippetSource", _
                    OleDbType.VarChar, 50)).Value = ds.Tables("snippetSource")
                    .Parameters.Add(New OleDbParameter("@snippetCode", _
                    OleDbType.LongVarWChar)).Value = ds.Tables("snippetCode")
                End With
                cn.Open()
                cmdd.ExecuteNonQuery()
                cn.Close()
                isconnecting = False
            End While


        Catch ex As Exception
            MsgBox("Error " & ex.Source & "  " & ex.Message)

        End Try

    End Sub
Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 3 Contributors
  • 3 Replies
  • 105 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by meffe

Dani AI

Generated

The code shown by and the follow-up from have a few consistent problems that stop rows being inserted into Access. The biggest single mistake is using the INSERT SQL text as the OleDbConnection string. Other issues are assigning parameter values to DataTable objects instead of individual DataRow fields, and not creating a proper Access connection string or transaction for bulk inserts.

Concrete, practical fixes to apply (in this order):

  • Use a real Access connection string, not the INSERT SQL. Example values:
    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\file.mdb; (Jet/.mdb) or
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\file.accdb;Persist Security Info=False; (ACE/.accdb).
  • Create an OleDbCommand with the INSERT statement, associate it with the OleDbConnection, then add parameters once to that command.
  • Iterate the rows in the DataTable (the table you filled from SQL Server). For each row set the parameter.Value to row("FieldName") (handle DBNulls) and then call ExecuteNonQuery().
  • Wrap the inserts in a transaction and use a single open connection to improve performance. Dispose connections/commands with Using blocks.
  • Remember OleDb parameter rules: the provider uses positional parameters — add parameters in the same order they appear in the SQL. For long text use Access Memo/LongVarWChar types and ensure size limits match.
  • If you are moving lots of rows, consider streaming with SqlDataReader instead of loading a full DataSet, or use the SQL Server Import/Export Wizard / SSIS or create a linked table in Access and run an append query as suggested.

Also watch platform issues: Jet is 32-bit only (compile as x86 or use ACE). These fixes address the errors seen in both and posts and will make the transfer reliable and much faster.

Recommended Answers

All 3 Replies

Member Avatar for kvprajapati 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")



            Dim str As String = _
            "INSERT INTO [snippet]  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
            Dim cn As New OleDbConnection(str)
            Dim cmdd As New OleDbCommand(str, cn)
            cmdd.Parameters.Add(New OleDbParameter("@snippetName",OleDbType.VarChar, 50))        
            cmdd.Parameters.Add(New OleDbParameter("@snippetSource",OleDbType.VarChar, 50))

            cmdd.Parameters.Add(New OleDbParameter("@snippetCode",OleDbType.LongVarWChar))
             cn.Open()
           For Each row as DataRow in ds.Tables("snippet")
                cmdd.Parameters("@snippetName").Value = row("snippetName")
                cmdd.Parameters("@snippetSource").Value = row("snippetSource")
                cmdd.Parameters("@snippetCode").Value = row("snippetCode")
                cmdd.ExecuteNonQuery()
           Next
           cn.Close()
    End Sub
Member Avatar for kvprajapati 
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")



            Dim str As String = _
            "INSERT INTO [snippet]  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
            Dim cn As New OleDbConnection(str)
            Dim cmdd As New OleDbCommand(str, cn)
            cmdd.Parameters.Add(New OleDbParameter("@snippetName",OleDbType.VarChar, 50))        
            cmdd.Parameters.Add(New OleDbParameter("@snippetSource",OleDbType.VarChar, 50))

            cmdd.Parameters.Add(New OleDbParameter("@snippetCode",OleDbType.LongVarWChar))
             cn.Open()
           For Each row as DataRow in ds.Tables("snippet")
                cmdd.Parameters("@snippetName").Value = row("snippetName")
                cmdd.Parameters("@snippetSource").Value = row("snippetSource")
                cmdd.Parameters("@snippetCode").Value = row("snippetCode")
                cmdd.ExecuteNonQuery()
           Next
           cn.Close()
    End Sub
Member Avatar for meffe

Why dont you just create the SQL statement from Sql 2000 then execute it in Access

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.