CODEWITHSUNDEEP
c#stored-proceduressql-server-2008-r2
Daanvl
Daanvl

Reputation: 630

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

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!

Upvotes: 2

Views: 1521

Answers (3)

Chris Haas
Chris Haas

Reputation: 55427

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?

Upvotes: 3

Shekhar_Pro
Shekhar_Pro

Reputation: 18430

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.

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48547

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.
}

Upvotes: 0

Related Questions