how to convert a string value into integer in vb.net

Question

Datatype of expense is int in Sql Server database Please Guide

The Value of Expense which i got from the table expense

'Total Expenses B/W Dates
    sql = "select COALESCE (SUM (amount), 0) from tblexpense Where transactiondate >= @p1 and transactiondate <= @p2"
    CmdObj = New SqlCommand(sql, ConObj)
    CmdObj.Parameters.Add("@p1", SqlDbType.Date).Value = DateTimePicker1.Value.Date
    CmdObj.Parameters.Add("@p2", SqlDbType.Date).Value = DateTimePicker2.Value.Date
    CmdObj.ExecuteScalar()
    Dim sumexpense As Integer = CmdObj.ExecuteScalar
    LblExp.Text = "Rs. " & sumexpense

Save Button to save The value of expense into table

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    ConObj = New SqlConnection(ConStr)
    ConObj.Open()
    Dim sql As String = "insert into tblbalance (tcon,pcon,ecoll,pendcoll,expense,bcoll,nprofit) values(@tcon,@pcon,@ecoll,@pendcoll,@expense,@bcoll,@nprofit)"

    With CmdObj
        .Parameters.Add("@tcon", SqlDbType.Int).Value = lblTcon.Text
        .Parameters.Add("@pcon", SqlDbType.Int).Value = LblPcon.Text
        .Parameters.Add("@ecoll", SqlDbType.Int).Value = LblEColl.Text
        .Parameters.Add("@pendcoll", SqlDbType.Int).Value = LblPColl.Text
        .Parameters.Add("@expense", SqlDbType.Int).Value = LblExp.Text
        .Parameters.Add("@bcoll", SqlDbType.Int).Value = LblBcoll.Text
        .Parameters.Add("@nprofit", SqlDbType.Int).Value = LblNet.Text

    End With
    CmdObj.Connection = ConObj
    CmdObj.ExecuteNonQuery()
    MsgBox("Saved Successfully")
    ConObj.Close()
End Sub

You should use the same parameterized statement like you did in your first block of code. Then ensure that lblExp.Text can parse to an integer to ensure that the input from the user can match the type and upon a successful parse, pass that variable into your parameter. — Charles May, Jan 23 '16 at 13:41
I tried but no success... conversion from string to integer is not valid — M Ahsan Asif, Jan 23 '16 at 13:48
did you fix the statement to use parameters? because your injected value is still being wrapped with apostrophes in this current version and wrapping values with apostrophes denotes a string type and not a numeric type. Parameters would also fix this concern. — Charles May, Jan 23 '16 at 13:49
Update the post to show the parameterized version. Also some of those objects should be disposed of when you are done [see this](http://stackoverflow.com/a/29187199/1070452) — Ňɏssa Pøngjǣrdenlarp, Jan 23 '16 at 13:57
Error comes... failed to covert parameter value from a string to a int32 — M Ahsan Asif, Jan 23 '16 at 14:29
what line do you get it on? which parameter, still expense? Are you sure every one of those is an integer column? Not some fractional form? what is the value of the offending label when you get the exception? — Ňɏssa Pøngjǣrdenlarp, Jan 23 '16 at 14:42
From LblExp.Text = "Rs. " & sumexpense ... I just want sumexpense value to insert in table. How can i extract from this or convert any other option. As we know if the datatype is integer we can not enter string. — M Ahsan Asif, Jan 23 '16 at 14:51
well, if you are stuffing letters into the control, `LblExp.Text = "Rs. " & sumexpense` you cant use that as a value to pass to the database. `"Rs.12345"` is not a number. Use a variable for the data — Ňɏssa Pøngjǣrdenlarp, Jan 23 '16 at 14:56

M Ahsan Asif · Answer 1 · 2016-01-23T15:16:50.830

Imports System.Text.RegularExpressions
Private Shared Function GetIntOnly(ByVal value As String) As Integer
    Dim returnVal As String = String.Empty
    Dim collection As MatchCollection = Regex.Matches(value, "\d+")
    For Each m As Match In collection
        returnVal += m.ToString()
    Next
    Return Convert.ToInt32(returnVal)
End Function

  Dim sql As String = "insert into tblbalance (tcon,pcon,ecoll,pendcoll,expense,bcoll,nprofit) values('" &
        lblTcon.Text & "','" & LblPcon.Text & "','" & GetIntOnly(LblEColl.Text) & "','" & GetIntOnly(LblPColl.Text) & "','" &
        GetIntOnly(LblExp.Text) & "','" & GetIntOnly(LblBcoll.Text) & "','" & GetIntOnly(LblNet.Text) & "')"

how to convert a string value into integer in vb.net

1 Answers1