Unable to bind datagrid item to vb variable

[HawkeyeD]

Hello everyone, I am having a bit of a problem with my asp.net code (vb). I am attempting to update a datagrid on my page. This is something I have already completed in a previous page. The only difference here that I can tell is that I have made the datagrid on this problem page an updatable datagrid without putting each row into edit mode (using textboxes instead of labels).

When I attempt to assign the values of my datagrid into my vb code I am getting that dreaded "Object reference not set to an instance of an object."

The assignment sits inside a for each. Yet if I pull it out and put it outside the for each (just to see if it will fail again) I receive the same error message. I am stumped. I can't figure out why it works on one asp page, but not this one.

Here is my code. Hopefully someone will be able to help me. P.S. This is for an school project and I am quite new to ASP (about 2 wks new Smile ). This is about the last step I have in the project and it is due this monday. So I am kinda sweating bullets here. Any help would be greatly appreciated.

Thank you in advance


<div style="text-align: center; background-color: lightgrey;">
<br />

<asp:checkboxlist ID="chkFocusSearch" runat="server" AutoPostBack = "false" RepeatDirection="Horizontal"
DataValueField="tid" DataTextField="Type_" Width="360px" >
</asp:checkboxlist>

<asp:Button ID="btnSlctAll" runat="server" OnClick="btnSlctAll_Click" Text="Selet All"
Width="114px" />&nbsp;

<asp:Button ID="btnSlctNone" runat="server" Text="Select None"
Width="114px" OnClick="btnSlctNone_Click" />&nbsp;

<asp:Button ID="btnRefresh" runat="server" Text="Refresh View" OnClick="RefreshInv"/>&nbsp;

<asp:DataGrid ID="mysqlInvGrid" runat="server" CellPadding="4" ForeColor="#333333" ShowFooter="True" BorderColor="Black"
OnItemCommand="Update"
AutoGenerateColumns ="False" BorderWidth="8px" CellSpacing="3" style="border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid" Height="299px" Width="360px">

<Columns>
<asp:BoundColumn DataField="prodid" Visible = "false" />
<asp:BoundColumn DataField="item" Headertext="Item" />

<asp:TemplateColumn Visible = "False">
<ItemTemplate>
<asp:Label ID="aspProdID" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "prodid") %>' />
</ItemTemplate>
</asp:TemplateColumn>


<asp:TemplateColumn HeaderText="Quantity">

<FooterTemplate>
<asp:Button ID="btnUpdate" runat="server" Text = "Update" />
</FooterTemplate>

<Itemtemplate>
<asp:TextBox ID="aspAmount" runat="server" Columns="5" Text='<%# Container.DataItem("amount") %>' />
</Itemtemplate>
</asp:TemplateColumn>

<asp:BoundColumn DataField="measure" Headertext="Measurement" />


</Columns>




Sub Update(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs)
Dim dgI As DataGridItem
Dim strSql As String
Dim sqlCmd As New MySqlCommand

sqlCmd = New MySqlCommand()
mysqlConn = DBConnect.Connect
sqlCmd.Connection = mysqlConn

Try

For Each dgI In mysqlInvGrid.Items
'get the Prod ID and the new quantity
Dim strProdID As String = CType(e.Item.FindControl("aspProdID"), Label).Text
Dim strQuantity As String = CType(e.Item.FindControl("aspAmount"), TextBox).Text

Dim intProdID As Integer = CInt(strProdID)
Dim intQuantity As Integer = CInt(strQuantity)

'Update the DB with the information we have gathered.

strSql = "update Inventory set quantity = " & intQuantity & " where prodID = " & intProdID

sqlCmd = New MySqlCommand(strSql, mysqlConn)
sqlCmd.ExecuteNonQuery()

Next
Catch ex As MySqlException
MsgBox("Insert failed", MsgBoxStyle.Exclamation, "Insert Status")
MsgBox("Error returned was '" & ex.Message, MsgBoxStyle.Information, "Error Message")
MsgBox("SQL command was: '" & strSql)


Catch ex As Exception

MsgBox("Insert failed", MsgBoxStyle.Exclamation, "Insert Status")
MsgBox("Error returned was '" & ex.Message, MsgBoxStyle.Information, "Error Message")

Finally

mysqlConn.Close()
BindGrid()

End Try


Here is my binding code. Thought it might be helpful too.


Sub BindGrid()

Dim sqlDataAdapter As MySqlDataAdapter
Dim sqlDS As DataSet
Dim sqlCmd As String
Try

mysqlConn = DBConnect.Connect()

sqlCmd = "select inventory.prodid as 'prodid', products.description as 'Item', inventory.quantity as 'amount', " _
& "products.measure as 'measure' from inventory" _
& " inner join products ON inventory.prodID = products.prodID"

sqlDataAdapter = New MySqlDataAdapter(sqlCmd, mysqlConn)

sqlDS = New DataSet()
sqlDataAdapter.Fill(sqlDS, "inventory")

mysqlInvGrid.DataSource = sqlDS
mysqlInvGrid.DataBind()




Catch e As MySqlException
MsgBox("Error returned is " & e.Message & " inner message " & e.InnerException.Message)

Catch e As SqlClient.SqlException
MsgBox("Error returned is " & e.Message & " inner message " & e.InnerException.Message)

Catch e As Exception
MsgBox("Error returned is " & e.Message & " inner message " & e.InnerException.Message)

Finally
mysqlConn.Close()
GetType_()

End Try

End Sub



GetType_() is just used to populate my checkboxlist

[HawkeyeD]

AAARRRGGHHH!!!

Been working on this for an hour and I spend 5 minutes to type up this post. Take another look at it and figure out the problem.


When I into the For each of my datagrid I made the mistake of passing in 'e' which was the datagridcommandeventargs handler and not the actual datagrid item.

the correct code is:


Sub Update(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs)
Dim dgI As DataGridItem
Dim strSql As String
Dim sqlCmd As New MySqlCommand

sqlCmd = New MySqlCommand()
mysqlConn = DBConnect.Connect
sqlCmd.Connection = mysqlConn

Try

For Each dgI In mysqlInvGrid.Items
'get the Prod ID and the new quantity
Dim strProdID As String = CType(dgI.FindControl("aspProdID"), Label).Text
Dim strQuantity As String = CType(dgI.FindControl("aspAmount"), TextBox).Text

Dim intProdID As Integer = CInt(strProdID)
Dim intQuantity As Integer = CInt(strQuantity)

'Update the DB with the information we have gathered.

strSql = "update Inventory set quantity = " & intQuantity & " where prodID = " & intProdID

sqlCmd = New MySqlCommand(strSql, mysqlConn)
sqlCmd.ExecuteNonQuery()

Next



Have a good night all.