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

AhelblingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
AshokSr. Software EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AshokSr. Software EngineerCommented:
Imports System.Data.OleDb

Add above reference in order to use OleDB

Ashok
0
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
Are you trying to insert NEW RECORD or update existing record?

I think you want to INSERT new record.

Ashok
0
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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
AshokSr. Software EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.