ADO Code
-
Hello Y'all
Unfortunately this one isn't going to be overly challenging, might result in an unused synapse firing or at the very least allow a condecending tone in the response. Anyway here's the query -
1. I wish to create an ADO object to connect to a SQL Server 2000 database that is sitting on a local webserver on my computer -don't ask!!!
2. I then want to query a table called evaluation
3. Finally I would like to write the results to a web page
I connect to SQL server using the local windows authentication. This is the code I've come up with - please blow a few holes in it as it isn't working for me.
<%
Dim Conn as Connection
Dim Com as Command
Dim Rs as Recordset
set conn = new connection
set Com = new command
set Rs = new recordset
conn.provider="SQLOLEDB"
conn.connectionstring="Data Source=local; initial catalog=Pubs user id=sa"
Com.activeconnection=Conn
Com.commandtext="select * from evaluation"
set Rs=com.execute
do until rs.eof=true
for each x in rs.fields
response.write(x.name)
response.write("=")
response.write(x.value)
next
loop
%>
Cheers,
Alan
-
Try
<%
Dim Conn
Dim Com
Dim Rs
set conn = CreateObject("ADODB.Connection")
set Com = CreateObject("ADODB.command")
set Rs = CreateObject("ADODB.recordset")
conn.provider="SQLOLEDB"
conn.connectionstring="Data Source=(local); initial catalog=Pubs; user id=sa; password="
Com.activeconnection=Conn
conn.open
set Com.activeconnection=Conn
Com.commandtext="select * from evaluation"
set Rs=com.execute
do until rs.eof=true
for each x in rs.fields
response.write(x.name)
response.write("=")
response.write(x.value)
next
rs.movenext
loop
%>
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
quote:
set conn = CreateObject("ADODB.Connection")set Com = CreateObject("ADODB.command")
set Rs = CreateObject("ADODB.recordset")
I always thought one must use something like
Set conn = Server.CreateObject("ADODB.Connection")
...
Is this the same?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
Yes, laziness on my part. If you leave off 'Server.' it is assumed (bad practice
).I know ASSuME makes an ASS of you and ME and Assumption is the mother of all F*** ups.
p.s. This is server side scripting.
Edited by - davidburrows on 07/07/2003 06:29:03 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Hi David,
quote:
Yes, laziness on my part. If you leave off 'Server.' it is assumed (bad practice).it was a serious question. I am a lazy coder, too!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Mine was a serious answer. I have found, via this forum, that I do too much by assumption and default which can lead to inefficiency.
All I was trying to point out is that in the case of the use of 'Server.', if the server has to do extra processing because 'Server.' is not specified then it is good procatice to supply it otherwise it is inefficient in use of time and excess wear and tear on keyboard and fingers.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
All I was trying to point out is that in the case of the use of 'Server.', if the server has to do extra processing because 'Server.' is not specified then it is good procatice to supply it otherwise it is inefficient in use of time and excess wear and tear on keyboard and fingers.good point.
Some time ago I code my first asp pages from an example i found somewhere else. There was Server... Because it works I encapsulated it and haven't change it. Maybe I should think it over.
quote:
excess wear and tear on keyboard and fingersHope you never get a typewriter's cramp
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi David and Frank,
...I hope my query isn't giving either of you any keyboard cramp! David, thanks for the code, which is a great improvement on what I had. Unfortunately, it didn't work for me which makes me think that it might be due to the security settings on the server, which was only recently installed on my computer - what do you guys think???
Cheers
-
Hi Alan,
quote:
Unfortunately, it didn't work for me which makes me think that it might be due to the security settings on the server, which was only recently installed on my computer - what do you guys think???any error messages???
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
...unfortunately not
the browser merely hangs for a few seconds and then returns a blank page. -
quote:
...unfortunately not the browser merely hangs for a few seconds and then returns a blank page.any error handling routines turned on?
BTW, I use this to connect to SQL Server
cnnString = "PROVIDER=MSDASQL;"
cnnString = cnnString & "DRIVER=SQL Server;"
cnnString = cnnString & "SERVER=DCCIV30002004;"
cnnString = cnnString & """;"
cnnString = cnnString & """;"
cnnString = cnnString & "DATABASE=FAI;"
Set cnn = CreateObject("ADODB.Connection")
cnn.open cnnString
Set Connect = cnnCheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Alan,
If you using the code as is, ie no error trapping, then it is unlikely that permissions being the problem as by default errors are shown. When you see the blank page right click and select View source and see if any message is shown there. Other than that are you sure there is data in the table!
Far away is close at hand in the images of elsewhere.
Anon. -
Hi Frank,
Haven't seen that code before - thought it was merely a matter of creating a connection object and then opening it.
Don't have any error handling routines switched on - is there any easy way of creating one?
Thanks
Alan
-
Hi Alan,
quote:
Haven't seen that code before - thought it was merely a matter of creating a connection object and then opening it.
in most cases it is that simple!
There can be of course a lot of places to look for errors.
SQL Server
IIS
...
quote:
Don't have any error handling routines switched on - is there any easy way of creating one?this is very easy. Your first asp command could be
On Error Resume Next
and you have your error handler. However, the pitfall is, you don't see where any error happens, in case the code isn't working properly. I place error handlers only in, when the code is near 100% error free.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Hi Frank,Yes, laziness on my part. If you leave off 'Server.' it is assumed (bad practice
).I know ASSuME makes an ASS of you and ME and Assumption is the mother of all F*** ups.
p.s. This is server side scripting.
Edited by - davidburrows on 07/07/2003 06:29:03 AM
Actually there is no overhead if you leave off Server. from CreateObject.. But there is a difference.
Server.CreateObject("Object") invokes the Microsoft Transaction Server (MTS) to create the object and handle it, where CreateObject simply goes straight to it.
If you try to instantiate an object via CreateObject that is erroneous, CreateObject will throw an error and that's it. Server.CreateObject, in the same situation, will throw an error and also log the error in the event log.
If you're using a component that deals with transactions (such as the ADODB set of objects do) It's good practice to use Server.CreateObject to let MTS control it. But if you're not using transactions you'll create uneeded cpu and memory overhead by using Server.CreateObject, so in that instance its good practice to pipe it through CreateObject.
Regards,
~Eric
).
the browser merely hangs for a few seconds and then returns a blank page.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply