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

Please support our VB.NET advertiser: Programming Forums - DaniWeb Sister Site
Thread Solved
1 2 3

•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

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

 
0
  #11
Dec 12th, 2008
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:
VB.NET Syntax (Toggle Plain Text) 
  1. Dim MappedSelect As String
  2. Dim oView As DataView
  3. MappedSelect = "FieldName1 AS NewName1, FieldName2 AS NewName2, FieldName3, FieldName4"
  4. ExportTableData(SourceConnectionString, SourceTableName, oView, MappedSelect, "")
  5. ImportTableData(TargetConnectionString, TargetTableName, oView, MappedSelect, "")
or
VB.NET Syntax (Toggle Plain Text) 
  1. Dim MappedSelect As String
  2. Dim oView As DataView
  3. MappedSelect = "SrcFieldName1 AS NewName1, SrcFieldName2 AS NewName2, FieldName3, FieldName4"
  4. ExportTableData(SourceConnectionString, SourceTableName, oView, MappedSelect, "")
  5. MappedSelect = "DestFieldName1 AS NewName1, DestFieldName2 AS NewName2, FieldName3, FieldName4"
  6. 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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #12
Dec 15th, 2008
may i write this code on load....without changing the existing code.or i make changes accordingly.

VB.NET Syntax (Toggle Plain Text) 
  1. MappedSelect = "SrcFieldName1 AS NewName1, SrcFieldName2 AS NewName2, FieldName3, FieldName4"
in this code do i have write from table name also????
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

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

 
0
  #13
Dec 15th, 2008
•
•
•
•
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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #14
Dec 15th, 2008
sir not able to implement.....things ant working
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #15
Dec 15th, 2008
Plz see my code(EXPORT)
VB.NET Syntax (Toggle Plain Text) 
  1.  
  2. Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  3.        ByRef oView As DataView, _
  4.        ByVal SelectSQL As String, ByVal WhereSQL As String)
  5.  
  6.         'Export SQL table to data view
  7.         Try
  8.             strSQL = ""
  9.             oCmd = Nothing
  10.             oDA = Nothing
  11.             oConn = New SqlConnection("server=192.9.200.82;database=eKalyan;user id=invite;password=invite")
  12.             oCmd = oConn.CreateCommand()
  13.             If SelectSQL.Length = 0 Then
  14.                 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"
  15.                 '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"
  16.             Else
  17.                 strSQL = "SELECT " & SelectSQL & " FROM " & "ekalyan.welpen"
  18.             End If
  19.             If WhereSQL.Length > 0 Then
  20.                 strSQL = strSQL & " WHERE " & WhereSQL
  21.             End If
  22.  
  23.             '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"
  24.             oCmd.CommandText = strSQL
  25.             oDA = New SqlDataAdapter(oCmd)
  26.             oRS = New DataSet
  27.             oDA.Fill(oRS, "ekalyan.welpen")
  28.             oView = New DataView(oRS.Tables(0))
  29.             oConnOleDb.Close()
  30.             oConn = Nothing
  31.             oDA = Nothing
  32.             oRS = Nothing
  33.             oCmd = Nothing
  34.  
  35.             'ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, "", "")
  36.             MessageBox.Show("data exported Successfully")
  37.  
  38.  
  39.         Catch ex As Exception
  40.  
  41.         End Try
  42.  
  43.  
  44.  
  45.     End Sub

for import

VB.NET Syntax (Toggle Plain Text) 
  1.   Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal welfare_pen As String, _
  2.        ByRef oImportView As DataView, _
  3.        ByVal SelectSQL As String, ByVal WhereSQL As String)
  4.  
  5.         Try
  6.  
  7.  
  8.             strSQL1 = ""
  9.             oConnOleDb = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb")
  10.             oConnOleDb.Open()
  11.             oCmdOleDb = Nothing
  12.             oDAOleDb = Nothing
  13.             oCmdOleDb = oConnOleDb.CreateCommand()
  14.             If SelectSQL.Length = 0 Then
  15.                 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"
  16.             Else
  17.                 strSQL = "SELECT " & SelectSQL & " FROM " & welfare_pen
  18.  
  19.             End If
  20.             If WhereSQL.Length > 0 Then
  21.  
  22.  
  23.                 strSQL1 = strSQL1 & " WHERE " & WhereSQL
  24.             End If
  25.             strSQL1 = "SELECT * FROM welfare_pen"
  26.             oCmdOleDb.CommandText = strSQL1
  27.             oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
  28.             oRS1 = New DataSet
  29.             oDAOleDb.Fill(oRS1, "welfare_pen")
  30.             oView = New DataView(oRS.Tables(0))
  31.             oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
  32.             Dim oCmdBuilder As OleDbCommandBuilder
  33.             oCmdBuilder = New OleDbCommandBuilder(oDAOleDb)
  34.             oDAOleDb.Update(oView.Table)
  35.             oConnOleDb.Close()
  36.             oConnOleDb = Nothing
  37.             oDAOleDb = Nothing
  38.             oRS1 = Nothing
  39.             oCmdOleDb = Nothing
  40.  
  41.             'ImportTableData("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb", "welfare_pen", oView, "", "")
  42.             MessageBox.Show("data Imported Successfully")
  43.             DisplayCustomerCount()
  44.  
  45.         Catch ex As Exception
  46.  
  47.         End Try
  48.  
  49.  
  50.     End Sub

on export button click


VB.NET Syntax (Toggle Plain Text) 
  1. 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"
  2.  
  3.  
  4.         ExportTableData("server=192.9.200.82;database=eKalyan;user id=invite;password=invite", "ekalyan.welpen", oView, "MappedSelect", "")
  5.         DisplayrecordCount()


same for import
Last edited by cscgal; Dec 15th, 2008 at 6:11 pm. Reason: Added missing end-code tag
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

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

 
0
  #16
Dec 15th, 2008
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"
VB.NET Syntax (Toggle Plain Text) 
  1. 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()
VB.NET Syntax (Toggle Plain Text) 
  1. 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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #17
Dec 16th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 710
Reputation: Teme64 will become famous soon enough Teme64 will become famous soon enough 
Solved Threads: 115
Teme64's Avatar
Teme64 Teme64 is offline Offline
Master Poster

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

 
0
  #18
Dec 16th, 2008
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.
Teme64 @ Windows Developer Blog
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #19
Dec 16th, 2008
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
VB.NET Syntax (Toggle Plain Text) 
  1.  
  2. 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
VB.NET Syntax (Toggle Plain Text) 
  1. 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....
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2008
Posts: 24
Reputation: kritiohri is an unknown quantity at this point 
Solved Threads: 0
kritiohri kritiohri is offline Offline
Newbie Poster

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

 
0
  #20
Dec 22nd, 2008
sir....not able to do aliasing..if any help it would be highly appreciated.
Reply With Quote Quick reply to this message  
Reply
1 2 3

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the VB.NET Forum


Views: 3369 | Replies: 20
Thread Tools Search this Thread



Tag cloud for VB.NET
.net .net2008 2008 access advanced application array basic beginner browser button buttons center class click client code combo convert cuesent data database datagrid datagridview date datetimepicker design designer dissertation dissertations dissertationtopic eclipse excel exists fade filter forms function html images lib listview map mobile module msaccess net number objects open panel pdf picturebox picturebox2 port position print printing problem read refresh regex richtextbox right-to-left save search serial settings shutdown socket sorting sqldatbase sqlserver studio temperature textbox timer timespan transparency txttoxmlconverter usercontol validation vb vb.net vb2008 vba vbnet visual visualbasic visualbasic.net visualstudio.net visualstudio2008 web webbrowser winforms winsock wpf wrapingcode xml year
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC