Click here to Skip to main content
15,886,919 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I Don't know why Update command is not working using OleDB in MDF file using parameter query

It is working normal when using same code below but SQL Client updating SQL server database but when trying to update access database it gave no error also no changes occurred to the record

What I have tried:

Dim cnnOLEDB As New OleDbConnection
Dim cmdOLEDB As New OleDbCommand
Dim cmdInsert As New OleDbCommand
Dim cmdUpdate As New OleDbCommand
Dim cmdDelete As New OleDbCommand



Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Computer.FileSystem.SpecialDirectories.Desktop & "\SupplierDB.mdb"



Dim strUpdate As String
Dim strSelect As String
DateUpdated.Text = Today
UpdatedBy.Text = SystemUserName
Dim DialString As String = TXT_Dial.Text
Dim FirstChar As Char = DialString.Chars(0)
If FirstChar = "0" Then
    DialString = DialString.Remove(0, 1)
End If
If cnnOLEDB.State = ConnectionState.Closed Then
    cnnOLEDB.Open()
End If
strSelect = "Select * From Customers"
strSelect &= "WHERE ID = (ID = @TXT_Cust_Record_ID)"
strUpdate = "Update Customers "
strUpdate &= "SET ServiceName =@TXT_ServName, Dial = @DialString, SupplierName = @TXT_SupplierName, ShortCode = @TXT_ShortCode, SR = @TXT_SR_ID, DateAdded = @DateAdded, ActionType = @TXT_Action, DateUpdated = @DateUpdated, UpdatedBy = @UpdatedBy, Reason = @TXTReason"
strUpdate &= " WHERE ID = @TXT_Cust_Record_ID;"
cmdUpdate = New OleDbCommand(strUpdate, cnnOLEDB)
cmdUpdate.CommandText = strUpdate
cmdUpdate.Parameters.AddWithValue("@DialString", DialString)
cmdUpdate.Parameters.AddWithValue("@TXT_ServName", TXT_ServName.Text)
cmdUpdate.Parameters.AddWithValue("@TXT_SupplierName", TXT_SupplierName.Text)
cmdUpdate.Parameters.AddWithValue("@TXT_ShortCode", TXT_ShortCode.Text)
cmdUpdate.Parameters.AddWithValue("@TXT_SR_ID", TXT_SR_ID.Text)
cmdUpdate.Parameters.AddWithValue("@DateAdded", DateAdded.Text)
cmdUpdate.Parameters.AddWithValue("@SystemUserName", SystemUserName)
cmdUpdate.Parameters.AddWithValue("@TXT_Action", TXT_Action.SelectedItem)
cmdUpdate.Parameters.AddWithValue("@DateUpdated", Now)
cmdUpdate.Parameters.AddWithValue("@UpdatedBy", UpdatedBy.Text)
cmdUpdate.Parameters.AddWithValue("@TXTReason", TXTReason.SelectedItem)
cmdUpdate.Parameters.AddWithValue("@TXT_Alias", TXT_Alias.Text)
cmdUpdate.Parameters.AddWithValue("@TXT_Cust_Record_ID", TXT_Cust_Record_ID.Text)

cmdUpdate.ExecuteNonQuery()
Posted
Updated 13-Oct-19 5:52am
Comments
Richard MacCutchan 13-Oct-19 11:55am    
You are adding three parameters that are not referenced in your UPDATE statement: @SystemUserName, @TXT_Alias and @TXT_Cust_Record_ID. Is that significant?

1 solution

This looks wrong:
strSelect = "Select * From Customers"
strSelect &= "WHERE ID = (ID = @TXT_Cust_Record_ID)"
But that won't affect the update.

Which means we can't tell - we don't have access to your DB, or to the values you are passing to it, particularly the
@TXT_Cust_Record_ID
value.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why. When you know exactly what is being passed to SQL, and have checked in yoru DB to see if there are any matching records you can start thinking about why it's a problem. I'd guess that no rows match the ID value, but I don't have any access to data to check.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
 
Share this answer
 
Comments
BassamKassem 13-Oct-19 16:21pm    
you were right , I debugged the value and it turned out that the problem was in @TXT_Cust_Record_ID from the SELECT query i changed it from

strSelect = "Select * From Customers"
strSelect &= "WHERE ID = (ID = @TXT_Cust_Record_ID)"

to

strSelect = "Select * From Customers"
strSelect &= "WHERE ID = (@TXT_Cust_Record_ID)"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900