Have a Problem with ASP & MS SQL Server

Dev Shed Forums Sponsor:

Laplink Gold 2008 - $15 Off with Coupon Code CJM9NE

July 1st, 2003, 05:22 PM

colonel_klink

Junior Member

Join Date: Jun 2003

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Have a Problem with ASP & MS SQL Server

Hey all I am trying to go through some basic tutorials here and I am having some problems with connections to the Dbase. I am running MS SQL Server 2000 and IIS on a win2k Adv Server box that we run for our company purposes.

This is the code I want to run:

"

<!- - #include file="adovbs.inc" - ->

<html>
<head>
<title>Recieving</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>


<%
Dim Connect, GBRS, Query

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "GB"
Set GBRS = Server.CreateObject("ADODB.Recordset")
Query = "SELECT * FROM guestbook"
GBRS.Open Query, Connect, adOpenStatic, adLockOptimistic
%>

<%
'GBRS.AddNew
'GBRS("Name") = Lname
'GBRS("Email") = Emailadd
'GBRS("Comments") = Comments
'GBRS.Update
%>
Your Information: 
Name: <%'=Request("Name")%> 
E-mail: <%'=Request("Email")%> 
Comment: 

<textarea name="textarea" cols="50" rows="10"><%'=Request("Comment")%></textarea>

<% 'If GBRS.Update = True

'Then
%>
YOUR INFORMATION HAS BEEN STORED SUCCESSFULLY!!

<% 'Else %>

THERE WAS AN ERROR HANDLING YOUR INFORMATION PLEASE TRY AGAIN!

<% 'End If %>

  
</body>
</html>

"

As you can see I have commented out most of the code, because whenever I submit the form (the form action is to post to this ASP page) IIS comes up and says this page connot be displayed HTTP 500 Internal Server Error, So if the begging part of the code won't even work, I do not want to tie myself up with other code to work with. I am at a loss, I have read through this book 100 times, and I know the code must work, but maybe I have screwed it up or soemthing?

ANY help would be extremly appriciated.

Thanks!

Chris

July 1st, 2003, 06:06 PM

colonel_klink

Junior Member

Join Date: Jun 2003

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Uhhg, that is probably why, this stupid book does'nt talk about connecting specifically to SQL server, why the connect string is so different?

Thank you a lot, for the DataSource will 'localhost' suffice or should it be 'local' or perhaps the IP within the network?

IE: does this look right:

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=DBTEST; User ID=dbtest1; Password=alpha"
Set GBRS = Server.CreateObject("ADODB.Recordset")
Query = "SELECT * FROM guestbook"
GBRS.Open Query, Connect, adOpenStatic, adLockOptimistic

Also the user ID and Password, I have set up a User ID and Password within SQL, but it is just an SQL account that ONLY has access to my test Dbase, that will work ok though right?

I am sorry for all these questions, but I am kinda new to scripting to connect to SQL. I thank you for you patients and response

Chris

July 1st, 2003, 06:50 PM

colonel_klink

Junior Member

Join Date: Jun 2003

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Alright, hmm something is'nt right..

I am using the following string

<%
Dim Connect, GBRS, Query

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=DBTEST; User ID=sa; Password=alpha"
'Set GBRS = Server.CreateObject("ADODB.Recordset")
'Query = "SELECT * FROM guestb"
'GBRS.Open Query, Connect, adOpenStatic, adLockOptimistic
%>

I have commented out all the ASP code on the page except for the set connect, and connect.open parts and IIS won't do it. It gives me a HTTP 500 Internal Server error and that the page cannot be displayed.

I have fooled around with the Data Source changing to localhost, local, 127.0.0.1, 192.168.xxx.xxx (my internal IP)

Nothing seems to work. So I thought well maybe my Database is completly messed up. So I tried substituting my original user ID and password for my master accounts, nothing. So I thought well maybe my Dbase is'nt even able to connect to. So I went into the ODBC control panel and ran a DSN test to the SQL server and more specifically to that Dbase, everything checks out. Soooooo I am thinking maybe there is something wrong with the code I am using? I am also including the adovbs.inc file which is at the top of my page:

<!- - #include file="adovbs.inc" - ->

So I think that part is right, besides that there is no more code to debug, so either my system is really messed up, or the code aint right somewhere??

It seems to me I might be screwed, but any assistance is helpfull, I try to solve things on my own so I hope any routes I took to attempt to rectify the problem help you in helping me, if you can.

Thank you so much again,

Chris
~ Not liking Dbases right now ~

July 1st, 2003, 08:36 PM

colonel_klink

Junior Member

Join Date: Jun 2003

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

Wow

it worked, I guess I did'nt have the .inc file in the same directory. THANKS!

I do unfortunatly have another question, This page is going to be meant to take information from a form and plop it into a Dbase.
Now that my connection string works and I can get a successful connection to the database I am having a problem with the recordset open function. I have tested the code line by line and found the following line of code (which has been bolded) to be the problem, any ideas?

<%
Dim Connect, GBRS, Query

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=GBTEST; User ID=gbtest1; Password=alpha"
Set GBRS = Server.CreateObject("ADODB.Recordset")
Query = "SELECT * FROM guestb"
GBRS.Open Query, Connect, adOpenStatic, adLockOptimistic
%>

Did I get the syntax wrong? or perhaps the commands for SQL are different then the ones in this book? (if that is the case, then I am buying a new book tommorrow so that I can deal with SQL which is the environment I am working in)

Thanks in advance,

Chris

PS: Last question I promise!!

[EDIT] I have discovered that the code does'nt work whenever I have the 'adLockOptimistic' within the recordset's open function.

i.e:

This works:
GBRS.Open Query, Connect, adOpenStatic

This Does NOT Work (I cannot figure out why):
GBRS.Open Query, Connect, adOpenStatic, adLockOptimistic

Is there perhaps something I have to add at the end? Something slighty wrong with the syntax? The command is definitly supported by the DBMS cause it is SQL 2000. And it connects fine now, so I don't understand. Cause IF you don't add 'adLockOptimistic' you cannot add and edit any fields in the Dbase.

Any help is appriciated!

Thanks!!

[/EDIT]

Last edited by colonel_klink : July 1st, 2003 at 10:56 PM.

July 2nd, 2003, 02:08 AM

colonel_klink

Junior Member

Join Date: Jun 2003

Posts: 12

Time spent in forums: < 1 sec
Reputation Power: 0

hehe are you the local guru? I will have to get to know you

It seems to work fine, wheather or not adding the '3' will take the place of adLockOptimistic we will see as I attempt to input data into the Dbase, so I will let you know.

My thanks for your assistance, I'll let you know how it goes, if it goes...

Thanks again,

Chris

[EDIT]

WOW it works! hahaha Thanks a ton echolalia, obviously I got a bunch to learn, but learn I will. Hope to talk to you again sometime, cya!

Last edited by colonel_klink : July 2nd, 2003 at 02:34 AM.

July 2nd, 2003, 03:48 AM

Shad

Junior Member

Join Date: Jun 2003

Posts: 24

Time spent in forums: < 1 sec
Reputation Power: 0

Sorry, bit late on this, but if you configured an ODBC system DSN for the database, then it would be much simpler to create a connection string and much more robust.

The ODBC control panel applet is in Administrative Tools on Win2k/XP.

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Have a Problem with ASP & MS SQL Server

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!