VB6 / SQL Statement

[dburnham]

I need to have a variable in an SQL statement that is assigned by the user through an interface screen. The user may select a specific date, ID or some other criteria. The SQL statement is to then filter the DB based on the user selection and return a Record Set accordingly. I am able to get the user input, however, I continue to receive an error message that the value is undefined, blank zero, etc..

Any ideas?

 

[tlbroadbent]

What is the user interface? How does the program connect to SQL Server? What does the SQL statement look like? What is the exact error message? It will be difficult if not impossible to help without additional information. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dburnham]

The user interface will come from a DataComboBox. The error message is as follows:

Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters

I have tried to include all of the relevent code below.



UserSelectsID$ = DataCombo1.Text

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Progress\chitest2.mdb;Persist Security Info=False"

strSQLSelect = "SELECT DailyProduction.`Date`, DailyProduction.ClockId, " & _
" AssociateInfo.FirstName, AssociateInfo.LastName, " & _
" TaskCodes.TaskDescription, TaskCodes.UnitOfMeasure, TaskCodes.TaskCode, " & _
" TaskCodes.ProductionRate, DailyProduction.Cartons, " & _
" DailyProduction.Stops , DailyHours.Hours " & _
" FROM DailyProduction, AssociateInfo, TaskCodes," & _
" DailyHours " & _
"WHERE DailyProduction.ClockId = AssociateInfo.ClockId AND " & _
"DailyProduction.Task = TaskCodes.TaskCode AND " & _
"AssociateInfo.ClockId = DailyHours.ClockId AND " & _
"DailyProduction.ClockId = UserSelectsID$ "


strSQLEfficiency = "SELECT Calculation.*" & _
"FROM Calculation"


Set oConn = New ADODB.Connection
oConn.Open strConn
Set oRs = New ADODB.Recordset
oRs.Open strSQLSelect, oConn, adOpenDynamic, adLockReadOnly

Set oConn2 = New ADODB.Connection
oConn2.Open strConn
Set oRs2 = New ADODB.Recordset
oRs2.Open strSQLEfficiency, oConn2, adOpenDynamic, adLockOptimistic

Thank you for your assistance and advice.

David

 

[tlbroadbent]

The last line of the code setting the value of strSQLSelect is incorrect. You have included UserSelectsID$ inside the quotation marks. That causes the literla string "UserSelectsID$" to be used as criteria. Change as follows.

"DailyProduction.ClockId = " & UserSelectsID$
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dburnham]

Thanks, That made sense but I now have a new error.

The run-time error is -2147217913(80040e07)
Data type mismatch in criteria expression.



The DailyProduction.ClockId is a Text field. I have declared UserSelectsID as a Sting, a variant, an Integer and several others.

 

[tlbroadbent]

If DailyProduction.ClockId is a text field use the following syntax.

"DailyProduction.ClockId = '" & UserSelectsID$ & "'" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dburnham]

Thank you !!!

 

[dburnham]

Thank you !!!

 

[dburnham]

Help again,

I am now trying the same function with a date field. I have changed the syntax around but I am back to getting the error message "No value given for one or morerequired parameters". When I place my mouse pointer over the Calendar1.Value it says the value = 10/08/01 . The syntax I am using is:

"DailyProdution.`Date` = " & Calendar1.Value

 

[tlbroadbent]

Use the following syntax.

DailyProdution.[Date] = '" & Calendar1 & "'"

Note 1: Don't use the Value property of a control. Either leave off the property and and use the default property or use the Text property. The Value property is the saved value of the text box control. The Text property is always current while the control has the focus.

Note 2: Use brackets [] to delimit column names rather than single or double quotes.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dburnham]

I made the syntax change and I am still reciving the message:

"No value given for one or more required parameters"

???

 

[tlbroadbent]

Are you using the calendar control? Could you post your code again, please?

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dburnham]

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Progress\chitest2.mdb;Persist Security Info=False"

strSQLSelect = "SELECT DailyProduction.`Date`, DailyProduction.ClockId, " & _
" AssociateInfo.FirstName, AssociateInfo.LastName, " & _
" TaskCodes.TaskDescription, TaskCodes.UnitOfMeasure, TaskCodes.TaskCode, " & _
" TaskCodes.ProductionRate, DailyProduction.Cartons, " & _
" DailyProduction.Stops , DailyHours.Hours " & _
" FROM DailyProduction, AssociateInfo, TaskCodes," & _
" DailyHours " & _
"WHERE DailyProduction.ClockId = AssociateInfo.ClockId AND " & _
"DailyProduction.Task = TaskCodes.TaskCode AND " & _
"AssociateInfo.ClockId = DailyHours.ClockId AND " & _
"DailyProdution.[Date] = '" & Calendar1 & "'"


Yes, I am using the Calendar Control.

 

[tlbroadbent]

Sorry to have not paid enough attention to your previous post. I see now that you are using Microsoft Access. This is a SQL Server forum. You should post future questions about MS Access SQL in the proper forum - forum701.

In Access you need to delimit the dates with #.

strSQLSelect = "SELECT DailyProduction.[Date], _
" DailyProduction.ClockId, " & _
" AssociateInfo.FirstName, AssociateInfo.LastName, " & _
" TaskCodes.TaskDescription, TaskCodes.UnitOfMeasure, & _
" TaskCodes.TaskCode, TaskCodes.ProductionRate, " & _
" DailyProduction.Cartons, " & _
" DailyProduction.Stops, DailyHours.Hours " & _
" FROM DailyProduction, AssociateInfo," & _
" TaskCodes, DailyHours " & _
" WHERE DailyProduction.ClockId=AssociateInfo.ClockId" & _
" AND DailyProduction.Task = TaskCodes.TaskCode " & _
" AND AssociateInfo.ClockId = DailyHours.ClockId " & _
" AND DailyProduction.[Date] = #" & Calendar1 & "#"

I also noted that DailyProduction was not spelled correctly. I assume that was just an typing error when you posted the query. If not, you'll need to correct the spelling in your program.

I suggest that you not use "Date" as a column name in a database. Date is a keyword and function name. It is good programming practice to avoid using keywords as column, object or variable names.

I would also encourage the use of ANSI style JOIN statements when dealing with SQL. In addition, I personally like to use aliases to shorten the length of queries. Here is the query using ANSI JOINS and aliases.

strSQLSelect = "SELECT dp.[Date], dp.ClockId," & _
" ai.FirstName, ai.LastName, tc.TaskDescription," & _
" tc.UnitOfMeasure, tc.TaskCode, tc.ProductionRate," & _
" dp.Cartons, dp.Stops, dh.Hours" & _
" FROM (((DailyProduction As dp" & _
" INNER JOIN Associateinfo As ai" & _
" ON dp.ClockId=ai.ClockId)" & _
" INNER JOIN TaskCodes As tc" & _
" ON dp.Task = tc.TaskCode)" & _
" INNER JOIN DailyHours As dh" & _
" ON dp.ClockId = dh.ClockId)" & _
" WHERE dp.[Date] = #" & Calendar1 & "#" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.