Pulling data from a specific row in dataRow using Like

[Patty05]

I am trying to pull values from a specific row with a select stmt in a database so I can set some variables. However, my code is not grabbing the value and I am getting the error "Index was outside the bounds of the array". I am not sure what is wrong.

conn = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; data source=c:\PR\Payroll.mdb")
Try
da = New OleDbDataAdapter("SELECT * FROM Hours", conn)
myDataTable = New DataTable
da.Fill(myDataSet, "Hours")
Catch ex As Exception
Console.WriteLine("Error Opening {0}", conn.DataSource)
EndTry
myCommand = New OleDbCommandBuilder(da)
 
'covert Today() to string and parse just the date to enter in database
Dim dateStr AsString = Today().ToString
Try
dateStr = FormatDateTime(dateStr, DateFormat.ShortDate)
Catch exp As Exception
MsgBox("Date not valid date format")
EndTry

 
Dim foundRows() As Data.DataRow
 
foundRows = myDataSet.Tables("Hours").Select("TodayD Like '" & dateStr & "' AND EmpNum Like '" & globalEmpNum & "'")
 
'ERROR IS HERE
globalTodayDate = foundRows(0).Item("TodayD") 'text type from database
globalTimeIn = foundRows(0).Item("TimeIn") 'date type from database
 
EndTry

Can someone please tell me what I am doing incorrectly? Is my Select stmt wrong? Is there a better way? Thanks

 

[cjard]

First, open the file in notepad to ascertain whether it is the file not being written with leading zeroes, or whether it is Excel reading it and parsing it as a number and showing you a number

Part of this related to what I noted before, about all the CSV file writers I saw on the internet being of poor logic and ill thought out - not being able to cope with commas in fields for example. You will frequently come across situations where the work of others is ill thought out and lacking in attention to detail - though it trips you up, the learning experience is a valuable one.

I have ascertained from a friend that:


If your line looked like this:
1,2,3,4,5

Excel would show it like this:
1|2|3|4|5

-
If your line looked like this:
001,002,003,004,005

Excel would show it like this:
1|2|3|4|5

-
If your line looked like this:
"001","002","003","004","005"

Excel would show it like this:
001|002|003|004|005


--

Using this information as a map of a route through the problem domain, work out where you are (notepad), where you want to be (brain) and how to get there

 

[Patty05]

I'm sorry... I am trying to use my brain. I'm just unfamilar with all the dot stuff.

Notepad shows "001" and Excel shows 1. I've done some research on csv files and leading zeros is in fact a known problem when viewing them in Excel. This is what I have learned...

"Excel will always remove leading zeros from fields before displaying them. It will also always remove leading spaces. It insists on helping you, even if removing these leading zeros will, for example, cause your numerically keyed records to sort out of order. "

So, a solution is when the csv will be viewed in Excel only is to double quote each field with leading zeros or spaces and place an equals sign (=) directly ahead of the first quote, like this ="000123", ="000456", etc. Excel accepts this as a formula and will then view properly the quoted string.

This is a little quirk that is suppose to work for Excel, but may be interpreted as corrupted data in other applications.

I haven't tried this yet as I have been bombarded at work with additional projects but my plan is to edit this bit of code by putting an (=) in front of ALL string values.

[COLOR=#0000ff]If[SIZE=2]TypeOf[/SIZE][/COLOR][SIZE=2] value [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]sw.Write("=")
[/COLOR][/SIZE][SIZE=2]sw.Write(""""c) [/SIZE][SIZE=2][COLOR=#008000]' thats four double quotes and a c
[/COLOR][/SIZE][SIZE=2]sw.Write(value)
sw.Write(""""c) [/SIZE][SIZE=2][COLOR=#008000]' thats four double quotes and a c
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]sw.Write(value)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If

[/COLOR][/SIZE]

I will also put some sort of a check box on my form to ask the user if they are not going to view with Excel, then provide code without the (=) to run.

I'm not sure of any other way to do this. I'm crossing my fingers that this will work! Let me know if I am way off base!

 

[cjard]

Apologies for delivering you advice from a friend (whom incidentally I regard to be an expert in Excel but maybe not CSV files) that I didnt verify myself first.

It sounds like you have had a good learning experience on how to research niggling problems like this!
Note:

you should try to only perform your trick for items with leading zeroes - it forces them to text which is left aligned.. number columns noramlly look best right aligned so choose which columns you add an = to carefully

 

[cjard]

So, a solution is when the csv will be viewed in Excel only is to double quote each field with leading zeros or spaces and place an equals sign (=) directly ahead of the first quote, like this ="000123", ="000456", etc. Excel accepts this as a formula and will then view properly the quoted string.

Your code will doublequote everything that is a string which may not be what you want. You may wish to doublequote only items with leading zeroes as I noted (great minds think alike, foold seldom differ etc)

You may find this to be a better test than merely testing if the type is a string.. I'm not sure:

If TypeOf x Is String And x.StartsWith("0") And (Not x.StartsWith("0.")) Then



Suck it and see!