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

[kritiohri]

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

[Teme64]

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:

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:

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

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.

[kritiohri]

Where this code has to be written????

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

[Teme64]

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

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

in one button's click event handler.

[kritiohri]

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]

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!!!!

[Teme64]

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

[kritiohri]

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

[Teme64]

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.

[kritiohri]

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

[Teme64]

Yes, that should do. But you have to use the same field name aliases in both procedures.

There's a SelectSQL parameter in both procedures, you can use it, for example:

Dim MappedSelect As String
Dim oView As DataView
MappedSelect = "FieldName1 AS NewName1, FieldName2 AS NewName2, FieldName3, FieldName4"
ExportTableData(SourceConnectionString, SourceTableName, oView, MappedSelect, "")
ImportTableData(TargetConnectionString, TargetTableName, oView, MappedSelect, "")

or

Dim MappedSelect As String
Dim oView As DataView
MappedSelect = "SrcFieldName1 AS NewName1, SrcFieldName2 AS NewName2, FieldName3, FieldName4"
ExportTableData(SourceConnectionString, SourceTableName, oView, MappedSelect, "")
MappedSelect = "DestFieldName1 AS NewName1, DestFieldName2 AS NewName2, FieldName3, FieldName4"
ImportTableData(TargetConnectionString, TargetTableName, oView, MappedSelect, "")

In the latter case both different original field names are aliased to same names.

The point is, when dataviews are merged, they are merged by matching field names. As I probably stated before, there may be a better way to map field names. But the code above does the job anyway.

[kritiohri]

may i write this code on load....without changing the existing code.or i make changes accordingly.

MappedSelect = "SrcFieldName1 AS NewName1, SrcFieldName2 AS NewName2, FieldName3, FieldName4"

in this code do i have write from table name also????

[Teme64]

may i write this code on load....without changing the existing code.or i make changes accordingly.

You may put the code on the Load event. Just make sure you "map" field names, if you need to do that, before calling ExportTableData and/or ImportTableData. The point is, as I wrote before, merging data view objects is based on field names being the same.

in this code do i have write from table name also????

No, you don't have to. See my first post for the implementation of ExportTableData and ImportTableData procedures, especially their ByVal SelectSQL As String argument how it is used in the code.

[kritiohri]

sir not able to implement.....things ant working

[kritiohri]

Plz see my code(EXPORT)

Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
       ByRef oView As DataView, _
       ByVal SelectSQL As String, ByVal WhereSQL As String)

        'Export SQL table to data view
        Try
            strSQL = ""
            oCmd = Nothing
            oDA = Nothing
            oConn = New SqlConnection("server=192.9.200.82;database=eKalyan;user id=invite;password=invite")
            oCmd = oConn.CreateCommand()
            If SelectSQL.Length = 0 Then
                strSQL = "SELECT newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode  FROM " & "ekalyan.welpen"
                'strSQL = "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"
            Else
                strSQL = "SELECT " & SelectSQL & " FROM " & "ekalyan.welpen"
            End If
            If WhereSQL.Length > 0 Then
                strSQL = strSQL & " WHERE " & WhereSQL
            End If

            'strSQL = "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"
            oCmd.CommandText = strSQL
            oDA = New SqlDataAdapter(oCmd)
            oRS = New DataSet
            oDA.Fill(oRS, "ekalyan.welpen")
            oView = New DataView(oRS.Tables(0))
            oConnOleDb.Close()
            oConn = Nothing
            oDA = Nothing
            oRS = Nothing
            oCmd = Nothing

            'ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, "", "")
            MessageBox.Show("data exported Successfully")


        Catch ex As Exception

        End Try



    End Sub

for import

Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal welfare_pen As String, _
       ByRef oImportView As DataView, _
       ByVal SelectSQL As String, ByVal WhereSQL As String)

        Try


            strSQL1 = ""
            oConnOleDb = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb")
            oConnOleDb.Open()
            oCmdOleDb = Nothing
            oDAOleDb = Nothing
            oCmdOleDb = oConnOleDb.CreateCommand()
            If SelectSQL.Length = 0 Then
                strSQL1 = "SELECT newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode  FROM " & "welfare_pen"
            Else
                strSQL = "SELECT " & SelectSQL & " FROM " & welfare_pen

            End If
            If WhereSQL.Length > 0 Then


                strSQL1 = strSQL1 & " WHERE " & WhereSQL
            End If
            strSQL1 = "SELECT * FROM welfare_pen"
            oCmdOleDb.CommandText = strSQL1
            oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
            oRS1 = New DataSet
            oDAOleDb.Fill(oRS1, "welfare_pen")
            oView = New DataView(oRS.Tables(0))
            oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
            Dim oCmdBuilder As OleDbCommandBuilder
            oCmdBuilder = New OleDbCommandBuilder(oDAOleDb)
            oDAOleDb.Update(oView.Table)
            oConnOleDb.Close()
            oConnOleDb = Nothing
            oDAOleDb = Nothing
            oRS1 = Nothing
            oCmdOleDb = Nothing

            'ImportTableData("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb", "welfare_pen", oView, "", "")
            MessageBox.Show("data Imported Successfully")
            DisplayCustomerCount()

        Catch ex As Exception

        End Try


    End Sub

on export button click

MappedSelect = "SELECT newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode FROM " & "ekalyan.welpen"


        ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, "MappedSelect", "")
        DisplayrecordCount()

same for import

[Teme64]

Both procedures contained parameters for
1) connection strings (ConnectionStr)
2) table names (TableName)
3) field names (SelectSQL)
the reason for this was (or should have been) obvious :)

MappedSelect = "SELECT newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode FROM " & "ekalyan.welpen"

MappedSelect = "newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode "

i.e. just field names and/or aliases, no SQL statements, procedures will handle them.

ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, "MappedSelect", "")
DisplayrecordCount()

ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, MappedSelect, "")

"MappedSelect" should be MappedSelect of course to pass the value to procedure.

Put the breakpoint in the lines oCmdOleDb.CommandText = strSQL and oCmd.CommandText = strSQL , dump the strSQL variables and compare the field lists. Field names (or aliases of the fields) should be same on both procedures.

[kritiohri]

did not undrestand these lines
Put the breakpoint in the lines oCmdOleDb.CommandText = strSQL and oCmd.CommandText = strSQL , dump the strSQL variables and compare the field lists.

[Teme64]

Forget that ;) If you remove SELECT and FROM from the MappedSelect variable (procedures will handle those), pass it (without quotes!) to both (unmodified, original) procedures, does it work? If not, compare the value of the strSQL variable from both export and import procedures (same number of fields and same names). If you get some error message, post it here.

[kritiohri]

sir i have a confusion probably...we have used MappedSelect...for alising.that means i have to replace the vareiable strSQL with MappedSelect???
and remove the statement

strSQL1 = "SELECT newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode  FROM " & "welfare_pen"

with

MappedSelect = "newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode "MappedSelect = "newuniqueid as UNIQ_ID,newteh_code as TEH_CD,newpcate_cd as PCATE_CD,newplano as PLA_NO, pen_nm,rel_cd,pen_fnm,ccate_cd,pan_code as PAN_CD,pvillage as VILLAGE,ppo_cd as PO_CD,spo_cd,start_qtr,last_qtr,bundle_typ,bundle_no,mo_srno,pen_amt,pen_comm,status,operatorcode "

please help me out....

[kritiohri]

sir....not able to do aliasing..if any help it would be highly appreciated.

[Teme64]

Hi! Have you solved the problem?

If not, remember these two points:
- fields are merged by their name
- if both tables have fields with the same names, there's no point to create aliases for those field names

HTH

[bhagawatshinde]

Hi all,
i used the code posted by Teme64 but i got error like Disk or Network error.

How i can solve this.

[elie.karkafy]

how we can check for duplicates data and prevent inserting them to the database ?