asked on

SQLce running as a service issue.

Hello all:

I am writting a windows service to handle some transfer of data and one of the things I need to do is udate a SQLce Database. I have several functions which I transfered from my original application (Before it was changed to a service) like CleanUpDB() which deletes records that are no longer needed. This function works great. No issues. On the other hand if i try to update any data in the datqbase nothing happens. Although i have cut and pasted the code from my "WORKING" app to my service app for some reason now the update isn't happening.
Am I missing something here?

Thanks
Dave

Private Sub CleanUpDB()
        'Deletes old records in the database.  see SQL Constant "CleanupPackets" for time frame. 
        Dim da As New SqlCeDataAdapter
        Dim con As New SqlCeConnection(ConString)
        Dim ra As Integer
        con.Open()
        Dim RemoveOldPackets As New SqlCeCommand(CleanupPackets, con)
        ra = RemoveOldPackets.ExecuteNonQuery()
        EventLog.WriteEntry("CleanUp removed: " & ra & " Record(s) from the Database.")
        con.Close()
    End Sub
 
 
    Private Sub InformDBPacketSent(ByVal RefNumber As String)
        EventLog.WriteEntry("InformeDB Started: " & RefNumber)
        Dim da As New SqlCeDataAdapter
        Dim con As New SqlCeConnection(ConString)
        con.Open()
        Dim ThisPacket As String = PacketSent + RefNumber + "'"
        EventLog.WriteEntry("Select command: " & ThisPacket)
        da.SelectCommand = New SqlCeCommand(ThisPacket, con)
        Dim ds As New DataSet
        da.Fill(ds, "Packets")
        Dim dt As DataTable = ds.Tables("Packets")
        Dim row As DataRow
        If dt.Rows.Count > 0 Then
            If dt.Rows.Count = 1 Then
                If Verbose Then EventLog.WriteEntry("DB Found 1 Packet for Ref#: " & RefNumber)
                row = dt.Rows(0)
                row("PacketSendSuccessful") = True
                row("PacketEndSendTime") = Now
                Dim ph As String = IIf(Not row.IsNull("PacketHistory"), row("PacketHistory", DataRowVersion.Current), "")
                If Verbose Then EventLog.WriteEntry("packet history before inform added text." & vbCrLf & ph)
                ph = ph & "Packet End Transmission " & Now & vbCrLf
                row("PacketHistory") = ph
            Else
                EventLog.WriteEntry("DB Found " & dt.Rows.Count & "Packet(s) for Ref#: " & RefNumber)
                Dim counter As Integer = 0
                For counter = 0 To dt.Rows.Count - 1
                    row = dt.Rows(counter)
                    row("PacketSendSuccessful") = True
                    row("PacketEndSendTime") = Now
                    Dim ph As String = IIf(Not row.IsNull("PacketHistory"), row("PacketHistory", DataRowVersion.Current), "")
                    ph = ph & "Packet End Transmission " & Now & vbCrLf
                    row("PacketHistory") = ph
                Next
            End If
            Dim cb As New SqlCeCommandBuilder(da)
            EventLog.WriteEntry("Before update copmmand")
            da.Update(ds, "Packets")
            con.Close()
            EventLog.WriteEntry("After connection close")
        Else
            EventLog.WriteEntry("Packet not found: " & RefNumber)
            con.Close()
        End If
        EventLog.WriteEntry("DB informed of send: " & RefNumber)
    End Sub

Open in new window

dublingills

Hi,

Is the Windows Service running in the same security context as your user account?

MedSouth

ASKER

No I have it running in local system.

dublingills

If you start the service under your own user account are you able to update the database?

MedSouth

ASKER

No I can't. Durring my testing I'm manually starting it while logged in to my user profile. I can Delete records but the update doesn't work. I have not tried to add yet. but if I have to I guess i could delete and add it back...

MedSouth

ASKER

Oh I see what you are saying... change the service to run under my account information and see if it updates... I will try it.

dublingills

Forgive me but my understanding is that manually starting a service when logged into your user profile doesn't start the service under your user account, the service starts as whatever account is specified in the Log On tab of the service properties.

dublingills

Also, at what point does the code fail? Which messages are written to the event log?

MedSouth

ASKER

I set it to run as a user and provided it with a account with administrator rights still failed. As a side note you can set a service to start up in "LocalSystem" it doesn't require a user account to run.

anyway the event logs get to "Before update copmmand" line #49 of the above code.

dublingills

The Local System account is actually the NT AUTHORITY\SYSTEM account which is a hidden account because it runs highly privileged.

Anyway, back to the update issue - can you add change the following (the idea is to check the update command being created by the command builder):

Dim cb As New SqlCeCommandBuilder(da)
EventLog.WriteEntry("Before update copmmand")
da.Update(ds, "Packets")
 
 
TO
 
 
Dim cb As New SqlCeCommandBuilder(da)
EventLog.WriteEntry("Before update copmmand")
cb.GetUpdateCommand()
EventLog.WriteEntry(cb.GetUpdateCommand().CommandText)
da.Update(ds, "Packets")

Open in new window

MedSouth

ASKER

I did as you asked and still get to the same event before it locks
I don;t get the event entry for the command.text

dublingills

OK I need to have a bit more of a think as that seems really weird. Am off to UK tomorrow so will have a think over the wknd and get back to you monday, hopefully someone else may have something for you before then.

MedSouth

ASKER

OK I figured it out. It was a couple of things actually.

1st thing was Line 29 from my original post code snippit
row = dt.rows(0) changed it to
row = dt.rows.item(0)

2nd thing was my select statements. I know it is good practice to select only the fields you want to work with so that is what I was doing.

ie: SELECT FirstName, LastName, DateOfBirth FROM People WHERE FirstName = "John"
this would fail every time with the commandbuilder update

if I changed it to SELECT * FROM People WHERE FirstName = 'John'
and did my commandbuilder update everything worked perfect.

I do not understand the difference but Hay it works now... Thanks for you time and effort dublingills
Have a great time in the UK.

thanks again.
Dave

ASKER CERTIFIED SOLUTION

dublingills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MedSouth

ASKER

THanks again dublingills