Need help updating access db

[Irvin]

I new to ASP.net and am using the following code to attempt to update
an Access 2000 mdb. The code does make it through the code following
"try". NO rows are updated. There is a row with the work_id of 1343.
What Am I missing? Your help is appreciated.

Irvin Amoraal. <><
______________________

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)
if not page.ispostback then
response.write("first time")
modify_data()
else
response.write("Second time<p>")
end if

end sub

sub modify_data()

Dim SQL As String
Dim workAdapter As New OleDbDataAdapter
Dim workData As New DataSet
dim accessdb as string
dim connectionString as string
dim myConnection as OleDbConnection
dim result as integer

accessdb = server.mappath("/DevWeb") &
"../../../resources/ma21.mdb"

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & accessdb
myConnection = new OleDbConnection( connectionString )
SQL = "select * from tbl_works where work_id = 1343"
workAdapter = New OleDb.OleDbDataAdapter(SQL, myConnection)
workAdapter.Fill(workData, "works")

Dim tblWorks As DataTable
tblWorks = workData.Tables("works")

'If workData.Tables(0).Rows.Count > 0 Then
try
response.write("<p>Updating " &
workData.Tables(0).Rows.Count & " row(s).")
workAdapter.UpdateCommand = New OleDbCommand
workAdapter.UpdateCommand.Connection = myConnection
workAdapter.UpdateCommand.CommandText = "Update tbl_works
set title = 'test title' where work_id = 1343" ' "Update tbl_works set
title = '" & title & "' where work_id = 1343"
result = workAdapter.Update(tblWorks)
response.write("<p>Results: " & result)

Catch ex As Exception
response.write("Type = " & ex.GetType.ToString & vbCr &
"Message = " & ex.Message)
'Else
Response.write("<p>The Work ID Does Not exist Please Try
Again")
end try
'End If

myconnection.close()
End Sub

</script>

[William Ryan eMVP]

Hi Irvin:

First off I'd elminate the use of Dynamic SQL Like that. Particularly since
this is a web app, use parameters instead...this is a big potential risk
here.

Next, right before you call DataAdapter.Update, verify that your
dataset.HasChanges.

Add this line before the code snippet:

Debug.Assert(tblWork.HasChanges)
<< result = workAdapter.Update(tblWorks)

response.write("<p>Results: " & result)>>
I think your assertino is going to fail (since it's the web you'l need to
watch it manually, I don't think that the assertion box will pop up.

The problem most likely is that you don't have anyway rows with a rowstate
or modified/deleted/inserted so when you call update, nothing happen. If
you don't have changes, you can call updates until the cows come and nothing
will ever fire... it looks to rows with rowstate modified to fire the update
command , added to fire the insert command and deleted to fire the delete
command. If rowstate isn't changed, nothing will ever get called.

Anyway, verify that you have changes b/c that's the most likely culprit.

Next, make sure the update command is valid, test it in access first and
make sure it's hitting some rows.

However, I can't emphasize enough, get rid of that dynamic sql with
concatenated values...it's way too much risk just to keep a bad habit.

HTH,

Bill
"Irvin" <ia******@hotmail.com> wrote in message
news:d1**************************@posting.google.c om... I new to ASP.net and am using the following code to attempt to update
an Access 2000 mdb. The code does make it through the code following
"try". NO rows are updated. There is a row with the work_id of 1343.
What Am I missing? Your help is appreciated.

Irvin Amoraal. <><
______________________

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)
if not page.ispostback then
response.write("first time")
modify_data()
else
response.write("Second time<p>")
end if

end sub

sub modify_data()

Dim SQL As String
Dim workAdapter As New OleDbDataAdapter
Dim workData As New DataSet
dim accessdb as string
dim connectionString as string
dim myConnection as OleDbConnection
dim result as integer

accessdb = server.mappath("/DevWeb") &
"../../../resources/ma21.mdb"

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & accessdb
myConnection = new OleDbConnection( connectionString )
SQL = "select * from tbl_works where work_id = 1343"
workAdapter = New OleDb.OleDbDataAdapter(SQL, myConnection)
workAdapter.Fill(workData, "works")

Dim tblWorks As DataTable
tblWorks = workData.Tables("works")

'If workData.Tables(0).Rows.Count > 0 Then
try
response.write("<p>Updating " &
workData.Tables(0).Rows.Count & " row(s).")
workAdapter.UpdateCommand = New OleDbCommand
workAdapter.UpdateCommand.Connection = myConnection
workAdapter.UpdateCommand.CommandText = "Update tbl_works
set title = 'test title' where work_id = 1343" ' "Update tbl_works set
title = '" & title & "' where work_id = 1343"
result = workAdapter.Update(tblWorks)
response.write("<p>Results: " & result)

Catch ex As Exception
response.write("Type = " & ex.GetType.ToString & vbCr &
"Message = " & ex.Message)
'Else
Response.write("<p>The Work ID Does Not exist Please Try
Again")
end try
'End If

myconnection.close()
End Sub

</script>

[William Ryan eMVP]

Hi Irvin:

First off I'd elminate the use of Dynamic SQL Like that. Particularly since
this is a web app, use parameters instead...this is a big potential risk
here.

Next, right before you call DataAdapter.Update, verify that your
dataset.HasChanges.

Add this line before the code snippet:

Debug.Assert(tblWork.HasChanges)
<< result = workAdapter.Update(tblWorks)

response.write("<p>Results: " & result)>>
I think your assertino is going to fail (since it's the web you'l need to
watch it manually, I don't think that the assertion box will pop up.

The problem most likely is that you don't have anyway rows with a rowstate
or modified/deleted/inserted so when you call update, nothing happen. If
you don't have changes, you can call updates until the cows come and nothing
will ever fire... it looks to rows with rowstate modified to fire the update
command , added to fire the insert command and deleted to fire the delete
command. If rowstate isn't changed, nothing will ever get called.

Anyway, verify that you have changes b/c that's the most likely culprit.

Next, make sure the update command is valid, test it in access first and
make sure it's hitting some rows.

However, I can't emphasize enough, get rid of that dynamic sql with
concatenated values...it's way too much risk just to keep a bad habit.

HTH,

Bill
"Irvin" <ia******@hotmail.com> wrote in message
news:d1**************************@posting.google.c om... I new to ASP.net and am using the following code to attempt to update
an Access 2000 mdb. The code does make it through the code following
"try". NO rows are updated. There is a row with the work_id of 1343.
What Am I missing? Your help is appreciated.

Irvin Amoraal. <><
______________________

<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)
if not page.ispostback then
response.write("first time")
modify_data()
else
response.write("Second time<p>")
end if

end sub

sub modify_data()

Dim SQL As String
Dim workAdapter As New OleDbDataAdapter
Dim workData As New DataSet
dim accessdb as string
dim connectionString as string
dim myConnection as OleDbConnection
dim result as integer

accessdb = server.mappath("/DevWeb") &
"../../../resources/ma21.mdb"

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & accessdb
myConnection = new OleDbConnection( connectionString )
SQL = "select * from tbl_works where work_id = 1343"
workAdapter = New OleDb.OleDbDataAdapter(SQL, myConnection)
workAdapter.Fill(workData, "works")

Dim tblWorks As DataTable
tblWorks = workData.Tables("works")

'If workData.Tables(0).Rows.Count > 0 Then
try
response.write("<p>Updating " &
workData.Tables(0).Rows.Count & " row(s).")
workAdapter.UpdateCommand = New OleDbCommand
workAdapter.UpdateCommand.Connection = myConnection
workAdapter.UpdateCommand.CommandText = "Update tbl_works
set title = 'test title' where work_id = 1343" ' "Update tbl_works set
title = '" & title & "' where work_id = 1343"
result = workAdapter.Update(tblWorks)
response.write("<p>Results: " & result)

Catch ex As Exception
response.write("Type = " & ex.GetType.ToString & vbCr &
"Message = " & ex.Message)
'Else
Response.write("<p>The Work ID Does Not exist Please Try
Again")
end try
'End If

myconnection.close()
End Sub

</script>