Unable to INSERT data using a SELECT statement

Question

I have two tables; Student and promote_student. I want to copy data from Student to promote_student.

For this task, I have written this query:

str = "INSERT INTO [promote_student] 
            SELECT * 
            FROM [Student] 
            WHERE [SrNo] = " & row.Cells("sr_no").Value & " 
              AND [session] = " & from_session.Text & ""

cmd = New OleDb.OleDbCommand(str, cn)
da.SelectCommand = cmd
da.Fill(ds, "promote_student")

Here sr_no is a unique value and from_session is '2018-2019' but this query is not copying anything. I'm also not getting any errors.

score 0 · Answer 1 · answered Apr 27 '19 at 08:33

0

In this query your promote_student table columns and Student table columns need to be same if it's not your query give an error runtime. And please don't concatenate your SQL queries, use parameters for protecting Sql injection. And your query looks like :

  str = "INSERT INTO [promote_student] SELECT * FROM [Student] WHERE [SrNo]=@sr and [session]=@session"; 

                cmd.Parameters.AddWithValue("@sr",row.Cells("sr_no").Value)
                cmd.Parameters.AddWithValue("@session",row.Cells("sr_no").Value)
                cmd = New OleDb.OleDbCommand(str, cn)
                da.SelectCommand = cmd
                da.Fill(ds, "promote_student")

answered Apr 27 '19 at 08:33

Mert Akkanat

111
7

it sshould be noted also that he was using strings values and they were not properly surrounded by quotes in the original query. Which is fixed naturally with the bound parameters in your answer – Thomas G Apr 27 '19 at 10:21
2

Since MS Access is using OleDB (and not SQL Server's native client), it does not supported the **named** parameters like `@sr` or `@session`. OleDB only supports **positional** parameters, meaning the order in which the parameters are defined in the query text, and the order in which they are added to the `OleDbCommand`, must be identical - the *names* of the parameters are not relevant..... You could just as easily use `WHERE [SrNo] = ? AND [Session] = ?` in the query text and then use `@p1` and `@p2` when you specify the parameters. – marc_s Apr 27 '19 at 10:41
See [the official OleDbCommand.Parameters documentation](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=netframework-4.8) or [this excellent CodeProject article](https://www.codeproject.com/Tips/231217/Parameters-SqlCommand-vs-OledbCommand-and-OdbcComm) for more details on this – marc_s Apr 27 '19 at 10:43
Received this error System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.' on line da.Fill(ds, "promote_student") but there is already a value – John Apr 27 '19 at 11:30
In my mdb file in promote_student table there is automatic generated filed ID primary key auto number I have removed it 3-4 times and everytime it comes again when I reopen mdb file also last two fields positions are changed fields are session and SrNo – John Apr 27 '19 at 11:34
2

You are going to want to move the two Parameters lines after the `cmd = New OleDb.OleDbCommand(str,cn)` They're being assigned to the previous `OleDbCommand` and then a new one is being created and passed to the `OleDbDataAdapter` without defining parameters. – Charles May Apr 27 '19 at 12:05

Bugs · Answer 2 · 2021-03-20T14:54:57.657

If you want to insert data, you should be calling cmd.ExecuteNonQuery().

I'm also not convinced that SELECT * FROM [Student] is the best way to insert your data, especially if you have primary keys in place. Instead you want to specify the columns you want insert into with the columns you want to select from.

I would strongly consider using parameters to avoid SQL injection. See Bobby Tables for more information on this. I use ? as a placeholder for the parameters. With OleDbCommand it's important to note that it's not the names of the parameters but the order in which they are declared that is important. I specify the data type so consider using the OleDbParameter Constructor (String, OleDbType) to add your parameters.

Lastly, I would also consider implementing Using:

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

This is some sample code:

Using con As OleDbConnection = cn,
      cmd As New OleDbCommand("INSERT INTO [promote_student] (column1, column2, column3) SELECT column1, column2, column3 FROM [Student] WHERE [SrNo] = ? AND [session] = ?", con)
        con.Open()

        cmd.Parameters.Add("@srNo", OleDbType.[Type]).Value = row.Cells("sr_no").Value
        cmd.Parameters.Add("@session", OleDbType.[Type]).Value = from_session.Text

        cmd.ExecuteNonQuery()
End Using

Note that I don't know your table structure so have provided an example. You will have to change the column names to suit

Also note that I have used OleDbType.[Type]. You will want to replace [Type] with the data type you've used on your database.

Unable to INSERT data using a SELECT statement

2 Answers2