Completely unable to update an access db from a datagrid

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.
**********************************************************************
 
G

Guest

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"
Click to expand...
Click to expand...
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.
**********************************************************************
 
R

Ron Allen

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.
**********************************************************************
Click to expand...
 
N

Norman Yuan

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.
**********************************************************************
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[Newbie Problem]DataGrid OnUpdateCommand "InvalidCastException" problem 2
updating from datagrid 1
Can' update access mdb with asp.net VB Microsoft.Jet.OLEDB.4.0 4
Updating an Access Yes/No field from a datagrid 1
[Newbie Problem] BC30311: Value of "TableCell' cannot be convertedto 'Integer'. 3
?? dropdownlist in datagrid, not defaulting to current value ??? THANK YOU 2
Update from datagrid 3
Unable to Update with DataGrid 5

Top