Completely unable to update an access db from a datagrid

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

People, I am at my wit's end.

I am using the exact code from
http://aspnet.4guysfromrolla.com/articles/071002-1.aspx

And yet, the code does not manage to update the database. When I go to
update my database, I am able to get the form fields, and I am able to
replace the data, but when I go "update", the old data remains.

I have done everything correct, including this:
http://datawebcontrols.com/faqs/Editing/EditedValuesNotSaved.shtml
And yet the db will still not update.

Below is a copy of my code:


<%@ Page Language="VB" Debug="true" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.OleDb" %>
<% @Import Namespace="System.Configuration" %>
<%@ OutputCache Duration="20" VaryByParam="*" Location="None"
VaryByHeader="User-Agent"%>
<%@ Register TagPrefix="METZ" TagName="Meta" Src="/ssi/meta.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Head" Src="/ssi/head.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Foot" Src="/ssi/foot.ascx" %>
<METZ:Meta Id="ctlMeta" Runat="Server" />
<METZ:Head Id="ctlHead" Runat="Server" />
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub

Sub BindData()
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim myCmd as New OleDbCommand("SELECT * FROM tblCarLinks", myConn)
myConn.Open()
dgCarLinks.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgCarLinks.DataBind()
myConn.Close() 'Close the connection
End Sub

Sub dgCarLinks_Edit(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = e.Item.ItemIndex
BindData()
End Sub

Sub dgCarLinks_Update(sender As Object, e As DataGridCommandEventArgs)
'Read in the values of the updated row
Dim iID as Integer = e.Item.Cells(0).Text
Dim strName as String = CType(e.Item.Cells(1).Controls(0),
TextBox).Text
Dim strURL as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
'Construct the SQL statement using Parameters
Dim strSQL as String = "UPDATE [tblCarLinks] SET
[CarLinksName]=@Name, [CarLinksURL]=@URL WHERE [ID]=@ID"

Dim objConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
objConn.Open()

Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
' myCommand.CommandType = CommandType.Text

' Add Parameters to the SQL query
Dim parameterID as OleDbParameter = new OleDbParameter("@ID",
OleDbType.Integer)
parameterID.Value = iID
myCommand.Parameters.Add(parameterID)

Dim parameterName as OleDbParameter = new OleDbParameter("@name",
OleDbType.VarWChar)
parameterName.Value = strName
myCommand.Parameters.Add(parameterName)

Dim parameterURL as OleDbParameter = new OleDbParameter("@URL",
OleDbType.VarWChar)
parameterURL.Value = strURL
myCommand.Parameters.Add(parameterURL)

myCommand.ExecuteNonQuery() 'Execute the UPDATE query

objConn.Close() 'Close the connection
'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
Sub dgCarLinks_Cancel(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
</script>
<div id="content">
<h2>Cool Car Links</h2>
<form runat="server">
<asp:datagrid id="dgCarLinks" runat="server"
showheader="true"
autogeneratecolumns="False"
edititemstyle-backcolor="#ffcccc"
oneditcommand="dgCarLinks_Edit"
onupdatecommand="dgCarLinks_Update"
oncancelcommand="dgCarLinks_Cancel"
cellpadding="10"
width="100%">
<HeaderStyle backcolor="#000000" forecolor="#ffffff" font-bold="true"
horizontalalign="center" />
<ItemStyle backcolor="#ffffff" forecolor="#000000" />
<AlternatingItemStyle backcolor="#cccccc" />
<Columns>
<asp:BoundColumn DataField="ID" HeaderText="ID" ReadOnly="true" />
<asp:BoundColumn DataField="CarLinksName" HeaderText="Site Name" />
<asp:BoundColumn DataField="CarLinksURL" HeaderText="Site URL" />
<asp:EditCommandColumn EditText="Edit" ButtonType="PushButton"
UpdateText="Update" CancelText="Cancel" />
</Columns>
</asp:datagrid>
</form>
</div>
<METZ:Foot Id="ctlFoot" Runat="Server" />



I hope that someone with sharper eyes than me can figure this monster out.

....Geshel
--
**********************************************************************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
**********************************************************************
 
Rogas69 said:
are you sure that iID parameter value is OK?
Peter
Dim strSQL as String = "UPDATE [tblCarLinks] SET [CarLinksName]=@Name,
[CarLinksURL]=@URL WHERE [ID]=@ID"
Nope. That wasn't the problem (just changed both, no change). Besides,
iID was the Integer. @ID was the parameter.

Thanks anyways
....Geshel
--
**********************************************************************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
**********************************************************************
 
Rene,
OleDbParameters are added by position not by name. Usually you indicate
them in the CommandText with just a ? character. You need to add your
parameters in the order they occur in the Update statement, i.e. Name, URL,
and then ID. This should work assuming all the variable types match up.

Ron Allen
René Kabis said:
People, I am at my wit's end.

I am using the exact code from
http://aspnet.4guysfromrolla.com/articles/071002-1.aspx

And yet, the code does not manage to update the database. When I go to
update my database, I am able to get the form fields, and I am able to
replace the data, but when I go "update", the old data remains.

I have done everything correct, including this:
http://datawebcontrols.com/faqs/Editing/EditedValuesNotSaved.shtml
And yet the db will still not update.

Below is a copy of my code:


<%@ Page Language="VB" Debug="true" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.OleDb" %>
<% @Import Namespace="System.Configuration" %>
<%@ OutputCache Duration="20" VaryByParam="*" Location="None"
VaryByHeader="User-Agent"%>
<%@ Register TagPrefix="METZ" TagName="Meta" Src="/ssi/meta.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Head" Src="/ssi/head.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Foot" Src="/ssi/foot.ascx" %>
<METZ:Meta Id="ctlMeta" Runat="Server" />
<METZ:Head Id="ctlHead" Runat="Server" />
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Sub BindData()
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim myCmd as New OleDbCommand("SELECT * FROM tblCarLinks", myConn)
myConn.Open()
dgCarLinks.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgCarLinks.DataBind() myConn.Close() 'Close the connection
End Sub

Sub dgCarLinks_Edit(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = e.Item.ItemIndex
BindData()
End Sub

Sub dgCarLinks_Update(sender As Object, e As DataGridCommandEventArgs)
'Read in the values of the updated row
Dim iID as Integer = e.Item.Cells(0).Text
Dim strName as String = CType(e.Item.Cells(1).Controls(0),
TextBox).Text
Dim strURL as String = CType(e.Item.Cells(2).Controls(0),
TextBox).Text
'Construct the SQL statement using Parameters
Dim strSQL as String = "UPDATE [tblCarLinks] SET [CarLinksName]=@Name,
[CarLinksURL]=@URL WHERE [ID]=@ID"

Dim objConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
objConn.Open()

Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
' myCommand.CommandType = CommandType.Text

' Add Parameters to the SQL query
Dim parameterID as OleDbParameter = new OleDbParameter("@ID",
OleDbType.Integer)
parameterID.Value = iID
myCommand.Parameters.Add(parameterID)

Dim parameterName as OleDbParameter = new OleDbParameter("@Name",
OleDbType.VarWChar)
parameterName.Value = strName
myCommand.Parameters.Add(parameterName)

Dim parameterURL as OleDbParameter = new OleDbParameter("@URL",
OleDbType.VarWChar)
parameterURL.Value = strURL
myCommand.Parameters.Add(parameterURL)

myCommand.ExecuteNonQuery() 'Execute the UPDATE query

objConn.Close() 'Close the connection
'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
Sub dgCarLinks_Cancel(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
</script>
<div id="content">
<h2>Cool Car Links</h2>
<form runat="server">
<asp:datagrid id="dgCarLinks" runat="server"
showheader="true"
autogeneratecolumns="False"
edititemstyle-backcolor="#ffcccc"
oneditcommand="dgCarLinks_Edit"
onupdatecommand="dgCarLinks_Update"
oncancelcommand="dgCarLinks_Cancel"
cellpadding="10"
width="100%">
<HeaderStyle backcolor="#000000" forecolor="#ffffff" font-bold="true"
horizontalalign="center" />
<ItemStyle backcolor="#ffffff" forecolor="#000000" />
<AlternatingItemStyle backcolor="#cccccc" />
<Columns>
<asp:BoundColumn DataField="ID" HeaderText="ID" ReadOnly="true" />
<asp:BoundColumn DataField="CarLinksName" HeaderText="Site Name" />
<asp:BoundColumn DataField="CarLinksURL" HeaderText="Site URL" />
<asp:EditCommandColumn EditText="Edit" ButtonType="PushButton"
UpdateText="Update" CancelText="Cancel" />
</Columns>
</asp:datagrid>
</form>
</div>
<METZ:Foot Id="ctlFoot" Runat="Server" />



I hope that someone with sharper eyes than me can figure this monster out.

...Geshel
--
**********************************************************************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
**********************************************************************
 
Here you go, again! See the thread posted yesterday by JeremyGrand.

René Kabis said:
People, I am at my wit's end.

I am using the exact code from
http://aspnet.4guysfromrolla.com/articles/071002-1.aspx

And yet, the code does not manage to update the database. When I go to
update my database, I am able to get the form fields, and I am able to
replace the data, but when I go "update", the old data remains.

I have done everything correct, including this:
http://datawebcontrols.com/faqs/Editing/EditedValuesNotSaved.shtml
And yet the db will still not update.

Below is a copy of my code:


<%@ Page Language="VB" Debug="true" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.OleDb" %>
<% @Import Namespace="System.Configuration" %>
<%@ OutputCache Duration="20" VaryByParam="*" Location="None"
VaryByHeader="User-Agent"%>
<%@ Register TagPrefix="METZ" TagName="Meta" Src="/ssi/meta.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Head" Src="/ssi/head.ascx" %>
<%@ Register TagPrefix="METZ" TagName="Foot" Src="/ssi/foot.ascx" %>
<METZ:Meta Id="ctlMeta" Runat="Server" />
<METZ:Head Id="ctlHead" Runat="Server" />
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub

Sub BindData()
Dim myConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
Dim myCmd as New OleDbCommand("SELECT * FROM tblCarLinks", myConn)
myConn.Open()
dgCarLinks.DataSource =
myCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgCarLinks.DataBind()
myConn.Close() 'Close the connection
End Sub

Sub dgCarLinks_Edit(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = e.Item.ItemIndex
BindData()
End Sub

Sub dgCarLinks_Update(sender As Object, e As DataGridCommandEventArgs)
'Read in the values of the updated row
Dim iID as Integer = e.Item.Cells(0).Text
Dim strName as String = CType(e.Item.Cells(1).Controls(0),
TextBox).Text
Dim strURL as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
'Construct the SQL statement using Parameters
Dim strSQL as String = "UPDATE [tblCarLinks] SET
[CarLinksName]=@Name, [CarLinksURL]=@URL WHERE [ID]=@ID"

Dim objConn as New
OleDbConnection(ConfigurationSettings.AppSettings("strConn"))
objConn.Open()

Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
' myCommand.CommandType = CommandType.Text

' Add Parameters to the SQL query
Dim parameterID as OleDbParameter = new OleDbParameter("@ID",
OleDbType.Integer)
parameterID.Value = iID
myCommand.Parameters.Add(parameterID)

Dim parameterName as OleDbParameter = new OleDbParameter("@Name",
OleDbType.VarWChar)
parameterName.Value = strName
myCommand.Parameters.Add(parameterName)

Dim parameterURL as OleDbParameter = new OleDbParameter("@URL",
OleDbType.VarWChar)
parameterURL.Value = strURL
myCommand.Parameters.Add(parameterURL)

myCommand.ExecuteNonQuery() 'Execute the UPDATE query

objConn.Close() 'Close the connection
'Finally, set the EditItemIndex to -1 and rebind the DataGrid
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
Sub dgCarLinks_Cancel(sender As Object, e As DataGridCommandEventArgs)
dgCarLinks.EditItemIndex = -1
BindData()
End Sub
</script>
<div id="content">
<h2>Cool Car Links</h2>
<form runat="server">
<asp:datagrid id="dgCarLinks" runat="server"
showheader="true"
autogeneratecolumns="False"
edititemstyle-backcolor="#ffcccc"
oneditcommand="dgCarLinks_Edit"
onupdatecommand="dgCarLinks_Update"
oncancelcommand="dgCarLinks_Cancel"
cellpadding="10"
width="100%">
<HeaderStyle backcolor="#000000" forecolor="#ffffff" font-bold="true"
horizontalalign="center" />
<ItemStyle backcolor="#ffffff" forecolor="#000000" />
<AlternatingItemStyle backcolor="#cccccc" />
<Columns>
<asp:BoundColumn DataField="ID" HeaderText="ID" ReadOnly="true" />
<asp:BoundColumn DataField="CarLinksName" HeaderText="Site Name" />
<asp:BoundColumn DataField="CarLinksURL" HeaderText="Site URL" />
<asp:EditCommandColumn EditText="Edit" ButtonType="PushButton"
UpdateText="Update" CancelText="Cancel" />
</Columns>
</asp:datagrid>
</form>
</div>
<METZ:Foot Id="ctlFoot" Runat="Server" />



I hope that someone with sharper eyes than me can figure this monster out.

...Geshel
--
**********************************************************************
My reply-to is an automatically monitored spam honeypot. Do not use it
unless you want to be blacklisted by SpamCop. Please reply to my first
name at my last name dot org.
**********************************************************************
 
Back
Top