Inserting values with SP

Question

Post reply

Inserting values with SP

altana

SSC Journeyman

Points: 96
More actions
March 17, 2014 at 7:46 am

#291067

I try to do all insert-actions in one SP. But it doesn't work. I couldn't insert any values into the DB. Is this possible or the wrong way?
use env
go
create proc [SP$insert](
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output,
@debug bit = 0
)
as
begin
set nocount on
begin try
if @p2='User Name'
begin
INSERT INTO env.dbo.Base(u_name) VALUES (@p1)
SELECT @id = @@IDENTITY
end
else if @p2='Serial Number'
begin
INSERT INTO env.dbo.Base(ser_num) VALUES (@p1)
SELECT @id= @@IDENTITY
end
end try
begin catch
if @debug = 1
throw;
end catch
set nocount off
end
go
C#:
try
{
cmd.CommandText = "SP$insert";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p1", textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@p2", f1.textBox1.Text));
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Direction = ParameterDirection.Output;
cmd.Connection = baglan;
baglan.Open();
cmd.ExecuteNonQuery();
baglan.Close();
MessageBox.Show("Inserted!");
}
catch (Exception)
{
MessageBox.Show("Not Inserted!");
throw;
}
finally

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What errors are you getting?

p.s. A catch block which just discards the error is not exactly good coding practice.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Could be that you had the wrong value in @p2, so the flow did not get inside the if statement.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

altana SSC Journeyman Points: 96 More actions · Answer 3

Adi Cohn-120898 (3/17/2014)
Could be that you had the wrong value in @p2, so the flow did not get inside the if statement.
Adi

No, it's the right value. Wenn I insert only into one column, it works.

altana SSC Journeyman Points: 96 More actions · Answer 4

GilaMonster (3/17/2014)
What errors are you getting?
p.s. A catch block which just discards the error is not exactly good coding practice.

I doesn't give any error. The message is 'Inserted' but the DB couldn't be updated.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Slightly off topic but you might look closely at what @@IDENTITY returns. Almost everytime I see this the person really wants the identity value from the insert they just executed. If that is what you want you should change your habit and use SCOPE_IDENTITY instead.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

altana (3/17/2014)
Adi Cohn-120898 (3/17/2014)
Could be that you had the wrong value in @p2, so the flow did not get inside the if statement.
Adi
No, it's the right value. Wenn I insert only into one column, it works.

Of course you don't get an error message back. You didn't pass a value for @debug so it defaults to 0. And your catch block is not error handling, it is error suppression. In other words, your stored proc raised an error of some kind but your catch block simply ignores it because @debug is 0. I would remove that section of code and let your stored proc return the error to your program so you can see what is going on.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

altana (3/17/2014)
GilaMonster (3/17/2014)
What errors are you getting?
p.s. A catch block which just discards the error is not exactly good coding practice.
I doesn't give any error.

Probably because the catch block just discards the error without bothering to log/output it. Fix the catch block so that it actually does something useful with the error (or remove it entirely), then see what error you get.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

altana SSC Journeyman Points: 96 More actions · Answer 8

altana

SSC Journeyman

Points: 96

March 17, 2014 at 8:26 am

#1697657

It still works same.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

What error are you getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

altana SSC Journeyman Points: 96 More actions · Answer 10

GilaMonster (3/17/2014)
What error are you getting?

Nothing. The message is 'Inserted' but any values can be inserted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Remove the error handling (database side and client side, since both hide the errors) and have a look at exactly what is being returned

Also try running the stored proc from SQL (with the useless catch block removed) and see what that returns.

What do you mean "any values can be inserted."?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

altana (3/17/2014)
It still works same.

How do you know that the users are entering the right values in your textbox?

Your C# code is passing the value from a textbox as p2.

cmd.Parameters.Add(new SqlParameter("@p2", f1.textBox1.Text));

If the user enters any value besides "User Name" or "Serial Number" your proc isn't going to do anything.

if @p2='User Name'

...

else if @p2='Serial Number'

...

For debugging you might consider adding "select @p2" to your code. Then consider changing the UI component from a textbox to a combobox so that YOU control the values going in.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Sean Lange (3/17/2014)
Slightly off topic but you might look closely at what @@IDENTITY returns. Almost everytime I see this the person really wants the identity value from the insert they just executed. If that is what you want you should change your habit and use SCOPE_IDENTITY instead.

+1!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

altana SSC Journeyman Points: 96 More actions · Answer 14

GilaMonster (3/17/2014)
Remove the error handling (database side and client side, since both hide the errors) and have a look at exactly what is being returned
Also try running the stored proc from SQL (with the useless catch block removed) and see what that returns.
What do you mean "any values can be inserted."?

This is the last form. I can't still insert.It's not giving error. There are only warnings.

'Warning1The currently targeted framework ".NETFramework,Version=v4.0,Profile=Client" does not include "System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" which the referenced assembly "CrystalDecisions.CrystalReports.Engine, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304, processorArchitecture=MSIL" depends on. This caused the referenced assembly to not resolve. To fix this, either (1) change the targeted framework for this project, or (2) remove the referenced assembly from the project.WindowsFormsApplication2

'

use env

go

create proc [SP$insert](

@p1 nvarchar(100),

@p2 nvarchar(100),

@id int output

)

as

begin

set nocount on

select @p2

if @p2='User Name'

begin

INSERT INTO env.dbo.Base(user_name) VALUES (@p1)

SELECT @id= @@IDENTITY

end

else if @p2='Serial Number'

begin

INSERT INTO env.dbo.Base(ser_num) VALUES (@p1)

SELECT @id = @@IDENTITY

end

set nocount off

end

go

public void button1_Click(object sender, EventArgs e)

{

SqlConnection baglan = new SqlConnection(@"Server=10.34.16.219; ....");

SqlCommand cmd = new SqlCommand();

Form1 f1 = new Form1();

f1.Show();

cmd.CommandText = "SP$insert";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@p1", textBox1.Text));

cmd.Parameters.Add(new SqlParameter("@p2", f1.textBox1.Text));

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Direction = ParameterDirection.Output;

cmd.Connection = baglan;

baglan.Open();

cmd.ExecuteNonQuery();

baglan.Close();

Form2_Load(sender, e);

this.Hide();

}