What's wrong with my ASP SQL Server connection?

Last Post 20 Mar 2012 08:49 AM by hopeful. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Resolved
hopeful
New Member
New Member

--
14 Mar 2012 02:24 PM
Going out of my mind with an ASP connection to SQL Server. Actually, there's no error message on the connection itself, but there is when I try to write a variable from the database, I get this: ********************* Microsoft VBScript runtime error '800a01c2' Wrong number of arguments or invalid property assignment: 'SSConn' ********************* Here's the code. But then, in the body of the page, this gives the error: <%=SSConn("Conference")%> So what is the correct code for all this? I'm sure this has been asked a hundred times, but I've spent hours looking through forums on this. It's just easier to ask it again. Thank you.
hopeful
New Member
New Member

--
14 Mar 2012 02:29 PM
Whoa! Submitted, then saw that most of the post disappeared - it was inside the ASP thingies. So let me try again. Going out of my mind with an ASP connection to SQL Server. Actually, there's no error message on the connection itself, but there is when I try to write a variable from the database, I get this: ********************* Microsoft VBScript runtime error '800a01c2' Wrong number of arguments or invalid property assignment: 'SSConn' ********************* Here's the code. AccessCode = "CorrectValue" 'The above line is NOT the problem dim strSQL strSQL = "SELECT Conferences.Conference FROM Conferences WHERE Conferences.ConferenceCode = " & AccessCode 'The above line does NOT give an error. set SSConn=Server.CreateObject("ADODB.Connection") DSN="Driver={SQL Server};Server=sql.CorrectServer.com;Database=CorrectDatabase;UID=CorrectUser;PWD=CorrectPassword" 'The above line does NOT give an error. SSConn.Open strSQL, DSN, adOpenKeyset,adLockReadOnly 'The above line does NOT give an error. But later, this gives the error: =SSConn("Conference") So what is the correct code for all this? I'm sure this has been asked a hundred times, but I've spent hours looking through forums on this. It's just easier to ask it again. Thank you.
hopeful
New Member
New Member

--
14 Mar 2012 02:37 PM
Okay, that got all the content into the post -- except the line breaks!!! Is that a Google Chrome problem? I'll try again here with Firefox. Sorry, folks!

Whoa! Submitted, then saw that most of the post disappeared - it was inside the ASP thingies. So let me try again.

Going out of my mind with an ASP connection to SQL Server. Actually, there's no error message on the connection itself, but there is when I try to write a variable from the database, I get this:

*********************
Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'SSConn'
*********************

Here's the code.


AccessCode = "CorrectValue"
'The above line is NOT the problem

dim strSQL
strSQL = "SELECT Conferences.Conference FROM Conferences WHERE Conferences.ConferenceCode = " & AccessCode
'The above line does NOT give an error.

set SSConn=Server.CreateObject("ADODB.Connection")
DSN="Driver={SQL Server};Server=sql.CorrectServer.com;Database=CorrectDatabase;UID=CorrectUser;PWD=CorrectPassword"
'The above line does NOT give an error.

SSConn.Open strSQL, DSN, adOpenKeyset,adLockReadOnly
'The above line does NOT give an error.


But later, this gives the error:

=SSConn("Conference")

So what is the correct code for all this?

I'm sure this has been asked a hundred times, but I've spent hours looking through forums on this. It's just easier to ask it again.

Thank you.
russellb
New Member
New Member

--
15 Mar 2012 08:22 AM
You need to create a recordset object. Then access the members of that. There is no "Conference" property of the connection object.

Something like this

Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")

rs.Open strSQL, SSConn

...
=rs("Conference")


By the way your code is an open window for a SQL Injection attak.
hopeful
New Member
New Member

--
16 Mar 2012 05:22 PM
Thank you, Russell. However, I still get an error. And I must say that ASP is a foreign language to me -- I was born speaking ColdFusion, and have only accidentally stumbled into an ASP village that I hope to exit soon. So I hope you'll be kind enough to provide the exact and complete code that I need -- thanks! Here's the code after trying to implement your suggestion: *************************************** AccessCode = "CorrectValue" dim strSQL strSQL = "SELECT Conferences.Conference FROM Conferences WHERE Conferences.ConferenceCode = " & AccessCode set SSConn=Server.CreateObject("ADODB.Connection") DSN="Driver={SQL Server};Server=sql.CorrectServer.com;Database=CorrectDatabase;UID=CorrectUser;PWD=CorrectPassword" SSConn.Open strSQL, DSN, adOpenKeyset,adLockReadOnly Dim rs Set rs = Server.CreateObject ("ADODB.Recordset") rs.Open strSQL, SSConn =rs("Conference") ************************************** The last line of this code now gives this error: ADODB.Recordset error '800a0e7d' The connection cannot be used to perform this operation. It is either closed or invalid in this context. Again, Russel, I'd really appreciate it if you would give me the complete code that I need, since I obviously don't know what to do with the pieces. And by the way -- how is this code an open window for a SQL Injection attack? Thank you!
hopeful
New Member
New Member

--
16 Mar 2012 05:25 PM
Wow! Submitted in IE and it collapsed all my paragraph marks! Just like Google Chrome! Why????? Anyway, here it is again with Firefox.

Thank you, Russell.

However, I still get an error. And I must say that ASP is a foreign language to me -- I was born speaking ColdFusion, and have only accidentally stumbled into an ASP village that I hope to exit soon. So I hope you'll be kind enough to provide the exact and complete code that I need -- thanks!

Here's the code after trying to implement your suggestion:

***************************************
AccessCode = "CorrectValue"

dim strSQL
strSQL = "SELECT Conferences.Conference FROM Conferences WHERE Conferences.ConferenceCode = " & AccessCode

set SSConn=Server.CreateObject("ADODB.Connection")
DSN="Driver={SQL Server};Server=sql.CorrectServer.com;Database=CorrectDatabase;UID=CorrectUser;PWD=CorrectPassword"

SSConn.Open strSQL, DSN, adOpenKeyset,adLockReadOnly

Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open strSQL, SSConn


=rs("Conference")
**************************************

The last line of this code now gives this error:

ADODB.Recordset error '800a0e7d'
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

Again, Russel, I'd really appreciate it if you would give me the complete code that I need, since I obviously don't know what to do with the pieces.

And by the way -- how is this code an open window for a SQL Injection attack?

Thank you!

hopeful
New Member
New Member

--
20 Mar 2012 08:49 AM
After seeking in another forum, I got a solution. For the record, here is what worked.

AccessCode = RScatalog_item("CONF NAME")
dim strSQL
strSQL = "SELECT TOP 1 semConferences.Conference FROM semConferences WHERE semConferences.ConferenceCode = '" & AccessCode & "'"
set SSConn=Server.CreateObject("ADODB.Connection")
SSConn.ConnectionString = "Driver={SQL Server};Server=sql.CorrectServer.com;Database=CorrectDatabase;UID=CorrectUser;PWD=CorrectPassword"
SSConn.open
Set rstMain = Server.CreateObject("ADODB.Recordset")
rstMain.Open strSQL, SSConn


Acceptable Use Policy
---