C# sql server 2008 r2 insert stored procedure won't work

Question

i've got this stored procudure that won't work. If I try this query in the management studio with my parameters filled in it works, i can't see if i've done anything wrong in my code but i hope someone here does notice something i'm doing wrong

CREATE PROCEDURE new_project 
-- Add the parameters for the stored procedure here
@proj_naam nvarchar = null,
@plaats nvarchar = null,
@opd_geef int = 0,
@status int = 0,
@proj_id int = 0  AS  BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO project (naam_project, plaats, opdrachtgeverZEEBREGTS_nr, status, project_NR)
VALUES (@proj_naam, @plaats, @opd_geef, @status, @proj_id)  END  GO

and c# code:

System.Data.SqlClient.SqlConnection con;
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = Global.ConnectionString_fileserver;
            con.Open();
            string stopro = "";
            switch (type)
            {
                case 1:
                    stopro = "new_project";
                    break;
                case 2:
                    stopro = "new_bedrijf";
                    break;
                case 3:
                    stopro = "new_persoon";
                    break;
            }
            SqlCommand command = new SqlCommand(stopro, con);
            command.CommandType = CommandType.StoredProcedure;
            switch (type)
            {
                case 1:
                    SqlParameter proj_naam = command.Parameters.Add("@proj_naam", SqlDbType.NVarChar);
                    SqlParameter plaats = command.Parameters.Add("@plaats", SqlDbType.NVarChar);
                    SqlParameter opdrachtgever = command.Parameters.Add("@opd_geef", SqlDbType.Int);
                    SqlParameter status = command.Parameters.Add("@status", SqlDbType.Int);
                    SqlParameter proj_id = command.Parameters.Add("@proj_id", SqlDbType.Int);
                    proj_naam.Value = tb_proj_projectnaam.Text; proj_naam.Direction = ParameterDirection.Input;
                    plaats.Value = tb_proj_plaats.Text; plaats.Direction = ParameterDirection.Input;
                    opdrachtgever.Value = cb_proj_opdrachtgever.SelectedValue; opdrachtgever.Direction = ParameterDirection.Input;
                    status.Value = cb_proj_status.SelectedValue; status.Direction = ParameterDirection.Input;
                    proj_id.Value = id; proj_id.Direction = ParameterDirection.Input;
                    break;  
            }  
            int nwok = command.ExecuteNonQuery();
            con.Close();

So i hope someone can help thnx in advance!

Can you define "won't work"? Does it give an error message, or not return anything? Does it actually get to the ExecuteNonQuery line, because it is after the break, but that just may have been because you snipped some code out. — John Koerner
– John Koerner, Commented Feb 17, 2011 at 14:29
no errors, the int nwok stores the number of rows affected, so if a row is added it will return 1, if not 0, and i'm only getting 0. And if I check my database the new row is not there. So it didnt do anything. — Daanvl
– Daanvl, Commented Feb 17, 2011 at 14:31
Also, I apologize but I just have to say the obvious because I've done this myself before. Are you sure you're on the right SQL Server? I can't tell you how many times I've executed against Dev but checked against Live. — Chris Haas
– Chris Haas, Commented Feb 17, 2011 at 15:25
@Chris Haas, haha no luckily this is not the case. This problem is now solved (thnx y'all) now i need to find why he keeps using the ID# from the last time a new record was added in stead of the next smallest non existing nr.. so I will have something to do this weekend :) — Daanvl
– Daanvl, Commented Feb 17, 2011 at 15:54

Chris Haas · Accepted Answer · 2011-02-17 15:19:21Z

3

You have a break before your int nwok = command.ExecuteNonQuery();

EDIT

Not related to why your SPROC isn't executing but you've got SET NOCOUNT ON which will cause -1 to be returned by ExecuteNonQuery. Are you running SET NOCOUNT OFF before the insert?

edited Feb 17, 2011 at 15:19

answered Feb 17, 2011 at 14:30

Chris Haas

55.7k13 gold badges152 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daanvl Over a year ago

this is because after that there are a few more irrelevant cases that i cut out so the post doesnt get a mile long :P but i will chek if execute gets called nonetheless. edit: no, execute gets called just fine..

Daanvl Over a year ago

NOCOUNT ON was one of the reasons i didnt work, stupid autogenerated microsoft code.. the other was (i think) not defining a length for the NVARCHAR's

Shekhar_Pro · Accepted Answer · 2011-02-17 15:01:43Z

1

i can see that if Case is not 1 in your switch case then the command instance never get those parameters so it wont work.

edited Feb 17, 2011 at 15:01

answered Feb 17, 2011 at 14:30

Shekhar_Pro

18.5k10 gold badges58 silver badges79 bronze badges

6 Comments

Shekhar_Pro Over a year ago

@Ardman it wasn't before the edit. so my answer was for that case. You can check his question's revisions

Daanvl Over a year ago

Is this not the right place for the break? at the and of the statement and before i define case 2: ? And I checked if case 1 was called with a messagebox and that also works fine.

Neil Knight Over a year ago

@Daanvl: Yes, it is the right place to define a break statement.

Shekhar_Pro Over a year ago

@Daanvl: no your break statement is perfectly in right place.. you should check in debugger if correct values are being passed to parameter and Command instance is getting the parameters

Daanvl Over a year ago

@Shekhar_Pro i found this program anjlab sql profiler and got this:exec new_project @proj_naam=N'Daan',@plaats=N'tilburg',@opd_geef=4,@status=2,@proj_id=946 and some other line's, whats the N before the '' is that the problem or is it to define an NVARCHAR?

|

Neil Knight · Accepted Answer · 2011-02-17 15:01:18Z

0

Here you are testing the type in a switch statement:

switch (type)             
{                 
    case 1:                     
        stopro = "new_project";                     
        break;                 
    case 2:
        stopro = "new_bedrijf";                     
        break;
    // etc.
}

You then retest the type in another switch statement which is just overkill. Move your parameters into the first switch statement and this may resolve your error.

switch (type)             
{                 
    case 1:                     
        stopro = "new_project";     
        SqlParameter proj_naam = command.Parameters.Add("@proj_naam", SqlDbType.NVarChar);                     
        SqlParameter plaats = command.Parameters.Add("@plaats", SqlDbType.NVarChar);
        SqlParameter opdrachtgever = command.Parameters.Add("@opd_geef", SqlDbType.Int); 
        SqlParameter status = command.Parameters.Add("@status", SqlDbType.Int); 
        SqlParameter proj_id = command.Parameters.Add("@proj_id", SqlDbType.Int);
        proj_naam.Value = tb_proj_projectnaam.Text; 
        proj_naam.Direction = ParameterDirection.Input;   
        plaats.Value = tb_proj_plaats.Text; 
        plaats.Direction = ParameterDirection.Input; 
        opdrachtgever.Value = cb_proj_opdrachtgever.SelectedValue; 
        opdrachtgever.Direction = ParameterDirection.Input;
        status.Value = cb_proj_status.SelectedValue; 
        status.Direction = ParameterDirection.Input;
        proj_id.Value = id; 
        proj_id.Direction = ParameterDirection.Input;                    
        break;                 
    case 2:
        stopro = "new_bedrijf";                     
    // etc.
}

edited Feb 17, 2011 at 15:01

answered Feb 17, 2011 at 14:54

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

2 Comments

Daanvl Over a year ago

I think i'm forced to do it the other way because I need to define the sqlcommand command first with the right string for the right stored procedure and then i can call commandparameter.add. or Is there another way? I would like to avoid defining command1, command2 command3 etc if its possible

Neil Knight Over a year ago

@Daanvl: you can create your SqlCommand before the switch statement without any issues.

Collectives™ on Stack Overflow

C# sql server 2008 r2 insert stored procedure won't work

3 Answers 3

2 Comments

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related