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")
        cn.Open()
        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")
        Try

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

            cn.Close()

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

End Class

Recommended Answers

All 3 Replies

Welcome to the forums. Please use code taggings.

Can you please tell me where the error happens?

how to access the data two day ago in sql commond

Ishuramjan, Please start a new thread and be specific.

Show some work

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.