Manipulate Data from a Bound DataSource

[Blowinup]

Hello,

What I'm trying to do seems so simple. But for the life of me I've not been able to figure it out on my own using the wealth of support out there.

My Objective:
I simply want to pull two rows from a SQL 2005 table, change one of them, and save it back to the database.

My Challenge:
One of the rows is stored in hours. I would like it to display in days on my form but save to the table in minutes.

My Problem:
I don't know how to code I'm just trying to put together a little utility to save us some time.

History:
I have a medical records application that charts patients encounters. These encounters need to be set to automatically lock after a given number of days. For some reason the vendor did not include this functionality in the GUI or admin consol; it must be set with an update statement. So I want to make a little utility that displays the name of the practice in a ComboBox and the corresponding lock interval in a TextBox.

This part was easy. What I can't for the life of me figure out is how to make the text box display in days instead of hours; I mean it's a simple x /24 for crying out loud!

I am able to accomplish this in SQL by creating a stored procedure or a view on the source database. But that’s not how I want to go about this. There has to be a way (probably several) to get this done in VB.

So I think I'm looking for one of three things but who knows, maybe I dont know enough to know what I' looking for:

1. To know how to do calculations on a TextBox populated with data from a DataSource.
   
2. To know how grab data from a DataSource and assign it to a variable so I can work with it and then put the results in a text box that is still connected to the original DataSource. I'm not sure if I explained this one right. What I need is for the lock encounter TextBox to still synch up with the lock Practice ComboBox.
   
3. To know how to incorporate SQL queries into VB code.

Would it help for me to paste the code I'm working with? Good God how do you people put up with us novices? Thx in advance for any help you can offer.

dave

 

[Blowinup]

Anybody... anybody..... Ferris?

 

[Tom]

The TimeSpan structure can be used to convert days to hours/minutes/seconds etc & vice versa.

        [COLOR="Green"]'TimeSpan(Days,Hours,Minutes,Seconds)[/COLOR]
        Dim ts As New TimeSpan(1, 0, 0, 0)

        Console.WriteLine("Days : {0}", ts.Days)
        Console.WriteLine("Hours : {0}", ts.TotalHours)
        Console.WriteLine("Minutes : {0}", ts.TotalMinutes)
        Console.WriteLine("Seconds : {0}", ts.TotalSeconds)
        Console.ReadLine()

     [COLOR="green"]   '~~~~~~~~~~~~~~~~~~~~
        ' Output Results :
        '
        '   Days : 1
        '   Hours : 24
        '   Minutes : 1440
        '   Seconds : 86400[/COLOR]

 

[cjard]

Read the DW2 link in my signature, section Creating a Simple Data App

This will tell you how to interact with databases
Download all relevant records into a DataTable, hopefully you'll download the number of hours as an integer

Because youre downloading 2 columns (you said rows, but i think you meant columns; columns are the vertical ones. Practice woul be one column, Hours would be the other columnh. If you downlaoded 100 patient records you'd have 100 rows of 2 columns) into a datatable, when you bind your combo (read the tutorial!) the combo will serve as a navigator. Dont download hundreds of rows; it makes combo a pain in the ass to use. Consider datagridview if youre doing 100s of rows

You can use an Expression to represent days.. Read up on DataColumn.Expression - google for it. You can add a column to your datatable that is basicallly "[Hours] / 24" and bind that.

You update another table with minutes, probably ForEach record in your datatable, and fire off an update statement


DO NOT write the SQL statement into your code. Go back to the tutorial and find a section about adding custom queries (er.. maybe in saving data.. i dunno, I worked this out intuitively) - right click your tableadapter (read the tutorial; it explains what it is) and Add Query. Choose Query That Updates Rows or whatever the wording is that i for update.
Write you UPDATE sql into the box, or use the designer to make it. UPDATE otherTable SET lockMinutes = @minutes WHERE patientID = @patient
Call your query UpdateLockMinutesForPatient or something like that
Use it like myTableAdapter.UpdateLockMinutes(therow.Hours, theRow.PatientID) in your ForEach row As PatientRow in myDataSet.Patient loop

Confused gby any of this? read the tutorials.. It all becomes clear. Practice practice practice, if you'll excuse the pun

 

[Blowinup]

Thanks for the help guys... I think I got this one now! On to my next little utility... a doc reader finds and removes a string.