I have been trying to formulate a fully editabe datagrid for a couple of days with no luck. I have used code from 4guys and some other sites and am at the point where I can render the datagrid correctly (as a bouncolumn and template column-textbox) but when I try to update the database it all falls apart. I am getting "input string was not in a correct format" and the error references ...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
I suspect the problem lies in the area of primary indexes and such. The table I am using is a simple two-column table with usernames, passwords. Username is the primary field. Here is the actual code I am using...
<code>
<%@. Page Explicit="True" Language="VB" Debug="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<html>
<script runat="server">
Dim Connect As OleDbConnection = New OleDbConnection
Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
Dim DS As DataSet = New DataSet
Dim ConnectString, SelectStatement As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not IsPostBack Then
SelectStatement = "Select * From Table"
ConnectString = "Provider=SQLOLEDB;UID=;PWD=;" & "Data Source=;Initial Catalog=;"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = New oleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Connection.Open
Adapter.Fill(DS, "Items")
sls.Datasource = DS.Tables("Items")
Page.Databind
Connect.Close()
End If
End Sub
Sub Click(sender As Object, e As EventArgs)
Dim myConnection as New OleDbConnection(ConnectString)
Dim updateSQL as String = "UPDATE Table SET password = @.Password WHERE Username = @.ID"
Dim myCommand as New oleDbCommand(updateSQL, myConnection)
Dim dgi as DataGridItem
For Each dgi in sls.Items
'Read in the Primary Key Field
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
Dim password as String = CType(dgi.FindControl("txtPass"), TextBox).Text
'Issue an UPDATE statement...
myCommand.Parameters.Clear()
myCommand.Parameters.Add("@.ID", id)
myCommand.Parameters.Add("@.Password", password)
myCommand.ExecuteNonQuery()
Next
End Sub
</script>
<body>
<form runat="Server">
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">
<Columns>
<asp:BoundColumn HeaderText="UserName" datafield="Username"/>
<asp:TemplateColumn HeaderText="Password">
<ItemTemplate>
<asp:TextBox runat="server" id="txtPass" Columns="10"
Text='<%# DataBinder.Eval(Container, "DataItem.Password") %>' />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid><asp:button id="Update" text="Update All" runat="Server" onclick="Click"/>
</form>
</body>
</html>
</code>
Anyone have any idea as to why the id variable (error message above) is causing problems?
You have the DataKeyField="UserName" in your datagrid setup rather than the Id...
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">
So when you try to pass the key field to the procedure with this line...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
it's going to error because it can't covnert an alphanumeric username to an int32. Change the DataKeyField to match your ID field and that should clear it up.|||
RJA,
Thanks for the response. I have datakeyfield set to "Username" because "Username" is the unique (primary key) column in that table. There are only two columns, username and password. I understand what you are saying about converting alphanumeric to integer but what would I use as the "ID field" you mentioned at the end of your post?
Thanks,
CM
No comments:
Post a Comment