[RESOLVED] [02/03] Sql statement not working?

[frankwhite]

Hi everyone.

I have an sql statement which looks fine to me but for some weird reason is not working. When I hit the command button it does not crash or break or throw up an error but for some reason its not saving the number in dleft into the database.

The following is how my coding was before the change and it worked perfectly:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandType = CommandType.Text
            .Connection = conn
     .CommandText = "UPDATE [Company] SET Dleft = Dleft - " & Val(txtdord.Text) & " WHERE CompID = lblcompid.text"
            .Parameters.Add("@p1", Me.cmbcomp.SelectedValue)
        End With
        conn.Open()
        cmd.ExecuteNonQuery()
    End Sub

However I had to change the coding (the difference is that now I dont want to minus a value in txtdord)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim cmd As New OleDbCommand
        With cmd
            .CommandType = CommandType.Text
            .Connection = conn
            .CommandText = "UPDATE [Company] SET Dleft = Dleft WHERE CompID = lblcompid.text"
            .Parameters.Add("@p1", Me.cmbcomp.SelectedValue)
        End With
        conn.Open()
        cmd.ExecuteNonQuery()
    End Sub

Any ideas why its not working? Thanks

[jmcilhinney]

It's no use adding a parameter to your OleDbCommand if the SQL code doesn't use it's value. Where in that SQL code do you have a parameter place-holder? Nowhere, so the value of your @p1 parameter will simply be ignored. Follow the Data Access link in my signature to see how to use parameters correctly.

[si_the_geek]

Neither of those versions should be working, as you have "WHERE CompID = lblcompid.text", and lblcompid is not a valid part of your query. You also have a parameter (which you should be using for all values from controls etc) that is not referenced from your query.

As to the second version... it isn't changing any data, as you have told it not to (SET Dleft = Dleft by definition has no effect!)


I have no idea to correct what you have, as I can't actually tell what it is you want to achieve.

[jmcilhinney]

Also, I just realised that you've got this in your SQL code:

"UPDATE [Company] SET Dleft = Dleft WHERE CompID = lblcompid.text"

That SQL code is executed on your database, not in your VB app. Does your database know anything about a Label on your form? If you want to insert a value into an SQL statement then use a parameter.

[frankwhite]

Thanks guys