asp.net 2.0, sql2005, double dataset table rows

I'm stumped. When using the classic asp page, the table produced on the web has 4 rows. But, when using the asp.net 2.0 side against the same stored proc, I get double the rows - 8. However, when I pull from the sql table directly there are only 4 rows. Any ideas? I've looked at this for too many hours...

da = New Data.SqlClient.SqlDataAdapter("pTblTempDisplayInsert2 0,'" & coList & "','" & tblList & "','" & varTblNameT & "'", cn)
da.Fill(ds, "myTables")
For Each drow As System.Data.DataRow In ds.Tables("myTables").Rows
varTxt &= drow.Item(
"titleID") & "
"

Next
Gives me:
1852
1850
2055
2053
1852
1850
2055
2053

If I run the last line of the stored procedure with the variable table name filled in:

SELECT d.titleID FROM ##varTblName d LEFT JOIN xtblTitles t ON d.tblID=t.tblID LEFT JOIN tblStatTitles s ON d.titleID=s.titleID LEFT JOIN xtblURL u ON u.urlID=t.urlID ORDER BY d.tblID, d.[year], d.lineOrder, d.countyID

I get:
1852
1850
2055
2053

If I do a simple select * from ##varTableName, I get four rows....

Thanks,
Janet



Comments

  • Well, found a fix, but I don't understand it.

    I added ds=new dataset right before filling the table with the adapter.

    Just to be safe, I did a search on all the code for "writeTables": the table within the dataset ds. This is the only place that it is used. So, even if the dataset is open from previous placeholder events on the page, this table within the dataset is only used for the first time with the btn click event, so why the doubling?

    I'd appreciate comments....

  • : Well, found a fix, but I don't understand it.
    :
    : I added ds=new dataset right before filling the table with the adapter.
    :
    : Just to be safe, I did a search on all the code for "writeTables": the table within the dataset ds. This is the only place that it is used. So, even if the dataset is open from previous placeholder events on the page, this table within the dataset is only used for the first time with the btn click event, so why the doubling?
    :
    : I'd appreciate comments....
    :
    :
    2.0 is a mysterious beast. So is SQL 2005, IMO.
    Did do this the very first time you loaded and ran it?
  • Hiya James,
    Well, I'm not sure what you mean, but the first time through when I didn't re-instantiate with "new", it kept doubling the rows - even the very first time I called the page in a 24-hour period. I know the answer is that I've probably confused the hell out of it with nested recordsets and loops and displaying items on the page only via clicks and placeholders? But, I did a search/find on the page, and this is the only place I've called a table that name?

    I'm currently working with sql2005 in 80 mode, not 90 so that I can gradually convert stuff. If you work in 90, know of any gotchas?

    Thanks for replying,
    Janet
  • : Hiya James,
    : Well, I'm not sure what you mean, but the first time through when I didn't re-instantiate with "new", it kept doubling the rows - even the very first time I called the page in a 24-hour period. I know the answer is that I've probably confused the hell out of it with nested recordsets and loops and displaying items on the page only via clicks and placeholders? But, I did a search/find on the page, and this is the only place I've called a table that name?
    :
    : I'm currently working with sql2005 in 80 mode, not 90 so that I can gradually convert stuff. If you work in 90, know of any gotchas?
    :
    : Thanks for replying,
    : Janet
    :
    Hmm...
    I don't know of any specific gotchas with version 9 other than getting used to how they re arranged things like the Query Analyzer. - Seems to me the basic engine is the same, but they changed the bells and whistles.

    I've encountered the same kind of thing you described about the dataset before, but I don't think it was caused by SQL Server, I think it was ADO. Same thing, you run the query directly and it returns the correct data. Thus the right stuff is getting handed to ADO.

    My theory was that somehow, the dataset was being created on the server somehow when the code first loads there as if MS is trying to run some caching scheme to speed things up. (??) And then when the code runs it was adding the data to itself. But when you said "New", when it runs, it reinitialized itself. (even though it shouldn't have done so in the first place)

    Just a theory.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories