Issues with datagridview last record updating sql database

[awade]

OK. I appreciate anyone's help with this. This is my first main
program using VB.NET 2005. I've previously programmed in VB6.

I've got a form with a datagridview object. Its bound to an SQL 2005
Express database. I currently can update the cells in the
datagridview, and it updates the database when i use the mouse to
click on another record in the datagridview. I don't really want it
to work this way, and it causes two seperate issues.

1. I have a filter set to filter out completed records once they've
been updated based on a check i have in place. This works ok, except
I am always left with one record in my datagridview that doesn't get
updated. I'm guessing this is because I don't have another record in
my datagridview to click on.

2. I don't want to have to depend on the manual 'clicking' of the
next item in my datagridview in order to have it update my database.
There obviously is a programmable means that I'm just not grasping at
the moment.

Situation: I have a data table in my SQL database, that is a list of
parts that are tied to a manufacturing job. As the individual parts
get made i currently update a date field and another 'completed' field
for each line item for that job. My startup form loops through my
database for uncompleted jobs, and then displays only the job number
for the jobs that still have uncompleted parts. The user clicks on
the job number in question and then it opens my sub-form... this is
the one that i'm having issues with. This form has a 'packing list
quantity' text box bound to the database, a 'quantity made' text box
that is also bound to the database, and a datagridview (bound as well)
showing the part names for that job and some details about the part.
I have a check box that allows them to hide the completed parts -
which can be quite helpful if you have a lot of parts in the list.
The check box looks at the 'completed' field and sets up the filter
for the data that gets loaded into the datagridview. The user is to
input the quantity of each part made for a given date, and my program
is to write a record out to a history table (that is working OK), and
update the 'date' and 'completed' fields when the line item parts are
finished. Idealy, the user could use the arrow keys to navigate down
the datagridview while using the number pad to input the quantity...
all without having to use the mouse.

OK. Here is my form_load on my sub-form... this seems to work well.

'Set manufacturing prefix
strJobNo = "05" & txtJobNo.Text

'Set filter for database
Select Case strTeam
Case "10"
lblTitle.Text = "STRUCTURAL"
strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and
BOM_PL_SEQ = '" & strTeam & "'"
Case "20"
lblTitle.Text = " COLDFORM "
strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and
BOM_PL_SEQ = '" & strTeam & "'"
Case "30"
lblTitle.Text = " PANEL "
strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and
BOM_PL_SEQ = '" & strTeam & "'"
Case "40"
lblTitle.Text = " TRIM "
strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and
BOM_PL_SEQ = '" & strTeam & "'"
Case "60"
lblTitle.Text = " UTILITY "
strJobFilter = ""
End Select
If chkHide.Checked Then
strJobFilter = strJobFilter + " and BOM_COMPLETED = '0'"
End If
strJobFilter2 = "BOM_QTY_LOADED = ''"

'Load packing list for job and manufacturing line
Me.BOMBindingSource.Filter = strJobFilter
Me.BOMTableAdapter.Fill(Me.GSMDataSet.BOM)

'Load history for packling list
Me.BOMTOBOMMFGBindingSource.Filter = strJobFilter2
Me.BOM_MFGTableAdapter.Fill(Me.GSMDataSet.BOM_MFG)

I currently use an key_press.enter event for adding and updating my
grids... here is what I have.

If Asc(e.KeyChar) = Keys.Enter And txtMadeToday.Text <> "" Then

' Update Quantity
strTotalQty = Str(Val(txtMadeToday.Text) +
Val(txtTotalMade.Text)
txtTotalMade.Text = strTotalQty
' Update Completion Date
If txtPLQty.Text = txtTotalMade.Text Then
'gets the time formated for our VSE system using
DateTime routine
txtBOM_COMPLETION_DATE.Text =
Mid(DateTime(Me.txtDate.Text), 1, 8)
' make database field for part completed here -
will be used for hiding completed records
txtComplete.Text = "1"
End If
Me.BOMTableAdapter.Update(Me.GSMDataSet.BOM)

' Calculate and load BOM_MFG table for History
strMfgKey = txtBOM_KEY.Text
strBomKey = strMfgKey
strMfgKey = strMfgKey + DateTime(Now)
strQtyMade = Str(Val(txtMadeToday.Text)
strDateMade = Mid(DateTime(Me.txtDate.Text), 1,
8)
strWeightMade = Str(Val(txtUnitWeight.Text) *
Val(strQtyMade))
strDateEntered = DateTime(Now) '
strQtyLoaded = "" : strDateLoaded = "" :
strWeightLoaded = "" : strLoadDateEntered = ""
BOM_MFGTableAdapter.Insert(strMfgKey, strBomKey,
strQtyMade, strDateMade, strWeightMade, strDateEntered, strQtyLoaded,
strDateLoaded, strWeightLoaded, strLoadDateEntered)
txtMadeToday.Text = ""
e.Handled = True
Me.BOM_MFGTableAdapter.Fill(Me.GSMDataSet.BOM_MFG)

End If

I'll be glad to provide more information... and any help that anyone
can give would be greatly appreciated.

Thanks in advance...

-andrew

 

[Rich]

I don't see any references to a sqlDataAdapter object in your code. In order
to make a suggestion related to your needs you should explain how you are
connecting to the Sql Server. Are you using a continuous connection (ODBC)?
or disconnected recordsets (sqlDataAdapter/ADO.Net)?

 

[awade]

Rich,

Hi, and thanks for the reply. For better, or worse, I have a defined
DataSet, BindingSource, and TableAdapter.

One a side note, does it hurt if i define multiple Bindingsource
objects and TableAdapter objects and point them to the same database
table?

Thanks,

-andrew

 

[Rich]

Hi Andrew,

OK. I sort of got your picture now. I used to use the bound elements of
the datagrid and datatables, but that was very inflexible. Now I use a free
standing sqlDataAdapter. I put together a simple test app to demonstrate how
to use the sqlDataAdapter and a datagridview. I will paste the code here.
The app consists of one Form, a datagridview control that I call dgrv1, and a
statusStrip control with 2 labels (which the labels display the rowCount and
rowPosition using a CurrencyManager object). The statusStrip lables are
called tssL2 and tssL3 (just a convention of mine).

-------------------------------------------------------------------------------------

Imports System.Data.SqlClient
Imports System.Data

ublic Class frmTableUpdating

Dim da As SqlDataAdapter, ds As DataSet, conn As SqlConnection, curMgr
As CurrencyManager

Private Sub frmTableUpdating_Load(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles MyBase.Load
conn = New SqlConnection
conn.ConnectionString = "Data Source=yourServer;Initial
Catalog=yourDBName;Integrated Security=True"
da = New SqlDataAdapter
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
ds = New DataSet
da.SelectCommand.CommandText = "Select * From tmp3 Order By RecordID"
da.Fill(ds, "tmpX")
dgrv1.DataSource = ds.Tables("tmpX")
curMgr = CType(Me.BindingContext(ds.Tables("tmpX")), CurrencyManager)
tssL2.Text = (curMgr.Position + 1).ToString
tssL3.Text = curMgr.Count.ToString

da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
"RecordID")
da.UpdateCommand.Parameters.Add("@SubscrID", SqlDbType.VarChar, 50,
"SubscrID")
da.UpdateCommand.CommandText = "Update tmp3 Set SubscrID = @SubscrID
Where RecordID = @RecordID"

End Sub

Private Sub dgrv1_CellContentClick(ByVal sender As System.Object, ByVal
e As System.Windows.Forms.DataGridViewCellEventArgs) Handles
dgrv1.CellContentClick

End Sub

Private Sub dgrv1_CellEndEdit(ByVal sender As Object, ByVal e As
System.Windows.Forms.DataGridViewCellEventArgs) Handles dgrv1.CellEndEdit
Console.WriteLine("end Edit" & e.ColumnIndex.ToString & " " &
e.RowIndex)
Dim dr, drF() As DataRow
drF = ds.Tables("tmpX").Select("RecordID=" &
dgrv1.Rows(e.RowIndex).Cells("RecordID").Value.ToString)
dr = drF(0)
dr.BeginEdit()
dr("SubscrID") = dgrv1.Rows(e.RowIndex).Cells("SubscrID").Value
dr.EndEdit()
da.Update(ds, "tmpX")
End Sub

Private Sub dgrv1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles dgrv1.Click
tssL2.Text = (curMgr.Position + 1).ToString
End Sub

Private Sub dgrv1_KeyUp(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyEventArgs) Handles dgrv1.KeyUp
If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
tssL2.Text = (curMgr.Position + 1).ToString
End If
End Sub
End Class


-------------------------------------------------------------------------------

You can actually copy and paste this code directly into a new form. Make
sure that this code replaces all of the code in the new form. Note: I
created a table on the server DB that I called tmp3 and populated it with
fake data. The RecordID field is a unique Int field. The SubscrID field is
also a unique field and is a varchar(50).

In the form Load event I create the sqlDataAdapter Select and Update
commands. I also create a dataset and populate it with the data from tmp3
into a memory table I call tmpX (I could call the memory table anything).
tmpX belongs to the dataset which I call ds. I populate ds with da.Fill
command (which is where the Select command is used). Then I set this data as
the datasource for dgrv1. Then I set the CurrencyManager object to the
dataset table ds.Tables("tmpX").

In the dgrv1 CellEndEdit event I perform updates to the Memory table (tmpX).
This event gets invoked as soon as you leave a datagridview cell that you
just edited. The update on tmpX is translated to tmp3 (the server table)
through the da.UpdateCommand and the parameters.

Note: I use a dataRow array object drF() to locate the correct row in the
memory table. This array object has a Select property for locating specific
rows. I do this because the datagridview can be Re-ordered on any column and
will throw off the ordering of the memory table. I located the correct row
using the Select property of the dataRow array and then set a dataRow object
to this row, dr. This is where the edit happens to the memory table. THen
da.Update(ds, "tmpX") translates this update to tmp3 on the server through
the da.UpdateCommand and the parameters. Now you have updated the correct
row on the server table.

This may seem a little convoluted at first, but after you have chewed on it
for a while you will appreciate the enormous flexibility it gives you over
the bound system. And if you think that an operation in MS Access is simple
-- this is what is going on under the hood. You can tweak it to your heart's
content.

Rich

 

[awade]

Rich,

Thanks again for the help. OK. I create your sample program as you
have it described... and I must admit, I have not got the full grasp
of it for looking at the code itself. Following your instructions...
I do feel like I understand what we're trying to do.

OK... i am able to run the app. It does show my sample table tmp3
data... with my 4 little records in the datagridview, dgrv1. I click
on the cell i want to update, and as soon as I tab (or hit enter /
mouse click / etc.. I imagine) the data gets updated. Very nice sweet
and simple. I need to look at it a little more closely and see if
this will work for me. It may not solve my issue, i'm not sure yet...
but it is something that I'm going to give a little time to and see
what happens when I try and use this method. I think the key here is
that this has me editing data in the datagridview object itself. I
currently am editing data in a text box outside of the datagridview
object... with the text box pointing to one field, and the
datagridview object showing just a small subset of data that's
actually in my record. Doing the editing inside the grid may not only
help with the updating of the data to the database, but it would also
help with my problem of wanting dual focus. Number pad to the text
box, and arrow keys to the datagrid.

Thanks again for taking time to help... I think i need to play with
this a bit and see what I can come up with.

-andrew

 

[Rich]

Hi Andrew,

Glad this sort of works for you because I noticed that my copy of the code
left out the P in Public at the beginning of the code.

Anyway, my sample basically shows how to use a sqlDataAdapter. It is the
same thing for OleDBAdapter also. And even though my sample edits the
datagridview directly, you can use it tp edit with your textboxes. Note: in
my sample, I have it set up to edit only one field - the SubscrID field. You
need to add a parameter for each field you want to edit. And the trick
there is that you can name the param anything you want (although, I think you
have to preface the param name with the @ symbol) but you have to reference
the field you want to edit by the field's exact name:

da.UpdateCommand.Parameters.Add("@bill", SqlDbType.VarChar, 50, "SubscrID")

da.UpdateCommand.Parameters.Add("@date1", SqlDBType.Datetime, 8, "EntryDate")
....
da.UpdateCommand.CommandText = "Update tmp3 Set SubscrID = @bill, EntryDate
= @date1 Where RecordID = @RecordID"

'--Perform the dataRow edit
Dim dr As DataRow = ds.Tables("tblWhatever").Rows(?)
or Dim drF As DataRow() = ds.Tables("tblWhatever").Select("RecordID = " &
txtRecordID.Text)
Dim dr As DataRow = drF(0) '--guaranteed to get the correct row now
dr.BeginEdit
dr("SubscrID") = txtSubscrID.Text
dr("EntryDate") = txtEntryDate.Text
....
dr.EndEdit
da.Upate(ds, "tblWhatever") '--tblWhatever references tmp3 on the server

And when editing the datagridview, you are not directly editing the
underlying table because you have to write the code to do that. You can do
the same thing in a textbox - only difference is that one is a cell (from the
datagridview) and one is just a textbox.

Here is another trick: You can use a sqlDataReader object to read a
specific row from your server table and populate you unbound textboxes:

da.SelectCommand.CommandText = "Select * From tmp3 Where RecordID = " &
txtRecordID.Text

Dim dtr As SqlDataReader = da.SelectCommand.ExecuteReader
If dtr.HasRows Then
dtr.Read
txtSubscrID.Text = dtr("SubscrID").ToString
txtEntryDate.Text = dtr("EntryDate").ToString
txtWhatever.Text = dtr("Whatever").ToString
End If
dtr.Close

Now you can edit a textbox and in the Leave event of that textbox (or
whatever event) you run the same code for da.UpdateCommand.

I would recommend steering away from bound textboxes, datagridviews, ...

The unbound methods offer way more performance, flexibility and ease of
programming (once you get the hang of it).

And you are probably thinking -- what if I have a master record and want to
display detail data for that master record in the datagridview?

All of my apps do this, but you have to start using the AddressOf delegate
to reference all of the master record textboxes and then set the datagridview
datasource to the detail table for each master recordID. This is not too
involved, but rather than write a book here, you may just want to get the
hang of using the sqlDataAdpater UpdateCommand for now.

Rich

 

[awade]

Hi Rich,

Yeah, I obviously found the P - ublic typo... as well as having to put
a few lines of code back together from where the post broke them up.
All-in-all, just minor clean up and all was well.

OK... so I'm trying to adapt your code using unbound boxes to fit my
program. I'm currently filling my datagrids with the data that i'd
like to see in them without much of an issue. Worked real well.

I'm still a little foggy on how to use the status strips... outside to
say that they assist in knowing the position of the data we're
editing, correct?

And I'm guessing that in order to have this work with the text box, I
get 'RecordID' of the record that i'm currently on in my datagrid and
I can freely use data for that record and have it update from any
event i choose from my text box. I understand that the datagrid is
not my actual table in my database, and thank goodness for that...
i've just got to make sure that i'm loading the correct data for the
user to view. Which I do with SQL commands with the
da.SelectCommand.CommandText line of code.

Do you think it is possible to keep focus on my text box and have the
e.KeyCode = Keys.Down move the control in my datagrid? I'm trying to
do that now, and not having much success. I have managed to have it
select the next record in my datagrid, but it doesn't make the the
currentrow... if you know what i mean.

Well, i'm moving away from the bound objects, and moving forward with
our new method. I think once i get my brain wrapped around what i'm
doing a little better it will be much easier.

Thanks again for your time and efforts... i appreciate all the help.

-andrew

 

[awade]

Rich,

Let me pick your brain for a bit... can I fill 3 text boxes and the
datagrid all from our memory table, tmpX? Where the text boxes show
the data of the same record that is current in the datagridview
object?

-andrew

 

[Rich]

Hi Andrew,

Think of the datagridview as a sort of subform - like an MS Access subform -
except with 100+ times more capablities.

In the main form's load event -- after (or before) you set the datasource
for your datagridview (dgrv1) -- add this line of code

dgrv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

This will select/highlight the entire row of the datagridview. I added a
button to my sample app which scrolls through the memory table tmpX using the
CurrencyManager object (curMgr). I added a couple of textboxes to my form
(txt0, txt1) and will populate these as I scroll through the dataTable tmpX
-- which also scrolls through the datagridview.

BTW, it is a good practice to use Panel controls to separate textboxes from
datagridviews. Add a Panel control to the top of your form and set its
ordering to SendBack (if you have to place the panel over already existing
textboxs). Dock the Panel to the top of the form and then resize it as
needed to enclose your textboxes and buttons. Underneath the Panel add your
datagridview and set its dock property to Fill. The dock property for all
controls is usually located near the bottom of the Property sheet (VS2005).
What this does is to resize the datagridview as you resize your form. And
there is an order in which you have to place the controls. First add a Panel
(or panels). Then add the status strip (I always add a status strip because
you can display all kinds of information). The status strip automatically
docks to the bottom of the form. Then add the datagridview last. If you
load the datagridview before the statusstrip -- a lot of times it will cover
the statusstrip. These are the primary controls. You can add textboxes and
buttons,... to the panel(s) afterwards -- they are secondary controls.
Anyway, here is what button1 in my sample app does:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
curMgr.Position += 1
tssL2.Text = (curMgr.Position + 1).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected = True

Dim SelectedRows As DataGridViewSelectedRowCollection
SelectedRows = dgrv1.SelectedRows
txt0.Text = SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text = SelectedRows(0).Cells("SubscrID").Value.ToString

End Sub

Button1 will select one row at a time here

dgrv1.Rows(curMgr.Position).Cells(0).Selected = True

then you set SelectedRows to this row. SelectedRows is a collection object.
It will only contain one row in this sample because I only selected one row.
Then you can set the contents of SelectedRows to your textboxes.

Give this a try,
Rich

 

[awade]

OK... more questions about your example code...

the dataadapter, da, updatecommand that is in the form_load section...
what does this do? Is just setting what parameters, aka fields, that
I can use when I actually build my update string? Either fields i
will update, or fields i will reference.

Form_load

strJobNo = "05" & txtJobNo.Text
strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and BOM_PL_SEQ
= '10'"

conn = New SqlConnection
conn.ConnectionString = "Data Source=GSMAPP\SQLEXPRESS;Initial
Catalog=GSM;Persist Security Info=True;User ID=<id
here>;Password=<pass here>"
da = New SqlDataAdapter
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
ds = New DataSet
' These are the fields that I need to either display,
reference, or update
da.SelectCommand.CommandText = "Select BOM_MARK, BOM_COL,
BOM_LENGTH, BOM_DESC, BOM_KEY, BOM_PL_QTY, BOM_COMPLETION_QTY,
BOM_COMPLETION_DATE, BOM_COMPLETED From BOM Where " & strJobFilter
da.Fill(ds, "tmpX")
StructuralList.DataSource = ds.Tables("tmpX")

curMgr = CType(Me.BindingContext(ds.Tables("tmpX")),
CurrencyManager)
tssl1.Text = (curMgr.Position + 1).ToString
tssl2.Text = curMgr.Count.ToString

' So I currently have one reference field - BOM_KEY... and one
field that I would update - BOM_COMPLETION_QTY? And if I wanted to
add more fields to update, then I would add more
UpdateCommand.Parameters.Add lines of code for each one.... right?
da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@BOM_COMPLETION_QTY",
SqlDbType.VarChar, 7, "BOM_COMPLETION_QTY")
da.UpdateCommand.Parameters.Add("@BOM_KEY", SqlDbType.VarChar,
38, "BOM_KEY")
da.UpdateCommand.CommandText = "Update BOM Set
BOM_COMPLETION_QTY = @BOM_COMPLETION_QTY Where BOM_KEY = @BOM_KEY"

End sub

** You're probably wondering why my Quantity field is a string... all
of this data has to be in string format because this database table is
being read from an VSE mainframe.

OK... The CellEndEdit is what actually updates the database, right?
In our example, we're only dealing with updating one field in the
datagrid (and our database)... i'm trying to wrap my brain around
updating 3 different fields when one field changes (not a biggie if
i'm understanding this correctly). When my Quantity gets updated... I
can update my other fields (today's date and such) off of that one
trigger... soooo... if i want my trigger to be a key_press.enter event
off of a text box... i use the same code?

I hope that some of that makes sense...

Thanks,

-andrew

 

[awade]

Hi Rich,

Oh I like that... as I play with your sample app, and try and apply
the practices to my own... I end up with all kinds of questions.
Luckily, I end up answering some of them myself just by re-reading and
following your code.

All of this to say... i'm sure i'll have some more questions to
follow... but thank you for taking your time to help me. I hope you,
and your family, have a safe and happy holiday season.

OK... now let me go look this thing over some more... and I'll get
back to you after a bit.

 

[Rich]

The UpdateCommand basically transfers the edits on the memory table to the
server table. In my sample I edit one field and update it immediately. That
was just to demonstrate Editing/Updating. In reality, my users will edit
dozens of fields in a row and hundreds of rows before calling the
UpdateCommand. But the setup is a little bit different.

For the users I support, they will edit lots of stuff and then consciously
click a button that performs the updates. You can automatically perform an
update on the datagridview's RowLeave event. So you can edit several cells
on a row and call the updateCommand when you leave the row. You will notice
that the Datagridview controls has hundreds of events (and hundreds of
properties) very powerful control. You can custom color cells, fonts, change
fonts size per cell per row, ...

Anyway, if you want to update multiple fields just add a parameter to the
UpdateCommand for each field you want to edit. All the UpdateCommand does is
to transfer whatever fields were edited in the memory table tmpX to the
server table. --Important Note: if you edit cells in the datagridview but
do not pass on these edits to the memory table - the memory table has no
knowledge that any edits were performed. This is were drF() comes into the
picture:

Scenario: dgrv1 was just re-ordered on column EntryDate. The row order in
dgrv1 is now different than the row order of tmpX. You edited row 15 in
dgrv1. But row 15 is actually row 337 in tmpX. How does the app know which
row to update in tmpX?

dim drF() As DataRow = ds.Tables("tmpX").Select("SomeKeyField = "
someKeyvalue)

then Dim dr As DataRow = drF(0)
dr.BeginEdit
dr(0) = dgrv1.Rows(15).Cells(0).Value
dr(1) = dgrv1.Rows(15).Cells(1).Value
dr(7) = dgrv1.Rows(15).Cells(7).Value
dr(18) = dgrv1.Rows(15).Cells(18).Value
dr.EndEdit
da.Update(ds, "tmpX")

Here I am only editing 4 fields in the datagridview. You can actually
create a loop if you have a large table and set the values of each field to
the value of the cells in a given row in dgrv1 even if the cell has not been
edited - no difference - you are just passing in the current value of the
cells.

dr.BeginEdit
For Each dc As DataColumn in ds.Tables("tmpX").Columns
If dc.ColumnName <> "yourKeyField" Then
dr(dc.ColumnName) = dgrv1.Rows(15).Cells(dc.ColumnName)
End If
Next
dr.EndEdit
da.Update(ds, "tmpX")

Note: dr does not contain dataColums, it only contains values reference by
the datacolumns. So you have to base the columns loop on the actual memory
table
ds.Tables("tmpX") when using a For Each Next type of loop. You could also
use a For Next Loop, but you still have to base the column count on
ds.Tables("tmpX").Columns.Count

For i As Integer = 0 To ds.Tables("tmpX").Columns.Count - 1
....
Next

 

[awade]

Hi Rich,

Wow... I'm so close to having the sample app do like i need my main
one. What I've done:

First, I added another field in the database called "Description",
varChar(50). In the form load i've changed the UpdateCommand and added
populating the text boxes as you had done earlier with your code:

da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
"RecordID")
da.UpdateCommand.Parameters.Add("@SubscrID",
SqlDbType.VarChar, 50, "SubscrID")
da.UpdateCommand.Parameters.Add("@Description",
SqlDbType.VarChar, 50, "Description")
da.UpdateCommand.CommandText = "Update tmp3 Set SubscrID =
@SubscrID, Description = @Description Where RecordID = @RecordID"

Dim SelectedRows As DataGridViewSelectedRowCollection
SelectedRows = dgrv1.SelectedRows
txt0.Text = SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text = SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToString

I've also added a third text box (txt2) and added this code:

Private Sub txt2_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyEventArgs) Handles txt2.KeyUp
Dim SelectedRows As DataGridViewSelectedRowCollection

If e.KeyCode = Keys.Enter Then
SelectedRows = dgrv1.SelectedRows
SelectedRows(0).Cells("Description").Value = txt2.Text
'update tmpx
curMgr.Position += 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToString
Else
If e.KeyCode = Keys.Down Then
curMgr.Position += 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected = True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToString
Else
If e.KeyCode = Keys.Up Then
curMgr.Position -= 1
tssL2.Text = (curMgr.Position).ToString
dgrv1.Rows(curMgr.Position).Cells(0).Selected =
True
SelectedRows = dgrv1.SelectedRows
txt0.Text =
SelectedRows(0).Cells("RecordID").Value.ToString
txt1.Text =
SelectedRows(0).Cells("SubscrID").Value.ToString
txt2.Text =
SelectedRows(0).Cells("Description").Value.ToString
End If
End If
End If
End Sub

Now, I've tried cleaning that up once using a case statement... but
then it wouldn't let me type anything into my text box. So, I'll
revisit that later. I set the tab order in my form to give txt2 a tab
stop of 0. Now i'm on txt2 when the form loads... my text boxes are
all populated with the current record in the datagrid... i can
navigate up or down with my up and down arrow keys... I can enter a
data for the description in txt2 and it updates the grid..... BUT...
now i'm having issues with the updating back to the database.

I noticed that on the CellEndEdit uses the index of the row... and
then the name of the column... If I wanted to update the database
after each field has been edited, couldn't I get my index and column
name to update tmpX? This is assuming that no reordering of my
datagrid has been done. I saw in your earlier post how you said I
should loop through and match my "KeyField"... and I agree... but do I
have to put the "KeyField", like our RecordID, in my datagrid. I'm
guessing the answer is yes, and then I hide it.

Its slowly sinking in... i'm going to work some more on the update of
tmpX from my Key_Press event.

Thanks again for you help...

-andrew

 

[awade]

In follow up to my last post....

I added the following under my comment 'update tmpx in the key_press
event

Dim dr, drF() As DataRow
drF = ds.Tables("tmpX").Select("RecordID=" &
dgrv1.Rows(SelectedRows(0).Index).Cells("RecordID").Value.ToString)
dr = drF(0)
dr.BeginEdit()
dr("SubscrID") =
dgrv1.Rows(SelectedRows(0).Index).Cells("SubscrID").Value
dr("Description") =
dgrv1.Rows(SelectedRows(0).Index).Cells("Description").Value
dr.EndEdit()
da.Update(ds, "tmpX")

now when I press enter (txt2), it updates my database for that
row....

You've been a big help... i'll post again after i've played with this
in my main code a bit...

Thanks again,

-andrew

 

[awade]

Rich,

Question: Do you see anything wrong with this code? The variable
strJobNo is set prior to LoadStructural being called. If I comment
out the last two lines of code prior to the end sub, my form pops up
and my data is in my datagrid, Structural List. With those last two
lines as they are my program fails at line:

txtStrPLQty.Text =
StructuralList.SelectedRows(0).Cells("BOM_PL_QTY").Value.ToString

I get an ArgumentOutOfRangeException was Unhandled error. It says
that the index was out of range. So, my collection is empty...
right? But with those lines commented out I have data in my
datagrid. I've even made sure that I have the selectionMode as
FullRowSelect. What am I missing?

Public Sub LoadStructural()

strJobFilter = "BOM_JOBNBR = '" & strJobNo & "' and BOM_PL_SEQ
= '10'"

conn = New SqlConnection
conn.ConnectionString = "Data Source=GSMAPP\SQLEXPRESS;Initial
Catalog=GSM;Persist Security Info=True;User ID=sa;Password=sa"
StructuralList.SelectionMode =
DataGridViewSelectionMode.FullRowSelect
da = New SqlDataAdapter
da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn
ds = New DataSet
da.SelectCommand.CommandText = "Select BOM_MARK, BOM_PL_QTY,
BOM_COL, BOM_COMPLETION_QTY, BOM_KEY From BOM Where " & strJobFilter
da.Fill(ds, "tmpX")
StructuralList.DataSource = ds.Tables("tmpX")
curMgr = CType(Me.BindingContext(ds.Tables("tmpX")),
CurrencyManager)
StrTSSL1.Text = (curMgr.Position + 1).ToString
StrTSSL2.Text = curMgr.Count.ToString

da.UpdateCommand = New SqlCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.Parameters.Add("@BOM_KEY", SqlDbType.VarChar,
38, "BOM_KEY")
da.UpdateCommand.Parameters.Add("@BOM_MARK",
SqlDbType.VarChar, 8, "BOM_MARK")
da.UpdateCommand.Parameters.Add("@BOM_COMPLETION_QTY",
SqlDbType.VarChar, 7, "BOM_COMPLETION_QTY")
da.UpdateCommand.CommandText = "Update BOM Set BOM_MARK =
@BOM_MARK, BOM_COMPLETION_QTY = @BOM_COMPLETION_QTY Where BOM_KEY =
@BOM_KEY"

txtStrPLQty.Text =
StructuralList.SelectedRows(0).Cells("BOM_PL_QTY").Value.ToString
txtSTRTotalMade.Text =
StructuralList.SelectedRows(0).Cells("BOM_COMPLETION_QTY").Value.ToString

End Sub

I appreciate any help that you can give... thanks again...

-andrew

 

[awade]

Got it. I omitted this line after setting my pointers.

StructuralList.Rows(curMgr.Position).Cells(0).Selected = True

So far, looking real good... i can't thank you enough for steering me
in this direction.

thanks,

-andrew