[RESOLVED] Operation Must Use an updateable Query

[gwboolean]

This is the message I receive when attempting to add a record to a table in an application that I just deployed. This error does not manifest when running the program in the debugger. This is a problem I have experienced previously, and while I did eventually resolve the issue, I still do not know how.

Starting with the fact that I fail to fully understand what is meant by the term, "updateable query", and what the requirements are, exactly, for an updateable query. Terms like this can frequently be nothing more than jargon, but I would assume that there might be an exact meaning (although I have not been able to nail that down) with some requirements/specifications.

While it appears to me to be that I might have missed loading a file, output or assembly while setting up the installation I have no idea what that would be. None of the material I have reviewed on this subject provides an indication as to exactly what one should include with an installation setup, so I do not know where to go to clear that up. However, I have seen some suggestions of other possible sources of this error in some of the material I have perused, but nothing that seems to relate to this occurrence.

I believe that the error is occurring when I use an ascension number system. However, that is only a guess based on where I believe the error is being thrown. I can include any code showing how that works if desired.

[jdc2000]

Possibly useful links:

https://software-solutions-online.co...ateable-query/

https://social.msdn.microsoft.com/Fo...ccessdatabases

[gwboolean]

Thanks a lot JDC. Information has been scant. Anyway, I am pretty sure (whatever that really means) that the error occurs in the update part of the query below. There are several of these tables used and each time I try to add a record this error occurs. I believe it is in the UPDATE of the ascension number tables I use. Here is the one that I have been running in the installed application:

            'Parameters
            MasterBase.MasterBaseQuery("SELECT colStaffID FROM setStaffID")
            Dim r As DataRow = MasterBase.ListTable.Rows(CurrentRecord)
            If r("colStaffID").ToString = "" Then
                RecordID = "EM100000"
            Else
                Dim Recno As String = r("colStaffID").ToString
                Dim intFileID As Integer = CInt(Mid(Recno, 3, 6))
                intFileID += 1
                RecordID = "EM" + CStr(intFileID)
            End If
            'Update ID with incremented value
            MasterBase.AddParam("@staffid", RecordID)
            MasterBase.MasterBaseQuery("UPDATE setStaffID SET colStaffID=@staffid " +
                                       "WHERE colStaffID=@staffID")
            Return RecordID
        End Function

I use several tables to create ascension numbers that meet two requirements. The first is that the number has a specific number of digits. The second is that the number have a two character designator. The field is a character field. Each of these tables consist of a single field and a single record. None of the tables had the field designated as a Primary field and I corrected that. Additionally, I added a WHERE clause to the query, since one did not previously exist.

I was hopeful that redeploying and reinstalling the application after this might resolve the problem, Alas that was not the case. I am still going through your suggestions and hoping I can make something work. However, assuming that I did not miss any required outputs/files/Assemblies, I really am having a hard time understanding why everything works in debug, but not as an installed application.

I am still going through your suggestions and hope I am getting close to resolution.

[gwboolean]

Oh yeah, I would be open to a better way to do ascension numbers if you have one.

[jdc2000]

Did you see the list of possible causes from the first link?

Some of the reasons are:

1. The table doesn’t have a primary key field.
2. You don’t have permission to access the “App_data” folder.
3. The MS-Access file is in a read-only folder, such as the Program Files folder.
4. Using the UPDATE query that includes a JOIN statement.
5. Your web application does not have “write permission” in the folder where the database is located.
6. The MS-Access file has a “Read Only” attribute.
7. Another database connection is already open.

What type of database are you using? Access? SQL? Other?

[gwboolean]

Yes I did. I just have not worked my way through the whole list yet. I am slow. #4 is a no go for me, since I have no join statements. The permissions I would not even know where to look for those.

As best as I can discern, the only queries that I am having trouble within the entire application are the ones I use to create an ascension number.

The database is in my bin\debug folder. I had assumed that was not a read-only folder. (is it?). But then we all know what occurs when you assume. As for #7 it will take me quite a while to trace through everything and determine if I have done that. Bottom line is it is going to take me awhile to check it all out. But so far I have not gotten a cigar.

My database is Access. I keep the database in the project bin\debug folder. I do not even honestly remember why I began putting it there, but that is where I always put it. Anyway, still working through them. I will probably have checked them all out by tomorrow.

So do you have any better strategies for making ascension numbers?

[OptionBase1]

So do you have any better strategies for making ascension numbers?

Do you mean accession numbers? If not, you may need to define what that phrase means to you in order to get assistance.

[gwboolean]

No, I mean ascension numbers. An ascension number is a number that increases some value (usually 1). I do, however, remember accession numbers from when I was a working scientist (years ago) and they are not equivalent.

Now what I need is a unique identifier that has a designator, i.e., a two letter designator followed by an ascension number of a specific length. For example, I might use MB111111 for each record in a table. The identifier number for the next record would then be MB111112. So what you see in the code above was my answer to fulfill that need. Based on what is occurring with the above code, when run from the application, it might well be that this was a poor strategy to achieve that end. So I am now trying to think of a better way to achieve the same end.

[gwboolean]

Now, before I abandon my current method it occurred to me that there might be more to the primary code being the cause than I previously thought. The table used in the code above only has one field and it was not, previously, designated as Primary. I have already changed that field to being a primary field, but it did not eliminate the error. It occurred to me that perhaps I need an autonumber field to use as the primary field and have added another field for that. I have not yet tested it out, and I have no idea why that should make any difference, but I am pretty fresh out of ideas for that particular possible cause.

[OptionBase1]

MasterBase.MasterBaseQuery("UPDATE setStaffID SET colStaffID=@staffid " +
                                       "WHERE colStaffID=@staffID")

One glaring problem I see in your query is that your logic makes no sense. If you fill in some fictitious values, you are saying this:

UPDATE setStaffID SET colStaffID="MB100001" WHERE colStaffID="MB100001"

Do you see the problem there? If the intent of the update is to overwrite the previous value with the new value, then your "WHERE" clause needs to have it match the previous value, not the new value.

Good luck.

[gwboolean]

It doesn't make that much sense to me either. But I has been suggested to me that it was a possibility. I originally had the code without the WHERE statement and will return to that. Having said that, the code executes either way.

I have pretty well exhausted the Primary Key. While I am still working through the other possibilities.

1. The table doesnÂ’t have a primary key field.

I beat that one to death. The table does not have a primary key and no change.

2. You don’t have permission to access the “App_data” folder.

I really do not know how to determine that. What would be the default for that be and where can one find that folder?

3. The MS-Access file is in a read-only folder, such as the Program Files folder.
6. The MS-Access file has a “Read Only” attribute.

I looked at the properties on the DB in the Program Files and it is not ReadOnly. I checked the folder that it was in and it was indeed ReadOnly. I changed that. However, the application still throws the error.

4. Using the UPDATE query that includes a JOIN statement.

Does not apply. I have no JOIN statements.

5. Your web application does not have “write permission” in the folder where the database is located.

This is not a web application, so I assume this does not apply.

7. Another database connection is already open.

I do not know what other database might be open. I do not believe that to be the case, but cannot say for certain.

I am still of the belief that there might be something critical that I missed adding from the files/outputs/assemblies prior to running the build. Having said that, I added everything that I understood to be important to the application.

[ChrisE]

this make no sense...

'....
 MasterBase.MasterBaseQuery("SELECT colStaffID FROM setStaffID")
'.....

I am only guessing here, do you want to check if that colstaffID is in the Database?
it is better to use a Select Count

here a sample

Private Function StaffIDExists(fNum As String) As Boolean
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Adressen.mdb")
        Dim read As OleDbDataReader
        con.Open()


        Dim cmd As OleDbCommand = con.CreateCommand
        cmd.CommandText = "Select Count(*) From setStaffID Where xID = @xID"
        cmd.Parameters.AddWithValue("@xID", TextBox1.Text)


        StaffIDExists = Convert.ToInt32(cmd.ExecuteScalar()) > 0

        read = cmd.ExecuteReader
        While read.Read
            'now check if StaffIDExists
            If CDbl(StaffIDExists) = 0 Then
                MessageBox.Show("your Insert SQL")
            Else
                MessageBox.Show("your Update SQL")
            End If

        End While
        read.Close()
        con.Close()
    End Function

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        StaffIDExists(TextBox1.Text)
    End Sub

[gwboolean]

I believe it does make sense. When the code is executed, either with, or without, the WHERE statement the query is successfully run every single time, while running the program in the debug/release state. The only time the error is thrown is after the application has been built and installed. I have run the code both ways, both through the debugger and on the built/installed versions and the query executes.

I don't believe I need to check if the ID is in the database? I am not sure what the benefit of this code would be. As the code stands now (sans the WHERE statement), there exists a coded first number (named recordID) already there, if the field, colStaffID is Null. I have executed this code many, many, many times and it has always functioned as expected.

When the installed application runs, it is clear to me that everything executes properly up to the point where the UPDATE is run. The UPDATE only fails when the application has been built and installed. I have gone through the whole list of possibilities, thus far, suggested and have not been able to verify that any of those apply.

I honestly no longer can see how the code itself might be at fault (although I am usually wrong with my conclusions where code is concerned). Additionally, I have reviewed a couple of applications I made in the past that use this same ascension numbering system and they all perform as they were expected.

It just seems to me that the answer lies somewhere in my build process and that I probably left something out of the build that was required. Having said that, I have no idea what that would be.

At the end of the day, this application requires that each record have a specific ascension number with a front end two letter designation, and an auto numbered field does not provide what is required. If the strategy that I am using will not provide this (and it has provided just that while creating/testing the application and even functioned in a previous built/installed application), then I will need to come up with an entirely different means to achieve that end.

Anyway, as I read your suggestion my understanding is that this code would do nothing more than check to see if the number exists. Perhaps if you would provide a little context I might be able to figure out where I am going wrong.

[bmwpete]

From what I read it appears your error will become apparent where you do not have permission to update the file, which would apply in your case if you have installed on another machine. I am also not convinced you 'key' generation process as shown in post #3 produces what you think it does.

[jdc2000]

When the code is executed, either with, or without, the WHERE statement the query is successfully run every single time, while running the program in the debug/release state. The only time the error is thrown is after the application has been built and installed. I have run the code both ways, both through the debugger and on the built/installed versions and the query executes.

Have you tested the installed version on the same machine where the debug version was created and tested OK, using the same database?

[gwboolean]

This has not been installed on a different machine. Having said that, the application is installed into the Program Files (x86) folder. I have checked every folder and file and ensured that none are Read Only.

I do not have a key generation process, unless that is what you are calling the process that generates the ascension number with designator. I set the primary key in the table properties.

As for the process generating the ID number, it works exactly as expected prior to building and installing the application. That seems very important to me, although no one else appears to believe that is the case.

Let me show you the output of my last failed attempt:

Attachment 185358

The Asset ID number has been generated and can be observed in the form. So the generator obviously functioned properly. Additionally, it has been demonstrated that the error has occurred while running the UPDATE, as we all seem to believe.

So the question becomes, what permission is required and where would I find this permission. Additionally, I have addressed all of the suggested possible causes, including #2, to the extent that I am capable and was unable to verify that any of them was the actual cause.

I am completely on-board with the idea that this is a permission issue. That being said, I would need some suggestions as to what needs to be change, since as far as I know I have ensured that nothing that was mentioned is set to Read Only. So what am I still missing? I checked the ReadOnly status and ensureed that nothing was set to ReadOnly. On the App_Data file I had to change that as an administrator.

[jdc2000]

the application is installed into the Program Files (x86) folder.

I presume that you are NOT storing your database or other user-updateable files in that same Program Files (x86) folder. That would be a problem, and would explain why the application works in development but not installed.

[gwboolean]

The database that is used with the installed application is indeed in the (86) folder. As are the databases that I am still using with previous applications. When I setup an install, I have been putting the database in with the items to be installed and they do end up in the (86) folder. Wouldn't setting the permissions for that folder take care of that issue? A rhetorical question, considering that I just made sure that the folders were not readonly.

[si_the_geek]

Using folders like ProgramFiles for writeable files has been off-limits for over 20 years.

These days it is more complex to get that working (partly due to virtualisation etc), and it isn't a wise move - those folders are protected to make it harder for malware to do bad things.


As to where you should be storing writeable files, there is some guidance in this article for VB6:
https://www.vbforums.com/showthread....m-uses-creates
(ignore the code, as that is for VB6 - the .Net equivalents are better and simpler)

[gwboolean]

I am only 20 years out of date? Damn, I am almost with it.

So what I am getting is that I probably need to rethink where I place the database. Does that apply to the other files, etc. that is in the installation package?

I have been exclusively using the Setup Wizard for setting up an installation. If I continue using that (i really would like to find a better way) can the installation package be setup to locate the database in a different location? Of course I will be checking on that even while I am waiting on your response.

I have seen the Publish in the project properties and have thought about using that, but I have seen nothing suggesting how to properly use that, nor have I seen where anyone does use that. I would like to try it, but it kind of scares the crap out of me trying something that is so unfamiliar.

[gwboolean]

It just occurred to me that I might be misunderstanding which files you are referring to. All of the files, including the database, are located in a folder that is nowhere near the (x86) folder, or any of the working folders. However, the database is included in the files that are added to my setup application folder and is included in the installation package. Everything in the installation package ends up in the (X86) folder.

So if I am to continue using the setup wizard should I not be placing the database in the installation package? Should I be placing the database separately in a different location? It seems to me like that would be a problem, but then many things seem to me to be one thing and turns out to be another.

[si_the_geek]

So what I am getting is that I probably need to rethink where I place the database. Does that apply to the other files, etc. that is in the installation package?

It applies to all writeable files (whether that is database files, text files, etc).

Executable files (such as .exe and .dll etc) should be in ProgramFiles or similar.

It just occurred to me that I might be misunderstanding which files you are referring to. All of the files, including the database, are located in a folder that is nowhere near the (x86) folder, or any of the working folders. However, the database is included in the files that are added to my setup application folder and is included in the installation package. Everything in the installation package ends up in the (X86) folder.

The location of the files at design time (in Visual Studio) is irrelevant.

What matters is the location when things have been installed.

So if I am to continue using the setup wizard should I not be placing the database in the installation package? Should I be placing the database separately in a different location? It seems to me like that would be a problem, but then many things seem to me to be one thing and turns out to be another.

You should have all files that your application needs in the installation package, you just need to make minor adjustments to ensure the files go to the correct locations for each file.

I haven't created a desktop installation for many years (I've been doing web and mobile apps), so I can't help with the details... but when creating the installation package there should be some way to specify the target location for each of the files. Some installers you have to click on each file and set properties, and others have a list of folders (eg: ProgramFiles, AppDataLocal) that you drag the files into.

[gwboolean]

Man this got complicated fast.

I really appreciate your information. BTW, I gave the Publish in the project properties a try and it became clear that I am nowhere near ready for that.

Anyway, I will followup on your suggestion just as soon as I get a chance. Thanks a lot!

[wes4dbt]

The Publish function doesn't have to be complicated. You can find many tutorials to help get you started. It really depends on what your goal is. Are you just trying to deploy your app to another machine or are you wanting to create an installer that someone else can download and install your app themselves.

[gwboolean]

I really appreciate the help. My goals are pretty simple. The applications I have built and am now building are all database desktop applications for a single user. That user being mostly me. My goals are driven primarily by cost. A simple single user desktop application is about all one can afford to write, since anything requiring IOs require money. To me, this is a merely hobby to keep my mind busy in my waning years (I am 73 and long retired).

I have no need for Web based or server applications to service multiple users. I am very interested in applications I could build to work with instrumentation and equipment, but that requires a lot of money for the instrumentation/equipment and, at this point in my life (my old lady would slice and dice me if I bought an HPLC and built an application to use/run and deal with the data), I won't be spending cash for things like that. Having said that, there are a few things that I could afford to buy and work with, and I still might work my way into a little bit of that (I am sure that I could afford to automate my log splitter, but then that doesn't particularly interest me).

While I have been able to obtain a fair amount of content, thanks to you guys, on deployment, I have not been able to obtain much material on what/where things should actually go. If you can point me to some specific information on that it would be very helpful.

Again, thanks for your time and information.

[si_the_geek]

The link I gave in post #19 has some guidance, under the heading "Which folder(s) should be used?"

Part of that is:

In summary, these three special folders are the valid locations:

AppData - for the current Windows user, on any computer on the network
Local_AppData - for the current Windows user, on this computer only
CommonAppData - for all Windows users on this computer

You should create your own sub-folders within these folders (eg: AppData\YourProgramName) to put files in to, otherwise you might find that your files are edited/deleted by other programs!

If you want the user to be able to navigate to the files using Explorer/My Computer, it is appropriate to save them to the Documents folder (known as "My Documents" in Windows XP and 2000), but note that the user can easily edit or delete them without using your program.

As you say single-user, you probably don't want to use CommonAppData, as that is shared by all users on the computer.

As for Local_AppData vs AppData (aka Roaming_AppData), the difference is whether the user will log on to a network (such as at work), and want to use the application on other computers on that network (eg: they might sometimes work from home, and log in remotely). If you suspect you will ever have users that want the network option, use AppData (instead of Local_AppData). For a user not on a network, both options are basically identical.

[gwboolean]

Cool! I think this last bit of information is going to get me there. I really appreciate that!