Connecting to database

[Johnnie100]

I am trying to connect to a database and return a recordset using the following code:

dim ReturnCount

'Define db connection variables
dim adoConn
dim sConn
dim strQuery
dim adoRsWebCrawler

'Create connection object
set adoConn =server.CreateObject("ADODB.Connection&quot

'Create connection string
sConn = ""
sConn = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=

http://wwwuser;"

& _
"Password =

http://wwwpass;"

& _
"Initial Catalog=mspyajj_WebCrawler;" & _
"Data Source=CSMS11"

'Open connection to database
adoConn.Open sConn

' get the records that match the keyword
set adoRsWebCrawler = Server.CreateObject("adodb.recordset&quot

strQuery = "SELECT URL, KEY1.rank " &_
"FROM CONTAINSTABLE(WebCrawlerImport, *, '(" &_
Request.form("keyword&quot&_
&quot')as KEY1 INNER JOIN WebCrawlerImport WI on WI.Count = KEY1.[key] ORDER BY KEY1.RANK DESC"

'Populate recordset
adoRsWebCrawler.Open strQuery, adoConn, 3

ReturnCount = adoRsWebCrawler.RecordCount

--BUT I get the following error - I have spent over an hour trying to figure out what the error code means but MSDN isn't very helpful. I can run the query in ISQLW so don't think that is the problem.

The thing is I'm using exactly the same connection string to connect to the db to write away my records in the first place. Does the fact that I'm using a table with a full text index populated on it have any bearing??

I'm getting the following error msg:


Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/mspyajj/WebCrawlerSearchEngine/WebCrawlerSearchPage.asp, line 75


Thanks

 

[JamesLean]

It looks like you have a slight problem when concatenating your form variable into the SQL string. Whenever you have a problem like this, it is a good idea to write the SQL string to the screen just before you execute it:

strQuery = "..."
Response.Write strQuery
adoRsWebCrawler.Open strQuery, adoConn, 3

I think you just have the quote the wrong side of one of the parentheses. --James

 

[Johnnie100]

I have followed your advice and written the query to the HTML page but it looks fine. I have also taken the query from the HTML page, copied it into an SQL window and ran the query, again this is fine.

It is something perculiar about connecting to the recordset using:

adoRsWebCrawler.Open strQuery, adoConn, 3

Any other thoughts on the matter would be greatly appreciated.

 

[JamesLean]

Can I just check - which is line 75? I guess it's either where you open the connection or open the recordset?

I can't see anything wrong with opening the recordset like that. If it's the connection, try removing the .1 from the provider:

sConn = "Provider=SQLOLEDB;" & _

--James