no value given for one or more required parameters

[orandov]

I am new to Access DB. I am trying to get data from Access DB in VB.NET
and I get this error: no value given for one or more required
parameters.

Here is my code:

sSql = "SELECT h.RO_DATE, Sum(h.CHARGE) AS SumOfCHARGE,
h.TECH_NO, t.TECH_NAME, Employee.EMPLOYEE_NO " _
& "FROM (HLabor AS h LEFT JOIN Tech AS t ON h.TECH_NO = t.TECH)
LEFT JOIN Employee AS e ON t.TECH_NAME = e.BNAME " _
& "WHERE h.RO_DATE >= #" & dtStartDate.ToShortDateString & "# " _
& "AND h.RO_DATE <= #" & dtEndDate.ToShortDateString & "# " _
& "GROUP BY h.RO_DATE, h.TECH_NO, t.TECH_NAME,
e.EMPLOYEE_NO " _
& "ORDER BY t.TECH_NAME DESC"

iRecCount = modMain.Access.GetDataTable(oDT, sSql)


Public Function GetDataTable(ByRef oDT As DataTable, ByVal sSQL As
String) As Integer
Dim iRetVal As Integer
Dim oOleDbConnection As OleDb.OleDbConnection = GetConnection()
Dim oOleDbCommand As OleDb.OleDbCommand
Dim oOleDbDataAdapter As OleDb.OleDbDataAdapter

oOleDbCommand = New OleDb.OleDbCommand
oOleDbCommand.Connection = oOleDbConnection
oOleDbCommand.CommandType = CommandType.Text
oOleDbCommand.CommandText = sSQL

oOleDbDataAdapter = New OleDb.OleDbDataAdapter(oOleDbCommand)
Try
oOleDbConnection.Open()
Catch err As OleDb.OleDbException
MessageBox.Show(err.Message)
End Try

Try
iRetVal = oOleDbDataAdapter.Fill(oDT)
Catch err1 As OleDb.OleDbException
MessageBox.Show(err1.Message)
End Try
oOleDbConnection.Close()

Return iRetVal
End Function


Any Suggestions?
Thank you,
Oran

 

[RoyVidar]

(e-mail address removed) wrote in message

I am new to Access DB. I am trying to get data from Access DB in
VB.NET and I get this error: no value given for one or more required
parameters.

Here is my code:

sSql = "SELECT h.RO_DATE, Sum(h.CHARGE) AS SumOfCHARGE,
h.TECH_NO, t.TECH_NAME, Employee.EMPLOYEE_NO " _
& "FROM (HLabor AS h LEFT JOIN Tech AS t ON h.TECH_NO =
t.TECH) LEFT JOIN Employee AS e ON t.TECH_NAME = e.BNAME
" _ & "WHERE h.RO_DATE >= #" & dtStartDate.ToShortDateString &
"# " _ & "AND h.RO_DATE <= #" & dtEndDate.ToShortDateString &
"# " _ & "GROUP BY h.RO_DATE, h.TECH_NO, t.TECH_NAME,
e.EMPLOYEE_NO " _
& "ORDER BY t.TECH_NAME DESC"

iRecCount = modMain.Access.GetDataTable(oDT, sSql)


Public Function GetDataTable(ByRef oDT As DataTable, ByVal sSQL As
String) As Integer
Dim iRetVal As Integer
Dim oOleDbConnection As OleDb.OleDbConnection =
GetConnection() Dim oOleDbCommand As OleDb.OleDbCommand
Dim oOleDbDataAdapter As OleDb.OleDbDataAdapter

oOleDbCommand = New OleDb.OleDbCommand
oOleDbCommand.Connection = oOleDbConnection
oOleDbCommand.CommandType = CommandType.Text
oOleDbCommand.CommandText = sSQL

oOleDbDataAdapter = New OleDb.OleDbDataAdapter(oOleDbCommand)
Try
oOleDbConnection.Open()
Catch err As OleDb.OleDbException
MessageBox.Show(err.Message)
End Try

Try
iRetVal = oOleDbDataAdapter.Fill(oDT)
Catch err1 As OleDb.OleDbException
MessageBox.Show(err1.Message)
End Try
oOleDbConnection.Close()

Return iRetVal
End Function


Any Suggestions?
Thank you,
Oran

Perhaps investigate a console.writeline of the sSql string?

Sometimes this message pops up due to typing errors in the where
condition, but also when missing parameters, or perhaps errors in
parameters.

A typical error, when passing dates, is the format of the dates. Access
expects US or ISO 8601 (or other unambiguous format), and I guess the
ToShortDateString method provides a date according to the locale?

There are probably more elegant methods of formatting, but I expect

dtStartDate.ToString("yyyy-MM-dd")

should ensure unambiguous date format, if that's the problem.

I haven't studied your SQL, but one tips there, I think is to open
Access and play with the SQL within the query tool there.

 

[orandov]

(e-mail address removed) wrote in message
<[email protected]> :

Perhaps investigate a console.writeline of the sSql string?

Sometimes this message pops up due to typing errors in the where
condition, but also when missing parameters, or perhaps errors in
parameters.

A typical error, when passing dates, is the format of the dates. Access
expects US or ISO 8601 (or other unambiguous format), and I guess the
ToShortDateString method provides a date according to the locale?

There are probably more elegant methods of formatting, but I expect

dtStartDate.ToString("yyyy-MM-dd")

should ensure unambiguous date format, if that's the problem.

I haven't studied your SQL, but one tips there, I think is to open
Access and play with the SQL within the query tool there.

Thank you for the suggestions. I ran the query in access and I realized
my error.
Oran