SqlClient Data Provider conversion error

[Eric Sabine]

On a fairly simple VB app, I'm instantiating a class and calling a method
which accepts 2 Integer parameters. The class fills a dataset from a stored
procedure, in a nutshell, here's the code.

_cnSqlServer = New SqlClient.SqlConnection(_sqlConString)
_cmdSqlServer = New SqlClient.SqlCommand
_prmSqlParam = New SqlClient.SqlParameter
With _cmdSqlServer
..Connection = _cnSqlServer
..CommandText = storedProcedureName
..CommandType = CommandType.StoredProcedure
End With
_prmSqlParam = _cmdSqlServer.Parameters.Add("@year", theYear)
_prmSqlParam.Direction = ParameterDirection.Input
_prmSqlParam.SqlDbType = SqlDbType.Int
_prmSqlParam = _cmdSqlServer.Parameters.Add("@period", thePeriod)
_prmSqlParam.Direction = ParameterDirection.Input
_prmSqlParam.SqlDbType = SqlDbType.Int
_prmSqlParam = _cmdSqlServer.Parameters.Add("RETURN_VALUE", Nothing)
_prmSqlParam.Direction = ParameterDirection.ReturnValue
_prmSqlParam.SqlDbType = SqlDbType.Int
_daSqlDataAdapter = New SqlClient.SqlDataAdapter(_cmdSqlServer)

If _cnSqlServer.State <> ConnectionState.Open Then
_cnSqlServer.Open()
End If
_daSqlDataAdapter.Fill(_dsDataSet, dataTableName)

'----------------------------------------------------
Here's the problem, in my calling app, I can run the method as such and it
works fine.
y = x.GenerateFinancialStatements(2003, 9)

But I want to pull the 2 integers from the form (combo boxes), so I try

y = x.GenerateFinancialStatements(Convert.ToInt32(Me.cboYear.Text),
Convert.ToInt32(Me.cboPeriod.Text))

And I get the exception at the bottom of this post. The same goes if I
don't convert it or I use CINT. The SQL Server stored procedure is looking
for the INT datatype, which is 32 bits. I can't figure it out.

Thanks,
Eric


Arithmetic overflow error converting numeric to data type numeric. <-- this
is weird!
..Net SqlClient Data Provider
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Schaefer.Financials.Windows.FinancialStatementsOld.ProcessStatement(Int32
theYear, Int32 thePeriod, String storedProcedureName, String dataTableName)
in E:\VB.NET\applications\USA\GetFinancialStatement.vb:line 105
Object reference not set to an instance of an object.

 

[Neil McKechnie]

Looks very wierd!

Have you tried stepping through the stored procedure in
the TSQL Debugger? Using this you will be able to see
whether the correct values in the combo boxes are being
passed to the stored procedure, and also see if the
problem is caused by data being returned into the dataset
or data passed into the command.

The exception is an arithmetic overflow, so look out for
data which is too large to fit into a particular field
(for example an int32 may not fit into NUMERIC(6,0)).

Hope this helps,

Neil.