|
 |
aspx_beginners thread: updating records in a database
Message #1 by jmnamahoe@h... on Fri, 19 Apr 2002 08:48:10
|
|
the cancle and edit portion of the page works fine. it when i try to
update the record that i receive an error: specified cast is not valid.
i'm tryin to piece the code together from the microsoft quick tutorial,
the 'asp.net for beg using vb.net'book, and a couple of asp.net pages. i
was able to create a page that adds records to a database via a form and
SQL statement. i took the code for that page and try to use it in this
page except for the SQL statement. i've tried other ways, but i can't seem
to get the data to update. my main problem is that i don't understand how
to get the data from the form into the SQL statement and two how to find
the data and update it. any help would be appreciated. thanx:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace="System.web.ui" %>
<%@ Import Namespace="system.web.ui.webcontrols" %>
<%@ Page Language="VB" Debug="true" %>
<html>
<script language="VB" runat="server">
Dim StrUpdate as String
Dim ObjCmd as OleDBCommand
Dim ObjConn as OleDBConnection
Dim strConn as string
Sub Page_Load(Sender As Object, E As EventArgs)
If Not (IsPostBack)
BindGrid()
End If
End Sub
Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub
Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub
Sub MyDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
StrUpdate = "UPDATE tbl_users SET usr_fname = " & strfnamefld
& " , usr_lname = " & strlnamefld & " , usr_id = " & struseridfld & ";"
StrConn = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=c:\begaspnet\db\testdb.mdb"
ObjConn = New OleDBConnection(StrConn)
ObjCmd = New OleDBCommand (StrUpdate, ObjConn)
Dim Cols As String() = {"usr_fname","usr_lname","usr_id"}
Dim NumCols As Integer = E.Item.Cells.Count
Dim I As Integer
For I=0 To NumCols 'skip first, second and last column
Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.Cells(I).Controls(0)
Dim ColValue As String = CurrentTextBox.Text
ObjCmd.Parameters(Cols(I-1)).Value = ColValue
Next
ObjConn.Open()
Try
ObjCmd.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>"
Catch Exp As Exception
Message.InnerHtml = Strfnamefld
Message.Style("color") = "red"
End Try
ObjConn.Close ()
BindGrid()
End Sub
Sub BindGrid()
StrConn = "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=c:\begaspnet\db\testdb.mdb"
ObjConn = New OleDBConnection(StrConn)
ObjConn.Open()
ObjCmd = New OleDBCommand ()
ObjCmd.Connection = ObjConn
ObjCmd.CommandText = "[qry_select_users]"
ObjCmd.CommandType = CommandType.StoredProcedure
MyDataGrid.DataSource = ObjCmd.ExecuteReader
(CommandBehavior.CloseConnection)
MyDataGrid.DataBind()
End Sub
</script>
<body style="font: 10pt verdana">
<form runat="server">
<h3><font face="Verdana">Updating a Row of Data w/ Read-Only
Column</font></h3>
<span id="Message" EnableViewState="false" style="font: arial 11pt;"
runat="server"/><p>
<ASP:DataGrid id="MyDataGrid" runat="server"
Width="800"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
AutoGenerateColumns="false"
>
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
UpdateText="Update" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="usr_fname" SortExpression="usr_fname"
DataField="usr_fname" ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="usr_lname" SortExpression="usr_lname"
DataField="usr_lname"/>
<asp:BoundColumn HeaderText="usr_id" SortExpression="usr_id"
DataField="usr_id"/>
</Columns>
</ASP:DataGrid>
</form>
</body>
</html>
Message #2 by "Chris Hudson" <chris@x...> on Fri, 19 Apr 2002 10:19:49
|
|
This is my code to update, which works.
Written in C#, but the basic flow is the same.
Gotta love my excellent error handling.
private void btnAddAgentSave_Click(object sender, System.EventArgs e)
{
try
{
OleDbConnection dbConn;
OleDbCommand dbCmd;
string strConn;
string sSQL;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=" + Server.MapPath("Agencies.mdb");
dbConn = new OleDbConnection (strConn);
dbConn.Open ();
if (btnAddAgentSave.Text == "Update")
{
sSQL = "UPDATE Agents " +
"SET Name='" + txtAddAgentName.Text + "', " +
"Phone='" + txtAddAgentPhone.Text + "', " +
"Email='" + txtAddAgentEmail.Text + "', " +
"Notes='" + txtAddAgentNotes.Text + "' " +
"WHERE ID=" + cboAgents.SelectedItem.Value;
dbCmd = new OleDbCommand (sSQL, dbConn);
dbCmd.ExecuteNonQuery ();
}
else
{
sSQL = "INSERT INTO Agents (Name, AgencyID,
Phone, Email, Notes) " +
"VALUES ( " +
" '" + txtAddAgentName.Text + "', " +
cboAgency.SelectedItem.Value + ", " +
" '" + txtAddAgentPhone.Text + "', " +
" '" + txtAddAgentEmail.Text + "', " +
" '" + txtAddAgentNotes.Text + "') ";
dbCmd = new OleDbCommand (sSQL, dbConn);
dbCmd.ExecuteNonQuery ();
}
dbConn.Close ();
if (btnAddAgentSave.Text == "Update")
{
RefreshAgent ();
}
else
{
cboAgents.Items.Clear ();
ReloadAgent ();
}
}
finally
{
}
EnableAddAgent (false);
EnableMainControls (true);
}
I cant see whats missing from yours
Message #3 by jmnamahoe@h... on Fri, 19 Apr 2002 20:42:27
|
|
thanx for the example provided, i appreciate the help. i have a few more
questions, when you have the time to respond. do you have columns setup
for your datagrid? in your columns, do you have a container that points to
a record in your database in the edititem column? i noticed that you do
not seem to make a request to the form for the values in the text, do you
just pull the text when the form is in an edit mode? again, thanx for your
help.
|
|
|
View Cart
