[RESOLVED] Post deployment error

[gwboolean]

I know the title sounds like something that should be discussed in a ED forum, but strangely enough it isn't.

I have an application that works as expected until after it is deployed and I am running the program. When I attempt to add a new record I receive the following error.

Operation muse use an updateable query.

I am pretty sure that the method below is the routine and the line (bold, italic) that the failure occurs, since the single row of the table used in the update is not updated.

        Public Function GetFileMasterID(ByVal FileID As String) As Object 'FileMasterID
            Dim recordID As String
            'Parameters
            FileMaster.FileMasterQuery("SELECT colFileMasterID FROM setFileMasterID")
            Dim r As DataRow = FileMaster.ListTable.Rows(CurrentRecord)
            If r("colFileMasterID").ToString = "" Then
                recordID = "FM10000001"
            Else
                Dim recno As String = r("colFileMasterID").ToString
                Dim intFileID As Integer = CInt(Mid(recno, 3, 8))
                intFileID += 1
                recordID = "FM" + intFileID.ToString
            End If
            'Update FileMaster ID in table
            FileMaster.AddParam("@recno", recordID)
            FileMaster.FileMasterQuery("UPDATE setFileMasterID SET colFileMasterID=@recno")
            Return recordID
        End Function

Now since this routine works without error while running the program in project mode it is my belief that the error is caused by my failing to get all the required files inserted into the build as I run the Setup Wizard. Of course that is merely a process N=1 guess.

So is there a reasonable possibility that my guess is right? And, if so, what might I have missed adding in my deployment package? On the other hand, the code I am using to create and keep track of an ascension number is probably kind of wonky, but it has served me well for quite some time and I have never looked for what is probably a better way of doing that. Additionally, I have deployed in the past, using this same method, without issue.

The ascension number comes from a table with a single column and a single record setFileMasterID. As you can see, the number (string) is pulled out of the table, run through the ascension routine and then returned to the table. This number is then bound to a control and later placed in the ID column of sitFileMaster.

[jmcilhinney]

I suspect that the issue is that you have no WHERE clause on that UPDATE statement. If it was executed as it is, it would update every record in the specified table, so the exception is probably saving you from that. We don't know what happens inside FileMaster though, so there's some speculation required.

[gwboolean]

The table referred to only has one record, and is meant to only have one record (although I did nothing special to the table to limit that). So a Where would still be required?

This also would beg the obvious question of, why would the Where be required in the build/deployed application and not in the project mode?

Having said all of that, I will take a shot at adding a WHERE into the update and see if that get's the job done. Thanks, will let you know if that works.

While we are still in the attic though, is there a better, more robust, way to create and use an ascension number?

[jmcilhinney]

I'm not completely sure what you mean by an ascension number but I'm guessing that you're referring to what most databases would call a sequence. Many proper databases have them built in but I don't think Access does, assuming that's what you're using.

[gwboolean]

An ascension number is just as I previously described. I have a need for an ascending number that has characters in it and I require that the number be a string to include letter characters. I also want ID to be formatted with leading 0s.

Database sequence numbers are equivalent, but not equal, to an ascension number. My familiarity with database sequence numbers is limited to the knowledge of an auto incrementing number field, which would not suit my requirement for an ascension number.

I changed the function to include a WHERE in the update, as seen below. The error still occurs in the build/deployed application.

        Public Function GetFileMasterID(ByVal FileID As String) As Object 'FileMasterID
            Dim recordID As String
            'Parameters
            FileMaster.FileMasterQuery("SELECT colFileMasterID FROM setFileMasterID")
            Dim r As DataRow = FileMaster.ListTable.Rows(CurrentRecord)
            If r("colFileMasterID").ToString = "" Then
                recordID = "FM10000001"
            Else
                LastID = r("colFileMasterID").ToString
                Dim intFileID As Integer = CInt(Mid(LastID, 3, 8))
                intFileID += 1
                recordID = "FM" + intFileID.ToString
            End If
            'Update FileMaster ID in table
            FileMaster.AddParam("@recno", recordID)
            FileMaster.AddParam("@lastno", LastID)
            FileMaster.FileMasterQuery("UPDATE setFileMasterID SET colFileMasterID=@recno " &
                                       "WHERE colFileMasterID=@lastno")
            Return recordID
        End Function

[gwboolean]

I ran the deployed application some more and found that this error applies to all of my update queries, not just the one illustrated above. All of my update queries function as expected in run mode.

Since all the update queries tested (3) in the built/deployed application failed, this strongly suggests to me that there is either some required element missing from the build, that the build process is incorrectly carried out, or that the build process is itself causing the issue.

Is that analysis correct? If not, then do you have any other suggestions as to why this might be so, or where I might look to find the answer?

[gwboolean]

I went back and rebuilt/redeployed the project. I added a number of files that I thought might relate, as well as all of the output and assembly files. to the build. I redeployed the application and now the update queries work (I have not yet tested all of them).

It will require some additional testing to figure out which file/output/assembly was the one that I was previously lacking. Additionally, I am now getting a User Account flash screen to open the application. I suspect that is due to a property I set and will be eliminating that.

Anyway, thanks for the help. I might not have been able to find what I needed without the assist.