Syntax error on INSERT INTO ... SELECT statement (Access SQL)

Question

I am trying to insert a field 'SID' Value into tbl1, But to get the SID It must reference the forename and surname to another table (tbl2) to get the SID, I then have used the following SQL Statement and subsequent code.

    Dim sqlquery As String = "INSERT INTO tblPayments (StudentID,Payment,PaymentDate) VALUES (SELECT StudentID FROM tblStudents WHERE Forename = @Forename AND Surname = @Surname , Payment = @SPaid, PaymentDate = @todaysdate)"

    Dim sqlcommand As New OleDbCommand
    With sqlcommand

        .CommandText = sqlquery

        .Parameters.AddWithValue("@SPaid", Paidtxt.Text)
        .Parameters.AddWithValue("@todaysdate", Today.Date)
        .Parameters.AddWithValue("@Forename", Forenametxt.Text)
        .Parameters.AddWithValue("@Surname", Surnametxt.Text)

        .Connection = conn

        .ExecuteNonQuery()
    End With
    MsgBox("query executed, closing connection")
    conn.Close()

Yet, the SQLQuery is giving the error :

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in query expression 'SELECT StudentID FROM tblStudents WHERE Forename = @Forename AND Surname = @Surname'.

But I can not see what is wrong with the part of the statement that is specified as wrong, can someone tell me where its wrong please?

Gordon Linoff · Accepted Answer · 2014-01-18 15:20:27Z

3

I think the error is very evident. The syntax for insert . . . select does not use the values keyword:

INSERT INTO tblPayments(StudentID, Payment, PaymentDate) 
    SELECT StudentID,  @SPaid, @todaysdate
    FROM tblStudents
    WHERE Forename = @Forename AND Surname = @Surname;

answered Jan 18, 2014 at 15:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OcelotcR Over a year ago

OH Of course, I apologies for the stupidity I rarely use SQL. Thank you for the speed response!

Collectives™ on Stack Overflow

Syntax error on INSERT INTO ... SELECT statement (Access SQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related