Troubleshooting a stored procedure 4

[scliffe]

I have a stored procedure that returns no results:

CREATE PROCEDURE dbo.sp_att (@ID Core_AdmissionsNumber)
AS
CREATE TABLE dbo.StudentAtt(StudID Core_AdmissionsNumber)
INSERT INTO dbo.StudentAtt (StudID)
SELECT AdmissionsNumber
FROM dbo.tbl_student_name
WHERE dbo.tbl_student_name.AdmissionsNumber=@ID
ORDER BY dbo.tbl_student_name.AdmissionsNumber
GO

and this is executed from an Access form with the following code:

Private Sub btnStudentAttWkMth_Click()

Dim db As Connection
Dim spsend As String
Dim StudentID As String
Set db = New Connection
StudentID = "Forms!frm_StudentInfo!cmbStudent"

'Open db connection
db.Open ("provider = sqloledb; data source = XXXXX; initial catalog = XXX; user id = XXXXXX;password = XXXXXXX")

'Run Stored Procedure to create Table and filter to correct students
spsend = "Execute sp_att @ID='" & StudentID & "'"
db.Execute spsend

Dim stDocName As String

stDocName = "rpt_Student_Attendance(Wk/Mth)"
DoCmd.OpenReport stDocName, acPreview

End Sub

The cmbStudent is populated and should be passed through to populate the table created with a single cell of data.

Additionally, what would be the best method when re-executing the table with new data? Truncating/Deleting???

Thanks

Simon

 

[SQLSister]

In the first place, why why why are you creating a table on the fly. This is a very poor practice. It appears to me that all you need is a select stament not insert into a table.

The reason why you see no result set is that you don't create one. To get results from a stored proc, then run a select statement.

"NOTHING is more important in a database than integrity." ESquared

 

[scliffe]

I need to create the table on the fly as I have found no other way to pass the data I need across from an Access form to the SQL view. If there is a better way, please enlighten me - I keep getting told "stored procedures are the way to go" but then when it comes to filtering my data, I get nowhere.

I don't quite understand your reason why I see no result - is that a typo or just the way I'm reading it. I have no programming background and I'm trying to learn this on the fly myself!

 

[ESquared]

CREATE PROCEDURE dbo.StudentInfo @ID Core_AdmissionsNumber
AS
SELECT AdmissionsNumber
FROM dbo.tbl_student_name
WHERE dbo.tbl_student_name.AdmissionsNumber=@ID
ORDER BY dbo.tbl_student_name.AdmissionsNumber

Now, if you could do the following, you'd be all set, but this only works in ADP files (the bold part):

Private Sub Report_Open(Cancel As Integer)
    Dim db As ADODB.Connection
    
    Set db = New ADODB.Connection
    db.Open "YourConnectionString"
    [b]Set Me.Recordset =[/b] db.Execute("Execute sp_att @ID='" & Forms!frm_StudentInfo!cmbStudent & "'")
End Sub

So you should look at How to Execute SQL Stored Procedures from Microsoft Access and Access 2000 and SQL Stored Procedure.

You could find these resources yourself with some searching—the way I did—if you really wanted the answer.

Also, this is actually an Access question. It is not a SQL Server question. So you shouldn't be asking in this forum at all. See the Microsoft: Access Reports forum for more help. Or see all of the forums about Access here on tek-tips.

Last, do NOT name your stored procedures with the prefix "sp_".

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[djj55]

SQLSister has a good point in that if you want a stored procedure to return a table use a select.

In Access you can link a table then insert data into the table. That is if you have the correct permissions.

Good Luck,
djj

 

[scliffe]

Thank you.

 

[scliffe]

Solution worked as follows:

CREATE PROCEDURE dbo.sp_att (@ID Core_AdmissionsNumber)
AS

CREATE TABLE dbo.StudentAtt(StudID Core_AdmissionsNumber)
INSERT INTO dbo.StudentAtt (StudID)
SELECT AdmissionsNumber
FROM dbo.tbl_student_name
WHERE RTrim(dbo.tbl_student_name.AdmissionsNumber) = @ID
ORDER BY dbo.tbl_student_name.AdmissionsNumber
GO



with the following on the access form:

Private Sub btnStudentAttWkMth_Click()

Dim db As Connection
Dim spsend As String
Dim spsend2 As String
Dim StudentID As String
Set db = New Connection
StudentID = Forms!frm_StudentInfo!cmbStudent
'MsgBox StudentID 'Test purposes only

'Open db connection
db.Open ("provider = xxxxxx; data source = xxxxxxx; initial catalog = xxxx; user id = xxxxx;password = xxxxxx")

'Drop old table
spsend = "Execute sp_droptab"
db.Execute spsend

'Run Stored Procedure to create Table and filter to correct students
spsend2 = "Execute sp_att @ID='" & StudentID & "'"
db.Execute spsend2

Dim stDocName As String

stDocName = "rpt_Student_Attendance(Wk/Mth)"
DoCmd.OpenReport stDocName, acPreview

End Sub

 

[AlexCuse]

Naming your sp's starting with sp is bad practice, because SQL Server will search for it starting with system stored procedures in the master database. Read this:

http://www.devx.com/tips/Tip/14432

And for what its' worth, a parameterized select here (either through an SP executed as ESquared points you towards, or even an access query selecting from a linked table) would be a much better idea than rebuilding your table each time.

Still, glad you got it working.

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!