Hi Folks:
The following is the complete coding for an application which I wrote to move data from sql 2000 to MS Access 2003. The coding looks fine to me but I am still getting an error.

Could someone tell me what I am doing wrong.

Thanks in advance

Bob Ghodsi

Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form
    Private ds As New DataSet
    Private dso As New DataSet
    Private cmd As New SqlCommand
    Private cmdo As New OleDbCommand
    Private da As New SqlDataAdapter
    Private dao As New OleDbDataAdapter
    Private Sub btnMove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMove.Click
        Dim cn As SqlConnection = New SqlConnection("Data Source=hp;Initial Catalog=library;integrated security=SSPI")
        Dim cnn As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data source=c:\temp\library")
        cmd = cn.CreateCommand
        cmd.CommandText = "select * from snippet order by snippetName"
        da.SelectCommand = cmd

        da.AcceptChangesDuringFill = False
        da.SelectCommand = New SqlCommand("SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet", cn)
        da.Fill(ds, "snippet")

        dao.InsertCommand = New OleDbCommand("INSERT INTO snippet(snippetID, snippetName, snippetSource," _
        & "snippetCode)Values(@snippetID,@snippetName, @snippetSource, @snippetCode)", cnn)

        dao.InsertCommand.Parameters.Add("@sippetID", OleDb.OleDbType.Integer, 0, "snippetID")
        dao.InsertCommand.Parameters.Add("@sippetName", OleDb.OleDbType.VarWChar, 50, "snippetName")
        dao.InsertCommand.Parameters.Add("@sippetSource", OleDb.OleDbType.VarWChar, 50, "snippetSource")
        dao.InsertCommand.Parameters.Add("@sippetCode", OleDb.OleDbType.LongVarWChar, 4000, "snippetCode")

        dao.UpdateCommand = New OleDbCommand("UPDATE snippet SET snippetID=sippetID," _
                                            & "SET snippetName=snippetName," _
                                            & "SET snippetSource=snippetSoure, SET snippetCode =snippetCode", cnn)
        dao.UpdateCommand.Parameters.Add("@snippetID", OleDb.OleDbType.Integer, 0, "snippetID")
        dao.UpdateCommand.Parameters.Add("@snippetName", OleDb.OleDbType.VarWChar, 50, "snippetName")
        dao.UpdateCommand.Parameters.Add("@snippetSource", OleDb.OleDbType.VarWChar, 50, "snippetSource")
        dao.UpdateCommand.Parameters.Add("@snippetName", OleDb.OleDbType.LongVarWChar, 4000, "snippetCode")

            dao.Update(ds, "c:\temp\snippet")


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

End Class

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

6 Years
Discussion Span
Last Post by finito

Welcome to the forums. Please use code taggings.

Can you please tell me where the error happens?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.