Strange SQL Compact Problem - Vista
-
On an XP machine it is returning 5 columns and on a Vista machine it is 4 columns even though the database has 5 columns.
-
Insufficient code. We have no idea what the SQL looks like that is returning these columns. But, based on looking at similar questions, I'd be willing to guess that it starts out something like "SELECT * FROM". Bad idea...
A guide to posting questions on CodeProject[^]
Dave KreskowiakYou may be right Dave it might be a bad idea from a security aspect but this code has to access two versions of a database one has four columns and the later has 5 columns. Since I don't know which it is going to be run on I have to use SELECT * in this instance. Despite that, why does it return one value on XP and a different one on Vista?
-
You may be right Dave it might be a bad idea from a security aspect but this code has to access two versions of a database one has four columns and the later has 5 columns. Since I don't know which it is going to be run on I have to use SELECT * in this instance. Despite that, why does it return one value on XP and a different one on Vista?
maybe because it is accessing a different database? are you using Visual Studio, have you declared the database to Visual, could it make a local copy and run your app on that one? you can always list the field names instead of using a * in your SELECT statement; if the field does not exist, you'll get a clear error message. And whatever you do, if the field isn't there, you won't get its content. :)
Luc Pattyn [Forum Guidelines] [My Articles] [My CP bug tracking] Nil Volentibus Arduum
Season's Greetings to all CPians.
-
You may be right Dave it might be a bad idea from a security aspect but this code has to access two versions of a database one has four columns and the later has 5 columns. Since I don't know which it is going to be run on I have to use SELECT * in this instance. Despite that, why does it return one value on XP and a different one on Vista?
TheComputerMan wrote:
it might be a bad idea from a security aspect
No, it's a bad idea from an SQL standpoint. Your code can't tell which columns it's getting back until it examines the columns themselves. Right now, your code is ASSUMING it's getting back 5 columns when, in fact, it's only getting 4. There is no difference between the XP and Vista versions, other than your code is obviously hitting the wrong database. If you change the schema of the database, you also change the number of columns you get back and also the order in which the columns arrive in your code. Since your code is assuming it's getting back certain columns in certain index positions, just making one change to the database may alter that order and totally screw up your code. You always specify the columns you want in the SQL and you access those columns in the returned dataset/reader/whatever with named indexes (using strings, not numbers) so you know your always getting the correct columns, no matter which order they get returned to you.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
On an XP machine it is returning 5 columns and on a Vista machine it is 4 columns even though the database has 5 columns.
TheComputerMan wrote:
On an XP machine it is returning 5 columns and on a Vista machine it is 4 columns even though the database has 5 columns.
Are you sure it's exactly the same database? This does not sound very likely.
I must get a clever new signature for 2011.
-
TheComputerMan wrote:
it might be a bad idea from a security aspect
No, it's a bad idea from an SQL standpoint. Your code can't tell which columns it's getting back until it examines the columns themselves. Right now, your code is ASSUMING it's getting back 5 columns when, in fact, it's only getting 4. There is no difference between the XP and Vista versions, other than your code is obviously hitting the wrong database. If you change the schema of the database, you also change the number of columns you get back and also the order in which the columns arrive in your code. Since your code is assuming it's getting back certain columns in certain index positions, just making one change to the database may alter that order and totally screw up your code. You always specify the columns you want in the SQL and you access those columns in the returned dataset/reader/whatever with named indexes (using strings, not numbers) so you know your always getting the correct columns, no matter which order they get returned to you.
A guide to posting questions on CodeProject[^]
Dave KreskowiakActually Dave you are wrong. It is not hitting the wrong database because there is only one on the machine and that has 5 columns in the table. The reason the program has the facility to read a 4 column table is because there is another user out there that is using the 4 column version of the database table. As I stated originally it is not a problem of change in order the program cannot find the fifth column (4) - says it does not exist. I am fully aware of how to access the columns using strings not numbers and normally do - that is not an issue here - but as you should be aware 'item' takes an integer argument. I will agree that the code is not ideal, but that does not detract from the fact that this is not the wrong database - it has 5 columns not 4, and it works on XP but not on Vista. When it was transferred to XP on the machine in New Zealand it was exactly the same setup program that was used to install it on the Vista machine. Same code, same database, same everything. I will however replace the integer values and send it out again to see if it works. I will let you know the result.
-
TheComputerMan wrote:
On an XP machine it is returning 5 columns and on a Vista machine it is 4 columns even though the database has 5 columns.
Are you sure it's exactly the same database? This does not sound very likely.
I must get a clever new signature for 2011.
I can assure you it is!
-
maybe because it is accessing a different database? are you using Visual Studio, have you declared the database to Visual, could it make a local copy and run your app on that one? you can always list the field names instead of using a * in your SELECT statement; if the field does not exist, you'll get a clear error message. And whatever you do, if the field isn't there, you won't get its content. :)
Luc Pattyn [Forum Guidelines] [My Articles] [My CP bug tracking] Nil Volentibus Arduum
Season's Greetings to all CPians.
Hi Luc, the problem here is that I don't want an error message, i.e. I don't want it to fail. It has to determine if the table has 4 or 5 columns and act accordingly. It is not a different database by the way. These are .sdf files and there is only one on the machine. I have got the user to check that.
-
I can assure you it is!
TheComputerMan wrote:
I can assure you it is [the same]!
TheComputerMan wrote (in a message below):
but this code has to access two versions of a database one has four columns and the later has 5 columns.
Either they are the same or they are different, they cannot be both.
I must get a clever new signature for 2011.
-
TheComputerMan wrote:
I can assure you it is [the same]!
TheComputerMan wrote (in a message below):
but this code has to access two versions of a database one has four columns and the later has 5 columns.
Either they are the same or they are different, they cannot be both.
I must get a clever new signature for 2011.
Sorry I was responding to your line ----Are you sure it's exactly the same database? This does not sound very likely.---- What I was meaning was that the program and database are exactly the same on each machine but runs on one and not on the other. Incidentally, if stepping through a series of data rows
For Each drowLat As DataRow In dtblLoc.RowsGot any ideas on how to go back one row? This is one of the reasons I was not using strings as column names because I have to get the previous row and you cannot use a field name in this type of statement.
strReturn = dtblLoc.Rows(intLastLatIndex).Item(PLACE_COL).ToString & "|" & dtblLoc.Rows(intLastLatIndex).Item(PROV_COL).ToString
If I can find a way round that I can get rid of the integer indexes.
-
Actually Dave you are wrong. It is not hitting the wrong database because there is only one on the machine and that has 5 columns in the table. The reason the program has the facility to read a 4 column table is because there is another user out there that is using the 4 column version of the database table. As I stated originally it is not a problem of change in order the program cannot find the fifth column (4) - says it does not exist. I am fully aware of how to access the columns using strings not numbers and normally do - that is not an issue here - but as you should be aware 'item' takes an integer argument. I will agree that the code is not ideal, but that does not detract from the fact that this is not the wrong database - it has 5 columns not 4, and it works on XP but not on Vista. When it was transferred to XP on the machine in New Zealand it was exactly the same setup program that was used to install it on the Vista machine. Same code, same database, same everything. I will however replace the integer values and send it out again to see if it works. I will let you know the result.
Since we can't see the code, we're just guessing. There are other ways to do what you want in SQL without using "SELECT *". ID'ing the database before hand and having the data layer switch queries based on that would be a better route to go, though it adds complexity.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Since we can't see the code, we're just guessing. There are other ways to do what you want in SQL without using "SELECT *". ID'ing the database before hand and having the data layer switch queries based on that would be a better route to go, though it adds complexity.
A guide to posting questions on CodeProject[^]
Dave KreskowiakHi Dave, I just finished re-writing the code to remove any 4 column support completely and using strings to access the columns as it should be and drawing down the column names rather than using * It causes me a slight problem when going back a couple of rows but I have a work around now. I shall send this out to New Zealand now and wait an see what the result is! I will let you know - either way. Thanks. David
-
Sorry I was responding to your line ----Are you sure it's exactly the same database? This does not sound very likely.---- What I was meaning was that the program and database are exactly the same on each machine but runs on one and not on the other. Incidentally, if stepping through a series of data rows
For Each drowLat As DataRow In dtblLoc.RowsGot any ideas on how to go back one row? This is one of the reasons I was not using strings as column names because I have to get the previous row and you cannot use a field name in this type of statement.
strReturn = dtblLoc.Rows(intLastLatIndex).Item(PLACE_COL).ToString & "|" & dtblLoc.Rows(intLastLatIndex).Item(PROV_COL).ToString
If I can find a way round that I can get rid of the integer indexes.
TheComputerMan wrote:
What I was meaning was that the program and database are exactly the same on each machine but runs on one and not on the other.
Well this still conflicts with your message below where you say that one system has four columns and the other has five; which is it?
TheComputerMan wrote:
Got any ideas on how to go back one row?
I don't think you can. The whole point of a
For Eachloop is that it traverses a complete collection exactly once. If you need to have a more random traversal then you will need to use row indices, in order to move back to a previous point.I must get a clever new signature for 2011.
-
TheComputerMan wrote:
What I was meaning was that the program and database are exactly the same on each machine but runs on one and not on the other.
Well this still conflicts with your message below where you say that one system has four columns and the other has five; which is it?
TheComputerMan wrote:
Got any ideas on how to go back one row?
I don't think you can. The whole point of a
For Eachloop is that it traverses a complete collection exactly once. If you need to have a more random traversal then you will need to use row indices, in order to move back to a previous point.I must get a clever new signature for 2011.
Yes I suspected as much. Since it is only ever one row back on any occasion I have decided to simply hold the previous row for reference as a floating datarow that gets updated at the end of each loop. Seems to work OK and I have now go rid of columns being referenced by integer. Thanks for your input. Waiting results from NZ.
-
Since we can't see the code, we're just guessing. There are other ways to do what you want in SQL without using "SELECT *". ID'ing the database before hand and having the data layer switch queries based on that would be a better route to go, though it adds complexity.
A guide to posting questions on CodeProject[^]
Dave KreskowiakI finally got a reply from New Zealand. On his Vista machine he has .NET 2.0.50727.4016 and mine is .NET 2.0.50727.3053 He only has the one database, which is the new 5 column one and he has run the latest version of the code which has no SELECT * and all data row elements are explicitly referred to by name. When it runs it just errors its way through the file so basically it is not seeing this 5th field. Would the difference in ,NET numbers be significant?
-
I finally got a reply from New Zealand. On his Vista machine he has .NET 2.0.50727.4016 and mine is .NET 2.0.50727.3053 He only has the one database, which is the new 5 column one and he has run the latest version of the code which has no SELECT * and all data row elements are explicitly referred to by name. When it runs it just errors its way through the file so basically it is not seeing this 5th field. Would the difference in ,NET numbers be significant?
Nope. You've both got .NET 2.0 SP2. The problem seems to be that you're assuming the database has this 5th column, when it doesn't. Or, the code is looking at a different database than you think it is.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Nope. You've both got .NET 2.0 SP2. The problem seems to be that you're assuming the database has this 5th column, when it doesn't. Or, the code is looking at a different database than you think it is.
A guide to posting questions on CodeProject[^]
Dave KreskowiakThanks Dave Yes I am beginning to wonder. I have to go on what he tells me, but of course I cannot confirm. I will call this a day here as I am sure the problem lies with the user. Many thank for your assistance.