Hello,

I've got serious problems and don't now where to start surching.

I need to insert some data in my Acces database. On my localHost it works without a problem. When I upload all my files to the internet it does not work, I get the below error.

Further down i'll post my connection Class and the function from my Customers Class.

It would be great if someone can give me an explenation.

Thank you in advance.
Frank

Server Error in '/' Application.
--------------------------------------------------------------------------------

ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.

ConnectionClass
Imports System.Data.OleDb.OleDbConnection

Public Class dbConnection

    Public Shared MyCon As OleDb.OleDbConnection
    Public Shared m_pool As New Stack()

    Public Function getConnection() As OleDb.OleDbConnection
        If Not MyCon.State = ConnectionState.Open Then
            Try
                MyCon.Open()
            Catch
            Finally
            End Try
        End If
        Return MyCon
    End Function

    Public Sub freeConnection(ByRef MyCon As OleDb.OleDbConnection)
        If Not MyCon Is Nothing Then
            MyCon.Close()
            m_pool.Push(Me)
            MyCon = Nothing
        End If
    End Sub

    Public Sub New()
        MyCon = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=C:\Inetpub\wwwroot\LaCieWebShop\MyDbase\Cre st_Comp.mdb;")
    End Sub

    Public Shared Function getInstance() As dbConnection
        If m_pool.Count > 0 Then
            Return CType(m_pool.Pop(), dbConnection)
        Else
            Return New dbConnection()
        End If
    End Function
End Class

insert function
Public Function insertNewAccount(ByVal thisAccount As clsKlant) As String
        Dim maxID As Integer 'bepaald personeelsID
        Dim result, result1 As String

        Dim pool As dbConnection = dbConnection.getInstance()
        Dim cnn As OleDb.OleDbConnection = pool.getConnection()
        Dim objCommand1 As OleDb.OleDbCommand
        Dim objCommand2 As OleDb.OleDbCommand
        objCommand1 = New OleDb.OleDbCommand("MaxID", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "SELECT MAX(KlantID) FROM tblKlanten"
        Try
            maxID = objCommand1.ExecuteScalar
            maxID = maxID + 1
        Catch
            'eerste klant aanmaken
            maxID = 1
        End Try
        'invoegen klantgegevens
        objCommand1 = New OleDb.OleDbCommand("INSERTtblKlant", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "INSERT INTO tblKlanten(KlantID, Naam, Familienaam, Adres, Postcode, Gemeente, Land, Email, Telefoon, GSM, Fax, Bedrijfsnaam, BTW, Nieuwsbrief) VALUES(" + "'" + maxID.ToString + "','" + thisAccount.Naam + "','" + thisAccount.Familienaam + "','" + thisAccount.Adres + "','" + thisAccount.Postcode + "','" + thisAccount.Gemeente + "','" + thisAccount.Land + "','" + thisAccount.Email + "', '" + thisAccount.Telefoon + "', '" + thisAccount.GSM + "', '" + thisAccount.Fax + "', '" + thisAccount.Bedrijfsnaam + "', '" + thisAccount.BTW + "', '" + thisAccount.Nieuwsbrief + "')"
        result = objCommand1.ExecuteNonQuery()
        objCommand1 = Nothing

        'ingevoegen KlantID tabel Login
        objCommand1 = New OleDb.OleDbCommand("INSERTtblLogin", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "INSERT INTO tblLogin(KlantID, Login, Paswoord) VALUES(" + "'" + maxID.ToString + "','" + thisAccount.Login + "','" + thisAccount.Password + "')"
        result = objCommand1.ExecuteNonQuery()
        objCommand1 = Nothing
        'connectie afsluiten
        pool.freeConnection(cnn)


    End Function


Frank Vandeven
Belgium

__________________
Frank Vandeven
Belgium

Hey,

In my personal opinion, I wouldn't do it that way... I would create the connection locally, open it locally, and close it locally. That may be an issue in this: you may not be able to return an open connection and it still be open when going from variable to variable (don't know for sure). Actually, if you are using a function to open and close the connection, why not use the global connection, instead of passing it to a local variable?

You could do:
Or do everything with the local variable.

Brian

You need to open your connection just before executing the query.

Thanks for helping but now I'll get a new error.

Server Error in '/' Application.
'C:\Inetpub\wwwroot\LaCieWebShop\MyDbase\CrestDB.m db' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

This is the path on my loacl machine.

In the code I redirect to the correct path needed to acces the database on the server. Is the local path hidden somewhere in some file?
this is how I did it now.???

I reduced the connection class just store the path as string

Public Class dbConnection

    Private _strMyCon As String

    Public Property strMyCon() As String
        Get
            Return "d:\...\...\...\data\CrestDB.mdb"
        End Get
        Set(ByVal Value As String)
            strMyCon = Value
        End Set
    End Property

End Class

I'll make the connection in the newCustomer class as follows

Public Function insertNewAccount(ByVal thisAccount As clsKlant) As String
        Dim maxID As Integer
        Dim result, result1 As String

        Dim MyConn As dbConnection = New dbConnection()
        Dim strMyConn As String
        strMyConn = MyConn.strMyCon

        Dim cnn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=" & strMyConn & ";") Dim objCommand1 As OleDb.OleDbCommand
        Dim objCommand2 As OleDb.OleDbCommand
        objCommand1 = New OleDb.OleDbCommand("MaxID", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "SELECT MAX(KlantID) FROM tblKlanten"
        Try
            cnn.Open() maxID = objCommand1.ExecuteScalar
            maxID = maxID + 1
            cnn.Close()
        Catch
            maxID = 1
        End Try
        objCommand1 = New OleDb.OleDbCommand("INSERTtblKlant", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "INSERT INTO tblKlanten(...) VALUES("...")"
        cnn.Open()
        result = objCommand1.ExecuteNonQuery()
        objCommand1 = Nothing
        cnn.Close()

        objCommand1 = New OleDb.OleDbCommand("INSERTtblLogin", cnn)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = ...(sqlstring)
        cnn.Open()
        result = objCommand1.ExecuteNonQuery()
        objCommand1 = Nothing
        cnn.Close()
        cnn = Nothing


    End Function

Frank Vandeven
Belgium

copy and paste this code into a new file.... save it as path_locator.asp and then put it in the directory that your database is in.... run the script http://www.yourdomain.com/your_direc...th_locator.asp and it should return the actual database path. This is used at http://www.maxwebportal.com i hope that helps a little.

<html>
<head>
<title>Current Location</title>
</head>
<body>
<b><%= Request.ServerVariables("PATH_TRANSLATED") %></b>
</body>
</html>



David

I already retrieve the path from server.mappath("...\*.mdb") in the page and pass it trough as a string variable to the class where I make a connection.

I'm getting close now, thank you all for your help.

Now I get a new error but I'll guess it has something to do with the security of the database.



Frank Vandeven
Belgium