Mark Klein
asked on
stored procedures and output parameters
I have a small table w/ 6 fields and 11 rows that hold the standard messages for an email notification system. I want to read a single row of the table to get the msg subject line and msg body text. I'll need to do this many times, so my plan is to create a function for the AppCode directory with a simple stored proc to query the table. My simple query works fine in query analyzer.
Problem is, I can't access the fields in my code. My first choice would be output parameters in the proc, and ExecuteNonQuery. My second choice is to use SqlDataReader with ExecuteReader. I can't get either of these to work. In the first case, I suspect I am not specifying the parameters correctly, but when debugging the error msg says that I am not supplying parameters that are called for. In the second case I'm not able to read the results. Here is the proc:
here are my parameter lines
and I've tried a few variations on this theme.
any ideas?
Problem is, I can't access the fields in my code. My first choice would be output parameters in the proc, and ExecuteNonQuery. My second choice is to use SqlDataReader with ExecuteReader. I can't get either of these to work. In the first case, I suspect I am not specifying the parameters correctly, but when debugging the error msg says that I am not supplying parameters that are called for. In the second case I'm not able to read the results. Here is the proc:
USE [lucidequipment]
GO
/****** Object: StoredProcedure [dbo].[uspGetMailFields] Script Date: 08/04/2011 08:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.uspGetMailFields @msgId int, @msgbody nvarchar OUTPUT, @msgSubject nvarchar OUTPUT
AS
BEGIN
SELECT msgSubject, msgBody
FROM eMailMessages
WHERE msgId = @msgId
END
GO
here are my parameter lines
cmd.Parameters.AddWithValue("@msgId", msgId)
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("msgsubject", SqlDbType.NVarChar, ParameterDirection.Output))
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("msgbody", SqlDbType.NVarChar, ParameterDirection.Output))
and I've tried a few variations on this theme.
any ideas?
Try this in your vb.net code
Let us know how it went.
cmd.Parameters.AddWithValue("@msgId", msgId)
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgsubject", SqlDbType.NVarChar, ParameterDirection.InputOutput))
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.InputOutput))
Let us know how it went.
Ooops... The last line should not be commented.
cmd.Parameters.AddWithValue("@msgId", msgId)
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgsubject", SqlDbType.NVarChar, ParameterDirection.InputOutput))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.InputOutput))
Also you are missing the @ symbol in front of the parameter names :
cmd.Parameters.AddWithValue("@msgId", msgId)
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgsubject", SqlDbType.NVarChar, ParameterDirection.Output))
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.Output))
ASKER
still not working. Err msg is "Procedure or function 'uspGetMailFields' expects parameter '@msgbody', which was not supplied."
the immediately preceding code is
Fails at the executeNonQuery line
the immediately preceding code is
Dim cmd As New SqlCommand()
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "uspGetMailFields"
cmd.Connection = con
cmd.Parameters.AddWithValue("@msgId", msgId)
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgsubject", SqlDbType.NVarChar, ParameterDirection.InputOutput))
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.InputOutput))
con.Open()
'Execute the Stored Procedure
cmd.ExecuteNonQuery()
con.Close()
Fails at the executeNonQuery line
ASKER
looking back at the proc, I wrote "SELECT msgSubject" and it became (perhaps b/c of the output parameters) "SELECT @msgSubject=msgSubject" which looks at best backwards.
I think the proc looks good, although I wonder why you're not specifying the size of your nvarchar variables. You can try out the proc in sql server just to see if it works
Try this code in a tsql window:
If you get results from this query, your sp is fine.
Then you can try to assign some values to your parameters from VB, just in case (since you're not using default values in the sp).
Something like this:
Since you have no default values for these parameters, try assigning something from VB.
cmd.Parameters.Add(New System.Data.SqlClient.SqlP arameter(" @msgsubjec t", SqlDbType.NVarChar, ParameterDirection.InputOu tput)).Val ue=""
cmd.Parameters.Add(New System.Data.SqlClient.SqlP arameter(" @msgbody", SqlDbType.NVarChar, ParameterDirection.InputOu tput)).Val ue""
Try this code in a tsql window:
DECLARE @v1 NVARCHAR
DECLARE @v2 NVARCHAR
DECLARE @mId INT
SELECT @v1='', @v2='', @mId=1
EXEC uspGetMailFields @mId, @v1, @v2
SELECT @v1, @v2
If you get results from this query, your sp is fine.
Then you can try to assign some values to your parameters from VB, just in case (since you're not using default values in the sp).
Something like this:
Since you have no default values for these parameters, try assigning something from VB.
cmd.Parameters.Add(New System.Data.SqlClient.SqlP
cmd.Parameters.Add(New System.Data.SqlClient.SqlP
ASKER
with the proc mod's suggested by Greg and the vb parameter mods from LionKing, the failure message now is
Procedure or function 'uspGetMailFields' expects parameter '@msgbody', which was not supplied. why at msgbody and not msgSubject?
Is the problem in the proc?
Procedure or function 'uspGetMailFields' expects parameter '@msgbody', which was not supplied. why at msgbody and not msgSubject?
Is the problem in the proc?
Try out the procedure with the tsql code I provided above. Let us know if you get the results you expect.
@msgBody is defined first in your parameter list in the stored procedure which is why it errors on that first.
You also have defined @msgSubject with a capital S in the stored procedure but with a lower case s in the Add parameter statement.
I would also change ParameterDirection to ParameterDirection.Output, changing it to ParameterDirection.InputOu tput is just confusing the issue and probably causing your current problem.
You also have defined @msgSubject with a capital S in the stored procedure but with a lower case s in the Add parameter statement.
I would also change ParameterDirection to ParameterDirection.Output,
ASKER
I did execute the short test program in tsql, and it returned exactly the expected result--the first record in the file, two fields, so the proc is probably ok.
I'm now going to specify the size of the variables. Back shortly.
I'm now going to specify the size of the variables. Back shortly.
ASKER
Whether I use inputoutput or just output doesn't seem to matter
I can't seem to feed parameters to the proc in a matter similar to the working short tsql program, which does return msgSubject and msgBody.
What does have an effect is whether the AddParameter lines have Value="" at the end. With that, the query executes, but returns "" instead of the db fields.
So I'm still not working.
I can't seem to feed parameters to the proc in a matter similar to the working short tsql program, which does return msgSubject and msgBody.
What does have an effect is whether the AddParameter lines have Value="" at the end. With that, the query executes, but returns "" instead of the db fields.
So I'm still not working.
ASKER
here is the current state of the code:
Dim con As New SqlConnection()
con.ConnectionString = ConfigurationManager.ConnectionStrings("lucidequipmentConnectionString").ConnectionString
Dim cmd As New SqlCommand()
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "uspGetMailFields"
cmd.Connection = con
cmd.Parameters.AddWithValue("@msgId", msgId)
cmd.Parameters.AddWithValue("@msgSubject", "")
cmd.Parameters.AddWithValue("@msgbody", "")
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar(50), ParameterDirection.InputOutput)).Value = ""
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar(400), ParameterDirection.InputOutput)).Value = ""
con.Open()
'Execute the Stored Procedure
cmd.ExecuteNonQuery()
con.Close()
Dim subject As String = CType(cmd.Parameters("@msgSubject").Value, String)
Dim body As String = CType(cmd.Parameters("@msgBody").Value, String)
Try
MailHelper.SendMailMessage(from, recipient, bcc, ccAddress, subject, body)
'successful
Label1.Text = "finally"
Catch ex As Exception
Response.Write(ex.Message) 'Write error message
Label1.Text = "still broken"
Exit Sub
End Try
End Sub
Please post the current Stored Procedure you are using as well as the .NET code. The .NET code you have posted here has the setting where you define the direction and data types commented out.
ASKER
There are a few parameter setting lines in the above code. Where one version might be commented out, another version is not. Following is both the proc and the code:
Proc first--
and the .NET code is
Proc first--
USE [lucidequipment]
GO
/****** Object: StoredProcedure [dbo].[uspGetMailFields] Script Date: 08/04/2011 11:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.uspGetMailFields @msgId int, @msgbody nvarchar (400) OUTPUT, @msgSubject nvarchar (50) OUTPUT
AS
BEGIN
SELECT msgSubject, msgBody
FROM eMailMessages
WHERE msgId = @msgId
END
GO
and the .NET code is
Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
Dim msgName As String = "bidaccepted"
Dim msgId As Integer = 4
Dim equipmentId As Integer = 242
Dim from As String = "admin@lucideq.com"
Dim recipient As String = "xxxxxxxxxxxxxxxxx"
Dim bcc As String = ""
Dim ccAddress As String = "andy@xxxxxxxxxt"
'Dim subject As String = "bid submitted"
'Dim body As String = "a friggin' test; if you are getting this, you know that email is finally going out after a long struggle"
'inline code to call the stored proc to read the emailMessage table
'Need a reader to read the returned data
Dim con As New SqlConnection()
con.ConnectionString = ConfigurationManager.ConnectionStrings("lucidequipmentConnectionString").ConnectionString
Dim cmd As New SqlCommand()
cmd.CommandType = System.Data.CommandType.StoredProcedure
cmd.CommandText = "uspGetMailFields"
cmd.Connection = con
cmd.Parameters.AddWithValue("@msgId", msgId)
' cmd.Parameters.AddWithValue("@msgSubject", "")
cmd.Parameters.AddWithValue("@msgbody", "")
cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar, ParameterDirection.Output)).Value = ""
'cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.InputOutput)).Value = ""
con.Open()
'Execute the Stored Procedure
cmd.ExecuteNonQuery()
con.Close()
Dim subject As String = CType(cmd.Parameters("@msgSubject").Value, String)
Dim body As String = CType(cmd.Parameters("@msgBody").Value, String)
Try
MailHelper.SendMailMessage(from, recipient, bcc, ccAddress, subject, body)
'successful
Label1.Text = "finally"
Catch ex As Exception
Response.Write(ex.Message) 'Write error message
Label1.Text = "still broken"
Exit Sub
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
P.S. You don't have to assign a value for any of the output parameters, I forgot to remove that.
And remember that the connection must be open before the cmd.ExecuteNonQuery() line.
Let us know how it goes.
And remember that the connection must be open before the cmd.ExecuteNonQuery() line.
Let us know how it goes.
ASKER
Hey, it's finally working. Many thanks for the patience in getting me through this. Now I need to put this code in a proper place, probably as a sub in a vb page in my app directory, and call it from there, feeding it the msgId or the msgName. My MailHelper routine to actually send the mail has been working for a while.
Also, the recipients are a dynamically generated list. I have to figure that out too. I am for now planning on putting a query in the emailmessages table field that will select the recipients. Then I have to execute the query and pass the result set to the email program, probably using a dataset and reader, all new code to me.
Also, the recipients are a dynamically generated list. I have to figure that out too. I am for now planning on putting a query in the emailmessages table field that will select the recipients. Then I have to execute the query and pass the result set to the email program, probably using a dataset and reader, all new code to me.
Greg
Open in new window