Solution add textbox in SQL as string in vb.net

Question

What is the solution if you see the code below there is a value (2000) then I want to change it to textbox so I can custom value.

Thanks

jack

Dim sql As String = "update GSDTS as t1 inner join IFGTS as t2 on t1.[ITM] = t2.[ITM] set t1.[CIU] = t2.[PRSOBNET]+2000 WHERE GDN = 'A.04.01.002.001'AND PNM=@PNM"
Using conn As New OleDbConnection(cn),
                    cmd As New OleDbCommand(sql, conn)
    cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
    conn.Open()
    cmd.ExecuteNonQuery()
End Using

Answer 1

You can just add another parameter to your query like here

' Read the textbox value here'
Dim newValue as Integer
Int32.TryParse(txtInput.Text, newValue)

' Put the parameter placeholder instead of a constant'
Dim sql As String = "update GSDTS as t1 inner join IFGTS as t2 
                                  on t1.[ITM] = t2.[ITM] 
                     set t1.[CIU] = t2.[PRSOBNET]+@addvalue
                     WHERE GDN = 'A.04.01.002.001'AND PNM=@PNM"
Using conn As New OleDbConnection(cn),
     cmd As New OleDbCommand(sql, conn)
     ' Add the other parameter and its value before the old one. See note below'
     cmd.Parameters.AddWithValue("@addvalue", newValue)

     cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)

     conn.Open()
     cmd.ExecuteNonQuery()
End Using

Also, take a look at how to replace AddWithValue with a single line Add. AddWithValue is dangerous because it can misrepresents your value when working with dates or decimals and it is inefficient with strings

** IMPORTANT **
You are working with the OleDb provider. In this context the parameters should be added in the same order in which their placeholders appear in the string. So the new parameter should be added before the @PNM one otherwise they will be read in the wrong order when the query is executed.

Answer 2

Your SQL syntax wouldn't work for any RDBMS I know of and you didn't tag the backend. That might be solely a reason for it not to work at all, you should start by correcting it. Second, do not use AddWithValue but Add and explicitly specify your data type. Having said that, for example MS SQL server as a backend:

    Dim sql As String = <sql>UPDATE GSDTS
SET [CIU] = t2.[PRSOBNET] + @addVal
FROM GSDTS AS t1
    INNER JOIN IFGTS AS t2
        ON t1.[ITM] = t2.[ITM]
WHERE t1.GDN = 'A.04.01.002.001'
      AND t1.PNM = @PNM;</sql>

    Dim addVal As Integer = Nothing
    If Integer.TryParse(txtAdd.Text, addVal) Then
    Using conn As New OleDbConnection(cn),
                  cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.Add("@addVal", OleDbType.Integer).Value = addVal
            cmd.Parameters.Add("@PNM", OleDbType.VarChar).Value = ComboBox1.SelectedValue
            conn.Open()
        cmd.ExecuteNonQuery()
        End Using
    End If

Note that order of variable declarations are same as their order they are used in the query. That is a necessity with OleDb (positional arguments). If your backend is something like MS SQL server then prefer using backend specific SqlConnection, SqlCommand (then you can also use named parameters).