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 SqlParameterCol lection...Line 52:
myCommand.Param eters("@Account ID").Value = AccountID"
Here's some of my HTML:
<asp:BoundColum n DataField="Acco untID" SortExpression= "AccountID"
HeaderText="ACC OUNT #"></asp:BoundColumn >
<asp:TemplateCo lumn HeaderText="KEY ">
<ItemTemplate >
<asp:CheckBox ID="cbKey" Enabled="True" Checked='<%#
Databinder.Eval (Container.Data Item, "KeyAccount ") %>' Runat="server" />
</ItemTemplate>
</asp:TemplateCol umn>
....etc. for the other checkboxes.
Here's my code behind:
Imports System.Data
Imports System.Data.Sql Client
Imports System.Text
Public Class AccountAttribut es
Inherits System.Web.UI.P age
Protected myComponent As New Component1
Protected WithEvents DemoGrid As System.Web.UI.W ebControls.Data Grid
Protected WithEvents btnUpdate As System.Web.UI.W ebControls.Butt on
Dim strConnNewProd As String =
ConfigurationSe ttings.AppSetti ngs("strConnNew Prod")
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) 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=NATWWW04 DEV\INET_DEV;UI D=EndoscopySqlU ser;PWD=2er@9b7 6m;Database=End oNewProduct")
Dim cmd As SqlCommand = New SqlCommand("Sel ect AccountID,
CUSTOMER_N, CITY, STATE_OR_R, KeyAccount, DefensiveAccoun t, [ASC], Academic,
Office FROM AccountAttribut es INNER JOIN dbo.tblAccountL ist ON
tblAccountList. CUSTOMER__ = AccountAttribut es.AccountID WHERE SALES_TERR =
90011326 ORDER BY CUSTOMER_N ASC", cn)
'Dim cmd As SqlCommand = New SqlCommand("Sel ect AccountID,
CUSTOMER_N, CITY, STATE_OR_R, SALES_TERR, KeyAccount, DefensiveAccoun t,
Academic, [ASC], Office FROM AccountAttribut es INNER JOIN dbo.tblAccountL ist
ON tblAccountList. CUSTOMER__ = AccountAttribut es.AccountID WHERE SALES_TERR =
" & _ID, cn)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteRead er()
DemoGrid.DataSo urce = 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.EventArg s) Handles btnUpdate.Click
'Declarations
Dim cn As SqlConnection = New
SqlConnection(" Server=xxx;UID= xxx;PWD=xxx;Dat abase=xxx")
Dim strSQL As String = "Select AccountID, CUSTOMER_N, CITY,
STATE_OR_R, KeyAccount, DefensiveAccoun t, [ASC], Academic, Office FROM
AccountAttribut es INNER JOIN dbo.tblAccountL ist ON tblAccountList. CUSTOMER__
= AccountAttribut es.AccountID WHERE SALES_TERR = 90011326"
Dim myCommand As New SqlCommand(strS QL, 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.FindC ontrol("cbKey") , CheckBox)
cbDef = CType(dgi.FindC ontrol("cbDef") , CheckBox)
cbASC = CType(dgi.FindC ontrol("cbASC") , CheckBox)
cbAca = CType(dgi.FindC ontrol("cbAca") , CheckBox)
cbMD = CType(dgi.FindC ontrol("cbMD"), CheckBox)
'Issue an UPDATE statement to the database
myCommand.Param eters("@Account ID").Value = AccountID
myCommand.Param eters("@KeyAcco unt").Value = cbKey.Checked
myCommand.Param eters("@Defensi veAccount").Val ue = cbDef.Checked
myCommand.Param eters("@ASC").V alue = cbASC.Checked
myCommand.Param eters("@Academi c").Value = cbAca.Checked
myCommand.Param eters("@Office" ).Value = cbMD.Checked
Dim updateSQL As String = "UPDATE AccountAttribut es SET
KeyAccount = @KeyAccount, DefensiveAccoun t = @DefensiveAccou nt WHERE
AccountID = @AccountID"
'Open the connection, update the data, close the connection
cn.Open()
myCommand.Execu teNonQuery()
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!!
