Connection string works only 1/2...!

adamsoderqvist · Jul 13, 2002

I have this connection string:

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

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../db/Members.mdb&quot

& "; Persist Security Info=False"
objConn.Open(sConnection)"

Now, when I try to get info using this statement:

"objRS("FirstName&quot

" - it wont return anything,

...thogh when I use this:

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

objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; & "DBQ=" & Server.MapPath("../db/Members.mdb&quot

"

...it works fine.

the reason why I have to have the first connection string is complex, and it's in an include file, which means that all connections are the same...

There is no problem when I add/updates or removes stuff from the DB with the first string...! - Just when I want to collect data!

hlp, pls!

ehsant · Jul 14, 2002

Hi there ...
I think maybe you are opening your Recordset with wrong
Cursortype or wrong cursorlocation
Try opening your recordset like this :
Set RS = Server.CreateObject("ADODB.RecordSet&quot

RS.Open SqlStr, DBConn, 3, 3, 1

TNX ...
E.T.

adamsoderqvist · Jul 15, 2002

No, tried that... the strange thing is that it finds the database (because it doesn't flags for missing file, or anything), but it just wont pick the records!

FesterSXS · Jul 15, 2002

Can we see the code where you create the objRS recordset?? Tony

http://www.phoenix-paints.co.uk/portfolio

adamsoderqvist · Jul 15, 2002

ok, here goes...

'Declare recordset object...
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset&quot

'Declare SQL query...
Dim strSQL
strSQL = "SELECT FirstName, LastName FROM tblMember WHERE UserName='" & strUserName & "'"

'Open the database and get the requested info!
objRS.Open strSQL, objConn

I've left out the different cursors and lock types, since they didn't do any difference!
It's really weird - it returns NOTHING! - not just a msg saying that the record is empty (which it isn't, but stil...), but an EMPTY record...!

ehsant · Jul 15, 2002

Hi Again ...
one more question ...
what do you mean by "but an EMPTY record"
i mean after opening the recordset ...
what's the RecordCount ?
0 or -1 ???

adamsoderqvist · Jul 16, 2002

Oh, I mean that the return string from the DB is empty....

But here's a neat one:

When I run the string THAT WORKS, the Record Count is "-1", but when I run the other one, it RETURNS NO Record Count!!!

whats goin on...?

ehsant · Jul 16, 2002

OK ...
it seems a little strange ...
can we have your code ( the one which does not work )
maybe after that I can say what's really happening...
just a copy of the file and also the script of the Table
which you are working with it in that code ...
TNX
E.T.

adamsoderqvist · Jul 17, 2002

ok, here's the code:

---begin code---

<%@ language=vbscript%>
<%option explicit%>
<%Response.Buffer = True%>




<html>
<%
'Get the username...
Dim strUserName
strUserName = Session("UserName&quot

'Declare var's for - and open - database connection
Dim objConn, sConnection
Set objConn = Server.CreateObject("ADODB.Connection&quot

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("../db/Members.mdb&quot

& "; Persist Security Info=False"
objConn.Open(sConnection)

'Declare SQL query...
Dim strSQL
strSQL = "SELECT FirstName, LastName FROM tblMember WHERE UserName='" & strUserName & "'"

'Declare recordset object...
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset&quot

objRS.Open strSQL, objConn

'Save First + Last name
Dim strName
strName = objRS("FirstName&quot

& " " & objRS("LastName&quot

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

<form name=Forum method=post action="Forum_Add_DB.asp?Name=<%=strName%>>

<table border=0>

<tr>
<td>
<font face=arial size=-2>
................................
</td>
</tr>

<tr>
<td>
<font face="Arial" size=-2>
<b>
<%=strName>
</td>
</tr>

<tr>
<td>
<font face="Arial" size=-2>
<%=Day(Date()) & " " & MonthName(Month(Date())) & " " & Year(Date())%>
</td>
</tr>

<tr>
<td>
<font face=arial size=-2>
.............................................. </td>
</tr>

<tr>
<td>
<br>
<textarea rows=10 cols=60 name=NewMsg class=BasicTextBox></textarea>
</td>
<td>
<font face=arial size=-2 color=#000000>

</td>
<tr>
<td align=right>
<input type=submit value=" OK " class=BasicButton>
</td>
</tr>

</table>
</form>

</html>

---end code---

the table is a simple access table with name, lastname etc. I'm not sure on what you mean by "the script of the table"...

FesterSXS · Jul 19, 2002

I think you may need to set your cursor type to adOpenStatic...I always used to get objRS.RecordCount = -1 unless I did this.

Also - what happens when you try...

Code:

objRS.Open strSQL, sConnection

instead of your existing code?
Tony

http://www.phoenix-paints.co.uk/portfolio

adamsoderqvist · Jul 19, 2002

nope... won't work.

tried setting the cursor to the suggested one, but with no effect, and the altered sConnection didn't either...

there's obvioulsy a reading error from the DB - but what!

tricky one...

ehsant · Jul 22, 2002

Hi there ...
I just worked on your code, but it works on my computer.
and one question ...
did you copy the code from your ASP file and paste it
here or you typed it ?
because there where tow errors that your code had and by
correcting them there were no problem ...
I don't know why it doesn't work on your computer.
E.T.

adamsoderqvist · Jul 23, 2002

ok, no I copied/pasted it, but I removed some code that was useless for the example in itself (some layout stuff etc...).

ehsant · Jul 23, 2002

Hi there ...
OK ... but in this code there where some problems ...
In the Line :
<form name=Forum method=post action="Forum_Add_DB.asp?Name=<%=strName%>>
you did'n close the " for action ...
also in the line :
<tr>
<td>
<font face="Arial" size=-2>
<b>
>>> <%=strName> <<<
</td>
</tr>

you didn't close the ASP tag correctly ...
try these ...
maybo your problem solves ...
E.T.

adamsoderqvist · Jul 24, 2002

nope, wasn't it. (it was one of those "edits" I mentioned..)

could it be differences in OS, or PWS vs. IIS etc..., since you got it working...? I use NT 4 with PWS on my mashine!

ehsant · Jul 28, 2002

Hi There ...
I don't think so ...
but, bytheway, if you have NT4 server, why don'y you
use IIS 4 or 4.5 ?
and you said that when you uses this connection string
it returns no recordcout !!!
what do you mean by NO RECORD COUNT ?
TNX.
E.T.

adamsoderqvist · Jul 28, 2002

No I run "NT4 Workstation"!

When I try to print the record count it usually prints "-1" (as is the case with the "working" connection string), but with this string it doesn't print anything AT ALL!

If I use this:

Code:

Response.Write &quot;Record count: &quot; & objRS.RecordCount

...it returns:

Code:

Record count:

- and then NOTHING!

ehsant · Jul 30, 2002

Hi There ...
have you ever try GetRows ?
I thing it may work ...
It's simple ...
GetRows is a method of the RecordSet ...
it stores all of the records in a recordset in an Array.
and then UBound of that array is your recordcount.
it's like this ...
objRS.Open
MyArray = objRS.GetRows ()
objRS.Close ()
It return a 2Dim array that cols are your fields and rows
are your records.
try it ...
hope it works...
TNX
E.T.

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 string works only 1/2...!

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor