| Author |
 Topic  |
|
|
Tiggerz
Starting Member
45 Posts |
 Posted - 01 December 2004 : 15:23:47
|
In my dodgy attempt at learning how to do things in asp (so I can merge the forum into my website) - i've tried my hand at accessing my sqlserver 2k db.
The table I am accessing contains 4 records each with 2 fields - an id and a description.
The problem is that I only get 2 of the 4 rows back and after a bit of experimentation, still cant get the other two.
This is vb script code I am using.
' Connect to the database
Dim dbConn
Dim dbRS
Dim dbCmd
Dim sqlString
Dim aString
dbConn = Server.CreateObject( "ADODB.Connection" )
dbRS = Server.CreateObject( "ADODB.Recordset" )
dbCmd = Server.CreateObject( "ADODB.Command" )
dbConn.ConnectionTimeout=60
dbConn.Open( "DSN=AndyDsn; uid=andyw; pwd=;" )
dbCmd.CommandText = "SELECT * FROM MenuTitle"
dbCmd.ActiveConnection = dbConn
dbRS.CursorLocation = 3
dbRS.Open( dbCmd, ,1,3 )
Response.Write( "Number of records returned " & dbRS.fields.count & "<br>" )
aString = "<TABLE BORDER=1><TR><TH>Title</TH></TR>"
Response.Write( aString )
Dim i
FOR i = 0 to dbRS.fields.count-1
aString = "<TR><TD>" & dbRS( "MenuTitleDesc" ).Value & "</TD></TR>"
Response.Write( aString )
dbRS.MoveNext
Next
aString = "</TABLE>"
Response.Write( aString )
' Close the connection & record set
dbRS.Close
dbRS = Nothing
dbConn.Close
dbConn = Nothing
The other question, I notice there is an include some examples have that defines constants that start with adXXXX. Is there some requirement for setting this up in visual studio.net (2k3) as it just throws an error on me. |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 02 December 2004 : 05:23:02
|
what is this 'cursorlocation' stuff? if you are starting at row 3 of 4, you will only get rows 3 and 4 back... why?
Now, the other q re constants - if you can provide code snippet samples it will be easier, but I'm guessing you're finding the differences between vbscript, vb, and vb.net - the .net version is rather dissimilar from the other two, so I'd suggest getting a good book/go on a course/go to some free MSDN events from M'oft just to get a feel for *how* different it is! If these constants were previously defined in the com objects, they will have changed name for .NET (eg .NET has a constant in the colors namespace just called 'Red' to represent a certain RGB value.
If the constants are defined in the code, remember .NET requires 'strong typing' - this means (in a nutshell) you can't say
dim MyConstant
MyConstant = "somewords"
you have to say
dim MyConstant as string
MyConstant = "somewords"[/
.NET really is worth learning, just take your time and treat it as a new language - although French and English look similar and use similar words and characters doesn't mean if you can read one you can read the other - same with vbscript and vb.net
hth |
 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 02 December 2004 : 20:27:45
|
Try
' Connect to the database
Dim dbConn
Dim dbRS
Dim dbCmd
Dim sqlString
Dim aString
dbConn = Server.CreateObject( "ADODB.Connection" )
dbRS = Server.CreateObject( "ADODB.Recordset" )
dbCmd = Server.CreateObject( "ADODB.Command" )
dbConn.ConnectionTimeout=60
dbConn.Open( "DSN=AndyDsn; uid=andyw; pwd=;" )
dbCmd.CommandText = "SELECT * FROM MenuTitle"
dbCmd.ActiveConnection = dbConn
dbRS.CursorLocation = 3
dbRS.Open( dbCmd, ,3,3 )
Response.Write( "Number of records returned " & dbRS.fields.count & "<br>" )
aString = "<TABLE BORDER=1><TR><TH>Title</TH></TR>"
Response.Write( aString )
Do while not dbRS.EOF
aString = "<TR><TD>" & dbRS( "MenuTitleDesc" ).Value & "</TD></TR>"
Response.Write( aString )
dbRS.MoveNext
Loop
aString = "</TABLE>"
Response.Write( aString )
' Close the connection & record set
dbRS.Close
dbRS = Nothing
dbConn.Close
dbConn = Nothing
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Tiggerz
Starting Member
45 Posts |
Posted - 04 December 2004 : 02:36:57
|
I take it the 1 changed to a 3. What are those numbers anyhow ?
Still only the first 2 records retreived. dbRS.fields.count is only reporting 2 records, but there is certainly 4 records in the table.
Just out of interest when I change the select * from to a select menutitledesc from (which is the name of the column) I only get one row back.. Changing it back to a * gives me 2 rows.
This is a straight out install of sqlserver 2k sp3 on an xp sp2 machine. I hope its not one of those bizare MS screwups again.
NB: Just tried reading a couple of different tables and get all the records back. Is there some cache or index that might be buggered on that table or something. |
Edited by - Tiggerz on 04 December 2004 03:03:16 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 04 December 2004 : 21:25:25
|
quote:
I hope its not one of those bizare MS screwups again.
Pretty unlikely.
quote:
Is there some cache or index that might be buggered on that table or something.
Unlikely as well, it's probably code related, have you even created indexes on the table ?.
The numbers are operands to the command to specify different options (usually you'd use the ADODB Constant instead of the number so its easier to read/maintain). You should be able to find tons of info in MSDN if you lookup each particular command. |
Kiwihosting.Net - The Forum Hosting Specialists
|
Edited by - Gremlin on 04 December 2004 21:26:00 |
|
|
|
Tiggerz
Starting Member
45 Posts |
Posted - 04 December 2004 : 21:57:11
|
I solved it in the end. it is a bug in the version of ado.net I had, the fix was to add another column in the table. Downloading the latest platform sdk now which, I am told contains a fix.
The problem was it didnt like a table with two columns, the first being int/pk and the second being char 255. For some reason the second field got treated as something different (some random type). Adding the third column, seem'd to fix it. |
Edited by - Tiggerz on 04 December 2004 22:01:01 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 05 December 2004 : 18:33:43
|
| What version of ADO did you have ? a Beta? |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
|
| |
Topic |
|
Topic Locked
