Pass List box Value to SQL Stored Proc (1 Viewer)

imtheodore · Mar 27, 2008

I have and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?

So if I have listbox1 which is a list of dates from another table
and my procedure is simply

select * from main
where date = *******

How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.

Thanks,
Dave

ajetrumpet · Mar 29, 2008

imtheodore said:
My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.

If that works for you, then obviously you are performing a procedure that uses the list box value that is selected as one of the parameters or variables. You are referencing it somehow.

imtheodore said:
How can I pass the list box value to the stored procedure?

you are already doing this. Can you explain this a little more for us?

pbaldy · Mar 29, 2008

Adam, GETDATE() is the SQL Server equivalent of Now() in Access, so the OP is not using a value from the listbox.

Dave, how are you executing the stored procedure? The SP will need to be modified to accept a parameter, and then the call to it will have to provide that parameter. That varies depending on how you execute the SP.

imtheodore · Mar 29, 2008

pbaldy said:
Adam, GETDATE() is the SQL Server equivalent of Now() in Access, so the OP is not using a value from the listbox.

Dave, how are you executing the stored procedure? The SP will need to be modified to accept a parameter, and then the call to it will have to provide that parameter. That varies depending on how you execute the SP.

I am just running a pass through query all it consists of is:

exec SP_ADD_EMPLOYEE

I tried:

exec SP_ADD_EMPLOYEE[forms!datepicker!list36]
but I get an error that SQL cannot convert nvarchar to a date field. I believe that error simply means that SQL reads the parameter directly from the pass through query thus sending "forms!datepicker!list36" and not the value in the listbox.
I know it can be done and I've even seen some code, unfortunately, I do not know where to put the code or how to adjust it. What I was told:

"Just adjust your "sql" as per necesary...

Currentdb.querydefs ("queryname").sql will allow you to adjust your "sql" "

pbaldy · Mar 29, 2008

Yes, you'd have to have:

exec SP_ADD_EMPLOYEE '3/29/08'

I generally use a function that I pass the various arguments to, but the guts of what you want to do would look like:

Code:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("PassThruName")

    qdf.SQL = "exec SP_ADD_EMPLOYEE '" & forms!datepicker!list36 & "'"

    qdf.close
    Set qdf = Nothing
    Set db = Nothing

I assume you've already modified the SP to use a parameter instead of GETDATE().

imtheodore · Mar 30, 2008

pbaldy said:
Yes, you'd have to have:

exec SP_ADD_EMPLOYEE '3/29/08'

I generally use a function that I pass the various arguments to, but the guts of what you want to do would look like:

Code:

Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("PassThruName") qdf.SQL = "exec SP_ADD_EMPLOYEE '" & forms!datepicker!list36 & "'" qdf.close Set qdf = Nothing Set db = Nothing

I assume you've already modified the SP to use a parameter instead of GETDATE().

Where does this code go? In a query, or VB? How do I call it up? Sorry for all the questions.

pbaldy · Mar 30, 2008

That would go in VBA, wherever you want to execute the SP from. Since you already have a form with the listbox, perhaps behind a button on that form. It will modify your pass through query to include the date. You can then run the pass through via whatever method you already are.

imtheodore · Apr 1, 2008

pbaldy said:
That would go in VBA, wherever you want to execute the SP from. Since you already have a form with the listbox, perhaps behind a button on that form. It will modify your pass through query to include the date. You can then run the pass through via whatever method you already are.

I can't thank you enough for your help with this. Your code worked.

Any chance you also know how to hard code the connection string into my linked tables so it doesn't ask me to log into SQL every time the db is opened?

namliam · Apr 1, 2008

Click the save password option when you link the tables...

Tho if your access is not " read only " I would recomend against saving your password

imtheodore · Apr 1, 2008

namliam said:
Click the save password option when you link the tables...

Tho if your access is not " read only " I would recomend against saving your password

There is no "save password" option. I just checked.

namliam · Apr 1, 2008

Yes there is! Tho I am using Oracle as a backend, I am pretty sure it is there as well on SQL Server.

imtheodore · Apr 1, 2008

namliam said:
Yes there is! Tho I am using Oracle as a backend, I am pretty sure it is there as well on SQL Server.

You are correct, I was looking in the wrong area. Thanks you for your help!

Pass List box Value to SQL Stored Proc (1 Viewer)

imtheodore

Registered User.

ajetrumpet

Banned

pbaldy

Wino Moderator

imtheodore

Registered User.

pbaldy

Wino Moderator

imtheodore

Registered User.

pbaldy

Wino Moderator

imtheodore

Registered User.

namliam

The Mailman - AWF VIP

imtheodore

Registered User.

namliam

The Mailman - AWF VIP

Attachments

imtheodore

Registered User.

Similar threads

Users who are viewing this thread