Hi. I'm just getting started with vb.NET development in VS 2003. I have a
datagrid that lists a bunch of customers (hospitals). Next to the customer
number and name is a bunch of checkboxes that I want to use to check whether
it's a key account, defensive account, ambulatory surgery center, academic
hospital, or MD office. The grid opens with the checkboxes in edit mode and
shows whether a box is checked or not. This initial binding is working fine.
The problem is when I try to use the edits to update the SQL database (for
all rows at once). I don't think my unique row identifier (AccountID) is
working. I'm getting the error "An SqlParameter with ParameterName
'@AccountID' is not contained by this SqlParameterCollection...Line 52:
myCommand.Parameters("@AccountID").Value = AccountID"
Here's some of my HTML:
<asp:BoundColumn DataField="AccountID" SortExpression="AccountID"
HeaderText="ACCOUNT #"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="KEY">
<ItemTemplate>
<asp:CheckBox ID="cbKey" Enabled="True" Checked='<%#
Databinder.Eval(Container.DataItem, "KeyAccount") %>' Runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
....etc. for the other checkboxes.
Here's my code behind:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Public Class AccountAttributes
Inherits System.Web.UI.Page
Protected myComponent As New Component1
Protected WithEvents DemoGrid As System.Web.UI.WebControls.DataGrid
Protected WithEvents btnUpdate As System.Web.UI.WebControls.Button
Dim strConnNewProd As String =
ConfigurationSettings.AppSettings("strConnNewProd" )
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
'Create a SqlConnection object.
'Modify the connection string as necessary for your environment.
Dim cn As SqlConnection = New
SqlConnection("Server=NATWWW04DEV\INET_DEV;UID=End oscopySqlUser;PWD=2er@9b76m;Database=EndoNewProduc t")
Dim cmd As SqlCommand = New SqlCommand("Select AccountID,
CUSTOMER_N, CITY, STATE_OR_R, KeyAccount, DefensiveAccount, [ASC], Academic,
Office FROM AccountAttributes INNER JOIN dbo.tblAccountList ON
tblAccountList.CUSTOMER__ = AccountAttributes.AccountID WHERE SALES_TERR =
90011326 ORDER BY CUSTOMER_N ASC", cn)
'Dim cmd As SqlCommand = New SqlCommand("Select AccountID,
CUSTOMER_N, CITY, STATE_OR_R, SALES_TERR, KeyAccount, DefensiveAccount,
Academic, [ASC], Office FROM AccountAttributes INNER JOIN dbo.tblAccountList
ON tblAccountList.CUSTOMER__ = AccountAttributes.AccountID WHERE SALES_TERR =
" & _ID, cn)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
DemoGrid.DataSource = reader
DataBind()
reader.Close()
cn.Close()
End If
End Sub
'Use this to update all records at once
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
'Declarations
Dim cn As SqlConnection = New
SqlConnection("Server=xxx;UID=xxx;PWD=xxx;Database =xxx")
Dim strSQL As String = "Select AccountID, CUSTOMER_N, CITY,
STATE_OR_R, KeyAccount, DefensiveAccount, [ASC], Academic, Office FROM
AccountAttributes INNER JOIN dbo.tblAccountList ON tblAccountList.CUSTOMER__
= AccountAttributes.AccountID WHERE SALES_TERR = 90011326"
Dim myCommand As New SqlCommand(strSQL, cn)
Dim i As Integer
Dim dgi As DataGridItem
Dim AccountID As Integer
Dim cbKey, cbDef, cbASC, cbAca, cbMD As CheckBox
'Loop through the grid
For i = 0 To DemoGrid.Items.Count - 1
'Read in the values from the TemplateColumns
dgi = DemoGrid.Items(i)
AccountID = CType(AccountID, Integer)
cbKey = CType(dgi.FindControl("cbKey"), CheckBox)
cbDef = CType(dgi.FindControl("cbDef"), CheckBox)
cbASC = CType(dgi.FindControl("cbASC"), CheckBox)
cbAca = CType(dgi.FindControl("cbAca"), CheckBox)
cbMD = CType(dgi.FindControl("cbMD"), CheckBox)
'Issue an UPDATE statement to the database
myCommand.Parameters("@AccountID").Value = AccountID
myCommand.Parameters("@KeyAccount").Value = cbKey.Checked
myCommand.Parameters("@DefensiveAccount").Value = cbDef.Checked
myCommand.Parameters("@ASC").Value = cbASC.Checked
myCommand.Parameters("@Academic").Value = cbAca.Checked
myCommand.Parameters("@Office").Value = cbMD.Checked
Dim updateSQL As String = "UPDATE AccountAttributes SET
KeyAccount = @KeyAccount, DefensiveAccount = @DefensiveAccount WHERE
AccountID = @AccountID"
'Open the connection, update the data, close the connection
cn.Open()
myCommand.ExecuteNonQuery()
cn.Close()
Next
End Sub
I'M REALLY FIRED UP ABOUT LEARNING HOW TO DO THIS AND I'VE SPENT AT LEAST 3
DAYS TRYING TO FIGURE OUT HOW TO DO IT TO NO AVAIL :( THANKS FOR ANY HELP!!
