SqlCommand Problem

[Jorge]

Hello

I have a stored procedure with several parameters and 2
are declared datatype Numeric as per the SQL table.

When i try to use the following:

Me.SqlCommand1.CommandType = CommandType.StoredProcedure
Me.SqlCommand1.Parameters.Clear()
Me.SqlCommand1.CommandText = "altera_linha_encomenda"

With Me.SqlCommand1.Parameters
<snip>
.Add("@punit", SqlDbType.Decimal).Value = punit
.Add("@prazo", SqlDbType.Decimal).Value = prazo
End With
Try
Me.SqlCommand1.ExecuteNonQuery()
Catch
MessageBox.Show(Err.Description)
End Try

I get an exception : Error converting data type numeric
to numeric.

I tried to use DeriveParameters on the stored procedure
and punit is declared as numeric(9,2) was converted to
decimal with scale 9 , precision 2 and size 0.

I tried the following :
..Add("@punit", SqlDbType.Decimal, 0).Value = punit
..Add("@punit", SqlDbType.Decimal, 9).Value = punit
..Add("@punit", SqlDbType.Decimal, 2).Value = punit
..Add("@punit", SqlDbType.Decimal, 11).Value = punit

And still I get an execption : Error converting data type
numeric to numeric.

I am running out of ideas, any suggestion would me most
welcome, thanks.

Kind Regards
Jorge

 

[Jorge]

Well i found an a solution i don't why the .add doesnt
work with numeric(9,2). This way i have to care about
assignig the values of the parameters.

Me.SqlCommand1.CommandType = CommandType.StoredProcedure
Me.SqlCommand1.Parameters.Clear()
Me.SqlCommand1.CommandText = "altera_linha_encomenda"
Dim objCommandBuilder As New SqlCommandBuilder
Dim p As New SqlParameter
objCommandBuilder.DeriveParameters(SqlCommand1)

For Each p In Me.SqlCommand1.Parameters
If p.ParameterName = "@numenc" Then p.Value =
Me.TextBox1.Text
If p.ParameterName = "@pos" Then p.Value =
posicao_enc_
If p.ParameterName = "@quant" Then p.Value =
Me.TextBox51.Text
If p.ParameterName = "@codigo" Then p.Value =
Me.TextBox52.Text
If p.ParameterName = "@nvias" Then p.Value =
Me.TextBox53.Text
If p.ParameterName = "@id" Then p.Value =
Me.TextBox63.Text
If p.ParameterName = "@punit" Then p.Value = punit
If p.ParameterName = "@obs" Then p.Value =
Me.TextBox16.Text
If p.ParameterName = "@zona" Then p.Value =
Me.TextBox50.Text
If p.ParameterName = "@cor" Then p.Value =
Me.ComboBox7.Text
If p.ParameterName = "@prazo" Then p.Value = prazo
If p.ParameterName = "@tipo" Then p.Value
= "'tipo'"
If p.ParameterName = "@refcli" Then p.Value =
Me.TextBox15.Text
If p.ParameterName = "@idcli" Then p.Value =
Me.TextBox60.Text

Next

 

[Brad Langhorst]

I tried the following :
.Add("@punit", SqlDbType.Decimal, 0).Value = punit

i think the error is occurring during your conversion of punit to decimal
type
what is punit...

are you sure you want the decimal type?

i've never used it

brad