0

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
3
  • 1
    Use a parameter as you already have here PNM=@PNM You should post the whole block of code that makes use of that string Commented May 13, 2022 at 9:31
  • @Steve , I have updated the code related to the "pnm". Commented May 13, 2022 at 9:50
  • What is your backend? That syntax doesn't seem to be right. Commented May 13, 2022 at 10:13

2 Answers 2

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.

Sign up to request clarification or add additional context in comments.

4 Comments

The SQL syntax depends on the backend that OP didn't specify and a syntax like this is likely to fail (on which RDBMS this works I don't know).
@CetinBasoz that's true but one step at time. This will try to answer the question with a syntax that should not have issues if the current query using the constant works.
@Steve , thanks for the reply from you. 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' I actually use the "addwithvalue" only for ComboBox1_SelectionChangeCommitted or filters only
@Steve , I use the code from you running perfectly
1

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).

3 Comments

thank you reply from you but I have tried the code from your syntax error and I have uploaded a screenshot
Probably this is due to MS-Access. (See OleDb tags)
@Steve, OleDb can be used with many backends and MS Access is just one of them (and a bad one to choose IMHO). It could be MS SQL server, postgreSQL, mySQL, Oracle, VFP,, ... anything with an Oledb driver. I couldn't see anything in tags that says it is MS access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.