| Author |
Topic |
|
GordonG
Starting Member
7 Posts |
 Posted - 2005-08-17 : 17:26:21
|
| I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.Any suggestions?Thanks!GordonG |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-17 : 17:57:51
|
| What happens when you run the command inside Query Analyzer?Tara |
 |
|
|
GordonG
Starting Member
7 Posts |
Posted - 2005-08-18 : 09:41:48
|
| Hi Tara.I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned. Weird? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-18 : 12:19:28
|
| So this is SQL Server 2005? If so, you may want to post your question in that forum as for the most part these other forums are for 2000 and lower. Most of us in the non-2005 forums don't have much experience on 2005, so we often can't help with these types of problems. On the other hand, people who visit the 2005 forum might have experience on 2005, so they may be able to better lend a hand.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-18 : 12:23:11
|
| Is it just me, or is this scary? [I suppose I should sit-tight until the cause is found, but.]Kristen |
|
|
|
GordonG
Starting Member
7 Posts |
Posted - 2005-08-18 : 12:41:07
|
| Thanks Tara. I am familiar with DB2 but I am a newbee at VB.NET and MSSQL so I thought this would be the appropriate forum. I will post as well on 2005 forum. :-)GordonG |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:51:16
|
| Gordon, this is such a weird problem you've described that I just have to ask the obvious question: Are you really, really sure that the records are not found in the SELECT * without any ordering? What I mean is, could it be that they are scattered throughout the resultset and just not in the section where you expect them to be?Other thoughts: Does the table have a clustered index defined on it? That would affect the order of the resultset without an ORDER BY clause. What if you define a clustered index on Employee, do they then appear where you expect them?---------------------------EmeraldCityDomains.com |
|
|
|
peterlemonjello
Yak Posting Veteran
53 Posts |
Posted - 2005-08-18 : 17:06:26
|
| I'm a java guy not .NET but could .NET be caching the resuls and hitting the cache instead of querying the database? Adding the order by may trigger .NET to refresh the cache by querying the db. I've seen this before in java. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 19:02:59
|
| Peter, yes .NET can hit a cache, but his later comment that he used Express Manager (SQL Express's client tool) and got those results should have overcome that. Express Manager should be hitting the database each time for the results.---------------------------EmeraldCityDomains.com |
|
|
|
GordonG
Starting Member
7 Posts |
Posted - 2005-08-19 : 11:01:29
|
| Hello all.To answer some of the questions. Because it seems so.... wierd I am very careful to make sure the result set did not include the added records. There are no clustered indexes. Access 2003 upsize did not create any. The database/Tables were created by the upsize command. I even tried with the sql2000 query analyzer on a different machine, which can connect to sqlexpress, same results. For now I have to accept that as a work-a-round I will always use ORDER BY. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-19 : 12:52:14
|
| GordonG,How many rows are in this table?Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-19 : 14:16:07
|
| What happens if you create a new table natively in SQL Server and insert all the rows from this table into it? Does the new table behave the same way?---------------------------EmeraldCityDomains.com |
|
|
|
GordonG
Starting Member
7 Posts |
Posted - 2005-08-22 : 14:55:47
|
| Hello all.I think I found the problem. When I orginally "upsized" the access data base to sqlexpress, one of the other tables, "EMPLOYEES", was created as "EMPLOY" which I simply renamed it as "EMPLOYEES" and moved on. This weekend I decided to delete the orginal database and re-create it again, including the troubling timecards. This time the EMPLOYEE table was correctly created and when I tested for the problem of selecting all records in timecards with or without the ORDER clause, the record counts were correct. I then added a couple of 'test' time cards and tried the same selects again and the record counts were correct including the added records. I guess during the original upsize, something went wrong, although I have no idea what it was.Thank you all for your input.Gordon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-22 : 15:41:09
|
| I'm relieved to hear it was somthing like that. Thanks for letting us knowKristen |
|
|
|
|
Select * from table does not select all records