updating,deleting,adding to a datalist does not work 3

[edelwater]

after pressing update, delete or add nothing happens e.g. no records are changed why ?

all in one file :

<%@ import Namespace=&quot;System.Data&quot; %>
<%@ import Namespace=&quot;System.Data.Oledb&quot; %>
<%@ Page Language=&quot;VB&quot; Debug=&quot;true&quot; %>
<script language=&quot;vb&quot; runat=&quot;server&quot;>

' #####################################################################
' # Returns a DataReader object
' #####################################################################

Protected Function LoadAdressListData() As OledbDatareader
Dim strSQL As String = &quot;SELECT * from adressen WHERE type='&quot; & Category.SelectedItem.Value & &quot;'&quot;
Dim strConnection As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\premfs4\sites\premium9\edelwater\database\edward.mdb;&quot; & _
&quot;Persist Security Info=False&quot;

Dim objConnection as New OledbConnection(strConnection)
objConnection.Open()
Dim objCommand as OleDbCommand
objCommand = new OledbCommand (strSQL, objConnection)
Dim objDataReader as OledbDataReader
objDataReader = objCommand.ExecuteReader(system.data.CommandBehavior.CloseConnection)

Return objDataReader
End Function

' #####################################################################
' # Bind the DataList to the DataSource
' #####################################################################

Sub bindList()
MyDataList.DataSource = LoadAdressListData
MyDataList.DataBind()
End Sub

' #####################################################################
' # On Page Load
' #####################################################################

Sub Page_Load(Src As Object, E As EventArgs)
If Not (Page.IsPostBack)
bindList()
End If
End Sub

' #####################################################################
' # After Selecting a Category
' #####################################################################

Sub Category_Select(Sender As Object, E As EventArgs)
bindList()
End Sub

' #####################################################################
' # DataList Edit
' #####################################################################

Sub DEDR_Edit(Sender As Object, E As DataListCommandEventArgs)
MyDataList.EditItemIndex = CInt(e.Item.ItemIndex)
bindList()
End Sub

' #####################################################################
' # DataList Update
' #####################################################################

Sub DEDR_Update(Sender As Object, E As DataListCommandEventArgs)
Dim strSQL As String = &quot;SELECT * from adressen WHERE type='&quot; & Category.SelectedItem.Value & &quot;'&quot;
Dim strConnection As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\premfs4\sites\premium9\edelwater\database\edward.mdb;&quot; & _
&quot;Persist Security Info=False&quot;
Dim da As New OleDbDataAdapter(strSQL, strConnection)
Dim ds As DataSet = New DataSet()
da.fill(ds,&quot;adressen&quot
Dim row As Integer = CInt(e.Item.ItemIndex)
Dim EditText As Textbox

EditText = e.Item.FindControl (&quot;naam&quot
ds.Tables(0).Rows(row).Item(&quot;naam&quot = EditText.Text
EditText = e.Item.FindControl (&quot;straat&quot
ds.Tables(0).Rows(row).Item(&quot;straat&quot = EditText.Text
EditText = e.Item.FindControl (&quot;postcode&quot
ds.Tables(0).Rows(row).Item(&quot;postcode&quot = EditText.Text
EditText = e.Item.FindControl (&quot;stad&quot
ds.Tables(0).Rows(row).Item(&quot;stad&quot = EditText.Text
EditText = e.Item.FindControl (&quot;telefoon1&quot
ds.Tables(0).Rows(row).Item(&quot;telefoon1&quot = EditText.Text
EditText = e.Item.FindControl (&quot;telefoon2&quot
ds.Tables(0).Rows(row).Item(&quot;telefoon2&quot = EditText.Text
EditText = e.Item.FindControl (&quot;mobile1&quot
ds.Tables(0).Rows(row).Item(&quot;mobile1&quot = EditText.Text
EditText = e.Item.FindControl (&quot;mobile2&quot
ds.Tables(0).Rows(row).Item(&quot;mobile2&quot = EditText.Text
EditText = e.Item.FindControl (&quot;email1&quot
ds.Tables(0).Rows(row).Item(&quot;email1&quot = EditText.Text
EditText = e.Item.FindControl (&quot;email2&quot
ds.Tables(0).Rows(row).Item(&quot;email2&quot = EditText.Text
EditText = e.Item.FindControl (&quot;url&quot
ds.Tables(0).Rows(row).Item(&quot;url&quot = EditText.Text
EditText = e.Item.FindControl (&quot;icq&quot
ds.Tables(0).Rows(row).Item(&quot;icq&quot = EditText.Text
EditText = e.Item.FindControl (&quot;nick&quot
ds.Tables(0).Rows(row).Item(&quot;nick&quot = EditText.Text
EditText = e.Item.FindControl (&quot;comments&quot
ds.Tables(0).Rows(row).Item(&quot;comments&quot = EditText.Text


ds.AcceptChanges
da.Update(ds,&quot;adressen&quot
Session(&quot;MyDataList&quot = Nothing
myDataList.EditItemIndex = -1
bindList()
End Sub

Sub DEDR_Cancel(Sender As Object, E As DataGridCommandEventArgs)
MyDataList.EditItemIndex = - 1
Session(&quot;MyDataList&quot = Nothing
bindList()
End Sub

Sub DEDR_Delete(Sender As Object, E As DataGridCommandEventArgs)
Dim strSQL As String = &quot;SELECT * from adressen WHERE type='&quot; & Category.SelectedItem.Value & &quot;'&quot;
Dim strConnection As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\premfs4\sites\premium9\edelwater\database\edward.mdb;&quot; & _
&quot;Persist Security Info=False&quot;
Dim da As New OleDbDataAdapter(strSQL, strConnection)
Dim ds As DataSet = New DataSet()
da.fill(ds,&quot;adressen&quot
Dim row As Integer = CInt(e.Item.ItemIndex)

ds.Tables(0).Rows(row).Delete
ds.AcceptChanges
da.Update(ds,&quot;adressen&quot
Session(&quot;MyDataList&quot = Nothing
myDataList.EditItemIndex = -1
bindList()
End Sub

Sub DEDR_Add(Sender As Object, E As DataGridCommandEventArgs)
Dim strSQL As String = &quot;SELECT * from adressen WHERE type='&quot; & Category.SelectedItem.Value & &quot;'&quot;
Dim strConnection As String = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\premfs4\sites\premium9\edelwater\database\edward.mdb;&quot; & _
&quot;Persist Security Info=False&quot;
Dim da As New OleDbDataAdapter(strSQL, strConnection)
Dim ds As DataSet = New DataSet()
da.fill(ds,&quot;adressen&quot

Dim newRow As DataRow
newRow = ds.Tables(0).NewRow()
newRow.Item(&quot;type&quot = Category.SelectedItem.Value
newRow.Item(&quot;naam&quot = &quot;&quot;
newRow.Item(&quot;straat&quot = &quot;&quot;
newRow.Item(&quot;postcode&quot = &quot;&quot;
newRow.Item(&quot;telefoon1&quot = &quot;&quot;
newRow.Item(&quot;telefoon2&quot = &quot;&quot;
newRow.Item(&quot;mobile1&quot = &quot;&quot;
newRow.Item(&quot;mobile2&quot = &quot;&quot;
newRow.Item(&quot;email1&quot = &quot;&quot;
newRow.Item(&quot;email2&quot = &quot;&quot;
newRow.Item(&quot;url&quot = &quot;&quot;
newRow.Item(&quot;icq&quot = &quot;&quot;
newRow.Item(&quot;nick&quot = &quot;&quot;
newRow.Item(&quot;comments&quot = &quot;&quot;

ds.Tables(0).Rows.Add(newRow)

ds.AcceptChanges
da.Update(ds,&quot;adressen&quot
Session(&quot;MyDataList&quot = Nothing
myDataList.EditItemIndex = -1
bindList()

End Sub

</script>
<html>
<head>
<style>
<!--
TD{ font-family: Arial, sans-serif;
font-size: 10px;
font-weight: bold;
color: #000000;
b{ color: red }
-->
</style>
</head>
<body style=&quot;font: 10pt verdana&quot;>

<asp:Label id=&quot;ErrorMessage&quot; runat=&quot;server&quot; /><br/>

<h3>Adresboek</h3>
<form runat=&quot;server&quot;>



<table style=&quot;font: 10pt verdana&quot; width=900>
<tr>
<td width=450><b>Select a Category:</b></td>
<td width=450 style=&quot;padding-left:15&quot;>
<ASPropDownList AutoPostBack=&quot;true&quot; id=&quot;Category&quot; OnSelectedIndexChanged=&quot;Category_Select&quot; runat=&quot;server&quot;>
<ASP:ListItem value=&quot;&quot;></ASP:ListItem>
<ASP:ListItem value=&quot;ALG&quot;>Algemeen</ASP:ListItem>
<ASP:ListItem value=&quot;FAMO&quot;>Familie Overig</ASP:ListItem>
<ASP:ListItem value=&quot;SER&quot;>Service</ASP:ListItem>
</ASPropDownList>
</td>
</tr>
</table>

<ASPataList id=&quot;MyDataList&quot; BorderWidth=&quot;0&quot;
RepeatColumns=&quot;1&quot; runat=&quot;server&quot;
OnEditCommand=&quot;DEDR_Edit&quot;
onUpdateCommand=&quot;DEDR_Update&quot;
inCancelCommand=&quot;DEDR_Cancel&quot;>
<ItemTemplate>
<table cellpadding=10 style=&quot;font: 10pt verdana&quot; width=950>
<tr>
<td valign=&quot;top&quot; width=50>
<asp:LinkButton CommandName=&quot;Edit&quot; text=&quot;Edit&quot; runat=&quot;server&quot;/>
<asp:LinkButton CommandName=&quot;Delete&quot; text=&quot;Delete&quot; runat=&quot;server&quot;/>
<td valign=&quot;top&quot; width=200>
<%# Convert.ToString(DataBinder.Eval(Container.DataItem, &quot;naam&quot) %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;straat&quot %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;postcode&quot %>
<%# DataBinder.Eval(Container.DataItem, &quot;stad&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>Telefoon: </b><%# DataBinder.Eval(Container.DataItem, &quot;telefoon1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;telefoon2&quot %><br>
<b>Mobile: </b><%# DataBinder.Eval(Container.DataItem, &quot;mobile1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;mobile2&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>E-mail: </b><a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %></a> /
<a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %></a><br>
<b>Url: </b><a href=&quot;<%# DataBinder.Eval(Container.DataItem, &quot;url&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;url&quot %></a><br>
<b>ICQ: </b><%# DataBinder.Eval(Container.DataItem, &quot;icq&quot%><br>
<b>Nick: </b><%# DataBinder.Eval(Container.DataItem, &quot;nick&quot %>
</td>
<td valign=&quot;top&quot; width=200>
<b>Comments: </b><%# DataBinder.Eval(Container.DataItem, &quot;comments&quot %>
</td>
</tr>
</table>
</ItemTemplate>
<AlternatingItemTemplate>
<table cellpadding=10 style=&quot;font: 10pt verdana&quot; width=950 bgcolor=lightblue>
<tr>
<td valign=&quot;top&quot; width=50>
<asp:LinkButton CommandName=&quot;Edit&quot; text=&quot;Edit&quot; runat=&quot;server&quot;/>
<asp:LinkButton CommandName=&quot;Delete&quot; text=&quot;Delete&quot; runat=&quot;server&quot;/>
<td valign=&quot;top&quot; width=200>
<%# Convert.ToString(DataBinder.Eval(Container.DataItem, &quot;naam&quot) %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;straat&quot %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;postcode&quot %>
<%# DataBinder.Eval(Container.DataItem, &quot;stad&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>Telefoon: </b><%# DataBinder.Eval(Container.DataItem, &quot;telefoon1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;telefoon2&quot %><br>
<b>Mobile: </b><%# DataBinder.Eval(Container.DataItem, &quot;mobile1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;mobile2&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>E-mail: </b><a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %></a> /
<a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %></a><br>
<b>Url: </b><a href=&quot;<%# DataBinder.Eval(Container.DataItem, &quot;url&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;url&quot %></a><br>
<b>ICQ: </b><%# DataBinder.Eval(Container.DataItem, &quot;icq&quot%><br>
<b>Nick: </b><%# DataBinder.Eval(Container.DataItem, &quot;nick&quot %>
</td>
<td valign=&quot;top&quot; width=200>
<b>Comments: </b><%# DataBinder.Eval(Container.DataItem, &quot;comments&quot %>
</td>
</tr>
</table>
</AlternatingItemTemplate>
<SelectedItemTemplate>
<table cellpadding=10 style=&quot;font: 10pt verdana&quot; width=950 bgcolor=cornsilk>
<tr>
<td valign=&quot;top&quot; width=50>
<asp:LinkButton CommandName=&quot;Edit&quot; text=&quot;Edit&quot; runat=&quot;server&quot;/>
<asp:LinkButton CommandName=&quot;Delete&quot; text=&quot;Delete&quot; runat=&quot;server&quot;/>
<td valign=&quot;top&quot; width=200>
<%# Convert.ToString(DataBinder.Eval(Container.DataItem, &quot;naam&quot) %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;straat&quot %><br>
<%# DataBinder.Eval(Container.DataItem, &quot;postcode&quot %>
<%# DataBinder.Eval(Container.DataItem, &quot;stad&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>Telefoon: </b><%# DataBinder.Eval(Container.DataItem, &quot;telefoon1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;telefoon2&quot %><br>
<b>Mobile: </b><%# DataBinder.Eval(Container.DataItem, &quot;mobile1&quot %> /
<%# DataBinder.Eval(Container.DataItem, &quot;mobile2&quot %>
</td>
<td valign=&quot;top&quot; width=250>
<b>E-mail: </b><a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email1&quot %></a> /
<a href=&quot;mailto:<%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;email2&quot %></a><br>
<b>Url: </b><a href=&quot;<%# DataBinder.Eval(Container.DataItem, &quot;url&quot %>&quot;><%# DataBinder.Eval(Container.DataItem, &quot;url&quot %></a><br>
<b>ICQ: </b><%# DataBinder.Eval(Container.DataItem, &quot;icq&quot%><br>
<b>Nick: </b><%# DataBinder.Eval(Container.DataItem, &quot;nick&quot %>
</td>
<td valign=&quot;top&quot; width=200>
<b>Comments: </b><%# DataBinder.Eval(Container.DataItem, &quot;comments&quot %>
</td>
</tr>
</table>
</SelectedItemTemplate>
<EditItemTemplate>
<table cellpadding=10 style=&quot;font: 10pt verdana&quot; width=950 bgcolor=yellow>
<tr>
<td valign=&quot;top&quot; width=50>
<asp:LinkButton CommandName=&quot;Update&quot; text=&quot;Update&quot; runat=&quot;server&quot;/>
<asp:LinkButton CommandName=&quot;Cancel&quot; text=&quot;Cancel&quot; runat=&quot;server&quot;/>
<td valign=&quot;top&quot; width=200>
Naam:<br>
<asp:TextBox id=&quot;naam&quot; Size=&quot;50&quot; Text='<%# Container.DataItem(&quot;naam&quot %>' runat=&quot;server&quot; /><br>
Straat:<br>
<asp:TextBox id=&quot;straat&quot; Size=&quot;50&quot; Text='<%# Container.DataItem(&quot;straat&quot %>' runat=&quot;server&quot; /><br>
Postcode:<br>
<asp:TextBox id=&quot;postcode&quot; Size=&quot;6&quot; Text='<%# Container.DataItem(&quot;postcode&quot %>' runat=&quot;server&quot; /><br>
Stad:<br>
<asp:TextBox id=&quot;stad&quot; Size=&quot;50&quot; Text='<%# Container.DataItem(&quot;stad&quot %>' runat=&quot;server&quot; /><br>
</td>
<td valign=&quot;top&quot; width=250>
Telefoon:<br>
1. <asp:TextBox id=&quot;telefoon1&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;telefoon1&quot %>' runat=&quot;server&quot; /><br>
2. <asp:TextBox id=&quot;telefoon2&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;telefoon2&quot %>' runat=&quot;server&quot; /><br>
GSM:<br>
1. <asp:TextBox id=&quot;mobile1&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;mobile1&quot %>' runat=&quot;server&quot; /><br>
2. <asp:TextBox id=&quot;mobile2&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;mobile2&quot %>' runat=&quot;server&quot; /><br>
</td>
<td valign=&quot;top&quot; width=250>
E-mail adres:<br>
1. <asp:TextBox id=&quot;email1&quot; Size=&quot;30&quot; Text='<%# Container.DataItem(&quot;email1&quot %>' runat=&quot;server&quot; /><br>
2. <asp:TextBox id=&quot;email2&quot; Size=&quot;30&quot; Text='<%# Container.DataItem(&quot;email2&quot %>' runat=&quot;server&quot; /><br>
Website:<br>
<asp:TextBox id=&quot;url&quot; Size=&quot;50&quot; Text='<%# Container.DataItem(&quot;url&quot %>' runat=&quot;server&quot; /><br>
UIN:<br>
<asp:TextBox id=&quot;icq&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;icq&quot %>' runat=&quot;server&quot; /><br>
Nickname:<br>
<asp:TextBox id=&quot;nick&quot; Size=&quot;15&quot; Text='<%# Container.DataItem(&quot;nick&quot %>' runat=&quot;server&quot; /><br>
</td>
</tr>
<tr>
<td valign=&quot;top&quot; colspan=4 width=800>
comments : <asp:TextBox id=&quot;comments&quot; Size=&quot;100&quot; Text='<%# Container.DataItem(&quot;comments&quot %>' runat=&quot;server&quot; /><br>
</td>
</tr>
</table>
</EditItemTemplate>
<HeaderTemplate>
<asp:LinkButton CommandName=&quot;Add&quot; Text=&quot;Voeg een nieuw adres toe&quot; runat=&quot;server&quot;/>
</HeaderTemplate>
</ASPataList>

</form>
</body>
</html>

 

[Alcar]

I had the same problem. For some strange reason (yet to understand) I had to change this code:

ds.AcceptChanges
da.Update(ds,&quot;adressen&quot
[\b]
to this:

da.Update(ds,&quot;adressen&quot


by removing the ds.AcceptChanges the ds was correctly updated, both in memory and in the database.

hth
Daren J. Lahey
Just another computer guy...

 

[Isadore]

Alcar: Thanks! This solved a related problem for me. Keep up the good work.

 

[Alcar]

=) you are welcome Isadore.

*whispers* and thank you for the star



Daren J. Lahey
Just another computer guy...

 

[Zarcom]

I can tell you why that didn't work.

All rows within a dataset's datatable object have a rowstate property. The update method for a dataAdapter checks this rowstate property to see what it should do. For example if the rowstate is set to added then the adapter calls the insert command. If it is set to removed then the adapter calls the delete command.

When you call the AcceptChanges method for a dataset it sets the rowstate property to unchanged for all rows in all tables.

HTH That'l do donkey, that'l do
Mark

 

[Alcar]

Thank you Mark!

This means that I would use the AcceptChanges method just for disconnected data solutions.
I've seen some examples where they have called:

da.Update(ds,&quot;TableName&quot;
Application.DoEvents();
ds.AcceptChanges();


could you explain why?


Daren J. Lahey
Just another computer guy...

 

[edelwater]

i changed it TANX !! But ... now i get :

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Source Error:


Line 108:
Line 109: 'ds.AcceptChanges
Line 110: da.Update(ds,&quot;adressen&quot
Line 111: Session(&quot;MyDataList&quot = Nothing
Line 112: myDataList.EditItemIndex = -1


--------------------

see :

http://www.de-leau.com/adres_net/main2.aspx

Edward@de-leau.com

http://www.de-leau.com

 

[Zarcom]

Alcar the reason for this would be that the dataset changed on the do events part but those changes aren't wanted in the database. Only other reason I could think of was that someone was being sure that the changes were accepted. So far I haven't actually found a use of the AcceptChanges method.

Edward the update command for you dataAdapter hasn't been set. Whether you created the adapter with the designer or manually it has 4 command objects within it: Select, Insert, Update, and Delete. These are actual Command types.

If you used the designer to build the adapter try building it again. If there are errors building it you will see them on the last screen of the wizard.

If your curious and want to check what the designer is doing.. look under the designer generated code region.

HTH That'l do donkey, that'l do
Mark

 

[Alcar]

I did use the AcceptChanges method once. Here is the scenario.

problem: needed to start my crystal reports from a given page number
xsd DataSet that has a table with a number of fields(datasource of the report).
Through the designer I added 1 field to the table that is not present on the DataBase.

Field: iPageStart

before I load the report class object, I assign a value (given to me from a javascript function) to the field I added.

&quot;UPDATE tblMyTable SET iPageStart, VALUE(&quot; + iValue
ds.AcceptChanges();

and then I assign the ds to the Crystal Report Object.
The database is not affected but the report is.

Daren J. Lahey
Just another computer guy...

 

[edelwater]

Zarcom : i dont even have visual studio installed ! I created the source using notepad and uploaded it to my hosting account.

So.... any idea what is wrong with my code ?

You can see it at see :

http://www.de-leau.com/adres_net/main2.aspx

this single aspx file is all there is. ( and the access database ofcourse). It still does not delete, update, add, etc...

btw: may thanx for your reactions !!
Edward@de-leau.com

http://www.de-leau.com

 

[Alcar]

your

da.SelectCommand
da.InsertCommand
da.DeleteCommand
da.UpdateCommand

properties must be filled with transac-sql statements

hth Daren J. Lahey
Just another computer guy...

 

[edelwater]

shouldt these da.selectcommand properties etc... only be filled with an SQL Database and not with an access database ? Edward@de-leau.com

http://www.de-leau.com

 

[Alcar]

You can use any record. insert,update,delete statement or procedure that your provider (in your case OLEDB for access) will provide. You could use SQL sstatements. Access will accept them.

&quot;SELECT * FROM tblMyTaqble&quot;
&quot;UPDATE tblMyTable SET myField = value&quot;
&quot;INSERT INTO tblMyTable (field1,field2,...) VALUES(value1,value2,...)&quot;
&quot;DELETE FROM tblMyTable WHERE field1 = value1&quot;

Good luck!
Daren J. Lahey
Just another computer guy...

 

[Zarcom]

Not to be picky or anything alcar, wait I am being picky, but access uses a slightly different version of sql called jet sql rather than t-sql.

Ed Did you get this fixed? I wasn't sure by your email. That'l do donkey, that'l do
Mark

 

[Alcar]

picky picky picky
those four generic statements are accepted =) But it was good to further explain since I could have pushed into the wrong direction =)


p.s. are they always that picky up north? heheheh ;P Daren J. Lahey
Just another computer guy...

 

[Zarcom]

Most of us are ya. :-D
Couldn't help it I had just ran across an article that pointed out the difference so I thought I would share it. That'l do donkey, that'l do
Mark

 

[edelwater]

well i changed the code to use sql commands, made this with some help from 4 guys :

but ... after edit, update it still does not do any updates (and no errors whatsoever)


Sub DEDR_Update(Sender As Object, e As DataListCommandEventArgs)

' #
' # Retrieve the values
' #
Dim id as Integer = CType(e.Item.FindControl(&quot;Id&quot, TextBox).Text
Dim naam as String = CType(e.Item.FindControl(&quot;naam&quot, TextBox).Text
Dim straat as String = CType(e.Item.FindControl(&quot;straat&quot, TextBox).Text
Dim postcode as String = CType(e.Item.FindControl(&quot;postcode&quot, TextBox).Text
Dim stad as String = CType(e.Item.FindControl(&quot;stad&quot, TextBox).Text
Dim email1 as String = CType(e.Item.FindControl(&quot;email1&quot, TextBox).Text
Dim email2 as String = CType(e.Item.FindControl(&quot;email2&quot, TextBox).Text
Dim telefoon1 as String = CType(e.Item.FindControl(&quot;telefoon1&quot, TextBox).Text
Dim telefoon2 as String = CType(e.Item.FindControl(&quot;telefoon2&quot, TextBox).Text
Dim mobile1 as String = CType(e.Item.FindControl(&quot;mobile1&quot, TextBox).Text
Dim mobile2 as String = CType(e.Item.FindControl(&quot;mobile2&quot, TextBox).Text
Dim icq as String = CType(e.Item.FindControl(&quot;icq&quot, TextBox).Text
Dim url as String = CType(e.Item.FindControl(&quot;url&quot, TextBox).Text
Dim nick as String = CType(e.Item.FindControl(&quot;nick&quot, TextBox).Text
Dim comments as String = CType(e.Item.FindControl(&quot;comments&quot, TextBox).Text

' #
' # Construct the SQL statement using Parameters
' #
Dim strSQL as String = _
&quot;UPDATE [Adressen] SET [naam] = @naam, &quot; & _
&quot;[straat] = @straat, [postcode] = @postcode, &quot; & _
&quot;[stad] = @stad, [telefoon1] = @telefoon1, &quot; & _
&quot;[telefoon2] = @telefoon2, [mobile1] = @mobile1,&quot; & _
&quot;[email1] = @email1, [email2] = @email2, &quot; & _
&quot;[mobile2] = @mobile2, [icq] = @icq, &quot; & _
&quot; = @url, [nick] = @nick, &quot; & _...com [URL unfurl="true"]http://www.de-leau.com

 

[edelwater]

Hmmm as far as i know i can do it in two ways either by a dataset (tried it) or using sql commands as alcar pointed out.

&quot;You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.&quot;
(

http://msdn.microsoft.com/library/d...lientsqlcommandclassexecutenonquerytopic.asp)

http://msdn.microsoft.com/library/d...ref/html/frlrfsystemdatadatasetclasstopic.asp

both methods show me no errors but on the other hand no update follows ... sigh... somebody got some idea ?

Edward de Leau
MCP MCSD

http://www.de-leau.com

Edward@de-leau.com

http://www.de-leau.com

 

[link9]

I recently completed a project where the client wanted access, and I had the same problem... execute the update, insert, and delete commands, and no changes would show in my datagrid.

However, when I examined the database, the change was there, and the next time the grid was bound, the change would show up.

I traced this to the fact that Access was not accepting the changes fast enough. I would execute the query, before Access reflected those changes, my select command would have already run, and I'd get the same data back. To get around this, I implemented a .75 second sleep before executing the rebinding.

system.threading.thread.sleep(750)
bindList()

This solved my problem.

hope it helps yours, too.

paul

*note to self (and others): don't use access unless forced, because MSDE is free*

 

[Zarcom]

I don't know why the ExecuteNonQuery isn't working, Pauls solution isn't working.

BUT!!!

You haven't, as both Alcar and I have said, set up the update, insert and delete properties on the dataAdapter.

We may be just not understanding each other so in hopes of avoiding more misunderstanding I will step through setting up a dataAdapter. Please tell me if I am way off base here.

A dataAdapter is an object that enscapulates all database calls into one object. The select operations are put behind the .fill method. The update, insert and delete operations are put behind the .update method. To setup a fully functional dataAdapter please follow the following steps. These steps assume that a fully typed dataset is already created and ready for use.



Dim dbconn As New OleDb.OleDbConnection()
Dim da As New OleDb.OleDbDataAdapter()
Dim sel As New OleDb.OleDbCommand()
Dim update As New OleDb.OleDbCommand()
Dim del As New OleDb.OleDbCommand()
Dim add As New OleDb.OleDbCommand()
Dim ds As New Data.DataSet()

'setup connection
'//////////////////////////////////////////////////////////////////////////
dbconn.ConnectionString = &quot;connection string here&quot;
'//////////////////////////////////////////////////////////////////////////
'setup command objects
'//////////////////////////////////////////////////////////////////////////
sel.Connection = dbconn
sel.CommandType = CommandType.Text
sel.CommandText = &quot;select statment here&quot;
'set up parameters that are needed
sel.Parameters.Add(New OleDb.OleDbParameter(&quot;paramName&quot;, Data.OleDb.OleDbType.VarChar, 8))

update.Connection = dbconn
update.CommandType = CommandType.Text
update.CommandText = &quot;select statment here&quot;
'set up parameters that are needed
update.Parameters.Add(New OleDb.OleDbParameter(&quot;paramName&quot;, Data.OleDb.OleDbType.VarChar, 8))

del.Connection = dbconn
del.CommandType = CommandType.Text
del.CommandText = &quot;select statment here&quot;
'set up parameters that are needed
del.Parameters.Add(New OleDb.OleDbParameter(&quot;paramName&quot;, Data.OleDb.OleDbType.VarChar, 8))

add.Connection = dbconn
add.CommandType = CommandType.Text
add.CommandText = &quot;select statment here&quot;
'set up parameters that are needed
add.Parameters.Add(New OleDb.OleDbParameter(&quot;paramName&quot;, Data.OleDb.OleDbType.VarChar, 8))
'//////////////////////////////////////////////////////////////////////////
'setup dataAdapter parameters
'//////////////////////////////////////////////////////////////////////////
da.SelectCommand = sel
da.UpdateCommand = update
da.DeleteCommand = del
da.InsertCommand = add
'//////////////////////////////////////////////////////////////////////////
'fill the dataset
'//////////////////////////////////////////////////////////////////////////
Try
dbconn.Open()
da.Fill(ds)
Catch
'error handling here
Finally
dbconn.Close()
End Try
'//////////////////////////////////////////////////////////////////////////
'after changes update the dataset
'//////////////////////////////////////////////////////////////////////////
Try
dbconn.Open()
da.Update(ds)
Catch
'error handling here
Finally
dbconn.Close()
End Try


You have to remember to think in object oriented terms, I find it makes things alot easier. The problem with your updates is the bolded lines above. You hadn't set the command objects to anything. As well remember NOT to call the accept changes method before you call the .update method That'l do donkey, that'l do
Mark
If you are unsure of forum etiquette check here faq796-2540