[RESOLVED] Problem in executing query

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

Code:

---

    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()

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

PHP Code:

---

"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.

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??/ :sick:

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

PHP Code:

---

"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.

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"

Quote:

---

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.

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

thanks for the tips techgnome!

Quote:

---

Originally Posted by 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.

PHP Code:

---

"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 :thumb: thanx again this forum rox :thumb:

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 :D