Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
Dim rs4 As Recordset = B1Connections.diCompany.GetBusinessObject(BoObjectTypes.BoRecordset)
Dim qry4 As String = "SELECT Name FROM [@CCS_LOGINDET]"
rs4 = obgn.Return_RecordSet(qry4)
Dim constr As String = "Data Source=" + B1Connections.theAppl.Company.ServerName + ";initial catalog=" + B1Connections.theAppl.Company.DatabaseName + ";User Id=" + B1Connections.diCompany.DbUserName + ";Password=" + CType(rs4.Fields.Item(0).Value, String)
Dim con As SqlConnection = New SqlConnection(constr)
con.Open()
Dim cmd As SqlCommand = New SqlCommand("CCS_SP_PYRL_BONUS_CALCULATION_AGAPPE", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@EMPID", txt_empid.Value.Trim)
cmd.Parameters.AddWithValue("@DATE", txt_cdate.Value.Trim)
Dim outParam As New Data.SqlClient.SqlParameter("@NETBONUS", AMOUNT)
outParam.Direction = Data.ParameterDirection.Output
cmd.Parameters.Add(outParam)
bonusamount = cmd.ExecuteNonQuery()
txt_bonus.Value = bonusamount

This code always returns -1.00.
The procedure works correctly in backend and it returns (numeric(19,6)). I think the problem is with my front end... Can anyone help me?

share|improve this question

It is not required to specify the DataTypes of the parameters but in your case you may face issues with implicit conversions using the AddwithValue method. Follow the previous suggestions.

But in your case I see another issue: You may have returned the number of affected rows from the cmd.ExecuteNonQuery() method becuase this value is returned by default from procedures.

So for the fact you are using an OUTPUT parameter I guess that you do not explicitely RETURN the desired value (bonusamount) from the procedure, because you seem to want it in your OUT parameter...

What you have to do is the following (this is c# code):

int rowsAffected = command.ExecuteNonQuery();
int bonusamount = (int)command.Parameters["@NETBONUS"].Value;

Hope this helps you - or others.

share|improve this answer

As @mrdenny said - you have not set the data type of your parameter so +1 to him for actually telling you what the problem is. I personally prefer not to use the AddWithValue command myself and prefer to do it like so:

cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = oValue

But if you prefer to use the AddWithValue command then you would need to keep a reference to the parameter to you have created so that you can set it's type:

SqlParameter p = cmd.Parameter.AddWithValue("@name", oValue)
p.DbType = DbType.NVarChar

I hope this helps you.

share|improve this answer

You need to specify the data type of the parameters.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.