error: too few parameters -- WHY?

R

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
 
G

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?
 
R

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)


GVaught said:
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?

Ronald Snelgrove said:
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
Click to expand...
Click to expand...
 
Top