none
Error [07002][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2. RRS feed

  • Question

  • Hi,

    I am trying to delete the table TempWO and create it again with data in another table. Database used is MS Access.

    The Drop table works fine but while creating the table again from selected data of another table the subject error occurs. You can see I am not using any parameters and all field names are enclosed in square brackets. Also the query works fine if paste it in the MS Access database new query.

    With Best Regards

    Rajdip


    Private Sub cmdUpdateWOPW_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdateWOPW.Click
       Dim myCnn As New Odbc.OdbcConnection("dsn=Salary")
       Dim myCmd As New Odbc.OdbcCommand("Drop Table TempWO", myCnn)
       myCnn.Open()
       Try
           myCmd.ExecuteNonQuery()

           myCmd.CommandText = "SELECT Count([Muster" & _
           Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]), [Muster" & _
           Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode] Into TempWO FROM Muster" & _
           Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & " WHERE ((([Muster" & _
           Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]) In  (""WO"",""PW""))) GROUP BY[Muster" & _
           Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode]"

    'Resulting Query will look like this "SELECT Count([Muster082009].[Status]), [Muster082009].[EmployeeCode] Into TempWO FROM Muster082009 WHERE ((([Muster082009].[Status]) In  ("WO","PW"))) GROUP BY [Muster082009].[EmployeeCode]"

                myCmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(myCmd.CommandText)
                MsgBox(ex.Message)
            Finally
                myCnn.Close()
            End Try
    • Edited by Rajdip Wednesday, September 2, 2009 10:36 AM
    Wednesday, September 2, 2009 10:27 AM

Answers

  • i think WO and PW values should be enclosed into single quotes, like

    myCmd.CommandText = "SELECT Count([Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]), [Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode] Into TempWO FROM Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & " WHERE ((([Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]) In  ('WO','PW'))) GROUP BY [Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode]"


    if that does not help, try to print the actual constructed SQL statement and execute it in Access to see if there are no issues, or you could post it here
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Rajdip Wednesday, September 2, 2009 10:38 AM
    Wednesday, September 2, 2009 10:31 AM
    Moderator

All replies

  • i think WO and PW values should be enclosed into single quotes, like

    myCmd.CommandText = "SELECT Count([Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]), [Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode] Into TempWO FROM Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & " WHERE ((([Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[Status]) In  ('WO','PW'))) GROUP BY [Muster" & _
                Format(dtpSalaryMonth.Value.Month, "00") & Format(dtpSalaryMonth.Value.Year, "0000") & "].[EmployeeCode]"


    if that does not help, try to print the actual constructed SQL statement and execute it in Access to see if there are no issues, or you could post it here
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Rajdip Wednesday, September 2, 2009 10:38 AM
    Wednesday, September 2, 2009 10:31 AM
    Moderator
  • Thanks a lot.
    Wednesday, September 2, 2009 10:39 AM