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