SqlCommand Problem

  • Thread starter Thread starter Jorge
  • Start date Start date
J

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
 
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
 
I tried the following :
.Add("@punit", SqlDbType.Decimal, 0).Value = punit
Click to expand...
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
 
Back
Top