[RESOLVED] Update Query

[gwboolean]

I am using the query below to update two values from a form.

        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@obsolete", True)
        MasterBase.MasterBaseQuery("UPDATE " & MyTable & " " &
                                   "SET Effective=@effective,Obsolete=@obsolete " &
                                   "WHERE MasterBaseID=@midno")

MyTable can be one of two tables. When this code is executed, the table should be updated, but is not. I am unable to see why the table is not updated.

I know that queries are very tricky and I often have trouble making them work. But I do not see the error here.

[jmcilhinney]

The fact that you are using a class that wraps all your actual ADO.NET code and you have only shown us the use of that class and not any of the actual ADO.NET code means that there could be any number of reasons that it's not working and we wouldn't be able to tell. Assuming that your ADO.NET code is all correct though, there is one thing that jumps out as a possible issue.

This is an example of why it's important to always provide all the relevant information. When dealing with databases, which databases it is is relevant information because there are always some differences from database to database. If you are using the SqlClient provider for SQL Server then the code you have should not be an issue but if, for instance, you're using the OleDb provider for Access then the code you have is wrong. While you can use names for Access parameters, they are only for your use and are ignored by the database. The database only cares about the position of the parameters, which means that you need to add them to the command in the same order that they appear in the SQL code. You're adding parameters to the command in a different order to that in the SQL code so that would cause the WHERE clause to fail to match any records or, even worse, match the wrong record(s). I would recommend matching the order of the parameters regardless of the ADO.NET provider or database but, in certain cases, it is absolutely critical that you do.

[gwboolean]

I fully understand that I provided little useful material to determine the issue and it's resolution. That was/is because I am not sure what the problem actually is or understand the process in it's entirety.

This is an example of why it's important to always provide all the relevant information.

That is true. But one must know what any/all of the relevant information is to be able to do that.

you have only shown us the use of that class and not any of the actual ADO.NET code means that there could be any number of reasons that it's not working

Again, true. How much actual code should be included when one has very little understanding of the process?

Additionally, the code, all of a sudden, began to execute as expected. I swear I never touched anything and it was spontaneous!!!


Anyway, here is an explanation of the process, as I understand it. I am using parameters to access tables and create/view/modify the data in the table. In general it goes like this:

I have a module that has the two methods I use for accessing my database\tables

        Public Sub MasterBaseQuery(MyQuery As String)
            RecordCount = 0
            Exception = ""
            Try
#Region "Open Connection/Load Table"
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(MyQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
#End Region
            Catch ex As Exception
                Exception = ex.Message
            End Try
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub

        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New OleDbParameter(Name, Value)
            Params.Add(NewParam)
        End Sub

Whenever I access a table I generally have an AddParams() method in the form class.

    Public Sub AddParams()
        'ChangeMaster parameters
        MasterBase.AddParam("@recno", lblChangeID.Text)
        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.AddParam("@name", txtName.Text)
        MasterBase.AddParam("@managerid", EmployeeID.ToString)
        MasterBase.AddParam("@manager", cboManager.Text)
        MasterBase.AddParam("@type", lblType.Text)
        MasterBase.AddParam("@owner", cboOwner.Text)
        MasterBase.AddParam("@made", txtMade.Text)
        MasterBase.AddParam("@reason", txtReason.Text)
        MasterBase.AddParam("@result", txtResult.Text)
        MasterBase.AddParam("@path", lblLink.Text)
        If Not String.IsNullOrWhiteSpace(txtOpen.Text) Then MasterBase.AddParam("@open", txtOpen.Text)
        If Not String.IsNullOrWhiteSpace(txtSubmit.Text) Then MasterBase.AddParam("@submit", txtSubmit.Text)
        If Not String.IsNullOrWhiteSpace(txtApprove.Text) Then MasterBase.AddParam("@approve", txtApprove.Text)
        If Not String.IsNullOrWhiteSpace(txtTrain.Text) Then MasterBase.AddParam("@train", txtTrain.Text)
        If Not String.IsNullOrWhiteSpace(txtEffective.Text) Then MasterBase.AddParam("@effective", txtEffective.Text)
        If Obsolete Then MasterBase.AddParam("@active", True) Else MasterBase.AddParam("@active", rdoActive.Checked)
        If Obsolete Then MasterBase.AddParam("@obsolete", True) Else MasterBase.AddParam("@obsolete", rdoObsolete.Checked)
        Obsolete = False
    End Sub

A table can be opened using the following query:

        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.MasterBaseQuery("SELECT MasterBaseID,Effective,Obsolete " &
                                   "FROM " & MyTable & " " &
                                   "WHERE MasterBaseID = @midno")

A record may be added using the following query:

        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@Obsolete", True)
        MasterBase.MasterBaseQuery("INSERT INTO " & MyTable & " (Effective, Obsolete) " &
                                     "VALUES (@effective, @obsolete); ")

I am not currently using this code set.

In this case I wanted to update a record

        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@obsolete", True)
        MasterBase.MasterBaseQuery("UPDATE " & MyTable & " " &
                                   "SET Effective=@effective,Obsolete=@obsolete " &
                                   "WHERE MasterBaseID=@midno")

Currently, I run the first query and third query in sequence. The second query I am not currently using. I do not believe I need to even run the first query to update the data. However, I have not tested that out. All of this is somewhat voodoo code for me and I do not fully understand the process.

It is when I run the third query that I have observed my problem, i.e., the table is not updated.

At this point it appears that the table is indeed being updated.

[jmcilhinney]

Despite the fact that I said this:

When dealing with databases, which databases it is is relevant information because there are always some differences from database to database.

you still haven't told us what database you're using. The fact that you're using OleDb means that there's a reasonable chance that you're using Access, but it's by no means guaranteed, so we're still in the dark. If it is Access - this may also apply to some other databases but I'm not sure which - then I have already provided the solution in post #2. You just have to read what I said and do as instructed and it will work as expected.

[gwboolean]

you still haven't told us what database you're using.

Sorry, the database is Access. And don't get on your high horse about using SQL, et. al., databases instead of Access. For my needs Access if fine. I am not going to spend beaucoup dollars on expensive databases I do not need.

I have already provided the solution in post #2. You just have to read what I said and do as instructed and it will work as expected

I appreciate that. However, I read your previous response, yet again, and was unable to derive the answer you claimed. I simply do not see where the order or content of my UPDATE query is in error.

You're adding parameters to the command in a different order to that in the SQL code so that would cause the WHERE clause to fail to match any records or, even worse, match the wrong record(s). I would recommend matching the order of the parameters regardless of the ADO.NET provider or database but, in certain cases, it is absolutely critical that you do.

I am not at all clear what you are expressing here. My understanding, from your solution, is that my SQL code is wrong around the WHERE statement. Could you possibly clarify that for me?

I do know that RecordCount = 0 when the Query is run, thus meaning that the record was not found. Having said that, I cannot see the error, even when I step through the code and check the query as it is being run.

Overall, I run the MasterBaseQuery() and the AddParam() routine with a number of queries, so I am certain (reasonably so) that any error I have is not there. It is clear to me that the error is almost certainly contained in my UPDATE code, but after watching it run numerous times am unable to see what is actually wrong. Or to put it bluntly, I know that there is an error, but am unable to see it. Can you clearly point out exactly what you think is wrong with the UPDATE, or if I am on the wrong track and the error is elsewhere?

[jmcilhinney]

And don't get on your high horse about using SQL, et. al., databases instead of Access.

Don't get on your high horse about what we can and can't post. If you don't agree with something or don't think it applies to you then just ignore it.

I am not going to spend beaucoup dollars on expensive databases I do not need.

The fact that you think using SQL Server requires beaucoup dollars shows that you don't know what you're talking about. SQL Server Express is free and can handle databases up to 10 GB.

My understanding, from your solution, is that my SQL code is wrong around the WHERE statement. Could you possibly clarify that for me?

I don't really understand how this isn't obvious.

You're adding parameters to the command...

        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@obsolete", True)

...in a different order to that in the SQL code

        MasterBase.MasterBaseQuery("UPDATE " & MyTable & " " &
                                   "SET Effective=@effective,Obsolete=@obsolete " &
                                   "WHERE MasterBaseID=@midno")

you need to add them to the command in the same order that they appear in the SQL code

        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@obsolete", True)
        MasterBase.AddParam("@midno", lblMasterID.Text)

[gwboolean]

Don't get on your high horse about what we can and can't post. If you don't agree with something or don't think it applies to you then just ignore it.

Touchy aren't we. How about this. Let's just stick to the subject and neither of us (that means myself as well) should continue with irrelevant side bars?

You are adding the parameters to the command in a different order than in the SQL code. How can you not see this?

I suppose I cannot see it because of three things. The first is that no matter what order I add the parameters, the Fill command of MasterBaseQuery() yields a RecordCount = 0. The second is that my understanding of the order for adding the parameters is applied to SET and WHERE independently. The last is that I set the code as you instructed and the query still fails.

Considering that I use the MasterBaseQuery() and AddParam() with many other methods, I do not believe the problem can be there and that the problem lies in my MakeObsolete() UPDATE method. Additionally, it appears that the order of parameters applies to the SET and WHERE sections of the query independently, i.e., This gets me the same result as the way I previously had the code.

        MasterBase.AddParam("@effective", False)
        MasterBase.AddParam("@obsolete", True)
        MasterBase.AddParam("@midno", lblMasterID.Text)
        MasterBase.MasterBaseQuery("UPDATE " & MyTable & " " &
                                   "SET Effective=@effective,Obsolete=@obsolete " &
                                   "WHERE MasterBaseID=@midno")

I don't mean to be obtuse, but I believe I do understand what you are saying. Additionally, I have long been aware of how order counts with parameters. I tested your suggestion and it still fails (in fact I had tested that more than once prior to even submitting this post).

[gwboolean]

OK, I have done some additional changes to the code and you are indeed correct about the order of the parameters applying to this method. It turns out that at the Fill command line of MasterBaseQuery() I had added some code to deal with the event that RecordCount = 0. That was not a good idea, as it sabotaged my UPDATE method. I still do not fully understand how everything works in MasterBaseQuery().

KUDOs about the order. It just required elimination of the extra code in MasterBaseQuery().

Thanks.

[jmcilhinney]

I still do not fully understand how everything works in MasterBaseQuery().

Where exactly did you get it from? If you don't understand it then I assume that you didn't write it. In my experience, a lot of people try to create a data access layer (DAL) but, while the motivation is laudable, the implementation often leaves something to be desired.

[gwboolean]

Where exactly did you get it from? If you don't understand it then I assume that you didn't write it.

Actually, I have have been using this approach for quite some time now and do not remember the source. I do remember that it was a You Tube video that I obtained the method from.

I said that there was some aspects that I still do not understand, not that I do not understand how the process works, (although I did not previously word it like that).

I wrote every bit of it. I am using this process in a lot of ways/places that is quite different from how it was being used from my original source. Like most content obtained in that manner, there was a lot that was not there and had to be figured out over time.

In my experience, a lot of people try to create a data access layer (DAL) but, while the motivation is laudable, the implementation often leaves something to be desired.

Although this approach can be very ticky (professional term for picky), I have found it to be very useful and easier to work with than other approaches for manipulating databases. My biggest problem is I have a mild dyslexia that can get me caught up in being unable to process a mistake. I have fewer problems with that using this method than I have had with other methods I have tried.

This case was not the dyslexia. It was the fact that I misinterpreted how the order rule for parameters was applied (it is indeed a rule for this method).

So what method do you prefer to communicate with your database and manipulate the data?