Jump to content

Trouble calling an SQL Stored Procedure


danielj

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.

Link to comment
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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...