0

I receive the following error: "Conversion from string "Invalid SQL statement; expected " to type 'Integer' is not valid."

Here's my code that I expected to update information in database:

Private Sub Updatebtn_Click(sender As System.Object, e As System.EventArgs) Handles Updatebtn.Click

    If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or TextBox5.Text = "" Or ComboBox1.Text = "" Then
        MsgBox("Please Fill All Details")
        Exit Sub
    End If

    Try
        con.Close()
        con.Open()
        str = "UPATE Customers set CustomerName=@name,Address=@Address,Phone=@Phone,EyePower=@EP,ConsutantDoctor=@CD where CustomerID=@CID"
        cmd = New OleDbCommand(str, con)
        cmd.Parameters.AddWithValue("@name", TextBox1.Text)
        cmd.Parameters.AddWithValue("@Address", TextBox2.Text)
        cmd.Parameters.AddWithValue("@Phone", TextBox3.Text)
        cmd.Parameters.AddWithValue("@EP", TextBox4.Text)
        cmd.Parameters.AddWithValue("@CD", TextBox5.Text)
        cmd.Parameters.AddWithValue("@CID", ComboBox1.Text)
        cmd.ExecuteNonQuery()

        MsgBox("Record Updated Successfully", MsgBoxStyle.Information, MsgBoxStyle.OkOnly)

    Catch ex As Exception
        MsgBox("Error", ex.Message)

    Finally
        con.Close()
    End Try
End Sub
Tu deschizi eu inchid
  • 4,117
  • 3
  • 13
  • 24
Tinyiko Baloyi
  • 1
  • 2
    `UPATE` should be `UPDATE`. Also according to [OleDbCommand.Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-7.0#remarks): _The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement_. You may consider using _Add_ (as shown [here](https://stackoverflow.com/a/69638011/10024425)) instead of using _AddWithValue_ because _AddWithValue_ may result in misleading error messages. – Tu deschizi eu inchid Aug 06 '23 at 02:29
  • 1
    According to [OleDbCommand.ExecuteNonQuery](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.executenonquery?view=dotnet-plat-ext-7.0): _Returns: The number of rows affected._. It's not possible to know whether or not the operation was successful without checking the return value. – Tu deschizi eu inchid Aug 06 '23 at 02:35
  • 2
    Don't use `AddWithValue`: https://www.dbdelta.com/addwithvalue-is-evil/ – Dai Aug 06 '23 at 03:17
  • @Tudeschizieuinchid *does not support named parameters* means that the name of the parameters is not considered, only the ordinal position. It doesn't mean that you cannot assign a *meaningful name*, it's quite easy to verify – Jimi Aug 06 '23 at 03:41
  • @Jimi: Apparently you didn't read the SO post that I referenced in my first comment, because the code in the post assigns a _meaningful name_. However, using parameter names in the SQL statement indicates that one may not realize that _OleDb_ doesn't support named parameters. – Tu deschizi eu inchid Aug 06 '23 at 13:25
  • @Tudeschizieuinchid I know the docs (read those multiple times) and the description is not correct (probably on purpose, still not correct). I've also seen your code, where, in the query, you replace the named parameters with questions marks. Which suggests that a `?` should be used in the query. Which is not the case -- OleDB doesn't *replace* anything, it doesn't *see* the assigned names at all. It just takes the parameters' values in the order they've been added. That's all. So, you can use the names in the query (without making use of unsupported characters) – Jimi Aug 06 '23 at 13:38
  • @Jimi: If you feel that the MS documentation isn't correct, then you may consider contacting MS to report the issue. I've reported an issue with MS documentation previously and received a response. One can do so [here](https://developercommunity.visualstudio.com/home). – Tu deschizi eu inchid Aug 06 '23 at 13:51
  • @Tudeschizieuinchid It's not a feeling, I know the code and, as mentioned, it can be easily tested -- Given the composition of the Team that handles the docs and the current priorities, suggesting corrections related to such an old tool (i.e., something that people have being using and testing for **a long time**) and for something that, per se, doesn't *cause harm* even if no longer intrinsically correct, the change won't ever happen – Jimi Aug 06 '23 at 14:07
  • @Tudeschizieuinchid Why would you want me to read stories? ➔ What I mentioned *can be easily tested*: when you have done that, please let me know your story about it (and maybe let others know, if/when it happens) – Jimi Aug 06 '23 at 14:37
  • @Jimi: _please let me know your story about it (and maybe let others know, if/when it happens)_: I've updated the comment in the code that I referenced above and I'm letting you know that it has happened, as requested. – Tu deschizi eu inchid Aug 06 '23 at 18:15
  • @Tudeschizieuinchid Well, you didn't say what you found out. Is using named parameters (preserving the ordinal position, of course) in the SQL possible or not? Is the documentation (*[...] for passing parameters to an SQL [...] the question mark (?) placeholder **must** be used [...]*) correct or not (or, maybe, just *too strict* and not exactly corresponding to the current behavior)? -- Using the `@` prefix in named parameters is quite a standard (in this and that other contexts, at least) – Jimi Aug 06 '23 at 18:43
  • @Jimi: I never said that a question mark _must_ be used. When I first started using _OLE DB_ I used named parameters and also used _AddWithValue_. That is until I spent countless hours debugging my code. While I agree that the `@` sign is _standard_ in places where named parameters are used. If I remember correctly, I tried to prefix the names in the _Add_ method with `@`, however it didn't seem to have any effect which is why I switched to prefixing them with `!` instead (for OLE DB). I found this somewhere on the internet, but don't remember where. – Tu deschizi eu inchid Aug 06 '23 at 19:10

0 Answers0