SELECT statement returning 0 Rows Incorrectly

[mechwarrior3]

Hello everyone,

I have an odd situation: I have a data table in a database with one column and only one row. This table is supposed to stay at the size of one row and one column. When I use the below SELECT statement on this table, I get no rows returned. Why?

"SELECT * FROM tbl_HCI_Missing_Lot_Count"

All suggestions are greatly appreciated.

 

[vis781]

Well, there's nothing wrong with that select statement. So i reckon we are gonna need to see a bit more to be able to say for sure.

 

[mechwarrior3]

That's what I figured. ::sighs::

Here is the part of the sub routine that deals with this datatable in the database:

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] lngDummy [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Long[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#008000]' A dummy number only used to later be converted into a string
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection(MYACCESSCONN)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtMixLotNum.Text = "000000-000N"
AccessAdapter.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT * FROM tbl_HCI_Missing_Lot_Count", AccessConn)
[/SIZE][SIZE=2][COLOR=#008000]' There should only be one row returned from the .Fill function.
[/COLOR][/SIZE][SIZE=2]AccessAdapter.Fill(AccessDataSet, "tbl_HCI_Missing_Lot_Count")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] AccessDataRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = AccessDataSet.Tables("tbl_HCI_Missing_Lot_Count").Rows(0)
lngDummy = IsLongNull(AccessDataSet.Tables("tbl_HCI_Missing_Lot_Count").Rows(0).Item("intLostLotNum"), 0)

IsLongNull is an overloaded function that I created. If the field has a null value in it, the function returns the second argument which is a zero. If the field value is not null, it returns the first argument: the field value.
MYACCESSCONN is the connection string to the Access database. That is a valid connection string. My program uses it for INSERT and UPDATE commands at other points in the program.

The error I get is attached. It happens at the lngDummy assignment line.


[/SIZE]

 

[mechwarrior3]

Resolved!

This issue has been resolved. Initially, my table did not actually possess any rows. When I looked at the table, I only saw the automatically created row with the default value in the table. Once I manually created a new row, everything was fine.

 

[cjard]

I was going to say: "The source table in the database contains no rows. Go check it with an SQL query tool"

point of note here.. if you were working with typed datasets then your IsNull methods would be made for you.. like this:

Dim AccessDataSet as New MyDataSet.AccessDataTable

DirectCast(AccessDataSet.tbl_HCI_Missing_Lot_Count.Rows(0), MyDataSet.AccessDataTableRow).IsintLostLotNumNull()


Alternatively, this:
DirectCast(AccessDataSet.tbl_HCI_Missing_Lot_Count.Rows(0), MyDataSet.AccessDataTableRow).intLostLotNum

Returns you a Nullable(Of Long) which you can call GetValueOrDefault() on:

lngDummy = DirectCast(AccessDataSet.tbl_HCI_Missing_Lot_Count.Rows(0), MyDataSet.AccessDataTableRow).intLostLotNum.GetValueOrDefault(0)



mmmm.. strong typing is NICE. most my vb6-only friends balk at it, but im trying to upgrade them to the 20th century..

Side point.. one of them was calling the oracle function TO_CHAR(myNumberStoredAsString, '0.00') the other day:

"matt, why doesnt this work?"
"what?"

"this oracle function says it doesnt know which overload to call"
"that means that it could reasonably call more than one function called to_char and it doesnt know which one to call"

"why not?"
"because its not a mind reader? which one do you want it to call"

"i'm trying to format a number"
"are you passing a number in?"

"yes"
"a number data type?"

"no, it's a string"
"that holds a number?"

"yes"
"so you want oracle to inspect the string, see that it holds only numerical characters, and call the number one for you?"

"yes"
"how can you reasonably expect it to do that? convert it to a number first!"

"why should I have to? why cant oracle do as i say?"
"it doesnt know which one to call.. you can tell it which to call based on the name of the parameter but you shouldnt have to have oracle do the number conversion implicitly based on its guess"

"why not?"
"because a computer cannot think any more than a submarine can swim. convert that string to a number if youre so sure it contains only numbers"

"how?"
"use to_number"

"instead of to_char?"
"no, aswell as tochar.. wrap the argument to it is converted to a number before a char"

"why?"
"because youre trying to format a number, pass in a number"

"why cant oracle do that for me?"
"because it cant be sure that string holds a number"

"why cant it look?"
"because it has to compile a route and the route might change if one day there isnt a number in there"

"so i have to go to all the hassle of writing to_number to convert my string to a number, only to convert it back to a string?"
"yes. it's what oracle would do implicitly anyway if it was sure of what to do.. it isnt, so do it explicitly, and shut your moaning"

"thats retarded"

oh, we have some amusing debates, i tell you. i'll drag him into the typed world yet, kicking and screaming if needs be (i'll do the kicking )

 

[mechwarrior3]

First, that's an interesting way of dealing with the datatype. I hadn't thought of that. Cool idea.


Second, that story made me laugh. That's hysterical. You better get your friend up to date. Who knows where else your friend might be thinking the computer will understand implicitly what to do.