datatype conversion

[eddie]

I have problem with select statement in which i am using decimal number as a parameter. Value that i use in insert is decimal type and that works fine. Also when i make select with round number select do it's work. but if i enter decimal number some error with conversion happens.

Piece of code that's part of select statement :
sWhere = sWhere & " (Koeficijent = '" & CDec(frmStats.txtKoef.Text) & "')"

end error is here :

Try

Dim adapter2 As New SqlClient.SqlDataAdapter(utakmice.utakmice_SQL, con)

con.Open()
adapter2.Fill(DS)
dgStats.DataSource = DS.Tables(0)
dgStats.Update()
con.Close()

Catch ex As Exception
MsgBox(ex.Message)
End Try

on adapter2.fill(DS) it says that conversion from varchar to numeric failed
don't get it

HELP !!

 

[InertiaM]

I have problem with select statement in which i am using decimal number as a parameter.

does not agree with

Value that i use in insert is decimal type and that works fine. Also when i make select with round number select do it's work. but if i enter decimal number some error with conversion happens.

or

sWhere = sWhere & " (Koeficijent = '" & CDec(frmStats.txtKoef.Text) & "')"

Use

sWhere = sWhere & " (Koeficijent = @Koef)"

and pass it a SQLParameter of the defined type.

 

[cjard]

Parameters
Parameters
Parameters

Read the PQ link in my signature

 

[eddie]

system:

I read that interesting thread . I'm not an expert in .net but most of the stuff i knew in theory. Still this time i can't fix this. i followed your tip on creating "search" query an it is working fine if i enter values in both text boxes. If i leave first one (string) empty, empty grid is shown. And if i leave second one (decimal number is needed) the conversion exception is shown. I feel like a moron already, doing this little piece few days and simply can't get it.

this is my SQL:

SELECT * FROM parovi
WHERE (tip = @tip OR @tip IS NULL) AND
(koeficijent = @koef OR @koef IS NULL)

this is my VB:

ParoviTableAdapter.search(KladionicaDataSet.parovi, txtTip.Text, txtKoef.Text)

on this txtKoef.text i tried CDec conversion and discovered that is not needed which is cool, but when is conversion needed then? Isn't that string type?

 

[InertiaM]

A starting point for you.

Dont use a textbox for koef, use a numeric up down control. This should stop you having to do any conversions.

I would then try the following - adjust it as necessary for your database type :-

        Dim pTIP As SqlParameter = New SqlParameter
        pTIP.ParameterName = "@tip"
        pTIP.SqlDbType = SqlDbType.VarChar
        pTIP.Size = 20 'adjust as necessary
        pTIP.IsNullable = True
        If txtTip.Text.Length > 0 Then
            pTIP.Value = txtTip.Text
        Else
            pTIP.Value = DBNull.Value
        End If

        Dim pKOEF As SqlParameter = New SqlParameter
        pKOEF.ParameterName = "@koef"
        pKOEF.SqlDbType = SqlDbType.Decimal
        pKOEF.IsNullable = True
        If nudKoef.Value <> 0 Then
            pKOEF.Value = nudKoef.Value
        Else
            pKOEF.Value = DBNull.Value
        End If

 

[cjard]

system:

I read that interesting thread . I'm not an expert in .net but most of the stuff i knew in theory. Still this time i can't fix this. i followed your tip on creating "search" query an it is working fine if i enter values in both text boxes. If i leave first one (string) empty, empty grid is shown. And if i leave second one (decimal number is needed) the conversion exception is shown. I feel like a moron already, doing this little piece few days and simply can't get it.

this is my SQL:

SELECT * FROM parovi
WHERE (tip = @tip OR @tip IS NULL) AND
(koeficijent = @koef OR @koef IS NULL)

this is my VB:

Ah, if youre using text boxes i dont think they are EVER null.. only "" empty strings. try setting the query to that:

(tip = @tip or @tip = '' )

ParoviTableAdapter.search(KladionicaDataSet.parovi, txtTip.Text, txtKoef.Text)

I think you need:

Private Function NullIfEmpty(s as String) as String
  If s Is Nothing OrElse s.Trim() = String.Empty Then Return Nothing
  Return s
End Function

then:
ParoviTableAdapter.search(KladionicaDataSet.parovi, NullifEmpty(txtTip.Text), NullIfEmpty(txtKoef.Text))

note that if the parameters of .search() are String, Decimal youll have to write TWO NullIfEmpty, called NullIfEMptyStr and NullIfEmptyDec doing the correct conversion on the return:

Private Function NullIfEmptyDec(s as String) as Decimal
  If s Is Nothing OrElse s.Trim() = String.Empty Then Return Nothing
  Return Convert.ToDecimal(s)
End Function

 

[eddie]

well, it's working fine if i enter that field with decimal number. if i leave it blank nothing is shown in grid. tried various stuff but nothing. It seems that select is doing fine but for some reason i must use that field koeficijent. I even created third textbox(string) and it is working good except that. what could it be?

 

[cjard]

I dont undertsand your post, sorry.. Please show your SQL and any code you have written that calls it, including hidden table adapter code if needs be