Problem with SELECT * INTO statement

Question

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 28-Apr-15 15:32pm

Member 11561472

Add a Solution

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!

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Abhinav S · Answer 1 · 2015-04-28T17:16:00

Solution 1

You need to create an OleDBCommand[^] first.

dbConFromDatabase.Open();
OleDbCommand command = new OleDbCommand(strSQL, dbConFromDatabase);
command.ExecuteNonQuery();

Posted 28-Apr-15 17:16pm

Abhinav S

Comments

Maciej Los 29-Apr-15 1:37am

+5

Abhinav S 29-Apr-15 1:51am

Thank you.

Member 11561472 · Answer 2 · 2015-04-28T22:41:00

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

Member 11561472 · Answer 3 · 2015-04-28T22:48:00

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

Problem with SELECT * INTO statement

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0