Jump to content
Sign in to follow this  
danielj

Trouble calling an SQL Stored Procedure

Recommended Posts

My goal is to have users fill out three fields and click on a button that will check an sql table to see if there is a record with those three fields already in the database. I'm not 100% sure, but I think my issue is in my C# code calling the procedure. I have tested my stored procedure from sql and it works. I've tried a few things, but nothing is working. The code below is one of my attempts that isn't working. I'm not sure, but when I step through the program it doesn't appear to be calling the procedure (though I could be wrong and the problem is elsewhere).

 

SqlConnection conn = new SqlConnection(GetConnectionString());
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SubmitCheck", conn);
SqlParameter sqlCheck = new SqlParameter("@result", DbType.Int16);
cmd.Parameters.AddWithValue("@pfName", fName.Text);
cmd.Parameters.AddWithValue("@plName", lName.Text);
cmd.Parameters.AddWithValue("@pssn", ssn.Text);
cmd.CommandType = CommandType.StoredProcedure;
bool check = Convert.ToBoolean(cmd.ExecuteScalar());
if (check)
{
checkEntrylbl.Text = "You have not yet completed this form. Please complete the form.";
checkEntrylbl.Visible = true;
lName.Enabled = false;
fName.Enabled = false;
ssn.Enabled = false;
submitButton.Enabled = true;
}
else
{
checkEntrylbl.Text = "You have completed this form. You do not have to fill it out again.";
checkEntrylbl.Visible = true;
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
Here is the stored procedure code.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SubmitCheck
-- Add the parameters for the stored procedure here
@pfName varchar(50),
@plName varchar(50),
@pssn int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Checks if there is an entry with the same first name, last name, and last four of ssn.
IF (EXISTS(SELECT fName, lName, ssn FROM dbo.compassSurvey
WHERE @pfName = fName AND @plName = lName AND @pssn = ssn))
BEGIN
RETURN (0);
END
BEGIN
RETURN (1);
END
END

 

Thanks for any suggestions.

Share this post


Link to post
Share on other sites

It seems fine to me, have you checked the values of the 3 parameters that you're adding to make sure they are what you think they are?

Share this post


Link to post
Share on other sites

The code seems fine. I don't see any reason why it skip calling the procedure without an error being created. It sounds like it's calling the procedure and sending it the correct values, and the procedure is returning 0.

Share this post


Link to post
Share on other sites

Thanks for taking the time to help.

 

I figured I would try a slightly different approach, but this isn't working either. I get the error: Insert Error:Procedure or function 'SubmitCheck' expects parameter '@pfName', which was not supplied.

 

From what I can see this should work. The code is below.

 

SqlConnection conn = new SqlConnection(GetConnectionString());
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SubmitCheck", conn);
SqlParameter sqlCheck = new SqlParameter("@check", DbType.Int16) {Direction=ParameterDirection.Output };
cmd.Parameters.Add(sqlCheck);
cmd.Parameters.AddWithValue("@pfName", fName.Text);
cmd.Parameters.AddWithValue("@plName", lName.Text);
cmd.Parameters.AddWithValue("@pssn", ssn.Text);
cmd.ExecuteNonQuery();
int check = int.Parse(cmd.Parameters["@check"].Value.ToString());
if (check==1)
{
checkEntrylbl.Text = "You have not yet completed this form. Please complete the form.";
checkEntrylbl.Visible = true;
lName.Enabled = false;
fName.Enabled = false;
ssn.Enabled = false;
submitButton.Enabled = true;
}
else
{
checkEntrylbl.Text = "You have completed this form. You do not have to fill it out again.";
checkEntrylbl.Visible = true;
}
}
ALTER PROCEDURE [dbo].[submitCheck]
-- Add the parameters for the stored procedure here
@pfName varchar(50),
@plName varchar(50),
@pssn int,
@check int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @check = 0;
-- Checks if there is an entry with the same first name, last name, and last four of ssn.
IF (EXISTS(SELECT fName, lName, ssn FROM dbo.compassSurvey
WHERE @pfName = fName AND @plName = lName AND @pssn = ssn))
BEGIN
-- This statement runs if there is an entry. Since there is an entry, they do not need to fill out the form.
-- Thus, return 0 (false).
RETURN (@check);
END
BEGIN
SET @check = 1;
-- This statement runs if there is not an entry. Since there is not an entry, they do need to fill out the form.
-- Thus, return 1 (true).
RETURN (@check);
END
END

Share this post


Link to post
Share on other sites

From what I can tell from the documentation online, it looks like you still need to set the command type.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...