How to import data from sql to ms access in vb.net through coding

[bghodsi]

The following is the complete coding for moving data from sql to access,

I get an error saying that Microsoft JET Database Engine missing (;) at the end of sql statement!!!! Could you tell me what I am doing wrong?

Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmMain
Inherits System.Windows.Forms.Form
Private cnn As New SqlConnection
Private cmd As New SqlCommand
Private ConnectionString As String
Private sql As String
Private str As String
Private cmdd As New OleDbCommand
Private iCount As Integer

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ConnectionString = "Server=hp;database=library;integrated security=SSPI;"

MessageBox.Show("Connection established")

sql = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet"
Try
cnn = New SqlConnection(ConnectionString)

cnn.Open()
cmd = New SqlCommand(sql, cnn)
cmd.ExecuteNonQuery()

cnn.Close()
MsgBox("ExecutionNonQuery in SqlCommand executed")

Catch ex As Exception
MsgBox("Can not open the connection")
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tmp\lib.mdb")
cn.Open()

str = "insert into snip(snipID, snipName, snipSource, snipCode) values (@snipID, @snipName, " _
& "@snipSource, @snipCode)IN 'c:\tmp\snip.mdb'"

cmdd = New OleDbCommand(str, cn)
iCount = cmdd.ExecuteNonQuery
cn = Nothing
MessageBox.Show(iCount)

Catch ex As Exception

' MsgBox("Error: " & ex.Source & " " & ex.Message)

End Try

End Sub
End Class

Thanks

Bob Ghodsi

[murugavel84]

1)Hi dont use button2. Just use it as amethod and habing parameters.
2)In Button1_click event u did major mistake. Executenonquery should use only to insert, update and delete operations.
3) For select query u have to use reader or dataadapter to get the datas.
4) here i would suggest u to use dataset.
5) use dataset as the parameter of the insert method.
6) Use for each loop and insert records accordingly.

[bghodsi]

Hi:
I do not understand what you are trying to tell me. Below is the revised coding based on what I understood you suggested. Please be mot specific next time you write

Bob Ghodsi

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
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 strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\tmp\library.mdb;"
Try

Dim cn As New OleDbConnection(strConnection)
Dim Str As String = "insert into snippet(snippetID, snippetName, snippetSource, snippetCode) values (@snippetID, @snippetName, " _
& "@snippetSource, @snippetCode) IN 'c:\tmp\snippet.mdb'"

Dim cmdd As New OleDbCommand(Str, cn)
cmdd.CommandText = Str
Dim iCount As Integer
cn.Open()
iCount = cmdd.ExecuteNonQuery
Label1.Text = CStr(iCount)
cn.Close()
MessageBox.Show(iCount)

Catch ex As Exception

MsgBox("Error: " & ex.Source & " " & ex.Message)

End Try
End Sub