Stored Procedure is not working in .net application
-
Hi experts,
I have a stored proc in SQL Server 2000 and used for ASP.NET application. In my application, it will check if the Request.QueryString "Jobid" is null. If it's null then this sp will do INSERT; If id is found, then user can modify data and the sp will do UPDATE.
However, my sp is working and my application throws me an exception : Input string is not in correct format. I am thinking if it's because the logic is not correct for the @jobid and I wonder if this sp knows if it's an insert or update when I define @jobid as outparameter. However, Job_ID in database is an identity datatype.
Is anyone can help me to workaround the stored proc.? my sp is like this:
------------------------------------------------------
CREATE Procedure FHDJobEditing
@JobId smallint output,
@OpenDate smalldatetime,
@Location varchar(7) ,
@GM varchar(24),
@dm varchar (24),
@Priority tinyint ,
@StatusID tinyint,
@VendorID char(15),
@MajCatgID smallint,
@SubCatgID smallint,
@DueDate smalldatetime,
@Description text,
@resolution text,
@Note text ,
@CloseDate smalldatetime
As
If Exists (Select * from JobTesting_Table where Job_Id=@JobId)
Update JobTesting_Table
Set
J_Sub_Category_ID =@SubCatgID,
where Job_Id=@JobId
Else
Begin
Insert Into JobTesting_Table(
J_Open_Date,
J_Location,
J_GM,
J_DM,
J_Priority ,
J_Status_ID,
J_Vendor_ID,
J_M_Category_ID,
J_Sub_Category_ID ,
J_Description,
J_Resolution,
J_Note,
J_Due_Date ,
J_Close_Date
)
Values (
@OpenDate,
@Location ,
@GM,
@dm,
@Priority,
@StatusID,
@VendorID,
@MajCatgID,
@SubCatgID,
@Description,
@Note,
@DueDate ,
@CloseDate
)
Set @JobID=@@Identity
End
GO
---------------------------------------------------------
Thank you in advance.
-
my vb code is :
If txtJobID.Text Is Nothing Then
JobId = -1
Else
JobId = CInt(Request.QueryString("Job_ID"))
End If
Dim strQ As New SqlCommand("FHDJobEditing", conHDDb)
strQ.CommandType = CommandType.StoredProcedure
strQ.Parameters.Add("@JobId", SqlDbType.SmallInt).Value = JobId
strQ.Parameters.Add("@OpenDate", SqlDbType.SmallDateTime).Value = txtOpenDate.Text
strQ.Parameters.Add("@Location", SqlDbType.Char, 7).Value = ddlLocList.SelectedItem.Text
strQ.Parameters.Add("@GM", SqlDbType.VarChar, 24).Value = lblRestMgr.Text
strQ.Parameters.Add("@DM", SqlDbType.VarChar, 24).Value = lblDistMgr.Text
strQ.Parameters.Add("@Priority", SqlDbType.TinyInt).Value = ddlPriority.SelectedItem.Text
strQ.Parameters.Add("@StatusID", SqlDbType.TinyInt).Value = ddlStatus.SelectedValue
If ddlVendorlist.SelectedItem.Text = "--No Data Found--" Then
ddlVendorlist.SelectedItem.Value = ""
End If
strQ.Parameters.Add("@VendorID", SqlDbType.Char, 15).Value = ddlVendorlist.SelectedItem.Value
strQ.Parameters.Add("@MajCatgID", SqlDbType.TinyInt).Value = lstMajCatg.SelectedValue
strQ.Parameters.Add("@SubCatgID", SqlDbType.TinyInt).Value = lstSubCatg.SelectedValue
strQ.Parameters.Add("@DueDate", SqlDbType.SmallDateTime).Value = txtDueDate.Text
strQ.Parameters.Add("@CloseDate", SqlDbType.SmallDateTime).Value = txtCompleteDate.Text
strQ.Parameters.Add("@Description", SqlDbType.Text).Value = txtDescription.Text
strQ.Parameters.Add("@Resolution", SqlDbType.Text).Value = txtResolution.Text
strQ.Parameters.Add("@Note", SqlDbType.Text).Value = txtNote.Text
If (txtCompleteDate.Text = "") Then
strQ.Parameters("@CloseDate").Value = DBNull.Value
Else
strQ.Parameters("@CloseDate").Value = txtCompleteDate.Text
End If
-
Correct me if I am wrong, but I thought Parameters had to be added in order of SP variables. Thus
strQ.Parameters.Add("@DueDate", SqlDbType.SmallDateTime).Value = txtDueDate.Text
strQ.Parameters.Add("@CloseDate", SqlDbType.SmallDateTime).Value = txtCompleteDate.Text
strQ.Parameters.Add("@Description", SqlDbType.Text).Value = txtDescription.Text
strQ.Parameters.Add("@Resolution", SqlDbType.Text).Value = txtResolution.Text
strQ.Parameters.Add("@Note", SqlDbType.Text).Value = txtNote.Text
Should be
strQ.Parameters.Add("@DueDate", SqlDbType.SmallDateTime).Value = txtDueDate.Text
strQ.Parameters.Add("@Description", SqlDbType.Text).Value = txtDescription.Text
strQ.Parameters.Add("@Resolution", SqlDbType.Text).Value = txtResolution.Text
strQ.Parameters.Add("@Note", SqlDbType.Text).Value = txtNote.Text
strQ.Parameters.Add("@CloseDate", SqlDbType.SmallDateTime).Value = txtCompleteDate.Text
Also I noticed a couple of items where the datatype does not match, although this is not usually an issue it is considered a bad practice.
See if that doesn't correct the issue.
-
The error is a result of passing and empty string to a int parameter (int,smallint,tinyint). .NET will not convert empty strings to integer.
As an additional note you are implicitly converting your data, it is better practice to do this explicitly (.NET has to convert anyway). If you had done this then you could debug the line that is causing the error instead of waiting for the execute.
Far away is close at hand in the images of elsewhere.
Anon. -
Correct me if I am wrong, but I thought Parameters had to be added in order of SP variables James,
Parameters can be created in any order as long as all of them are created and are of the correct type.
Far away is close at hand in the images of elsewhere.
Anon. -
I made correction to my datatypes to match all columns in database. Also, I entered all the fields on the entry form but I still unable to save it. (Error : Input string is not in a correct format. 
I even tried that I left the data unchanged, (that means I didn't have any empty strings) and I still received the same error when I tried to update it!what could go wrong now?
-
Thanks
-
strQ.Parameters.Add("@JobId", SqlDbType.SmallInt).Value = JobId
@JobID is declared as an Output parameter. It's been a while, but I believe you have to declare output parameters differently. Usually I don't use output parameters as input parameters as well (I use one variable for each and pass the value from one to the other in the proc), so I'm not sure if that is what's causing problems.
Like said, though, this is a VB issue; you should probably be posting elsewhere.
cl
Signature is NULL
-
hey jenniferhu
i dont know what iam telling is valid...but again what strikes me ,iam telling it:
while declaring a parameter to b an output parameter, does n't the direction is to b set as output in .net? check this for jobid?
is ur pblm solved? if yes how??
bye!
Rajiv.
-
they do need to be handled differently. if anyone is interested:
comm.Connection =
this.conn;
comm.CommandText =
"sp_mysproc";
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(
"@id1", SqlDbType.Int).Value=51;
comm.Parameters.Add(
"@notes", SqlDbType.VarChar).Value=userinfo;
comm.Parameters.Add(
"@id2", SqlDbType.Int).Value=2;
comm.Parameters.Add(
"@id3", SqlDbType.Int).Value=1;
comm.Parameters.Add(
"@message", SqlDbType.Int).Value=0;
SqlParameter param = comm.Parameters.Add(
"@out1", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
SqlParameter ret = comm.Parameters.Add(
"@return1", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;
comm.ExecuteNonQuery();
if ((int)comm.Parameters["@return1"].Value == 0)
{
return comm.Parameters["@out1"].Value.ToString();
}
else
{
throw new Exception();
}
cheers
dbgeezer
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply