[RESOLVED] Application Datatable connection error

[staticbob]

Guys,

How come when I try to populate a datatable in Application_start, do I get a SQL error ...

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

If I do this on a page_load event, it works fine ?

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Fires when the application is started
 
        'Create and hold the Staff, Region and Office tables
 
        Dim dt As New DataTable
        db.Staff.Fill(dt)
        Application("Staff") = dt
 
 
    End Sub
 
 
Class db
       Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return "data source=BLS5test2;persist security info=False;initial catalog=HPT;integrated security = sspi"
           End Get
    End Property
 
 
    Public Shared cnn As SqlConnection
    Private m_connectionstring As String
    Private m_commandtimeout As Integer = 60
    Shared Sub New()
        'If Not gapprunning Then Return
        cnn = New SqlConnection(ConnectionString)
           End Sub
 
    Class Staff
        Private Shared mda As SqlDataAdapter
        Friend Shared Function Table(ByVal RegionID As Integer, ByVal OfficeID As Integer) As DataTable
            Dim dt As New DataTable("Staff")
            Fill(dt)
            Return dt
        End Function
        Friend Shared Function Fill(ByVal dt As DataTable) As Integer
            Return DataAdapter.Fill(dt)
        End Function
        Friend Shared Function Update(ByVal dt As DataTable) As Integer
            Return DataAdapter.Update(dt)
        End Function
        Private Shared Function DataAdapter() As SqlDataAdapter
            If Not mda Is Nothing Then Return mda
            mda = New SqlDataAdapter
            With mda
                     .SelectCommand = New SqlCommand
                With .SelectCommand
                    .Connection = cnn
                    .CommandText = "select * from vw_HPTStaff"
                    '.CommandType = CommandType.StoredProcedure
                End With
                              AddHandler .FillError, AddressOf Fill_Error
                AddHandler .RowUpdated, New SqlRowUpdatedEventHandler(AddressOf DataAdapter_OnRowUpdated)
            End With
            Return mda
        End Function
        Private Shared Sub Fill_Error(ByVal sender As Object, ByVal e As FillErrorEventArgs)
            If TypeOf e.Errors Is Data.ConstraintException Then e.Continue = True
        End Sub
        Private Shared Sub DataAdapter_OnRowUpdated(ByVal sender As Object, ByVal args As SqlRowUpdatedEventArgs)
            Dim newID As Integer = 0
            Dim idCMD As SqlCommand = New SqlCommand("SELECT @@IDENTITY", DataAdapter.UpdateCommand.Connection)
            If args.StatementType = StatementType.Insert Then
                Try
                    newID = CInt(idCMD.ExecuteScalar())
                Catch ex As Exception
                    EH.Log(ex, EH.eErrorLogging.DisplayAndFile)
                End Try
 
                args.Row("ID") = newID
            End If
        End Sub
    End Class

[mendhak]

First, make sure your connection string is like this:

"Data Source=BLS5test2;Initial Catalog=HPT;Integrated Security = SSPI;"

I don't care what they say about case sensitivity, it should be right in strings like this.

Second, keep in mind that since you're connecting from your ASP.NET application, you are connecting as the ASP.NET worker process and not as your login. Therefore, I suggest that you create a sql server login in your SQL Server, and then use those credentials to connect to the database, so that your connection string looks something like this:

"Data Source=BLS5TEST2;Initial Catalog=HPT;User Id=someuser;Password=asdflasdfasdf;"

HTH

[staticbob]

Thanks MH,

I have corrected my connection string.... fair comment.

I have tried using the SA account explicitly declared in the connection string, but am still getting a login fail. I thought that doing this in the web.config . .

<authentication mode="Windows" />
<identity impersonate="true" />

. . . should sort this out and use the local user account ?

Cheers
Bob

[mendhak]

When using the sa login, were your credentials correct? Also, did you get the error message saying that the user was null?

[staticbob]

Thanks MH, I thought I had everything correct, but I must of had a typo, its worked fine with a new SQL user login.

Thanks
Bob

[mendhak]

That's good. Add [Resolved] to the thread title, and all that other stuff you must be familiar with by now.