Runtime Error, pls help

[styleest]

I'm an intermediate vb programmer, i need help . I wrote a code that'll fetch a particular employee id (EM_ID) and print the report but its saying this whenever i compile and run.

Runtime Error 2147217904 [80040e10] [ODBC Microsoft Access Driver] Too few parameters

The code is below

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & Me.lvwInfo.SelectedItem.Text & " GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction having Month(dDate)='" & Month(Me.dt1.Value) & "' And Day(dDate)='" & Day(Me.dt1.Value) & "' And Year(dDate)='" & Year(Me.dt1.Value) & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

[AndreRet]

The error message "Too few parameters" simply means you are not passing the appropriate parameters with your SQL statement OR the tablename / fieldname used doesn't exist in the database you were trying to connect.

Change your sql statement at this part -

"FROM tblPayroll WHERE EM_ID =" & Me.lvwInfo.SelectedItem.Text & " GROUP BY

to

"FROM tblPayroll WHERE EM_ID =" & "'" & Me.lvwInfo.SelectedItem.Text & "' GROUP BY

Have a look at your field structure EM_ID. Is it set up to receive only integers? If text is added to the field that can only accept numbers, same error will show.

Finally, the problem might be with your report. Check the column headers as well.

[styleest]

Thanks but it did not solve the problem. If i put in this code, it prints all the report
rs.Open "Select * from tblPayroll where Month(dDate)='" & Month(Me.dtPick.Value) & "' And Year(dDate)='" & Year(Me.dtPick.Value) & "'ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

But, when i want it to print a particular report, i put in this code and its the one not working.

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "" & Me.lvwInfo.SelectedItem.Text & " GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction having Month(dDate)='" & Month(Me.dt1.Value) & "' And Day(dDate)='" & Day(Me.dt1.Value) & "' And Year(dDate)='" & Year(Me.dt1.Value) & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

pls i need your help

[AndreRet]

You have an incorrect statement still -

"FROM tblPayroll WHERE EM_ID =" & "" & Me.lvwInfo.SelectedItem.Text & " GROUP BY

Have a look at the ' in between brackets -

"FROM tblPayroll WHERE EM_ID =" & "'" & Me.lvwInfo.SelectedItem.Text & "' GROUP BY

[styleest]

Thanks for your help, i've used it still, its not working. Please i dont know if you can help me with another code that will fetch a particular person's information from a list of many information or can i attach my project so u'll see it and help me rectify the problem?
THanks for your help. Its a nice forum

[styleest]

pls i need your email to forward a copy of the project to you.

[AndreRet]

Copy and post the code exactly as it is below. Test it and let me know if it worked for you.

Dim MyDate As Date = Me.dt1.Value
Dim MyString As String = Me.lvwInfo.SelectedItem.Text

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "'" & MyString & "' GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction HAVING Month(dDate)='" & MyDate & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenStatic, adLockOptimistic

[styleest]

Thanks but it still din't work, can u pls send me ur email to send the full project as a zip file so u'll see it well

[AndreRet]

See your private messages...

[styleest]

I just read thru the forum rules and discovered , we can't receive or send mails for program solving purpose. I wont bug you again, i think i'll try to find the solution somewhere else cause its not working and if you have another code that can make it work, i'll appreciate. Thanks for your effort

[AndreRet]

It's a pleasure. Thank you for reading our posting rules AND abiding by it. I have just gave you some points for that.

Back to your problem, if you say it does not work, what error are you getting and on what line of your code gets highlighted. Lets try and narrow this down and get this to work for you.:)

[styleest]

ok, i've seen my private message, pls check urs too

[AndreRet]

I'll let you know...

[styleest]

i guess my network is really bad, still trying to send it.

[AndreRet]

Paste the code for only the form you are using. I should be able to read from that and find your solution. Also post the table names and fields in the database that is applicable.

[styleest]

Just got this your comment. i'm pasting the codesince i cant attach the whole project

[styleest]

THis is the code to print the selected persons payroll info.

Private Sub cmdPrintSel_Click()
Dim MyDate As Date
Date = Me.dt1.Value
Dim MyString As String
MyString = Me.lvwInfo.SelectedItem.Text

dt1.Value = Me.lvwInfo.ListItems(Me.lvwInfo.SelectedItem.Index).SubItems(3)
If rs.State = adStateOpen Then rs.Close

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "'" & MyString & "' GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction HAVING Month(dDate)='" & MyDate & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenStatic, adLockOptimistic

Set drtSel.DataSource = rs
drtSel.PrintReport
drtSel.Show 1
Exit Sub
err:
MsgBox err.Description, vbCritical, "Error"
Set rs = Nothing
End Sub

THe table name is,tblPayroll. With fields,

EM_ID,EM_Name,Monthly_Rate,dDate,Bonus,OvertimePay,AwardTicket,FineTicket,pension,w/Tax,OtherDeductions,OtherEarnings,Loans,TotalDeductions,Medical,Housing,Transport,Furniture,Feeding,Miscellaneous,NetPay,RemainingBalance,TotalPaid,LoanCollected,MonthlyDeduction.

Thanks very much. Will wait for your reply. Meanwhile, i'm still trying to attach the whole project

[AndreRet]

What information is returned with the following code?

dt1.Value = Me.lvwInfo.ListItems(Me.lvwInfo.SelectedItem.Index).SubItems(3)

Is it a proper date that gets returned?

[styleest]

yes its a date that get returned and i used a list view to display all my info inorder to select it, click on it and it will print. I've suceeded in attaching 97% of the whole project pls hold on so that you can get the whole view.

Thanks

[styleest]

Pls keep trying to work on that info. The full project has refused to download. i'm still working on it .

Thanks

[AndreRet]

Paste the following and see if it works now. You had no value set for the MyDate function.

Private Sub cmdPrintSel_Click()
Dim MyDate As Date
Dim MyString As String

MyString = Me.lvwInfo.SelectedItem.Text

dt1.Value = Me.lvwInfo.ListItems(Me.lvwInfo.SelectedItem.Index).SubItems(3)
'Add MyDate value here....
MyDate = dt1.Value

If rs.State = adStateOpen Then rs.Close

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "'" & MyString & "' GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction HAVING Month(dDate)='" & MyDate & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenStatic, adLockOptimistic

Set drtSel.DataSource = rs
drtSel.PrintReport
drtSel.Show 1
Exit Sub
err:
MsgBox err.Description, vbCritical, "Error"
Set rs = Nothing
End Sub

[AndreRet]

Also, what do you use to open your connection to the database? cn?

[styleest]

i declared my connection public in a module and its ok cos i use it in all my forms and its working. You think its connection problem cos its complains of that when the error handler is there. Remove the error handler and you will see the real problem.

Thanks

[AndreRet]

Try to use a separate connection just to test your code. If the new connection is working, you know that the problem is with the reference to cn connection in your module.:)

[styleest]

i decided to call my connection on the top before the code and the error is now
Runtime error -2147217904 (80040e10)
No value given for one or more required parameters.

Its almost like the first error and when i click on debug, it highlights the code i sent to you as the problem

[AndreRet]

Now we are getting there. No value given means that in one of the fields to be reported on, a value is outstanding.

Now check if the date returned IS a date in a proper format (2010/11/08) for instance, and not just a month or a day and without the time.

Also check to see that the string returned by "MyString" is a valid string that can read the data.

[styleest]

THis is real funny now, i checked all the date format and made them short date but from my list view i see both the date and time and the also after running the program, it changes my system date itself to april 2004. i reset the time and run the program again, the same error and my system time setting changes

[AndreRet]

Use the format function to return the correct date format. The data will not be read without the correct format.

MyDate = Format(MyDate, "yyyy/mm/dd")

I have no idea why your system date is changing though. Are you making use of a date function or API somewhere in a module in your app that might effect this system date change?

[styleest]

No i dint make use of any date function and i use the date the way u ask me to but its not working cos its stil complainin of the same error 'No value given for one or more required parameter'

[AndreRet]

Send me the code for your listview population (What data gets added to the listview which the user can select from).

Please encase your code in code tags.

I am busy re-creating your form and codes to see where your problem is.

Lastly, you are using vb6, am I right?