Problem with stored procedure

[Gaab]

Hi Folks,

I've created a stored procedure (Incident_Add) and called it, but when I
excecute it in the code it says: "System.Data.SqlClient.SqlException: Line
1: incorrect syntax at Incident_Add."

When I run the procedure from VisualStudio ServerExplorer it works, but not
in this way,

Any Suggestions?

Stored Procedure:
ALTER PROCEDURE dbo.Incident_Add

@IncidentName NVarChar(50),

@IncidentPriority Int,

@RegisteredBy Int,

@ClientId Int,

@AffectedUser Int,

@AffectedAsset int,

@Severity Int,

@Category1 Int,

@Category2 Int,

@Category3 Int,

@Category4 Int,

@Category5 Int,

@Category1Other NVarChar(30),

@Category2Other NVarChar(30),

@Category3Other NVarChar(30),

@Category4Other NVarChar(30),

@Category5Other NVarChar(30)
AS

INSERT Incident

(

IncidentName,

IncidentPriority,

RegisteredBy,

ClientId,

AffectedUser,

AffectedAsset,

Severity,

Category1,

Category2,

Category3,

Category4,

Category5,

Category1Other,

Category2Other,

Category3Other,

Category4Other,

Category5Other

)

VALUES

(

@IncidentName,

@IncidentPriority,

@RegisteredBy,

@ClientId,

@AffectedUser,

@AffectedAsset,

@Severity,

@Category1,

@Category2,

@Category3,

@Category4,

@Category5,

@Category1Other,

@Category2Other,

@Category3Other,

@Category4Other,

@Category5Other

)

RETURN @@IDENTITY

Code:

SqlCommand scSaveIncident = new SqlCommand("Incident_Add",this.oSql);

scSaveIncident.Parameters.Add(new
SqlParameter("@IncidentName",OleDbType.VarChar)).V alue =
this.textBoxSubject.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@IncidentPriority",OleDbType.Integer )).Value =
Convert.ToInt32(this.dropPriority.SelectedItem.Tex t);

scSaveIncident.Parameters.Add(new
SqlParameter("@RegisteredBy",OleDbType.Integer)).V alue =
this.dropUser.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@ClientId",OleDbType.Integer)).Value =
this.dropClient.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@AffectedUser",OleDbType.Integer)).V alue =
this.dropClientUser.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@AffectedAsset",OleDbType.Integer)). Value =
this.dropAsset.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Severity",OleDbType.Integer)).Value =
Convert.ToInt32(this.dropSeverity.SelectedItem.Tex t);

scSaveIncident.Parameters.Add(new
SqlParameter("@Category1",OleDbType.Integer)).Valu e =
this.dropCategory1.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category2",OleDbType.Integer)).Valu e =
this.dropCategory2.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category3",OleDbType.Integer)).Valu e =
this.dropCategory3.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category4",OleDbType.Integer)).Valu e =
this.dropCategory4.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category5",OleDbType.Integer)).Valu e =
this.dropCategory5.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category1Other",OleDbType.VarChar)) .Value =
this.CategoryOther1.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category2Other",OleDbType.VarChar)) .Value =
this.CategoryOther2.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category3Other",OleDbType.VarChar)) .Value =
this.CategoryOther3.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category4Other",OleDbType.VarChar)) .Value =
this.CategoryOther4.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category5Other",OleDbType.VarChar)) .Value =
this.CategoryOther5.Text;

//scSaveIncident.Parameters.Add(new
SqlParameter("@Description",OleDbType.LongVarChar) ).Value =
this.dropClient.SelectedValue;
scSaveIncident.ExecuteQuery();

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I believe you are missing the ( ) around the arguments, this is what VS.NET
gives you when you select "New SP"
CREATE PROCEDURE dbo.StoredProcedure1

/*

(

@parameter1 datatype = default value,

@parameter2 datatype OUTPUT

)

*/

AS

/* SET NOCOUNT ON */

RETURN
Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Gaab" <sp**@adiso.nl> wrote in message
news:10*************@corp.supernews.com...

Hi Folks,

I've created a stored procedure (Incident_Add) and called it, but when I
excecute it in the code it says: "System.Data.SqlClient.SqlException: Line
1: incorrect syntax at Incident_Add."

When I run the procedure from VisualStudio ServerExplorer it works, but not in this way,

Any Suggestions?

Stored Procedure:
ALTER PROCEDURE dbo.Incident_Add

@IncidentName NVarChar(50),

@IncidentPriority Int,

@RegisteredBy Int,

@ClientId Int,

@AffectedUser Int,

@AffectedAsset int,

@Severity Int,

@Category1 Int,

@Category2 Int,

@Category3 Int,

@Category4 Int,

@Category5 Int,

@Category1Other NVarChar(30),

@Category2Other NVarChar(30),

@Category3Other NVarChar(30),

@Category4Other NVarChar(30),

@Category5Other NVarChar(30)
AS

INSERT Incident

(

IncidentName,

IncidentPriority,

RegisteredBy,

ClientId,

AffectedUser,

AffectedAsset,

Severity,

Category1,

Category2,

Category3,

Category4,

Category5,

Category1Other,

Category2Other,

Category3Other,

Category4Other,

Category5Other

)

VALUES

(

@IncidentName,

@IncidentPriority,

@RegisteredBy,

@ClientId,

@AffectedUser,

@AffectedAsset,

@Severity,

@Category1,

@Category2,

@Category3,

@Category4,

@Category5,

@Category1Other,

@Category2Other,

@Category3Other,

@Category4Other,

@Category5Other

)

RETURN @@IDENTITY

Code:

SqlCommand scSaveIncident = new SqlCommand("Incident_Add",this.oSql);

scSaveIncident.Parameters.Add(new
SqlParameter("@IncidentName",OleDbType.VarChar)).V alue =
this.textBoxSubject.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@IncidentPriority",OleDbType.Integer )).Value =
Convert.ToInt32(this.dropPriority.SelectedItem.Tex t);

scSaveIncident.Parameters.Add(new
SqlParameter("@RegisteredBy",OleDbType.Integer)).V alue =
this.dropUser.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@ClientId",OleDbType.Integer)).Value =
this.dropClient.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@AffectedUser",OleDbType.Integer)).V alue =
this.dropClientUser.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@AffectedAsset",OleDbType.Integer)). Value =
this.dropAsset.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Severity",OleDbType.Integer)).Value =
Convert.ToInt32(this.dropSeverity.SelectedItem.Tex t);

scSaveIncident.Parameters.Add(new
SqlParameter("@Category1",OleDbType.Integer)).Valu e =
this.dropCategory1.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category2",OleDbType.Integer)).Valu e =
this.dropCategory2.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category3",OleDbType.Integer)).Valu e =
this.dropCategory3.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category4",OleDbType.Integer)).Valu e =
this.dropCategory4.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category5",OleDbType.Integer)).Valu e =
this.dropCategory5.SelectedValue;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category1Other",OleDbType.VarChar)) .Value =
this.CategoryOther1.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category2Other",OleDbType.VarChar)) .Value =
this.CategoryOther2.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category3Other",OleDbType.VarChar)) .Value =
this.CategoryOther3.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category4Other",OleDbType.VarChar)) .Value =
this.CategoryOther4.Text;

scSaveIncident.Parameters.Add(new
SqlParameter("@Category5Other",OleDbType.VarChar)) .Value =
this.CategoryOther5.Text;

//scSaveIncident.Parameters.Add(new
SqlParameter("@Description",OleDbType.LongVarChar) ).Value =
this.dropClient.SelectedValue;
scSaveIncident.ExecuteQuery();

[Marc Scheuner [MVP ADSI]]

>Code:

SqlCommand scSaveIncident = new SqlCommand("Incident_Add",this.oSql);

You have to tell it that it's a STORED PROC !

scSaveIncident.CommandType = CommandType.StoredProcedure;

Marc
================================================== ==============
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch