0
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?

3 Answers 3

2

You need to specify the data type of the parameters.

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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