danielj Posted November 14, 2013 Share Posted November 14, 2013 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 More sharing options...
justsomeguy Posted November 15, 2013 Share Posted November 15, 2013 What happens when you run that? What does check get set to? Link to comment Share on other sites More sharing options...
danielj Posted November 15, 2013 Author Share Posted November 15, 2013 It is always gets set to false. Whether I try a entry that is not there (should be true) or try an entry that is there (should be false). Link to comment Share on other sites More sharing options...
justsomeguy Posted November 15, 2013 Share Posted November 15, 2013 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? Link to comment Share on other sites More sharing options...
danielj Posted November 15, 2013 Author Share Posted November 15, 2013 In Visual Studios, cmd >Parameters>base>Results View- it shows all three parameters having the expected values. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 15, 2013 Share Posted November 15, 2013 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. Link to comment Share on other sites More sharing options...
danielj Posted November 19, 2013 Author Share Posted November 19, 2013 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 More sharing options...
danielj Posted November 19, 2013 Author Share Posted November 19, 2013 I get the error once it reaches the line cmd.ExecuteNonQuery();. Link to comment Share on other sites More sharing options...
justsomeguy Posted November 20, 2013 Share Posted November 20, 2013 From what I can tell from the documentation online, it looks like you still need to set the command type. Link to comment Share on other sites More sharing options...
danielj Posted November 20, 2013 Author Share Posted November 20, 2013 Great, that works. One site I was looking at didn't have it, so I had that line commented out. Thanks for the help. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now