stored procedures and output parameters

Mark Klein
Mark Klein used Ask the Experts™
on
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:
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

Open in new window


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))

Open in new window


and I've tried a few variations on this theme.

any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to assign values to the output parameters.

Greg


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 = msgSubject, @msgBody = msgBody
       FROM eMailMessages
       WHERE msgId = @msgId
END
GO

Open in new window

Try this in your vb.net code

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))

Open in new window


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))

Open in new window

Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
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)) 

Open in new window


Author

Commented:
still not working.  Err msg is "Procedure or function 'uspGetMailFields' expects parameter '@msgbody', which was not supplied."

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()

Open in new window


Fails at the executeNonQuery line

Author

Commented:
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:

DECLARE @v1 NVARCHAR
DECLARE @v2 NVARCHAR
DECLARE @mId INT

SELECT @v1='', @v2='', @mId=1
EXEC uspGetMailFields @mId, @v1, @v2

SELECT @v1, @v2

Open in new 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.SqlParameter("@msgsubject", SqlDbType.NVarChar, ParameterDirection.InputOutput)).Value=""
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, ParameterDirection.InputOutput)).Value""

Author

Commented:
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?
Try out the procedure with the tsql code I provided above. Let us know if you get the results you expect.
Paul JacksonSoftware Engineer
Top Expert 2011

Commented:
@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.InputOutput is just confusing the issue and probably causing your current problem.

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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

Open in new window

Top Expert 2012

Commented:
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.

Author

Commented:
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--
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

Open in new window


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

Open in new window

I tried your code and in fact it doesn't return anything, but I tried this and it worked.
Dim cmd As New SqlCommand()

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "uspGetMailFields"
        cmd.Connection = con

        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgId", SqlDbType.Int)).Value = 1
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgSubject", SqlDbType.NVarChar, 400)).Value = ""
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@msgbody", SqlDbType.NVarChar, 400)).Value = ""

        cmd.Parameters("@msgSubject").Direction = ParameterDirection.Output
        cmd.Parameters("@msgBody").Direction = ParameterDirection.Output

        If cmd.ExecuteNonQuery() <> 0 Then
            MessageBox.Show(String.Concat("Param 1: ", cmd.Parameters.Item("@msgSubject").Value.ToString()), "", MessageBoxButtons.OK)
            MessageBox.Show(String.Concat("Param 2: ", cmd.Parameters.Item("@msgBody").Value.ToString()), "", MessageBoxButtons.OK)
        End If

Open in new window


I remember running into this problem when I set the direction of the parameter in the same instruction. I changed and voila! it worked.
Let us know how it goes.

BTW, your last SQL script isn't the one you should be using because it's not assigning the output variables with anything.

This is the one you should be using:

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 (400) OUTPUT, @msgSubject nvarchar (50) OUTPUT
AS
BEGIN
       SELECT  @msgSubject=msgSubject, @msgbody=msgBody
       FROM eMailMessages
       WHERE msgId = @msgId
END
GO

Open in new window

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.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial