0

Hi I'm trying to update an entry in accessdb with using vb.net here is my code

Try

                cmd.Connection = cn
                cmd.CommandText = "update Client set [client_name]=@client_name,[company_name]=@company_name,[email]=@email,[contact_no]=@contact_no,[registration_no]=@registration_no,[date]=@date,[address]=@address where [Client_Id]=@client_id"
                cmd.Parameters.AddWithValue("@client_id", Convert.ToInt32(client_id.Text))
                cmd.Parameters.AddWithValue("@client_name", client_name.Text.ToString)
                cmd.Parameters.AddWithValue("@company_name", company_name.Text.ToString)
                cmd.Parameters.AddWithValue("@email", email.Text.ToString)
                cmd.Parameters.AddWithValue("@contact_no", contact_no.Text.ToString)
                cmd.Parameters.AddWithValue("@registration_no", registration_no.Text.ToString)
                cmd.Parameters.AddWithValue("@date", datetime.Value.ToShortDateString)
                cmd.Parameters.AddWithValue("@address", address.Text.ToString)
                cmd.ExecuteNonQuery()
                Dim result As Integer = MessageBox.Show("Client Updated." + Environment.NewLine + "Want To Update Another One.", "Client Updated", MessageBoxButtons.YesNo, MessageBoxIcon.Information)
                If result = DialogResult.No Then
                    Me.Close()
                ElseIf result = DialogResult.Yes Then
                    client_name.Clear()
                    company_name.Clear()
                    email.Clear()
                    contact_no.Clear()
                    registration_no.Clear()
                    address.Clear()
                    str = "Select * from Client WHERE Client_Id=(SELECT MAX(Client_Id) FROM Client)"
                    cmd = New OleDb.OleDbCommand(str, cn)
                    da.SelectCommand = cmd
                    da.Fill(ds, "Client")
                    Dim lastid As Integer = Convert.ToInt32(cmd.ExecuteScalar()) + 1
                    client_id.Text = lastid
                End If

            Catch ex As Exception
                MessageBox.Show("Client Not Updated Because" + Environment.NewLine + e.ToString + Environment.NewLine + "Please Contact to the Developer", "Client Not Updated", MessageBoxButtons.OK, MessageBoxIcon.Error)
                client_name.Clear()
                company_name.Clear()
                email.Clear()
                contact_no.Clear()
                registration_no.Clear()
                address.Clear()
                str = "Select * from Client WHERE Client_Id=(SELECT MAX(Client_Id) FROM Client)"
                cmd = New OleDb.OleDbCommand(str, cn)
                da.SelectCommand = cmd
                da.Fill(ds, "Client")
                Dim lastid As Integer = Convert.ToInt32(cmd.ExecuteScalar()) + 1
                client_id.Text = lastid
            End Try

problem is when I try to update it says updated successfully but nothing is changed in db also when I try to search with new updated data is returns none but when I try with old data it returns successfully.

I already has set accessdb property to copy if never from copy always.

John MacClain
  • 11
  • 4
  • 1
    [OleDb with Access does not pay attention to the parameter names, only their order.](https://stackoverflow.com/questions/16263755/update-query-on-access-database-not-working-c-net/16263812#16263812) In your case, supply the value for `@client_id` as the last parameter instead of the first. – HansUp Sep 05 '22 at 20:11
  • According to [OleDbParameter Class](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparameter?view=netframework-4.8#remarks): _The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters._ In your SQL statement, the parameter names are ignored. The parameters must be added in the order that they're used in the SQL statement. Also, if a parameter is used more than once, it must be added more than once. – Tu deschizi eu inchid Sep 05 '22 at 20:14

1 Answers1

0

Instead of cmd.ExecuteNonQuery() try msgbox (cmd.ExecuteNonQuery() & " records updated") for debugging purposes

I suspect the answer will be 0. From your query I suspect it requires the syntax [field1] = 'value1', [field2] = 'value2'

Also try move the cmd.CommandText line to just above the cmd.ExecuteNonQuery

cmd.Parameters.AddWithValue("@address", address.Text.ToString)

cmd.CommandText ......

cmd.ExecuteNonQuery()

Gari
  • 1
  • 2