Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inserting values using stored procedure

Author  Topic 

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 11:18:39
Hi I am trying to insert few values from the web page into a table.
Here is my stored procedure:

ALTER PROCEDURE [dbo].[SP_InsertInvestigationSearchDetails]

	@rl_id uniqueidentifier,
	@rl_name nvarchar(250),
	@rl_description nvarchar(555),
	@rl_criteria1 nvarchar(155),
	@rl_criteria2 nvarchar(155),
	@rl_reg nvarchar(5),
	@rl_cashier nvarchar(50),
	@rl_type nvarchar(50),
	@rl_evenodd nvarchar(50),
	@rl_cat1 nvarchar(50),
	@rl_cat2 nvarchar(50),
	@rl_exp nvarchar(10),
	@rl_value nvarchar(50),
	@rl_exp1 nvarchar(10),
	@rl_value1 nvarchar(50),
	@rl_qty1 int = 0,
	@rl_qty2 int = 0,
	@rl_createdDate datetime,
	@rl_updatedDate datetime
	
	AS
	BEGIN
	Declare @ruleName nvarchar(250)
	--remove extra spaces from the entered rule name string
	set @ruleName = replace(replace(replace(@rl_name,' ','<>'),'><',''),'<>',' ');
	begin
		-- insert statement to insert exception rule details into exception rule detalis table
		Insert into ExceptionRuleDetail(rl_id, rl_name, rl_description,rl_register, rl_type,
		rl_criteria1, rl_criteria2, rl_category1, rl_category2,rl_amount,rl_amount_comparison,rl_amount_dollar_value1,
		 rl_amount_dollar_value2,rl_quantity_comparison,rl_quantity_dollar_value1,rl_quantity_dollar_value2,rl_createdDate,rl_updatedDate)
		values (@rl_id, ltrim(rtrim(@ruleName)), @rl_description,@rl_reg, @rl_type, @rl_criteria1,
		@rl_criteria2,@rl_cat1,@rl_cat2,@rl_evenodd,@rl_exp,@rl_value,@rl_value1,@rl_exp1,@rl_qty1,@rl_qty2,@rl_createdDate,@rl_updatedDate)
		--use return statement to return value from stored procedure
	
	end
	
END



And my function which is used to add parameters is:
public void InsertSearchDetails()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = (ConfigurationManager.AppSettings["Gulfcoast"]);
conn.Open();
SqlCommand cmd = new SqlCommand("SP_InsertInvestigationSearchDetails",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@rl_id", Guid.NewGuid());
cmd.Parameters.Add("@rl_name", SqlDbType.NVarChar).Value = txtSearchName.Text.Trim();
cmd.Parameters.Add("@rl_description", SqlDbType.NVarChar).Value = txtDesc.Text.Trim();
cmd.Parameters.Add("@rl_criteria1", SqlDbType.NVarChar).Value = Criteria.Text.Trim();
cmd.Parameters.Add("@rl_criteria2", SqlDbType.NVarChar).Value = Criteria1.Text.Trim();
cmd.Parameters.Add("@rl_type", SqlDbType.NVarChar).Value = Type.SelectedValue.ToString();
cmd.Parameters.Add("@rl_reg", SqlDbType.NVarChar).Value = register.Text.Trim();
cmd.Parameters.Add("@rl_cashier", SqlDbType.NVarChar).Value = Cashier.SelectedValue.ToString();
cmd.Parameters.Add("rl_evenodd", SqlDbType.NVarChar).Value = AnyEven.SelectedValue.ToString();
cmd.Parameters.Add("rl_cat1", SqlDbType.NVarChar).Value = Category1.SelectedValue.ToString();
cmd.Parameters.Add("rl_cat2", SqlDbType.NVarChar).Value = Category2.SelectedValue.ToString();
cmd.Parameters.Add("rl_exp", SqlDbType.NVarChar).Value = Expressions.SelectedValue.ToString();
cmd.Parameters.Add("rl_value", SqlDbType.NVarChar).Value = Value.Text.Trim();
cmd.Parameters.Add("rl_value1", SqlDbType.NVarChar).Value = Value1.Text.Trim();
cmd.Parameters.Add("rl_exp1", SqlDbType.NVarChar).Value = CompareQnty.SelectedValue.ToString();
if (((QtyValue1.Visible == true) && (QtyValue1.Text == "")) || (QtyValue1.Visible == false))
{
cmd.Parameters.Add("rl_qty1", SqlDbType.Int).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("rl_qty1", SqlDbType.Int).Value = Convert.ToInt32(QtyValue1.Text.Trim());
}
if (((QtyValue2.Visible == true) && (QtyValue2.Text == "")) || (QtyValue2.Visible == false))
{
cmd.Parameters.Add("rl_qty2", SqlDbType.Int).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("rl_qty2", SqlDbType.Int).Value = Convert.ToInt32(QtyValue2.Text.Trim());
}

cmd.Parameters.Add("@rl_createdDate", DateTime.Now);
cmd.Parameters.Add("@rl_updatedDate", DateTime.Now);
}

But nothing is being inserted into the table, nor it is throwing any errors. when i debug and check the parameter values, they appear right as the ones i enter in the web page. Please help me with this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-04 : 11:27:46
After setting up all the parameters as you have done, you also need a cmd.ExecuteNonQuery(). That is what actually sends the command to the SQL server.
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 11:29:51
I tried doing that. But it is still not working. :(
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 11:31:43
I am getting this error when i add cmd.ExecuteNonQuery();

System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-04 : 11:37:45
That error is coming from SQL server, so you reached the server. That is progress :--)

The error indicates that SQL is trying to convert a string to a number and it is not able to do so. Check your parameter values by stepping through the code to make sure that you are indeed sending numbers where the stored procedure is expecting numbers.

You could also run the stored procedure from SSMS using exactly the same parameter values you are using in the C# code and to see if that gives you an error.
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 11:52:51
ya now i understood what the problem is.
hey i am getting an error here :
cmd.Parameters.Add("rl_value", SqlDbType.Decimal).Value = Convert.ToDecimal(Value.Text.Trim());
cmd.Parameters.Add("rl_value1", SqlDbType.Decimal).Value = Convert.ToDecimal(Value.Text.Trim());

I checked all the datatypes. They match..but still getting this :(
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 11:55:14
System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-04 : 11:58:31
One more thing: Your parameter names in the C# code must match exactly with what is in the stored procedure. For example, in you C# code, a parameter is rl_cat1 without an "@", but in SQL it is @rl_cat1. Match them up exactly.

Once you get it working, you may want to wrap the connection and command objects in "using" statements so they will be disposed of properly and connections returned to connection pool. See an example here: http://msdn.microsoft.com/en-us/library/dw70f090.aspx
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 12:03:34
Hey I have a question:
While inserting values into the table, i am selecting only few columns and inserting those values. I am not using few columns in that table.
Do i have to set them to Null or not necessary?
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 12:07:25
I am not able to make out which parameter is throwing this error:
System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-04 : 12:54:12
I would do the following:

1. Go through your C# code and make sure that in every place where you have a stored procedure parameter, it is in the form "@abcdef", i.e., starting with an "@" sign, exactly as it is in the stored procedure.

2. Add a break point just before the cmd.ExecuteNonQuery() statement and inspect the parameter collection and the values to make sure that they are what you expect them to be.

3. In SQL management studio, in object explorer, look at the columns of your table and make sure that you are sending in numbers where columns are of numeric type.

4. If all of that fails, do an insert from SQL management studio using exactly the same values that you are seeing in the C# code.


Regarding whether you need to insert nulls: if the columns are nullable (which you can see by looking at the columns in the object exporer), you don't have to provide a value. If the column is not nullable and if you don't provide a value, SQL will complain loudly.
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 14:49:23
I did all of these. I see all the datatypes match. But still getting that error.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-03-04 : 15:02:23
1) Can you post the Create Table Statement of your table "ExceptionRuleDetail".

2) Provide the values of all parameters you are trying to pass via application e.g. (@rl_id, ltrim(rtrim(@ruleName)), @rl_description,@rl_reg, @rl_type, @rl_criteria1,@rl_criteria2,@rl_cat1,@rl_cat2,@rl_evenodd,@rl_exp,@rl_value,@rl_value1,@rl_exp1,@rl_qty1,@rl_qty2,@rl_createdDate,@rl_updatedDate)
in the same order as Highlighted.

Cheers
MIK
Go to Top of Page

sindhu122
Starting Member

32 Posts
Posted - 2011-03-04 : 15:38:55
hey thank you...i figured out the error.
got it working..thanks!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site