Update Datagrid - errors? 1

[nicks60610]

This is what I want to do, update a datagrid where a user enters some data, and this is the update part:

Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click


Dim dgi As DataGridItem
For Each dgi in DataGrid1.Items
'Read in the Primary Key Field
'Dim ID As Integer = Convert.ToInt32(DataGrid1.DataKeys(dgi.ItemIndex))
Dim SunderlandOnly As String = CType(dgi.FindControl("Sunderland Only"), TextBox).Text
Dim PcsInspected As String = CType(dgi.FindControl("Pcs Inspected"), TextBox).Text
Dim PcsDefective As String = CType(dgi.FindControl("Pcs Defective"), TextBox).Text
Dim PcsSold As String = CType(dgi.FindControl("Pcs Sold"), TextBox).Text
Dim ProductComplaints As String = CType(dgi.FindControl("Product Complaints"), TextBox).Text
Dim PcsSoldBF As String = CType(dgi.FindControl("Pcs Sold (Big Flats)"), TextBox).Text
Dim ProdCompBF As String = CType(dgi.FindControl("Product Complaint (Big Flats)"), TextBox).Text


'Issue an UPDATE statement...

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Quality Metrics Database.mdb"


Dim conMetrics As OleDbConnection = New OleDbConnection(connectString)


conMetrics.Open()

Dim strUpdateQuality As String

Dim dcmdUpdateQuality = New OleDb.OleDbCommand(strUpdateQuality, conMetrics)

strUpdateQuality = "UPDATE [All Plants Summary Data] SET [Sunderland Only] = @Sunderland , [Pieces Inspected] = @PcsInsp, [Pieces Defective] = @PcsDef, [Pieces Sold] = @PcsSold, [Product Complaints] = @ProdComp, [Pieces Sold Big Flats] = @PcsSoldBF, [Product Complaints Big Flats] = @ProdCompBF where [Month] = '" & Session("Month") & "' and [Year] = '" & Session("Year") & "' and [Plant] = '" & Session("Plant") & "'"

'dcmdUpdateQuality.Parameters.Clear()
dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)
dcmdUpdateQuality.Parameters.Add("@PcsInsp", PcsInspected)
dcmdUpdateQuality.Parameters.Add("@PcsDef", PcsDefective)
dcmdUpdateQuality.Parameters.Add("@PcsSold", PcsSold)
dcmdUpdateQuality.Parameters.Add("@ProdComp", ProductComplaints)
dcmdUpdateQuality.Parameters.Add("@PcsSoldBF", PcsSoldBF)
dcmdUpdateQuality.Parameters.Add("@ProdCompBF", ProdCompBF)

dcmdUpdateQuality.ExecuteNonQuery()

Next

End Sub

And this is the error I receive on this line:

dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)

Object reference not set to an instance of an object

Does this mean that there is no data, i.e. it never retrieves any data from the database??

 

[dvannoy]

Object reference not set to an instance of an object

means that the control cannot be found. check to make sure you spelled it correctly.

Dim SunderlandOnly As String = CType(dgi.FindControl("Sunderland Only"), TextBox).Text

Sunderland Only ?? is this the name of your textbox in the edit portion of your grid?

 

[nicks60610]

well, it is a template column as I have set all templatecolumn fields to show in edit mode without having to click Edit for each row...but when I check on the column name in the edit mode:

Columns[1] - Sunderland Only is what the edit portion calls it...

So do I have to include the Columns[1] portion as well?

 

[dvannoy]

did you add an edit,update ,cancel button to your grid? this should not be a template item.

also change your sub heading

from

Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click

to

Sub Update_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)

 

[nicks60610]

No, I didn't add an edit button because I wanted the users to be able to edit all items before submitting and not have to click each row edit, and then update; this way was recommended to me by a MSDN developer as it would minimize the time involved in editing, but you don't think it would work doing this - maybe this is why I cannot update??

Thanks dvannoy

 

[dvannoy]

remember, if you decide to update all records at once you will have to loop through the entire grid (recordset) to update all rows. I personally don't think this is a good way UNLESS you really need to do it. I would rather update on a row by row bases. that's just me.

 

[nicks60610]

I would, but I know my users will complain if they have to edit each row as sometimes there are 20 rows with 7 columns of data...they are doing it manually right now in a Database (Access) form, so I want to make it as similar as possible...

 

[nicks60610]

I am using the example from "FourguysfromRolla" now, Parts 17-1 and 17-2, and following example line by line...still no go?

 

[dvannoy]

OK, then it looks like you need to update all records at once. I would do something like this

Dim dgItem As DataGridItem

For Each dgItem In dgDetail.Items

loop through the grid with findcontrols

did you read part 17 of the link I sent you in the last post?

http://aspnet.4guysfromrolla.com/articles/121504-1.2.aspx

this does exactly what you want.

 

[dvannoy]

you must have posted at the same time as me..

 

[nicks60610]

hehehee, looks like it...now this is interesting, I get an error here:

Dim Index As Integer = Convert.ToInt32(DataGrid1.DataKeys(dgi.ItemIndex))

And this is true even if I leave Index as ID (in their example), and I remember seeing this when I had a Datagrid that I updated line/line, it had something to do with the way it counted the rows?

This is the error:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

 

[dvannoy]

take a look at this

http://www.dotnetjohn.com/articles.aspx?articleid=83

 

[dvannoy]

is that your key field?
what I did there was put the Key in the grid as a column.

Dim StrID As Integer = CType(e.Item.FindControl("txtID"), TextBox).Text

I referenced it everywhere except in my update statement since you cannot update your key field.

 

[nicks60610]

Ok, I got past the index error, now it goes back to this"

Dim SunderlandOnly As String = CType(dgi.FindControl("SD DESCRIPTION"), TextBox).Text

And the error: Object reference not set to an instance of an object

If you look at the HTML code, this is what it shows:

<asp:TemplateColumn HeaderText="Sunderland Only">
<ItemTemplate>
<asp:TextBox id=TextBox1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SD DESCRIPTION") %>'>
</asp:TextBox>
</ItemTemplate>

Hence the reason I changed it to "SD DESCRIPTION", but it still does not want to go....could it be because there is a blank cell and it cannot update a blank cell??

 

[dvannoy]

TextBox id=TextBox1
that is the object name..

that's what it's trying to reference.

 

[dvannoy]

one more thing...right click the grid and edit each template column. name all the text boxes, labels etc to something that refers to that column. that makes it easier then just staying with textbox1 etc..

 

[nicks60610]

Right, genius! Thanks dvannoy....funny how you can stare at something for days and never see it - Thanks a ton!

 

[dvannoy]

glad to help.. I have pulled my hair out with the grid as well. good luck

 

[nicks60610]

Ok, so now that I have gotten through all that, it seems that my Update statement contains no data.

I see this error when I try to update:

Command text was not set for the command object

And it faults to:

dcmdUpdateQuality.ExecuteNonQuery()


When I did a Response.Write(dcmdUpdateQuality.CommandText)

It displayed nothing...

This the update statement:

Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Quality Metrics Database.mdb"

Dim conMetrics As OleDbConnection = New OleDbConnection(connectString)

conMetrics.Open()

Dim strUpdateQuality As String

Dim dcmdUpdateQuality As OleDbCommand

dcmdUpdateQuality = New OleDbCommand(strUpdateQuality, conMetrics)

strUpdateQuality = "UPDATE [All Plants Summary Data] SET [Sunderland Only] = @Sunderland , [Pieces Inspected] = @PcsInsp, [Pieces Defective] = @PcsDef, [Pieces Sold] = @PcsSold, [Product Complaints] = @ProdComp, [Pieces Sold Big Flats] = @PcsSoldBF, [Product Complaints Big Flats] = @ProdCompBF where [Month] = '" & Session("Month") & "' and [Year] = '" & Session("Year") & "' and [Plant] = '" & Session("Plant") & "'"

dcmdUpdateQuality.Parameters.Clear()
dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)

dcmdUpdateQuality.Parameters.Add("@PcsInsp", PcsInspected)

dcmdUpdateQuality.Parameters.Add("@PcsDef", PcsDefective)

dcmdUpdateQuality.Parameters.Add("@PcsSold", PcsSold)

dcmdUpdateQuality.Parameters.Add("@ProdComp", ProductComplaints)

dcmdUpdateQuality.Parameters.Add("@PcsSoldBF", PcsSoldBF)

dcmdUpdateQuality.Parameters.Add("@ProdCompBF", ProdCompBF)

dcmdUpdateQuality.ExecuteNonQuery()

Next

 

[dvannoy]

I think your problem is you need to specify where the data is..

dcmdUpdateQuality.Parameters.Add("@Sunderland", SunderlandOnly)

should be something like

dcmdUpdateQuality.Parameters.Add("@Sunderland", YourTextBox.Text)

again, I don't use access but that would make sense since you see no data with response.write

you see no data because you have not referenced where the data is.