Solved

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

Posted on 2003-11-18
31
734 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:ktt2
  • 14
  • 11
  • 3
  • +2
31 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9771978
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9772195
Namasi I think it's Access vba not ASP code

Hilaire
0
 

Author Comment

by:ktt2
ID: 9772227
yes access vba code in my form that is not working...
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9772230
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9772243
Can you issue a
msgbox strSQL and see if the statement looks OK ?

Hilaire

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9772288
>>If you use ADO the ADO Connexion object has a timeout property<<
Actually this would be the CommandTimeout not the ConnectionTimeout.

Anthony
0
 

Author Comment

by:ktt2
ID: 9772341
i have issued a msgbox and the code looks identical to what i have in the QA...
0
 

Author Comment

by:ktt2
ID: 9772378
the list box goes blank as soon as I execute the search button command with has this sql code.
0
 

Author Comment

by:ktt2
ID: 9773908
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9774047
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9774052
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
 
LVL 18

Accepted Solution

by:
lludden earned 250 total points
ID: 9776067
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9776104
lludden,

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

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9776109
lludden,

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

Anthony
0
 

Author Comment

by:ktt2
ID: 9781043
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ktt2
ID: 9782525
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9783100
Do you need help maintaining your open questions?

Anthony
0
 

Author Comment

by:ktt2
ID: 9783230
Anthony, thank you for your reminder...they are being taken care of...can anyone help me with this problem please?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9783406
Post your Stored Procedure.

Anthony
0
 

Author Comment

by:ktt2
ID: 9784718
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9784741
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
 

Author Comment

by:ktt2
ID: 9784773
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
 

Author Comment

by:ktt2
ID: 9784796
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 9785080
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
 

Author Comment

by:ktt2
ID: 9785106
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9788102
>>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
 
LVL 18

Expert Comment

by:lludden
ID: 9788963
If you are connected via an ADP, then you can use the CurrentProject.ActiveConnection property to get your link to the server.
0
 

Author Comment

by:ktt2
ID: 9790521
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9793179
I have no idea.  You may want to post the question in the MS Access Topica Area.

Anthony
0
 

Author Comment

by:ktt2
ID: 9859380
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
 

Author Comment

by:ktt2
ID: 9914100
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now