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]

Oh dear.

Such are the consequences of not filling in accurate information in your vbdotnetforums.com profile. Underneath your name it says you use 2005. This means I can gear all my data access advice towards 2005, and it is very, very different to 2003 in the sample project I posted.

Please correct your profile here to reflect that your development base is .NET 1.1 (2003).

I can only be of limited use now, as I work intensely with the data access facilities introduced in 2005. Your 2003 has no such thing as tableadapters, but you can probably still design a set of dataadapters to behave like tableadapters (a TA is merely a DA that is associated with a specific table, and manages its own connections to/from the db)

You can, of course, download the free version of VB 2005, called Express, if you want to see the example project. You can also open most of the documents in a text editor and copy blocks of code out, such as sql command and parameters setup. A free version of sql server is available too. The free VBB2005 supports only these two databases, but if you plan to use Access for the time being, you may consider trying the VB2005

 

[vis781]

You are getting that problem because of the following bit....

Msgbox(ex)

should be..

Messagebox.Show(ex.Message)

It won't cure your exception being thrown, but it will mean that you get a nice descriptive error message rather than that unhandled exception business. (BTW MessageBox.Show is the new .Net way of doing things.)

 

[Patty05]

Thank you cjard and vis781.

First of all, I discovered the problem with the sql statement. I needed an aggregate function to make it work.

modbcmdSelect.CommandText = "SELECT EmpNu, SUM((OutDate-Indate)*24) As HoursOnTask, Purpose From Hours Worked etc.

Now this works like a charm! Whewwwwwww.... talk about going insane!

Next, I am not sure what I want to do as far as using VB 2005 Express. I would love to be able to whiz through all this coding stuff, but that means a whole nother learning process again. I'm on somewhat of a time limit here and have wasted so much time already learning the basic stuff that I honestly don't think I can spare learning it over again another way. I think I just need to get this project done and then the next one can be done with the newer version.

I do understand now what my code is doing, I understand this book and I am able to do what I need to do, select, update, add, group totals, aggregate functions, etc. Heck I even think I can juggle now!!!!! What a trip this has been!

So I think I can get this app done now even tho the code might not be as pretty as it should be. So thanks goes out to everyone who has helped along the way. I can't wait till I know enough to give back help to someone else!

 

[cjard]

well, the sample project I knocked up took about half an hour, and the hardest bit was finding a PC that still had Access installed to make the database.. The development process in .NET2 is very much simplified.. It would take me a similar amount of time to teach you to remake the sample project in VBX2005 - if you want to give it a go in future, and need some spot help, I have accoutns on the major IM networks.

Any column in a select list, that is not part of a GROUP BY clause, needs to have some sort of aggregate function if the query is an aggregating (grouping) one. Sorry if i made some typo that had you going down a garden path...

 

[Patty05]

Thanks cjard! You are the best!

I appreciate your offer so much. However, I almost have this project done now and I must admit, I'm pretty proud of it, although I know that the code is ancient. But if it works, that is all that really matters, right?

I didn't know about the aggregate function until my problem and I'm sure it again is just basic stuff a 'normal' experienced programmer would already know. I also understand that with your experience, it is difficult to remember all the stuff newbies aren't familar with and its easy to just talk psuedo code.

So all is good and I'm on the home front. I'd send you my code when I'm done, but I'd be embarassed!!!!!!!!!!!!!

Thanks again!

 

[Patty05]

cjard, You offered a great deal of help previously, but I am having trouble with a SQL statement similar to what you posted. Can you tell me what is wrong with it? The problem is with the CASE statements. I've taken 1 out and it still doesn't work. I get the error "Error Info.GetDescription failed with E_FAIL(0x80004005).

The second SELECT stmt works correctly and displays the total hours for each employee. The CASE statements are suppose to display the regular and overtime hours.

Thanks for your help.

[SIZE=2]modbcmdSelect.CommandText = [/SIZE]
[SIZE=2]"SELECT [/SIZE]
[SIZE=2]EmpNum, DeptNum, LastName, FirstName[/SIZE]
[SIZE=2]CASE WHEN TotalHours > 40 THEN [/SIZE]
[SIZE=2]40 [/SIZE]
[SIZE=2]ELSE TotalHours [/SIZE]
[SIZE=2]END As NormalHours [/SIZE]
[SIZE=2]CASE WHEN TotalHours > 40 THEN[/SIZE]
[SIZE=2]TotalHours - 40[/SIZE]
[SIZE=2]ELSE[/SIZE]
[SIZE=2]0[/SIZE]
[SIZE=2]END as OverTimeHours[/SIZE]
[SIZE=2]FROM [/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]SELECT DeptNum,EmpNum, LastName, FirstName, SUM(TotalDayHours) As TotalHours FROM Hours WHERE DeptNum='" & globalDeptNum & "' [/SIZE]
[SIZE=2]GROUP BY EmpNum, DeptNum, FirstName, LastName[/SIZE]
[SIZE=2])[/SIZE]
[SIZE=2]GROUP BY EmpNum, DeptNum, FirstName, LastName"[/SIZE]
[SIZE=2]

[/SIZE]

 

[cjard]

modbcmdSelect.CommandText = [/SIZE]
[SIZE=2]"SELECT [/SIZE]
[SIZE=2]EmpNum, DeptNum, LastName, FirstName [/SIZE][SIZE=4][B][COLOR=red],[/COLOR][/B]
[/SIZE][COLOR=red][B][SIZE=2]IIf(TotalHours > 40,[/SIZE][SIZE=2]40,[/SIZE][SIZE=2]TotalHours)[/SIZE][/B][/COLOR][SIZE=2][COLOR=red][B] As NormalHours[/B][/COLOR] [/SIZE][SIZE=4][B][COLOR=red],[/COLOR][/B]
[/SIZE][COLOR=red][B][SIZE=2]Iif(TotalHours > 40,[/SIZE][SIZE=2]TotalHours - 40,0)[/SIZE][/B][/COLOR][SIZE=2] as OverTimeHours[/SIZE]
[SIZE=2]FROM [/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]SELECT DeptNum,EmpNum, LastName, FirstName, SUM(TotalDayHours) As TotalHours FROM Hours WHERE DeptNum=[COLOR=#ff0000][COLOR=magenta]'" & globalDeptNum & "'[/COLOR] [/COLOR][/SIZE]
[SIZE=2]GROUP BY EmpNum, DeptNum, FirstName, LastName[/SIZE]
[SIZE=2])[/SIZE]
[SIZE=2]"[/SIZE]

try not to do that bit in pink; you should use parameters wherever possible.

it is, however, not the cause of the problem (nor is the extraneous group clause on the bottom of the sql)

access (your db tech, right?) doesnt do case - that's oracle syntax, so i apologise if i gave it to you. i've amended the query to be access compatible and the changes are in red.​

 

[Patty05]

Ok cjard, I knew I could count on you as this works like a charm! Now I have just one more question about this crazy program.

My company's pay periods is for 2 weeks. For each week if a person works over 40 hours, they get overtime for anything over 40 hours. So, what I have done is run the query once for each week each having a different date span to give me to the total regular hours for each week and also the total overtime for each week.

This is all good, but what I need to do is combine the 2 weeks now so I get the total reg. hours (Query1week1RegHours + Query2week2RegHours) and the total overtime hours (Query1week1OThours + Query2week2OThours) for EACH employee.

What I need is either or two things.... one query that would do it all, or to combine the two datatables somehow. What is the best way to go about this and how in the world do I do it?

I then also need to write the completed 2 week period totals back to a new table in the database to store for reports I will be running throughout the year. All I have been doing so far is writing one value back to a specific row back to the database. I don't know how to create multiple rows for multiple entries in a new table. Any help there????

What a relief this will be to have this program done!!!!!!!!!!!!!!!!!!!!!!! Thanks for your help!

 

[cjard]

Well, I dont know about your database table structure, and I cant work anything out from the queries you have posted, so i'll use my example:​

This gets us all the info per employee over the last 2 weeks:

[/LEFT]
 

[LEFT]SELECT
[LEFT] empID,

 IIf(TotalHoursWk1 > 40,40,TotalHoursWk1 ) As NormalHoursWk1,

 Iif(TotalHoursWk1 > 40,TotalHoursWk1 - 40,0) as OverTimeHoursWk1,[LEFT] IIf(TotalHoursWk2 > 40,40,TotalHoursWk2 ) As NormalHoursWk2,
[LEFT] Iif(TotalHoursWk2 > 40,TotalHoursWk2 - 40,0) as OverTimeHoursWk2[/LEFT]
[SIZE=2][/SIZE]
[/LEFT]

FROM
(
 
SELECT 
  empID,
  SUM(IIF(INT((TODAY - inDate)/7) = 0,(outDate-inDate) * 24,0)) as TotalHoursWk1,
  SUM(IIF(INT((TODAY - inDate)/7) = 1,(outDate-inDate) * 24,0)) as TotalHoursWk2
FROM 
  work
WHERE 
  outDate IS NOT NULL and 
  inDate > [I]TODAY()-14[/I] 
GROUP BY 
  empID, INT((TODAY - inDate)/7)

 )
[/LEFT]

​

Wht happens here? Well we are selecting only the last 2 weeks dates:
inDate > TODAY()-14

then we use this to decide which week it is:
INT((TODAY - inDate)/7)
the result will be 0 for last week, 1 for week before last

so we have an IF that says "if the data of some record is in the last week WK1 put the hours, otherwise put 0"
and the same for week before last WK2

so really, suppose we have 4 days work, some in week before last, some in last week:

emp, indate, outdate
JON, 01/01/1901 09:00:00, 01/01/1901 17:00:00
JON, 02/01/1901 09:00:00, 02/01/1901 17:00:00
JON, 01/07/1901 09:00:00, 01/07/1901 17:00:00
JON, 02/07/1901 09:00:00, 02/07/1901 17:00:00

we get 4 rows that look like:

empID, hourswk1, hourswk2
JON, 8, 0
JON, 8, 0
JON, 0, 8
JON, 0, 8

these are grouped and summed (all in the first query so far) to just one line for jon giving:
JON, 16, 16

then the IIF in the top query work out his overtime:
JON, 16,0 16,0


as noted before, though you might not see it yet, there is no need to store back into a table, the number of hours worked by an employee in any given day, week etc. It can always be worked out from the indate and outdate

 

[Patty05]

You have amazed me once again! With a little fanigaling, your SQL works perfectly. I sure hope you are making the big bucks somewhere because you obviously know your stuff!

I just need to do one more big task and that is make the CSV file. Is it best to drop the dataset into a grid and create it from that somehow or can I create a csv file from the dataset? I'm searching the web on how to do this now.

Thanks again!

 

[Patty05]

Nevermind post above. I created a CSV file from the dataset with info I gathered online! Works like a charm!!! I'm loving this again!!!

 

[cjard]

When you say dataset, i think you mean datatable - as I occasionally note, DataSets are a collection of DataTables. They dont hold any data themselves, but merely provide a way of collecting together tables that contain related data

Beware. All the examples I found by googling http://www.google.co.uk/search?q=DataTable+to+csv would not work in the case of a field that contained a comma. I thoroughly recommend that you research deeply for an example that can deal with both commas and " double quotes/speech marks.

If you do not succeed, return here and i'll help you modify your chosen example to cope with all values. Also, do not be afraid to use a C# example (many .net examples I find are in C# and it is my preferred language) because C# and VB.NET can be intermixed within a solution.. The C# code would have to go in its own project but other than that it can exist alongside your VB codes in the same solution, and the debugger can step back and forth between the two syntaxes seamlessly (they are the same underneath)

 

[Patty05]

Yes, I did mean datatable its just the dataset I use contains only 1 table. I promise to get my terminology right next time.

The following code is what I am using to write the .csv file. My datatable does not contain any commas or double quotes so I haven't tested against that.

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] delim [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sw [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] StreamWriter
[/SIZE][SIZE=2][COLOR=#008000]' Write out the header row
[/COLOR][/SIZE][SIZE=2]delim = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]sw = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] StreamWriter("C:\PR\EmpHours.csv", [/SIZE][SIZE=2][COLOR=#0000ff]False[/COLOR][/SIZE][SIZE=2], UnicodeEncoding.Default)
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.DataTable [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] dsCurrent.Tables
delim = ""
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] col [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataColumn [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] dt.Columns
sw.Write(delim)
sw.Write(col.ColumnName)
delim = ","
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]sw.WriteLine()
[/SIZE][SIZE=2][COLOR=#008000]' write out each data row
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] row [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] dt.Rows
delim = ""
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] value [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] row.ItemArray
sw.Write(delim)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]TypeOf[/COLOR][/SIZE][SIZE=2] value [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/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][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]sw.Write(value)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]delim = ","
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]sw.WriteLine()
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
Console.Write("ERROR: " & ex.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]sw.Close()
MsgBox(".CSV file written successfully.")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

It works great although there are 2 column names that I would like to change. I would do it in the query, but the column names need to have a # in the name (Dept# and Emp# and also need another column name Sal/Hour) instead of DeptNum or EmpNum or SalHour. (This is not something I can change because this csv file is what will be uploaded to our payroll company and is the way they want it.)

I tried changing it in the query, for example SELECT EmpNum As Emp# FROM ....., but of course that would not work as it is probably assuming a date and the Sal/Hour probably looks like a division problem. I tried Emp" & # & " , and all sorts of other tricks but nothing seems to do my trick.

I also need to do one more thing by adding two lines to the csv file before the data begins that reads:
" Client NumberE90866 in two seperate cells, then I need a blank row before the data begins.

Is this a huge task to do this in code? If so, I can just modify the csv file before sending.

I am learning so much from you cjard. I owe you so much!

 

[cjard]

It is a trivial task.

Identify the section of your code where the values are written to the file. There will be a section where the headers are written:

[/COLOR]
[COLOR=black]For[SIZE=2]Each[/SIZE][SIZE=2] col [/SIZE][SIZE=2]As[/SIZE][SIZE=2] DataColumn [/SIZE][SIZE=2]In[/SIZE][/COLOR][SIZE=2][COLOR=black] dt.Columns
  sw.Write(delim)
  sw.Write(col.ColumnName)
  delim = ","
[/COLOR][/SIZE][SIZE=2][COLOR=black]Next

[/SIZE]

well, sack that off completely - you said you dont want to write the headers as they are presented, so just write your own:

sw.WriteLine("Header1,Hi,Mum,Header4,Some/Column,Blah#,Blah2#")

The line has been written to the file.


You want two lines before that with some other content? No problem.. Just write them in the same way.

I guess your code will finish up as:

'write 3 lines of custom data
.
.
.
'write the data block to the file
...

 

[Patty05]

Well, again you have taught me well. I understand and it works! You wouldn't believe what my little program does now!! I'm so excited. It's almost done and ready for some testing, but there is just one final little thing that I don't understand how to fix.

In my database I have EmpNum as a text field. A typical EmpNum is 000456 or 000081 (again, this is not my company's choice, it is the payroll company that I must upload to that designates these numbers). When I create the csv files, it does not include the 0000's. For example, for 000456, it will only show 456 in the csv file. I've tried changing the type to a number and I still have the problem. Is there some way I can write the true values (the values with 0's ) to the CSV files?

Thank you!