Ad:

VB.NET Discussion Thread
Marked Solved
Views: 6387

Page 1
2
3
Next

You are currently viewing page 1 of this multi-page discussion thread

Nov 26th, 2008

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

Expand Post »

VB.NET Syntax (Toggle Plain Text)
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        connetionString = ("server=192.9.200.82;database=eKalyan;user id=invite;password=invite")
 
 
 
 
 
 
 
 
        MessageBox.Show("connection established")
        sql = ("select newuniqueid,newteh_code,newpcate_cd,newplano, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code,pvillage,ppo_cd,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode from ekalyan.welpen")
 
        cnn = New SqlConnection(connetionString)
        Try
            cnn.Open()
            cmdd = New SqlCommand(sql, cnn)
            cmdd.ExecuteNonQuery()
 
            cnn.Close()
            MsgBox(" ExecuteNonQuery in SqlCommand executed !!")
 
        Catch ex As Exception
            MsgBox("cannot open the connection")
        End Try
 
        'Dim objdataadapter1 As New SqlDataAdapter("select Teh_code,Pcate_Cd,Wtng_No,Applicationdate,Pen_Nm,Rel_Cd1,Pen_Fnm,pan_code,Po_Cd,disabilitypercentage,Ccate_Cd,IndiviualsIncome,Village from ekalyan.WtngN", objconnection)
 
 
 
    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:\database1.mdb")
            cn.Open()
 
 
            str = "insert into Table1(newuniqueid,newteh_code,newpcate_cd,newplano, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code,pvillage,ppo_cd,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode) values (@newuniqueid,@newteh_code,@newpcate_cd,@newplano, @pen_nm,@rel_cd,@pen_fnm,@ccate_cd,@pan_code,@pvillage,@ppo_cd,@spo_cd,@start_qtr,@last_qtr,@bundle_typ,@bundle_no,@mo_srno,@pen_amt,@pen_comm,@status,@operatorcode)IN  'C:\Table1.mdb'"
            cmd = New OleDbCommand(str, cn)
            icount = cmd.ExecuteNonQuery
            cn = Nothing
            MessageBox.Show(icount)
 
        Catch
        End Try
        cn.Close()
    End Sub
End Class
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        connetionString = ("server=192.9.200.82;database=eKalyan;user id=invite;password=invite")








        MessageBox.Show("connection established")
        sql = ("select newuniqueid,newteh_code,newpcate_cd,newplano, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code,pvillage,ppo_cd,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode from ekalyan.welpen")

        cnn = New SqlConnection(connetionString)
        Try
            cnn.Open()
            cmdd = New SqlCommand(sql, cnn)
            cmdd.ExecuteNonQuery()

            cnn.Close()
            MsgBox(" ExecuteNonQuery in SqlCommand executed !!")

        Catch ex As Exception
            MsgBox("cannot open the connection")
        End Try

        'Dim objdataadapter1 As New SqlDataAdapter("select Teh_code,Pcate_Cd,Wtng_No,Applicationdate,Pen_Nm,Rel_Cd1,Pen_Fnm,pan_code,Po_Cd,disabilitypercentage,Ccate_Cd,IndiviualsIncome,Village from ekalyan.WtngN", objconnection)



    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:\database1.mdb")
            cn.Open()

       
            str = "insert into Table1(newuniqueid,newteh_code,newpcate_cd,newplano, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code,pvillage,ppo_cd,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode) values (@newuniqueid,@newteh_code,@newpcate_cd,@newplano, @pen_nm,@rel_cd,@pen_fnm,@ccate_cd,@pan_code,@pvillage,@ppo_cd,@spo_cd,@start_qtr,@last_qtr,@bundle_typ,@bundle_no,@mo_srno,@pen_amt,@pen_comm,@status,@operatorcode)IN  'C:\Table1.mdb'"
            cmd = New OleDbCommand(str, cn)
            icount = cmd.ExecuteNonQuery
            cn = Nothing
            MessageBox.Show(icount)

        Catch
        End Try
        cn.Close()
    End Sub
End Class

Similar Threads

Populating & Retrieving Data in a listbox : ASP.NET (w/ VB.NET) in ASP.NET
Simple ASP.Net Login Page using C# in C#
Updated : Simple ASP.Net Login Page in ASP.NET
ASP.Net Security 101 Part 1 in ASP.NET

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Permalink

Nov 26th, 2008

Re: how to import data from sql to ms access in vb.net through coding

I had a code which uses data view to transfer from Access to SQL Server. All you have to modify to transfer from SQL Server to Access, is to change OleDB* variables (objects) to Sql* variables (objects) and vice versa.

Read Access table to data view:

VB.NET Syntax (Toggle Plain Text)
Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  ByRef oView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
  '
  ' Export Access table to data view
  Dim strSQL As String
  Dim oRS As DataSet
  Dim oConnOleDb As OleDbConnection
  Dim oCmdOleDb As OleDbCommand
  Dim oDAOleDb As OleDbDataAdapter
 
  Try
    strSQL = ""
    oCmdOleDb = Nothing
    oDAOleDb = Nothing
    oConnOleDb = New OleDbConnection(ConnectionStr)
    oCmdOleDb = oConnOleDb.CreateCommand()
    If SelectSQL.Length = 0 Then
      strSQL = "SELECT * FROM " & TableName
    Else
      strSQL = "SELECT " & SelectSQL & " FROM " & TableName
    End If
    If WhereSQL.Length > 0 Then
      strSQL = strSQL & " WHERE " & WhereSQL
    End If
    ' Execute
    oCmdOleDb.CommandText = strSQL
    oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
    oRS = New DataSet
    oDAOleDb.Fill(oRS, TableName)
    oView = New DataView(oRS.Tables(0))
    oConnOleDb.Close()
    oConnOleDb = Nothing
    oDAOleDb = Nothing
    oRS = Nothing
    oCmdOleDb = Nothing
  Catch ex As Exception
 
  End Try
 
End Sub
Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  ByRef oView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
  '
  ' Export Access table to data view
  Dim strSQL As String
  Dim oRS As DataSet
  Dim oConnOleDb As OleDbConnection
  Dim oCmdOleDb As OleDbCommand
  Dim oDAOleDb As OleDbDataAdapter

  Try
    strSQL = ""
    oCmdOleDb = Nothing
    oDAOleDb = Nothing
    oConnOleDb = New OleDbConnection(ConnectionStr)
    oCmdOleDb = oConnOleDb.CreateCommand()
    If SelectSQL.Length = 0 Then
      strSQL = "SELECT * FROM " & TableName
    Else
      strSQL = "SELECT " & SelectSQL & " FROM " & TableName
    End If
    If WhereSQL.Length > 0 Then
      strSQL = strSQL & " WHERE " & WhereSQL
    End If
    ' Execute
    oCmdOleDb.CommandText = strSQL
    oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
    oRS = New DataSet
    oDAOleDb.Fill(oRS, TableName)
    oView = New DataView(oRS.Tables(0))
    oConnOleDb.Close()
    oConnOleDb = Nothing
    oDAOleDb = Nothing
    oRS = Nothing
    oCmdOleDb = Nothing
  Catch ex As Exception

  End Try

End Sub

And insert data view to SQL Server:

VB.NET Syntax (Toggle Plain Text)
Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  ByRef oImportView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
  '
  ' Import table from data view
  Dim strSQL As String
  Dim oConn As SqlConnection
  Dim oCmd As SqlCommand
  Dim oDA As SqlDataAdapter
  Dim oRS As DataSet
  Dim oView As DataView
 
  Try
    strSQL = ""
    oConn = New SqlConnection(ConnectionStr)
    oConn.Open()
    oCmd = Nothing
    oDA = Nothing
    oCmd = oConn.CreateCommand()
    If SelectSQL.Length = 0 Then
      strSQL = "SELECT * FROM " & TableName
    Else
      strSQL = "SELECT " & SelectSQL & " FROM " & TableName
    End If
    If WhereSQL.Length > 0 Then
      strSQL = strSQL & " WHERE " & WhereSQL
    End If
    ' Execute
    oCmd.CommandText = strSQL
    oDA = New SqlDataAdapter(oCmd)
    oRS = New DataSet
    oDA.Fill(oRS, TableName)
 
    oView = New DataView(oRS.Tables(0))
    ' Set rowstates so that rows are inserted
    oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
    ' The trick is to use CommandBuilder to create INSERT statement "automatically"
    Dim oCmdBuilder As SqlCommandBuilder
    oCmdBuilder = New SqlCommandBuilder(oDA)
    ' Finally call update to commit changes to database table
    oDA.Update(oView.Table)
 
    oConn.Close()
    oConn = Nothing
    oDA = Nothing
    oRS = Nothing
    oCmd = Nothing
  Catch ex As Exception
 
  End Try
 
End Sub
Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  ByRef oImportView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
  '
  ' Import table from data view
  Dim strSQL As String
  Dim oConn As SqlConnection
  Dim oCmd As SqlCommand
  Dim oDA As SqlDataAdapter
  Dim oRS As DataSet
  Dim oView As DataView

  Try
    strSQL = ""
    oConn = New SqlConnection(ConnectionStr)
    oConn.Open()
    oCmd = Nothing
    oDA = Nothing
    oCmd = oConn.CreateCommand()
    If SelectSQL.Length = 0 Then
      strSQL = "SELECT * FROM " & TableName
    Else
      strSQL = "SELECT " & SelectSQL & " FROM " & TableName
    End If
    If WhereSQL.Length > 0 Then
      strSQL = strSQL & " WHERE " & WhereSQL
    End If
    ' Execute
    oCmd.CommandText = strSQL
    oDA = New SqlDataAdapter(oCmd)
    oRS = New DataSet
    oDA.Fill(oRS, TableName)

    oView = New DataView(oRS.Tables(0))
    ' Set rowstates so that rows are inserted
    oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
    ' The trick is to use CommandBuilder to create INSERT statement "automatically"
    Dim oCmdBuilder As SqlCommandBuilder
    oCmdBuilder = New SqlCommandBuilder(oDA)
    ' Finally call update to commit changes to database table
    oDA.Update(oView.Table)

    oConn.Close()
    oConn = Nothing
    oDA = Nothing
    oRS = Nothing
    oCmd = Nothing
  Catch ex As Exception
     
  End Try

End Sub

And here's how you use it

VB.NET Syntax (Toggle Plain Text)
Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")
Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")

Just provide connection strings and table name(s). Naturally you should handle errors properly etc.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Nov 27th, 2008

Re: how to import data from sql to ms access in vb.net through coding

Where this code has to be written????

VB.NET Syntax (Toggle Plain Text)
Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")
Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Permalink

Nov 27th, 2008

Re: how to import data from sql to ms access in vb.net through coding

Okay, your Button1 reads data and Button2 writes data. If you want to keep it that way, set Dim oView As DataView as global variable in your class (form), like Private oView As DataView . Put ExportTableData(SourceConnectionString, SourceTableName, oView, "", "") in your Button1 and ImportTableData(TargetConnectionString, TargetTableName, oView, "", "") to Button2. Finally remember to modify my original code (see my previous reply for what and why) and provide proper values to connection strings and table names.

When I tested it (from SQL Server to Access in my case), I had this whole code

(Toggle Plain Text)

Dim oView As DataView
ExportTableData(SourceConnectionString, SourceTableName, oView, "", "")
ImportTableData(TargetConnectionString, TargetTableName, oView, "", "")

in one button's click event handler.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Nov 29th, 2008

Re: how to import data from sql to ms access in vb.net through coding

I want to ask when we are importing the data into sql why we are using select command in SQL.....it should only be used in MS access and in sql we should write a insert statement.....

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Permalink

Nov 29th, 2008

Re: how to import data from sql to ms access in vb.net through coding

just want to thank you..for helping me out!!!!I don know if we can put messages like this but from my heart iam really thankful for your help.

I have got the records in MS access....For further help il look forward to you!!!!

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Permalink

Dec 1st, 2008

Re: how to import data from sql to ms access in vb.net through coding

Hi! If your question is solved, please mark the thread as solved. Thank you!

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Dec 8th, 2008

Re: how to import data from sql to ms access in vb.net through coding

no doubts i have accomplished the task.I have given the names of coloumns in MS access same as that in the SQl table and then the data is imported successfully but i want to change the names of coloumns of ms access.can we do alising so that we can refer a coloumn name by another name also and getting the data imported successfully

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Permalink

Dec 8th, 2008

Re: how to import data from sql to ms access in vb.net through coding

Yes, you can do that. Change "SELECT * FROM" to "SELECT field1 AS NewName1, field2 AS NewName2... FROM". That will work.

There might be a more sophisticated way to do column mapping, but can't get any good ideas straight away in to my mind.

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Dec 12th, 2008

Re: how to import data from sql to ms access in vb.net through coding

do i need to write select fieldname as newfieldname ........in SQL part of the code only???coz i have tried and not able to import data

kritiohri

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

24 posts
since Nov 2008

Page 1
2
3
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: How to Delete a Record by searching that record in two tables

Next Thread in VB.NET Forum Timeline: Please Help Me Urgent Crystal Report

DaniWeb Message

Cancel Changes

User Name
Password