Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 12th, 2004, 06:27 PM
Authorized User
 
Join Date: Mar 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to frankv25
Default Server error for database connection

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
 
Old August 12th, 2004, 07:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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:
Code:
objCommand1 = New OleDb.OleDbCommand("INSERTtblLogin", MyCon)
        objCommand1.CommandType = CommandType.Text
        objCommand1.CommandText = "INSERT INTO tblLogin(KlantID, Login, Paswoord)  VALUES(" + "'" + maxID.ToString + "','" + thisAccount.Login + "','" + thisAccount.Password + "')"
        MyCon.Open()
        result = objCommand1.ExecuteNonQuery()
        MyCon.Close()
Or do everything with the local variable.

Brian
 
Old August 13th, 2004, 08:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

 
Old August 13th, 2004, 11:27 AM
Authorized User
 
Join Date: Mar 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to frankv25
Default

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
 
Old August 14th, 2004, 06:57 AM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to RPG SEARCH
Default

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
 
Old August 14th, 2004, 07:33 AM
Authorized User
 
Join Date: Mar 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to frankv25
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Database connection with SQL Server yubaraj JSP Basics 3 May 29th, 2007 04:39 AM
IIS server, connection to offsite database server Hazzardusa Classic ASP Databases 1 August 14th, 2005 10:03 PM
Cannot add database connection in server explorer bbhill VS.NET 2002/2003 1 June 30th, 2003 09:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.