error: too few parameters -- WHY?

[Ronald Snelgrove]

To All:

I am fairly new to both VB.net and Access and have a question regarding the
combination of these via ODBC. The code in question is at the end of the
message.

I have a procedure with which I am connecting to an Access2000 database and
counting the number of records in table 'mc' that match my criteria. The
sql statement (TheSQL) that is passed through an ODBC connection to the
database returned the proper result when it consisted of only the first two
elements (MC_DATE and MC_TIME). When the third element (SERIAL) is added,
the following error message is displayed:

Any ideas of why and how to overcome this?

Thanks in advance,
Ron Snelgrove

======================
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 4.
======================


Sub MyProcedure()
Dim conn As OdbcConnection
Dim command As OdbcCommand
Dim TheSQL As String
Dim NumRecords As Int16
Dim TheDate, TheTime As DateTime

TheDate = CDate("4/29/2005")
TheTime = CDate("8:22 am")

conn = New OdbcConnection("DSN=MorningCheckoutDB")
command = conn.CreateCommand()
conn.Open()

' ERROR
TheSQL = "SELECT COUNT(*) FROM mc WHERE (MC_DATE = ?) AND _
(MC_TIME = ?) AND (SERIAL = ?)"

' CORRECT RESULT
'TheSQL = "SELECT COUNT(*) FROM mc WHERE (MC_DATE = ?)"

command.CommandText = TheSQL
command.Parameters.Add("@1", TheDate.ToString("yyyy/MM/dd"))
command.Parameters.Add("@2", TheTime.ToString("hh:mm"))
command.Parameters.Add("@3", 2158)

Console.WriteLine(TheDate.ToString("yyyy/MM/dd"))
Console.WriteLine(TheTime.ToString("hh:mm"))
Console.WriteLine(TheSQL)

Try
NumRecords = command.ExecuteScalar
Catch ex As OdbcException
Console.WriteLine(ex.Message)
End Try

command.Parameters.Clear()

Console.WriteLine("Number of matching records = " &
CStr(NumRecords))
conn.Close()

End Sub

 

[GVaught]

Where is the reference to Serial? You Dim Date and Time and add criteria,
but I don't see any criteria for Serial? Where is that data coming from?

 

[Scott M.]

This is a newsgroup for .NET Data Access, not MS Access.

 

[Ronald Snelgrove]

Hmmm. I was under the impression that the third "Parameters.Add" (see
below) statement was sufficient (I did warn everyone that I'm new to both
VB.net and Access -- I have more experience in the Unix world

command.Parameters.Add("@3", 2158)

Where is the reference to Serial? You Dim Date and Time and add criteria,
but I don't see any criteria for Serial? Where is that data coming from?

To All:

I am fairly new to both VB.net and Access and have a question regarding the
combination of these via ODBC. The code in question is at the end of the
message.

I have a procedure with which I am connecting to an Access2000 database and
counting the number of records in table 'mc' that match my criteria. The
sql statement (TheSQL) that is passed through an ODBC connection to the
database returned the proper result when it consisted of only the first two
elements (MC_DATE and MC_TIME). When the third element (SERIAL) is added,
the following error message is displayed:

Any ideas of why and how to overcome this?

Thanks in advance,
Ron Snelgrove

======================
ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 4.
======================


Sub MyProcedure()
Dim conn As OdbcConnection
Dim command As OdbcCommand
Dim TheSQL As String
Dim NumRecords As Int16
Dim TheDate, TheTime As DateTime

TheDate = CDate("4/29/2005")
TheTime = CDate("8:22 am")

conn = New OdbcConnection("DSN=MorningCheckoutDB")
command = conn.CreateCommand()
conn.Open()

' ERROR
TheSQL = "SELECT COUNT(*) FROM mc WHERE (MC_DATE = ?) AND _
(MC_TIME = ?) AND (SERIAL = ?)"

' CORRECT RESULT
'TheSQL = "SELECT COUNT(*) FROM mc WHERE (MC_DATE = ?)"

command.CommandText = TheSQL
command.Parameters.Add("@1", TheDate.ToString("yyyy/MM/dd"))
command.Parameters.Add("@2", TheTime.ToString("hh:mm"))
command.Parameters.Add("@3", 2158)

Console.WriteLine(TheDate.ToString("yyyy/MM/dd"))
Console.WriteLine(TheTime.ToString("hh:mm"))
Console.WriteLine(TheSQL)

Try
NumRecords = command.ExecuteScalar
Catch ex As OdbcException
Console.WriteLine(ex.Message)
End Try

command.Parameters.Clear()

Console.WriteLine("Number of matching records = " &
CStr(NumRecords))
conn.Close()

End Sub