Stored Procedure from a SQL statement in MS ACCESS 2k Form Possible?

In reference to this question:

[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20787509.html[/url]

In my search form, apparently it seems to time out when I input a date range in addition to the account number.  The list box just comes up blank.

If I run the SQL statement that is compiled from the code:

WHERE"

' If the Full CC # is checked then search complete Account Number
If Me.CCcheckBox.Value = 1 Then
    strSQL = strSQL & " [Accountnumber] = '" & txtFullCCNbr & "'"
    If (IsNull(Me.txtFullCCNbr)) Then
        MsgBox "Please Enter an Account Number For Your Search"
    End If
    If (Not IsNull(Me.txtAmount)) Then
        strSQL = strSQL & " AND [Amount] = " & Me.txtAmount.Value & ""
    End If
    If (Not IsNull(Me.txtFromDate)) And (Not IsNull(Me.txtToDate)) Then
        strSQL = strSQL & " AND [Transactiondate] Between '" & Me.txtFromDate & "' and '" & Me.txtToDate & "'"
        strSQL = strSQL & " ORDER by [Transactiondate]"
    End If
Else
'Check for Partial Account Number
    strSQL = strSQL & " [Lastfour] = '" & Me.txtPartialCCNbr.Value & "'"
    If (IsNull(Me.txtPartialCCNbr)) Then
        MsgBox "Please Enter the Last Four Digits of Account Number for Your Search"
    End If
    If (Not IsNull(Me.txtAmount)) Then
        strSQL = strSQL & " AND [Amount] = " & Me.txtAmount.Value & ""
    End If
    If (Not IsNull(Me.txtFromDate.Value)) And (Not IsNull(Me.txtToDate.Value)) Then
        strSQL = strSQL & " AND [Transactiondate] Between '" & Me.txtFromDate.Value & "' and '" & Me.txtToDate.Value & "'"
        strSQL = strSQL & " ORDER by [Transactiondate]"
    ElseIf (IsNull(Me.txtFromDate.Value)) And (Not IsNull(Me.txtToDate.Value)) Then
        strSQL = strSQL & " AND ([Transactiondate] <= '" & Me.txtToDate.Value & "')"
    ElseIf (Not IsNull(Me.txtFromDate.Value)) And (IsNull(Me.txtToDate.Value)) Then
        strSQL = strSQL & " AND ([Transactiondate] >= '" & Me.txtFromDate & "')"
    ElseIf (IsNull(Me.txtFromDate.Value)) And (IsNull(Me.txtToDate.Value)) Then
    End If
End If

---all works find under SQL...The SQL statement i run in QA is as follows:

SELECT amount, transactiondate, accountnumber
FROM fullCC
WHERE amount = 192.11 AND transactiondate BETWEEN '10/10/02' AND '10/30/02' AND lastfour = '4000'

-----

The code in my access form is writing the SQL statement exactly as the one used in QA, only it appears to be timing out in the form.  Therefore, I'm thinking a stored procedure for that SQL statement may be a good alternative...

I just don't know if a stored procedure is possible since I am getting the values from a text box in a form and don't know how to exactly structure it in SQL to where it sees the values from the text box.

So far this is what I have, not much but I guess it's a start:

CREATE PROCEDURE sp_CCpart AS

DECLARE @Amount money
DECLARE @Accountnumber nvarchar(30)
DECLARE @Lastfour char(4)
DECLARE @Transactiondate smalldatetime(4)



Any suggestions?  I have no idea as to why this is happening.

Thank you,
Kevin
ktt2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
When you do

Response.Write strSQL

do you get this?
SELECT amount, transactiondate, accountnumber
FROM fullCC
WHERE amount = 192.11 AND transactiondate BETWEEN '10/10/02' AND '10/30/02' AND lastfour = '4000'

When dealing with dates there are useful functions you can use vbscript functions IsDate, CDate, FormatDate etc.

Namasi.
0
HilaireCommented:
Namasi I think it's Access vba not ASP code

Hilaire
0
ktt2Author Commented:
yes access vba code in my form that is not working...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HilaireCommented:
How long does the query take to fetch the resultset in QA ?

I see no particular reason for it to timeout in VBA if the syntax is OK.
There must be a syntax error somewhere ?

If you use ADO the ADO Connexion object has a timeout property, you can set it to any value in secs.
Try to increase the current value and see if it changes something.

HTH

Hilaire

0
HilaireCommented:
Can you issue a
msgbox strSQL and see if the statement looks OK ?

Hilaire

0
Anthony PerkinsCommented:
>>If you use ADO the ADO Connexion object has a timeout property<<
Actually this would be the CommandTimeout not the ConnectionTimeout.

Anthony
0
ktt2Author Commented:
i have issued a msgbox and the code looks identical to what i have in the QA...
0
ktt2Author Commented:
the list box goes blank as soon as I execute the search button command with has this sql code.
0
ktt2Author Commented:
What if I just change from using DAO to ADO 2.1 and try to open a connection on the Form Load Event?

something like:

Private strSQL As String
Private cnnFullCC As New ADODB.Connection
Private rstFullCC As New ADODB.Recordset
Option Explicit

---
Private Sub Form_Load()

'Specify Provider and open Connection

With cnnFullCC
     .Provider = "MSDatashape"
     .Open "Data Provider=SQLOLEDB.1;Data Source=(Local);UID=ccdbusr;PWD=aaaaa1;Database=CreditCard"
End With

strSQL = "SELECT * from FullCC"
With rstFullCC
    Set .ActiveConnection = cnnFullCC
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open strSQL
End With

Set Me.Recordset = rstFullCC
Me.UniqueTable = "FullCC"

-----

It's telling that ccdbusr does not exist???? In the Users Section, it is clearly there...Keep in mind this is a Local Machine where the Access Front End Resides on the same machine as the SQL Server....


0
Anthony PerkinsCommented:
Change this line:
  .Open "Data Provider=SQLOLEDB.1;Data Source=(Local);UID=ccdbusr;PWD=aaaaa1;Database=CreditCard"

To:
  .Open "Data Provider=SQLOLEDB.1;Data Source=(Local);User ID=ccdbusr;Password=aaaaa1;Data Source=yourservername; Initial Catalog=yourdatabasename"

Anthony
0
Anthony PerkinsCommented:
Also, please maintain these old open questions:

1 10/12/2003 250 Making a W2k Pro Bootable CD with third ...  Open Windows 2000
2 10/22/2003 125 Help with Design on a YES/NO Questionnai...  Open Microsoft Access
3 09/17/2003 250 Dell Poweredge 1300 Server not booting u...  Open Hardware

Thanks,
Anthony
0
lluddenCommented:
This is ugly, but it does work.  Call the SP passing any combo of Amount, LastFour, or (Start/End) Dates.  The defaults in the SP should be adjusted to cover any possible dates if none are specifically given.


CREATE PROCEDURE [dbo].[test] @Amount money = Null, @LastFour char(4) = Null, @StartDate datetime = '1/1/1900', @EndDate datetime = '12/31/2099' AS

If @Amount Is Not Null and @LastFour Is Not Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
      WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Not Null and @LastFour Is  Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
      WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate

If @Amount Is Null and @LastFour Is Not Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
      WHERE transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Null and @LastFour Is Null
    SELECT amount, transactiondate, accountnumber FROM fullCC

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
lludden,

>>This is ugly, but it does work.<<
Are you sure about that?  

Anthony
0
Anthony PerkinsCommented:
lludden,

Please ignore that last comment.  I see where you are coming from now.

Anthony
0
ktt2Author Commented:
Alright update:

To make things easier in connecting to the SQL Server, I just created a MS Access Project that connects to the SQL Server.

So now I have a fullCC table, imported the search form and report.  Only thing is that now the I have the row source for the report with the stored procedure example lludden provided.

The stored procedure seems to work fine in SQL, but how do I bind the textbox values from the search from onto the stored procedure?

txtAmount
txtFullCCNbr
txtPartialCCNbr
txtFromDate
txtToDate

these are the text boxes where the user will enter a search criteria.  From then the SQL statement is compiled
0
ktt2Author Commented:
Basically I don't know if it's possible to pass the values from the textboxes to the variables in the stored procedure in the access project.

Pass the value from txtAmount to @amount...etc...

Am i going about this the right way>???
0
Anthony PerkinsCommented:
Do you need help maintaining your open questions?

Anthony
0
ktt2Author Commented:
Anthony, thank you for your reminder...they are being taken care of...can anyone help me with this problem please?
0
Anthony PerkinsCommented:
Post your Stored Procedure.

Anthony
0
ktt2Author Commented:
ALTER PROCEDURE sp_PartCC @Amount money = Null, @LastFour char(4) = Null, @StartDate smalldatetime, @EndDate smalldatetime AS

If @Amount Is Not Null and @LastFour Is Not Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
     WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Not Null and @LastFour Is  Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
     WHERE amount = @Amount AND transactiondate BETWEEN @StartDate AND @EndDate

If @Amount Is Null and @LastFour Is Not Null
    SELECT amount, transactiondate, accountnumber FROM fullCC
     WHERE transactiondate BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour

If @Amount Is Null and @LastFour Is Null
    SELECT amount, transactiondate, accountnumber FROM fullCC

basically in the command button on click event i had:

DoCmd.openstoredprocedure ("sp_PartCC") '&txtAmount.Value&' AND '&txt.PartialCCNbr&' AND &'txtFromDate.Value&' AND &'txtToDate.Value&'

I did that thinking that the order of the variables in the stored procedure that it would pass the parameters from the text boxes to the stored procedure...Instead the stored procedure runs when clicking on it, but I get a prompt for Amount, LastFour, StartDate,EndDate...ignoring the textbox values...


The RowSource in the Form's List Box properties is pointing to the view from the Database table.
0
Anthony PerkinsCommented:
First of all if you run this Stored Procedure outside of your code does it return the data you expect?

Second, as you can tell this is the MS SQL Server Topic Area and you may find more specific help in the MS Access Topic Area.

Having said that, I can give you the ADO code that I would use from Visual Basic if the database was MS SQL Server, if that would help you.

Anthony
0
ktt2Author Commented:
Anthony that would be greatly appreciated, but then the variables and code written that I have to display the checkboxes would be invalid....

Private Sub CCcheckBox_AfterUpdate()

Me.txtFullCCNbr.Visible = Me.CCcheckBox.Value = 1
Me.txtPartialCCNbr.Visible = Me.CCcheckBox.Value = 2

End Sub

Private Sub Form_Current()

Me.txtFullCCNbr.Visible = Me.CCcheckBox.Value = 1
Me.txtPartialCCNbr.Visible = Me.CCcheckBox.Value = 2

End Sub

-----
About the stored procedure, it does return the data I expect by answering the prompts that pop up...
0
ktt2Author Commented:
In addtion, I went ahead and changed the cmdbutton On Click event to:

strSQL = "EXEC sp_PartCC '" & txtAmount.Text & "' & '" & txtPartialCCNbr.Text & "' & '" & txtFromDate.Text & "' & '" & txtToDate.Text & "'"

And it told me that RunTime ERROR 2185...You can't reference a property or method for a control unless the control has the focus??

Am I getting Closer?
0
Anthony PerkinsCommented:
This is how you would do it in VB6 using ADO:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "your connection string goes here
cn.Open

Set cmd = New ADODB.Command
With cmd
   Set .ActiveConnection = cn
   .CommandText = "sp_PartCC"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@Amount", adCurrency, adParamInput, 0, CCur(txtAmount.Value))
   .Parameters.Append .CreateParameter("@LastFour", adVarChar, adParamInput, 4, txt.PartialCCNbr)
   .Parameters.Append .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 0, CDate(txtFromDate.Value))
   .Parameters.Append .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 0, CDate(txtToDate.Value))
   Set rs = .Execute
End With
Set cmd = Nothing

'rest of your code goes here

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Anthony
0
ktt2Author Commented:
Do I need to include the cn to connect to the server if I this is a Access Database project file that is already connecting to the data?
0
Anthony PerkinsCommented:
>>Do I need to include the cn to connect to the server<<
Do be candid I do not know.  Since it is used in the Command object you will need something like it, perhaps MS Access has something that will return the current database. But this question is best posted in the MS Access Topic Area.

Anthony
0
lluddenCommented:
If you are connected via an ADP, then you can use the CurrentProject.ActiveConnection property to get your link to the server.
0
ktt2Author Commented:
I made the change to Anthony's suggestion per lludden's help for the connection below and it seems to work (connecting that is):

---------

Private Sub btnSearch_Click()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
With cmd
   Set cn = CurrentProject.Connection
   Set .ActiveConnection = CurrentProject.Connection
   .CommandText = "sp_CCPart"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@Amount", adCurrency, adParamInput, 0, CCur(txtAmount.Value))
   .Parameters.Append .CreateParameter("@LastFour", adVarChar, adParamInput, 4, txtPartialCCNbr.Value)
   .Parameters.Append .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 0, CDate(txtFromDate.Value))
   .Parameters.Append .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 0, CDate(txtToDate.Value))
    Set rs = .Execute
End With

End Sub

---------

ALTER PROCEDURE sp_CCPart @Amount money, @Lastfour char(4), @StartDate smalldatetime, @EndDate smalldatetime AS

If @Amount Is Not Null and @LastFour Is Not Null
    SELECT * FROM viewfullCC
    WHERE amount = @Amount AND date BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour
Else If @Amount Is Not Null and @LastFour Is  Null
    SELECT * FROM viewfullCC
    WHERE amount = @Amount AND date BETWEEN @StartDate AND @EndDate
Else If @Amount Is Null and @LastFour Is Not Null
    SELECT * FROM viewfullCC
    WHERE date BETWEEN @StartDate AND @EndDate AND lastfour = @LastFour
Else If @Amount Is Null and @LastFour Is Null
    SELECT * FROM viewfullCC
    WHERE date BETWEEN @StartDate AND @EndDate
----------


The problems that I'm running into now/still:

1) I still cannot append the parameters from the textbox values to the variables in the stored procedure

2) If I run the procedure separately and manually type in a value on the pop up dialog box, the procedure runs fine.

3) I am trying to bind the listbox (lstFullCC) to the recordset to see if the actuall btnSearch_Click event is working or not
   in opening/running the stored procedure passing the parameters to it.

I am increasing the points as I am really appreciative of the help and learning little by little.

note to lludden:  I had to ad the Else IF to the procedure, otherwise it would run all the if when I only wanted to see which
condition it met and run that.

Thanks again,
Kevin
0
Anthony PerkinsCommented:
I have no idea.  You may want to post the question in the MS Access Topica Area.

Anthony
0
ktt2Author Commented:
lluden,

how would I create a default value for date if in the table the date field is in smalldatetime?  You wrote in your procedure datetime, but it creates an overflow error.

Is there a way I can write 01/01/1998 and 12/31/2039 in smalldatetime format??

Thanks,
Kevin
0
ktt2Author Commented:
I didn't realize I didn't award points for your help.  Thank you lludden and acperkins!

Still having some issues but you two helped me get farther.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.