Click here to Skip to main content
15,916,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Below is the VB.Net code I have so far to copy Table1 to a new Table2 within the same MS Access database. However, I get the error message with the line that says: dbConFromDatabase.Executenonquery strSQL, and the message is ExecuteNonQuery is not a member of System.Data.OleDbConnection. I am new to VB.Net. What is the statement that I need in order to execute the SQL SELECT * INTO... statement? Can someone help me correct this code so that it will run without an error? I will be eternally greatful!

VB
Module Module1

    Private Sub CopyTable(strFromDatabase As String, strTableName As String, strToDatabase As String)

        Dim strSQL As String
        Dim dbConFromDatabase As OleDb.OleDbConnection


        Try
            'open connection from database
            dbConFromDatabase = New OleDb.OleDbConnection
            dbConFromDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strToDatabase & ";Persist Security Info=False"
            dbConFromDatabase.Open()

            strSQL = "SELECT * INTO " & strTableName & " FROM " & strTableName & " IN '" & strFromDatabase & "';"


            dbConFromDatabase.Executenonquery strSQL

            dbConFromDatabase.Close()

            dbConFromDatabase = Nothing
        Catch ex As Exception



        End Try

    End Sub

End Module
Posted
Comments
Maciej Los 29-Apr-15 1:41am    
In addtition to Solution1 by Abhinav S[^], i need to warn you: Your code is completely SqlInjection[^] vulnerable!

You need to create an OleDBCommand[^] first.
dbConFromDatabase.Open();
OleDbCommand command = new OleDbCommand(strSQL, dbConFromDatabase);
command.ExecuteNonQuery();
 
Share this answer
 
Comments
Maciej Los 29-Apr-15 1:37am    
+5
Abhinav S 29-Apr-15 1:51am    
Thank you.
Thank you for your response Abhinav.

I changed my VB.Net code as you indicated in your post - see below. However, I get squiggly lines under the OleDbCommand.command = New OleDbCommand(strSQL, dbConFromDatabase) and the error message says: Command is not a member of System.Data.OleDbCommand.

There is also a squiggly line under the statement that says: Command.ExecuteNonQuery and the error message says: ExecuteNonQuery is not a member of String. I also added Imports System.Data.OleDb at the top of the module.

What am I doing wrong here? I would sure like to figure this out. Any help will be greatly appreciated.

Imports System.Data.OleDb

Module Module1

    Private Sub CopyTable(strFromDatabase As String, strTableName As String, strToDatabase As String)

        Dim strSQL As String
        Dim dbConFromDatabase As OleDb.OleDbConnection

        Try
            'open connection from database
            dbConFromDatabase = New OleDb.OleDbConnection
            dbConFromDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strToDatabase & ";Persist Security Info=False"
            dbConFromDatabase.Open()

            strSQL = "SELECT * INTO " & strTableName & " FROM " & strTableName & " IN '" & strFromDatabase & "';"

            OleDbCommand.Command() = New OleDbCommand(strSQL, dbConFromDatabase)
            Command.ExecuteNonQuery()

            dbConFromDatabase.Close()
            dbConFromDatabase = Nothing

        Catch ex As Exception
            MsgBox(Err)

        End Try
    End Sub
End Module
 
Share this answer
 
I am the original poster in this thread and in my last post the code got screwed up so I have copied it below. I still have the problems as stated in my prior post. Help will be greatly appreciated.

Imports System.Data.OleDb

Module Module1

    Private Sub CopyTable(strFromDatabase As String, strTableName As String, strToDatabase As String)

        Dim strSQL As String
        Dim dbConFromDatabase As OleDb.OleDbConnection

        Try
            'open connection from database
            dbConFromDatabase = New OleDb.OleDbConnection
            dbConFromDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strToDatabase & ";Persist Security Info=False"
            dbConFromDatabase.Open()

            strSQL = "SELECT * INTO " & strTableName & " FROM " & strTableName & " IN '" & strFromDatabase & "';"

            OleDbCommand.Command() = New OleDbCommand(strSQL, dbConFromDatabase)
            Command.ExecuteNonQuery()

            dbConFromDatabase.Close()
            dbConFromDatabase = Nothing

        Catch ex As Exception
            MsgBox(Err)

        End Try

    End Sub



End Module
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900