SQL lookup on server 2008 running as a win service.

[dimmslider]

Hi all,

I am writing a service to get a count from a table on an SQL server.

THe SQL box is in the same domain, and the service is runnign under the same account as the SQL box.

The box service is running on is Win 2008 in the domain.

The interesting thing is that when I use this same code and run a normal EXE on that server, it runs no issues. In a service it crashe with:

Service cannot be started. System.IndexOutOfRangeException: Cannot find table 0.
   at System.Data.DataTableCollection.get_Item(Int32 index)
   *at EmailerTestService.Service1.query()
   at EmailerTestService.Service1.OnStart(String[] args)
  * at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state)***

I have checked and the connection strings imports correctly. The connection string is:

  Server=SERVER_HOST_NAME;Database=dbUSR;User ID=code1\user1;Password=PASSWORD;Trusted_Connection=False;

SQL Query I use is:

select count(*) from cat_validation

Here is the sub:

 Private Sub query()
        Dim conString As String = readSettings("SQLconnection")
        WriteLogMessage(readSettings("SQLconnection"), EventLogEntryType.Information)

        Dim objDataAdapter As New SqlDataAdapter(readSettings("SQLQuery"), conString)
        WriteLogMessage(readSettings("SQLQuery"), EventLogEntryType.Information)
        Dim dsResult As New DataSet("Result")

        If Not IsNothing(objDataAdapter) OrElse Not IsNothing(dsResult) Then
            ' Fill data into dataset
            Try
                Dim x As String = dsResult.Tables(0).Rows(0)(0).ToString()
                sendAlert("current User count is: " & x, "current User count is:")
                objDataAdapter.Dispose()
            Catch ex As SmtpException
                WriteLogMessage(ex.ToString, EventLogEntryType.Error)
                Return
            End Try
        End If

    End Sub

Thanks!

[Begginnerdev]

Dim x As String = dsResult.Tables(0).Rows(0)(0).ToString()

 Dim x As String = dsResult.Tables(0).Rows(0).Item(0).ToString

[dimmslider]

Nope... Still

System.IndexOutOfRangeException: Cannot find table 0.

I did try this before too. The odd thing is that the above code runs fine when run in an EXE, but doesn't when run as a service...

[Begginnerdev]

For one thing, I never see you fill the data set with the data adapter.

Example:

da.Fill(ds,"tableName")

[adam_k]

For another, there is no need for a data set if you are only retrieving a count. Use ExecuteScalar instead and get the value directly in string.