Issues with datagridview last record updating sql database

awade@gulfstatesmanufacturers.com

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)?



"" wrote:

> 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
>
>

awade@gulfstatesmanufacturers.com

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

awade@gulfstatesmanufacturers.com

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

awade@gulfstatesmanufacturers.com

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


"" wrote:

> 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
>

awade@gulfstatesmanufacturers.com

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_Q TY",
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




On Dec 14, 9:24 am, wrote:
> 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

awade@gulfstatesmanufacturers.com

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


"" wrote:

> 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
>

awade@gulfstatesmanufacturers.com

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_Q TY",
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




On Dec 14, 9:24 am, wrote:
> 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