• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

How to convert this VB6 ADO code to VB.Net ?

How do I convert this VB6 ADO code to VB.Net ?

TIA,
Andrew
Private Sub ListTables(ByVal db_name As String)
Dim statement As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

    Set conn = New ADODB.Connection
    
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Persist Security Info = False;" & _
        "Data Source=C:\Program Files\Helios11\Data\Link.mdb"
    conn.Open
 
    Set rs = conn.OpenSchema(adSchemaTables, _
        Array(Empty, Empty, Empty, "Table"))
        
        conn.Execute "insert into sales_code values ('XES','Elite Select Transfer','C',NULL,NULL,NULL,'S',1,2,0,0,'C',0,0,0,0,'C',NULL,'0','1','0','0','0','0','0','0','0','X','0',0,11,'0',0,'C',0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,'SELECT',0,#12/31/2025 0:00:00#,0,'0',NULL,'O',0,0,0,0,0,NULL,0,0,'0',NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,1,15,88.88,NULL,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,NULL,'C',NULL,'C',1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'C',NULL,0,0,0,0,0,0,0,0,0,'0',1,0,0,0,0,0,0,0,0,#6/10/2008 11:29:58#,NULL,0,NULL,NULL,0,NULL,0,NULL,0,0,0)"
        conn.Execute "insert into sales_code values ('CETR','Cancel Elite Transfer','V',NULL,NULL,NULL,'S',2,25,0,0,'C',0,0,0,0,'C',NULL,'0','1','0','0','0','0','0','0','0','C','0',0,0,'0',0,'C',0,'C',0,NULL,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,NULL,NULL,NULL,0,NULL,0,'0',NULL,'C',0,0,0,0,0,NULL,0,0,'0',NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,1,15,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,NULL,'C',NULL,'C',1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'C','ELITE',0,0,0,0,0,0,0,0,0,'0',1,0,0,0,0,0,0,0,0,#6/10/2008 11:31:09#,NULL,0,NULL,NULL,0,NULL,0,NULL,0,0,0)"
        conn.Execute "insert into sales_code values ('CESTR','Cancel Elite Select Trans','V',NULL,NULL,NULL,'S',2,25,0,0,'C',0,0,0,0,'C',NULL,'0','1','0','0','0','0','0','0','0','C','0',0,0,'0',0,'C',0,'C',0,NULL,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,395,NULL,0,'C',0,NULL,NULL,NULL,0,NULL,0,'0',NULL,'C',0,0,0,0,0,NULL,0,0,'0',NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,1,15,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,NULL,'C',NULL,'C',1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'C','SELECT',0,0,0,0,0,0,0,0,0,'0',1,0,0,0,0,0,0,0,0,#6/10/2008 11:31:55#,NULL,0,NULL,NULL,0,NULL,0,NULL,0,0,0)"
        conn.Execute "insert into sales_code values ('SCRATCH','Scratchers June 2008','C',NULL,NULL,NULL,'S',3,30,0,0,NULL,0,0,0,0,'C',NULL,'0','1','0','0','0','0','0','0','0','S','0',2,2,'0',NULL,'C',0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,NULL,0,NULL,0,'0',NULL,NULL,0,0,0,0,0,NULL,0,0,'0',NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,0,0,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,NULL,'C',NULL,'C',1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'C',NULL,0,0,0,0,0,0,0,0,0,'0',1,NULL,0,0,0,0,0,0,0,#6/10/2008 11:23:01#,NULL,0,NULL,NULL,0,NULL,0,NULL,0,0,0)"
        conn.Execute "insert into sales_code values ('XE','Elite Transfer ','C',NULL,NULL,NULL,'S',1,2,0,0,'C',0,0,0,0,'C',NULL,'0','1','0','0','0','0','0','0','0','X','0',0,11,'0',0,'C',0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,0,'C',0,NULL,NULL,'ELITE',0,#12/31/2025 0:00:00#,0,'0',NULL,'O',0,0,0,0,0,NULL,0,0,'0',NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,NULL,0,0,0,0,0,0,1,15,68.88,NULL,0,NULL,0,NULL,0,NULL,0,NULL,0,NULL,NULL,'C',NULL,'C',1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'C',NULL,0,0,0,0,0,0,0,0,0,'0',1,0,0,0,0,0,0,0,0,#6/10/2008 11:28:33#,NULL,0,NULL,NULL,0,NULL,0,NULL,0,0,0)"
           
    rs.Close
            
    conn.Close
    cmdRun.Visible = False
    lblRun.Caption = "Updates Complete!"
    cmdClose.Visible = True
End Sub

Private Sub cmdRun_Click()
cmdRun.Enabled = False
Call ListTables("Link.mdb")
End Sub

Open in new window

0
Ahelbling
Asked:
Ahelbling
  • 10
  • 4
1 Solution
 
CIGHNCommented:
0
 
AhelblingAuthor Commented:
Link to the DL no good. I was hoping someone could show me the difference actually. Especially regarding the connection string and executing multiple queries.
0
 
AshokCommented:
Private Sub ListTables(ByVal db_name As String)
  Dim statement As String
  Dim SQLStr As String
  Dim conn As OleDBConnection
  Dim cmd;

  conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Program Files\Helios11\Data\Link.mdb';Persist Security Info=False;")  
 
  conn.Open()

  SQLStr = "insert into sales_code vales(.......)"
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()

  SQLStr = "insert into sales_code vales(.......)"
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()

  SQLStr = "insert into sales_code vales(.......)"
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()

  SQLStr = "insert into sales_code vales(.......)"
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()

  SQLStr = "insert into sales_code vales(.......)"
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()
           
    conn.Close()
    conn.Dispose()
    cmdRun.Visible = False
    lblRun.Caption = "Updates Complete!"
    cmdClose.Visible = True
End Sub

HTH
Ashok

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
AshokCommented:
Imports System.Data.OleDb

Add above reference in order to use OleDB

Ashok
0
 
AshokCommented:
BTW

You may be able to insert multiple records in one statement like this.....

  SQLStr = "insert into sales_code vales(.......); " &
                 "insert into sales_code vales(.......); " &
                 "insert into sales_code vales(.......); " &
                 "insert into sales_code vales(.......); " &
                 "insert into sales_code vales(.......); "
  cmd = New oleDBCommand(SQLStr, conn);
  cmd.ExecuteNonQuery()

Try it and let me know if it works with MS-Access.

Ashok
0
 
AshokCommented:
I made a spelling mistake in my previous posts.

vales(.....)
should be
values(.....)

Ashok
0
 
AhelblingAuthor Commented:
Ashok, thanks for your responses thus far,

Get an error when try to combine the queries into one...

        SQLStr = "insert into Addresses values ('Jimmy','John','1111','STL','MO','01269') WHERE client_no = 1" & _
                    "insert into Addresses values ('Johhny','Rocket','2222','STL','MO','01269') WHERE client_no = 2"
        cmd = New OleDbCommand(SQLStr, conn)
        cmd.ExecuteNonQuery()

ERROR:
Syntax error (missing operator) in query expression 'client_no = 1insert into Addresses values ('Johhny','Rocket','2222','STL','MO','01269') WHERE client_no = 2'.

Andrew
0
 
AshokCommented:
SQLStr = "insert into Addresses (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (1, 'Jimmy','John','1111','STL','MO','01269')" & _
               "insert into Addresses  (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (2, 'Johhny','Rocket','2222','STL','MO','01269')"

Replace "FirstName, LastName, Street1, Street2, State, Zipcode" with
actual field names in your table.

You cannot use WHERE clause with INSERT statement.

HTH
Ashok
0
 
AshokCommented:
Are you trying to insert NEW RECORD or update existing record?

I think you want to INSERT new record.

Ashok
0
 
AshokCommented:
You need to make sure there is a space between each word so my previous post, I forgot one space on 2nd line. BTW one extra space on last line does not hurt.

CORRECT IS AS FOLLOWS.....

SQLStr = "insert into Addresses (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (1, 'Jimmy','John','1111','STL','MO','01269') " & _
               "insert into Addresses  (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (2, 'Johhny','Rocket','2222','STL','MO','01269') "

Replace "FirstName, LastName, Street1, Street2, State, Zipcode" with
actual field names in your table.

HTH
Ashok
0
 
AshokCommented:
You need to make sure there is a space between each word so my previous post, I forgot one space on 2nd line. BTW one extra space on last line does not hurt.  I think you also need semicolon at the end of each statement.

CORRECT IS AS FOLLOWS.....

SQLStr = "insert into Addresses (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (1, 'Jimmy','John','1111','STL','MO','01269'); " & _
               "insert into Addresses  (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (2, 'Johhny','Rocket','2222','STL','MO','01269'); "

Replace "FirstName, LastName, Street1, Street2, State, Zipcode" with
actual field names in your table.

HTH
Ashok
0
 
AshokCommented:
If multiple INSERTs does not work, separate them using similar approach as in my previous answer 30966475.
I hope multiple INSERTs would work.

Ashok
0
 
AhelblingAuthor Commented:
Gotcha, changed it up and now get this error

{"Characters found after end of SQL statement."}
 SQLStr = "insert into Addresses (client_no, first_name, last_name, Address, City, State, Zip)" & _
               " values (1, 'Jimmy','John','1111','STL','MO','01269'); " & _
               "insert into Addresses  (client_no, first_name, last_name, Address, City, State, Zip)" & _
               " values (2, 'Johhny','Rocket','2222','STL','MO','01269'); "

        cmd = New OleDbCommand(SQLStr, conn)

Open in new window

0
 
AhelblingAuthor Commented:
and I found a few references to this:

Access queries cannot execute more than one statement (unlike SQL Server)

You could do the equivalent in VBA code...

CurrentDB.Execute "INSERT INTO...", dbFailOnError

CurrentDB.Execute "INSERT INTO...", dbFailOnError

CurrentDB.Execute "INSERT INTO...", dbFailOnError
0
 
AshokCommented:
Private Sub ListTables(ByVal db_name As String)
  Dim statement As String
  Dim SQLStr As String
  Dim conn As OleDBConnection
  Dim cmd;

  conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Program Files\Helios11\Data\Link.mdb';Persist Security Info=False;")  
 
  conn.Open()

  SQLStr = "insert into Addresses (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (1, 'Jimmy','John','1111','STL','MO','01269')"
  cmd = New oleDBCommand(SQLStr, conn)
  cmd.ExecuteNonQuery()

  SQLStr = "insert into Addresses  (Client_No, FirstName, LastName, Street1, Street2, State, Zipcode)" &_
               " values (2, 'Johhny','Rocket','2222','STL','MO','01269')"
  cmd = New oleDBCommand(SQLStr, conn)
  cmd.ExecuteNonQuery()
 
'  Do samething for more records.....
         
    conn.Close()
    conn.Dispose()
    cmdRun.Visible = False
    lblRun.Caption = "Updates Complete!"
    cmdClose.Visible = True
End Sub

HTH
Ashok

HTH
Ashok
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now