Missing Select Keyword for Insert Statement

Question

I have the following code in VBA and whenever I try to run it, I get an error message saying "missing select keyword".

I don't see why it even wants a select keyword when I don't want to read anything, I only want to write to the database:

Private Sub SubmitButton_Click(sender As System.Object, e As System.EventArgs) Handles          SubmitButton.Click
    SubmitButton.Enabled = False

    DeliveryDateTimePicker.CustomFormat = "yyyyMMdd"
    Dim sysdate As Date = Date.Now
    Dim XConnStr As String = "DSN=ivr.dsn; SERVER=ivr.dsn.world; UID=user; PWD=password;"

    Dim SQLString As String = "INSERT INTO sv.ivr_resched ([ORDER_NO], [PHONE_NO], [REQUESTED_DELIVERY_DATE], [RESCHEDULE_REASON], [CREATE_DATE], [CREATE_USERID], [IN_PROGRESS], [STATUS]) VALUES (@ORDER_NO, @PHONE_NO, @REQUESTED_DELIVERY_DATE, @RESCHEDULE_REASON, @CREATE_DATE, @CREATE_USERID, @IN_PROGRESS, @STATUS);"
    Dim objXConn As New OdbcConnection(XConnStr)
    Dim objCommand As New OdbcCommand(SQLString, objXConn)

    objCommand.Parameters.AddWithValue("@ORDER_NO", MSTextBox.Text)
    objCommand.Parameters.AddWithValue("@PHONE_NO", PhoneTextBox.Text)
    objCommand.Parameters.AddWithValue("@REQUESTED_DELIVERY_DATE", DeliveryDateTimePicker.Value)
    objCommand.Parameters.AddWithValue("@RESCHEDULE_REASON", ReasonComboBox.SelectedValue)
    objCommand.Parameters.AddWithValue("@CREATE_DATE", sysdate)
    objCommand.Parameters.AddWithValue("@CREATE_USERID", TransferID)
    objCommand.Parameters.AddWithValue("@IN_PROGRESS", "N")
    objCommand.Parameters.AddWithValue("@STATUS", "newrecrd")
    Try
        Using objXConn
            objXConn.Open()
            objCommand.ExecuteNonQuery()
            objXConn.Close()
        End Using
        MsgBox("Success")
        MSTextBox.Text = ""
        PhoneTextBox.Text = ""
        ReasonComboBox.SelectedValue = ""
        DeliveryDateTimePicker.Value = Date.Now
    Catch ex As Exception
        MessageBox.Show("Error: " & ex.ToString)
    End Try

    SubmitButton.Enabled = True

End Sub

Any help you can provide will be appreciated.

Issue has been resolved, here is the working code:

    Private Sub SubmitButton_Click(sender As System.Object, e As System.EventArgs) Handles SubmitButton.Click
    SubmitButton.Enabled = False
    Dim sysdate As Date = Date.Now
    Dim XConnStr As String = "DSN=ivr.dsn; SERVER=ivr.dsn.world; UID=user; PWD=password;"
    Dim SQLString As String = "INSERT INTO sv.ivr_resched (ORDER_NO, PHONE_NO, REQUESTED_DELIVERY_DATE, RESCHEDULE_REASON, CREATE_DATE, CREATE_USERID, IN_PROGRESS, STATUS) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
    Dim objXConn As New OdbcConnection(XConnStr)
    Dim objCommand As New OdbcCommand(SQLString, objXConn)

    objCommand.Parameters.Add("ORDER_NO", OdbcType.Char)
    objCommand.Parameters.Item("ORDER_NO").Value = MSTextBox.Text
    objCommand.Parameters.Add("PHONE_NO", OdbcType.Char)
    objCommand.Parameters.Item("PHONE_NO").Value = PhoneTextBox.Text
    objCommand.Parameters.Add("REQUESTED_DELIVERY_DATE", OdbcType.Char)
    objCommand.Parameters.Item("REQUESTED_DELIVERY_DATE").Value = DeliveryDateTimePicker.Value.ToString("yyyMMdd")
    objCommand.Parameters.Add("RESCHEDULE_REASON", OdbcType.Char)
    objCommand.Parameters.Item("RESCHEDULE_REASON").Value = ReasonComboBox.SelectedValue
    objCommand.Parameters.Add("CREATE_DATE", OdbcType.Date)
    objCommand.Parameters.Item("CREATE_DATE").Value = sysdate
    objCommand.Parameters.Add("CREATE_USERID", OdbcType.Char)
    objCommand.Parameters.Item("CREATE_USERID").Value = TransferID
    objCommand.Parameters.Add("IN_PROGRESS", OdbcType.Char)
    objCommand.Parameters.Item("IN_PROGRESS").Value = "N"
    objCommand.Parameters.Add("STATUS", OdbcType.Char)
    objCommand.Parameters.Item("STATUS").Value = "NEWREC"

    Try
        Using objXConn
            objXConn.Open()
            objCommand.ExecuteNonQuery()
            objXConn.Close()
        End Using
        MsgBox("Success")
        MSTextBox.Text = ""
        PhoneTextBox.Text = ""
        ReasonComboBox.SelectedValue = ""
        DeliveryDateTimePicker.Value = Date.Now
    Catch ex As Exception
        MessageBox.Show("Error: " & ex.ToString)
    End Try

    SubmitButton.Enabled = True

End Sub

Your `INSERT` statement looks solid. The only thing I see that's different from what I'd do is that you have a semicolon at the end of the statement. See if removing it helps. — Ed Gibbs, Nov 04 '14 at 23:00
I did try removing the semicolon and that did not make a difference, thanks for the advice though. — Sean, Nov 05 '14 at 16:01

score 1 · Accepted Answer · answered Nov 04 '14 at 23:03

1

Try removing the brackets when you enumerate the column names:

Dim SQLString As String = "INSERT INTO sv.ivr_resched (ORDER_NO, ...

answered Nov 04 '14 at 23:03

Shawn Mire

306
2
5

When I remove the brackets I get a "missing expression" error message. I believe one of my column names are not accepted by itself. (I believe it is the Status column). – Sean Nov 04 '14 at 23:07
@Sean I have a suspicion your issue now is with the @ sign. Try replacing them with a colon. Based on what I read [here](https://community.oracle.com/thread/383481?tstart=0). – Shawn Mire Nov 05 '14 at 00:51
Read through the thread you posted and tried both solutions that it mentioned, the use of colons and the not using of the "objcommand.parameters.addwithvalue" and both of these still gave the "missing select keyword" – Sean Nov 05 '14 at 14:35
@Sean So with the brackets gone and the @ signs gone, you're back to getting the "missing select keyword" error? Can you provide the updated SQLString? – Shawn Mire Nov 05 '14 at 15:48
The brackets aren't gone because one of my columns (I believe the status one) is using a name that is also a keyword for something else. The following is what it looks like now: – Sean Nov 05 '14 at 16:04
Dim SQLString As String = "INSERT INTO sv.ivr_homedelivery_resched_req ([ORDER_NO], [PHONE_NO], [REQUESTED_DELIVERY_DATE], [RESCHEDULE_REASON], [CREATE_DATE], [CREATE_USERID], [IN_PROGRESS], [STATUS]) VALUES(:ORDER_NO, :PHONE_NO, :REQUESTED_DELIVERY_DATE, :RESCHEDULE_REASON, :CREATE_DATE, :CREATE_USERID, :IN_PROGRESS, :STATUS)" – Sean Nov 05 '14 at 16:05
objCommand.Parameters.AddWithValue("ORDER_NO", MSTextBox.Text) objCommand.Parameters.AddWithValue("PHONE_NO", PhoneTextBox.Text) objCommand.Parameters.AddWithValue("REQUESTED_DELIVERY_DATE", DeliveryDateTimePicker.Value) objCommand.Parameters.AddWithValue("RESCHEDULE_REASON", ReasonComboBox.SelectedValue) objCommand.Parameters.AddWithValue("CREATE_DATE", sysdate) objCommand.Parameters.AddWithValue("CREATE_USERID", TransferSSO) objCommand.Parameters.AddWithValue("IN_PROGRESS", "N") objCommand.Parameters.AddWithValue("STATUS", "newrecrd") – Sean Nov 05 '14 at 16:06
@Sean The missing select error is caused by the brackets. Brackets can be used with SQL Server but not with Oracle. When I try an insert with brackets directly on an Oracle database, I get the same error, and when I remove the brackets the insert works fine. "STATUS" isn't an Oracle keyword, but if it were it would be handled via quotes from [this](http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle) thread. Can you try without the brackets and with the colons in place of the @ signs? – Shawn Mire Nov 05 '14 at 16:21
Definitely try the suggestion from @shawnmire. My comment above mistook this for a SQL Server question because of the @ sign and the square brackets. In Oracle, it's colon and double quotes as Shawn says. One additional difference: when an Oracle identifier is in double quotes it's case sensitive, so if you get a "column doesn't exist" type of message make sure the name matches by case. That said, none of your column names (including status) are [Oracle reserved words](http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm) – Ed Gibbs Nov 05 '14 at 16:59
Removing the brackets and the @ signs seems to have made a difference. The new error message I have reads "not all variables bound". – Sean Nov 05 '14 at 17:08
@Sean The new error is likely due to the OLEDB data provider being used, as mentioned in the [link](https://community.oracle.com/thread/383481?tstart=0) I provided earlier. Also see this [thread](http://stackoverflow.com/questions/13122243/inserting-values-in-a-oracle-databse-using-oledb-in-vb-net-vs-2008). – Shawn Mire Nov 05 '14 at 18:59
Thanks guys for all the assistance! Everything is running now and I will post the updated code, and yes it is very similar to the thread you posted 44 minutes ago. – Sean Nov 05 '14 at 19:45

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

Try using double quotes instead of brackets around the field names. See the answer to java.sql.SQLException: ORA-00928: missing SELECT keyword. when inserting record to DB using JDBC.

Dim SQLString As String = "INSERT INTO sv.ivr_resched (""ORDER_NO"", ""PHONE_NO"", ""REQUESTED_DELIVERY_DATE"", ""RESCHEDULE_REASON"", ""CREATE_DATE"", ""CREATE_USERID"", ""IN_PROGRESS"", ""STATUS"") VALUES (@ORDER_NO, @PHONE_NO, @REQUESTED_DELIVERY_DATE, @RESCHEDULE_REASON, @CREATE_DATE, @CREATE_USERID, @IN_PROGRESS, @STATUS);"

edited May 23 '17 at 11:49

Community

1
1

answered Nov 04 '14 at 23:51

gmm

478
3
13

Double quotes do not work the same way in VBA as they do in Java. I tried this and I got the "missing expression" error message due to one of my columns being the same name as a reserved word. – Sean Nov 05 '14 at 14:23

Missing Select Keyword for Insert Statement

2 Answers2