listbox with parameter sp of unbound control of forms

[sknaina]

Hi everyone,
I've a A2003 adp & mssql2k base application Where I want to use List box with control source parameter sp . My form is bound but forms controls are unbound. now i describe what i've follow to achieve the task
forms record source:: Select * from HDC_ChallanT
listbox record source:: DailyChallan

CREATE Procedure dbo.DailyChallan
@Pdate datetime
AS
Select Challanno,depositor,HDc_EconomicT.Economic+'-'+HDc_EconomicT.Engdesc,Amount  
from HDC_ChallanT, HDC_EconomicT
Where HDC_challanT.Economic=Hdc_EconomicT.EcoiD
AND HDC_ChallanT. ChallanDate=@Pdate
GO

when i open this form it prompts access default form then I set forms input-parameter :@pdate datetime =forms!HDC_ChallanDetailf!txtChallandate
now it prompts "Bad parameter @pdate"
would any one suggest me what ive to do to success this project .Any suggestion would be highly appreciated.

[Jim Doherty]

@sknaina
Hi

The InputParameters property of a form is intended to pass parameters to the SQL Server to restrict any recordset supplied from the server intended to act as the recordsource for your FORM only. Think of listboxes and comboboxes as entirely separate and unconnected from this

To use a stored procedure to populate a listbox (or combobox) you simply give the listbox its rowsource at the appropiate point in time. ie:

The syntax for passing the rowsource of a combo is as simple as below where datecriteria is a variable date value passed and concatenated to that string line. The value for this variable you can set, in code, at whatever point you wish, whether that be hard coded or taken from the value of a textbox somewhere or other on screen

Me!YourListboxName.RowSource = "EXEC dbo.dbo.DailyChallan " & datecriteria

1) If the relevant listbox or combobox is page2 of a tab control for instance, then don't load until till page2 is viewed and so on , or if 'always in view' not until the control gets the focus.

2) The purpose of this logic at 1) is this:- think network load. There is no point populating any list over a network if you do not intend to use it. Yes I know, some might argue but its only a small list...yes, but it is one less server process to profile when and if performance ever becomes an issue in a multi user wide area network environment when your app becomes the size of a house. :)

So... essentially then an EXEC statement command line is passed as a string command to the SQL server and it in turn 'executes' the relevant stored procedure returning the dataset requested to the control

Regards

[sknaina]

@Jim Doherty
Jim thanks for your reply
accordance to your suggestion create a Private function and call this function on after event of form control. But no desired result achieved. that means listbox show nothing below I would post both code
Private Function To execute the parameter Sp.

Private Function ListUpdate()
Me!Lst_Challan.RowSource = "Exec dbo.DailyChallan " & Format$(Me.TxtChallanDate, "mm/dd/yyyy")
End Function
 

After update Event

Private Sub tXtamount_AfterUpdate()
        If CheckSave = False Then Exit Sub ' its a function to validate all required fields are filled up
            If ChallanSave = True Then ' its function to execute challan save paramenter sp
Me.LblYes.Visible = True 'MsgBox
            ListUpdate ' its function to execute the parameter Sp for Listbox Population
            PartialClear ' its function to clear text box value after saving data
Else
            Me.LblNo.Visible = True 'MsgBox
        End If
            End Sub
 

[Jim Doherty]

@sknaina
Hi


First of all you need to have a serious look at how SQL server stores dates. The absolute mechanics of that I will not go into, as it is fully documented in Books Online the SQL Server help reference... so read up on that. You will then appreciate the issues involved as relates to casting values (particularly dates) to an acceptable format.

Suffice it to say this the clues are in the amendments I post back to you below:

Secondly look at your SQL you are including in that a column a concatenation of two fields to produce a column that will bear no column name (not that this stops the code in fairness but. I have amended that to reflect at least something meaningful instead of the usual 'Expr1' that would ordinarily be returned

At this point in time I am not particulary interested in the functionality of your logic flow concerning label visibility, saving and stuff like that so much as the issue to get that listbox populated in line with the value expressed in the TxtChallanDate textbox, afterupdate of the amount entered into the textbox tXtamount

Now look at the following pasted code and examine the differences between this and your last post.

You will see where I have included the apostrophe wrapping the date value. Yes... I know the convention for referring to dates is the # wrapper usually however see for yourself, if you create a simple view in an ADP the convention used in the query grid automatically inserts the apostrophe (try it for yourself). You try and use the apostrophe instead and see how far it gets you......absolutely nowhere!

Insofar as relates to your stored procedure, you need to deal with the date value parameter passed in terms of Casting and Converting (which you can read up on) so that the parameter value passed in meets with the agenda required to compare correctly against the way in which a date is stored, which I assume to be datetime data type on the server in your case.

The storage value is best looked at using Query Analyser in which you will see dates stored 'back to front', year first and always including the time portion. Again I won't go into the logic of this as it is fully documented.

If you look at your table data in Access it does not show you these differences because Access knows how to present your date value to you, formatted according to your localised regional settings

The revised edition to populate your listbox...

Me!Lst_Challan.RowSource = "Exec dbo.DailyChallan '" & Format(Me.TxtChallanDate, "mm/dd/yyyy") & "'"

The revised edition of your stored procedure...

CREATE Procedure dbo.DailyChallan 
@Pdate datetime 
AS 
SELECT Challanno,depositor,HDc_EconomicT.Economic+'-'+HDc_EconomicT.Engdesc AS MyExtraColumn,Amount   
FROM HDC_ChallanT, HDC_EconomicT 
WHERE HDC_challanT.Economic=Hdc_EconomicT.EcoiD 
AND HDC_ChallanT. ChallanDate=CONVERT(DATETIME,@Pdate, 102)GO

[sknaina]

@Jim Doherty
Jim, first of all sorry for late response due to we have lot of power problem. Any way, many many thanks for your meaningful information . And Once again Sorry for my SP (this was unintentional mistake). AND last of all, it works fine.

Thanks for your great co-operation.

>>>>>>Naina<<<<<<<<<

[Jim Doherty]

@sknaina
You are welcome and I am pleased you got this working.
Thankyou for posting back the result :)