[RESOLVED] Problem in executing query

[007iceman]

hi i have the following query which inserts selected rows from one table into another based on a condition.I get the following error when i use the ExecuteReader() method to fire the query

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

         Dim con As OleDb.OleDbConnection
         Dim strinsert As String
         Dim cmdinsert As OleDb.OleDbCommand
        con = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=D:\master.mdb")

        con.Open()
         
strinsert = "insert into qtest(Qid,Question,Option A,Option B,Option C,Option D,Solution,Date Created) select * from Questionbank where Qid>3"
        cmdinsert = New OleDb.OleDbCommand(strinsert, con)
        cmdinsert.ExecuteReader()
        con.Close()

I get an error saying "Syntax Error in Insert Statement" & the following line gets highlighted

cmdinsert.ExecuteReader()

[Shuja Ali]

You are not reading anything from the database so you should be using ExecuteNonQuery method. And your Insert statement should look like this

"Insert Into qtest(Qid,Question,Option A,Option B,Option C,Option D,Solution," _ 
& "[Date Created]) Select Qid,Question,Option A,Option B,Option C,Option D,Solution,[Date Created] From Questionbank where Qid>3" 

Notice the square brackets in for the Date Created field and the list of columns in the Select query.

[007iceman]

It still does not work I made the changes as u told,n how do u say ders no query to the DB the select statement inside the insert does query the db doesnt it? So how can i use the ExecuteNonquery method??/

[Shuja Ali]

Well there are other fields too that have a space in their field names. All of them should be put in square brackets.

"Insert Into qtest(Qid,Question,[Option A],[Option B],[Option C]," _ 
& " [Option D],Solution,[Date Created]) Select Qid,Question,[Option A],[Option B],[Option C],[Option D],Solution,[Date Created] From Questionbank where Qid>3" 

Select is a subquery in this and Insert is the actual query, so you are not reading anything from the database that needs to be stored in the client side. You should execute it as NonQuery.

[juanmf]

do u really need to specify the fields? if the * in the SELECT statement has the same order and column count. shouldn´t it be:
strinsert = "insert into qtest select * from Questionbank where Qid>3"

mysql> insert into prestamos select * FROM PRESTAMOS WHERE NOMBRE LIKE "%JI%";
Query OK, 2 rows affected (0.00 sec)

it works too and have less probability of error.

[techgnome]

juanmf - intentions are in the right place, but not entirely correct. It is actualy SAFER to specify the field names. Two reasons... if a new field is added... or you don't want to copy over any ID fields....

The query as Shuja Ali has provided should work....

-tg

[juanmf]

thanks for the tips techgnome!

[007iceman]

Well there are other fields too that have a space in their field names. All of them should be put in square brackets.

"Insert Into qtest(Qid,Question,[Option A],[Option B],[Option C]," _ 
& " [Option D],Solution,[Date Created]) Select Qid,Question,[Option A],[Option B],[Option C],[Option D],Solution,[Date Created] From Questionbank where Qid>3" 

Select is a subquery in this and Insert is the actual query, so you are not reading anything from the database that needs to be stored in the client side. You should execute it as NonQuery.

Thanx Shuja Ali the code now worked like a charm & I was mistaken about NonQuery I made sum research on the net n found tht u were right indeed thanx again this forum rox

[Shuja Ali]

You are welcome.

If your problem is resolved, could you please Mark This thread resolved by opening Thread Tools and select Mark Thread as Resolved.

I spammed again