Connection Strings 1

auger · Oct 27, 2001

I'm new to ASP and am trying to learn about conn strings. I use Brinkster basic.

I have a third party News Manager that I want to use to post articles to a web page. It's an ASP app with it's own DB. Brinkster requires a DSNless connection.

The code in the app to declare variables and strings is basically this:

Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection&quot

Set objRS = Server.CreateObject("ADODB.Recordset&quot

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data_ Source=\BrinksterUserName\db\DBName.mdb;" & "Persist_ Security Info=False"

' Open News table in database
objRS.Open "News", objConn, adOpenStatic, adLockReadOnly,_ adCmdTable

' Move to the last record
objRS.MoveLast

**Using this code, the browser starts looking for a DB on C:\ and errors out.

So I changed the conn code to this:

objConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & Server.MapPath("/BrinksterUserName/db/DBName.mdb&quot

from

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data_ Source=\BrinksterUserName\db\DBName.mdb;" & "Persist_ Security Info=False"

I think that changes the conn string to a DSNless connection but I still can't post to the DB. Browser returns error: HTTP 500 - Internal server error

Do I have to alter the recordset some how? Other? Help please. Thanks in advance.

ToddWW · Oct 27, 2001

OK, first of all, both of those are DSNless connections. You may have been successful in connecting to the database, but your server might be returning a different error. Unfortunately, you have "Friendly Errors" turned ON in your browser settings so all you get to see is the HTTP 500 - Internal Server Error.

If you want to see the real error, follow these instructions.

In the browser that your are using to connect to the website, select Internet Options or Options from the Tools menu. Click on the Advanced tab. Under the Browsing category, disable Show Friendly HTTP error messages. Then connect to the web site again. You'll get a true error message telling you the exact problem.

If you can't troubleshoot it, send us that error message to look at..

ToddWW

ToddWW · Oct 27, 2001

Oh, another option would be to use Netscape to connect to the website. It will display the ASP error.

ToddWW

auger · Oct 28, 2001

The error the browser returns is:

Microsoft VBScript runtime error '800a01a8'
Object required: 'Provider=Microsoft.J'
/BrinksterUserName/news/submitstory.asp, line 37

Lines 36 & 37 in the code are:

' Execute the SQL insert command
objConn.Execute cmdSQLinsert

The entire code for the page is:

<%Option Explicit%>
<html>

<head>
<title>Thank you!</title>
</head>

<body>

<%

' Define variants
Dim strTitle, strStory, strLink, strSubmittedBy, strDatestamp, objConn, cmdSQLinsert

' Check if some field entered by user is empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''&quot

End Function

' Process values from the form
strTitle = ChkString(Request.Form("title&quot

)
strStory = ChkString(Request.Form("story&quot

)
strLink = ChkString(Request.Form("link&quot

)
strSubmittedBy = ChkString(Request.Form("submittedby&quot

)
strDatestamp = Now()

' Define database file type and location
Set objConn = Server.CreateObject("ADODB.Connection&quot

objConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & Server.MapPath("/myBrinksterUserName/db/myDBName.mdb&quot

' Prepare the SQL insert command
cmdSQLinsert = "insert into News (Title, Story, Link, SubmittedBy, Datestamp) values ('" & strTitle & "', '" & strStory & "', '" & strLink & "', '" & strSubmittedBy & "', '" & strDatestamp & "')"

' Execute the SQL insert command
objConn.Execute cmdSQLinsert

' Close and clean up
Set objConn = Nothing

%>

<h1>Thank you!</h1>

<p>Your story has been submitted.</p>

<p><a href="default.asp">Click here</a> to return.</p>

<p> </p>

</body>
</html>

Thanks for your time. I really appreciate it. Any other toughts?

ToddWW · Oct 28, 2001

Well, that particular error is because your assigning the connection string to the connection object. As much as that sounds like it makes sense, it's not the proper method. You need to assign the connection string to another variable, then call that variable in the Execute method.

Here's how it needs to be done.

<%Option Explicit%>
<html>

<head>
<title>Thank you!</title>
</head>

<body>

<%

' Define variants
Dim strTitle, strStory, strLink, strSubmittedBy, strDatestamp, objConn, cmdSQLinsert, strProvider

' Check if some field entered by user is empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''&quot
End Function

' Process values from the form
strTitle = ChkString(Request.Form("title&quot)
strStory = ChkString(Request.Form("story&quot)
strLink = ChkString(Request.Form("link&quot)
strSubmittedBy = ChkString(Request.Form("submittedby&quot)
strDatestamp = Now()

' Define database file type and location
Set objConn = Server.CreateObject("ADODB.Connection&quot

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & Server.MapPath("/myBrinksterUserName/db/myDBName.mdb&quot

' Prepare the SQL insert command
cmdSQLinsert = "insert into News (Title, Story, Link, SubmittedBy, Datestamp) values ('" & strTitle & "', '" & strStory & "', '" & strLink & "', '" & strSubmittedBy & "', '" & strDatestamp & "')"

' Execute the SQL insert command
objConn.Execute cmdSQLinsert, strProvider

' Close and clean up
Set objConn = Nothing

%>

<h1>Thank you!</h1>

<p>Your story has been submitted.</p>

<p><a href="default.asp">Click here</a> to return.</p>

<p> </p>

</body>
</html>

Try that. Should work like a charm, so long as everything is Kosher with the connection string.

ToddWW

auger · Oct 28, 2001

If I change the code on the submit story page as you have suggested, the browser now returns the error:

ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.
/BrinksterUserName/news/submitstory.asp, line 42

Where lines 41 & 42 are:

' Execute the SQL insert command
objConn.Execute cmdSQLinsert, strProvider

This error occurs when I have *not* changed any code on the other pages (there are 5 asp pages and 1 HTML form page). I tried to incorporate the strProvider variable on some of the other pages that reference the db but it seemed to be a step backward so I reverted to the code as you suggested.

I can query and insert to the db using the Brinkster DB Manager but not from the asp page. Is the error telling me that my connection object is closed?

I commented out this line from my original code:

'objConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist_ Security Info=False;Data Source=" & Server.MapPath_("/BrinksterUserName/db/DBName.mdb&quot

...which leaves the code just as you suggested.

Thanks for your help thus far. It seems to be pointing me in the right direction. I don't want to take too much advantage of your generosity and time but do you have any other suggestions?

ToddWW · Oct 28, 2001

I'm very sorry, I don't mean to waste your time. I must have been brain dead on my previous example. Here, this should definitely work as long as the connection string is valid.

My sincere apologies.

<%Option Explicit%>
<html>

<head>
<title>Thank you!</title>
</head>

<body>

<%

' Define variants
Dim strTitle, strStory, strLink, strSubmittedBy, strDatestamp, objConn, cmdSQLinsert, strProvider

' Check if some field entered by user is empty
Function ChkString(string)
If string = "" Then string = " "
ChkString = Replace(string, "'", "''&quot
End Function

' Process values from the form
strTitle = ChkString(Request.Form("title&quot)
strStory = ChkString(Request.Form("story&quot)
strLink = ChkString(Request.Form("link&quot)
strSubmittedBy = ChkString(Request.Form("submittedby&quot)
strDatestamp = Now()

' Define database file type and location
Set objConn = Server.CreateObject("ADODB.Connection&quot

strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Persist Security Info=False;Data Source=" & Server.MapPath("/myBrinksterUserName/db/myDBName.mdb&quot

Conn.Open strProvider

' Prepare the SQL insert command
cmdSQLinsert = "insert into News (Title, Story, Link, SubmittedBy, Datestamp) values ('" & strTitle & "', '" & strStory & "', '" & strLink & "', '" & strSubmittedBy & "', '" & strDatestamp & "')"

' Execute the SQL insert command
objConn.Execute cmdSQLinsert 'This line goes back the way it was

' Close and clean up
Set objConn = Nothing

%>

<h1>Thank you!</h1>

<p>Your story has been submitted.</p>

<p><a href="default.asp">Click here</a> to return.</p>

<p> </p>

</body>
</html>

I owe you one !!

ToddWW

auger · Oct 28, 2001

No need for appologies, really. I appreciate any help someone is kind enought to offer.

I made the changes and voila! Works like a charm.

Sincere thanks Todd. This ASP learning curve is steep but with some persistance, and the help of kind souls like yourself on the web, I may just get it yet. You made my day!

s-)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Connection Strings 1

auger

Technical User

ToddWW

Programmer

ToddWW

Programmer

auger

Technical User

ToddWW

Programmer

auger

Technical User

ToddWW

Programmer

auger

Technical User

Similar threads

Part and Inventory Search

Sponsor