473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help updating access db

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>
Jul 21 '05 #1
2 1764
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>

Jul 21 '05 #2
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>

Jul 21 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: gaosul | last post by:
I am non-programming scientist and I am using a Program called Easyarticles from Synaptosoft Inc., which is based the database program Access. Unfortunately, the owner of this company has...
0
by: | last post by:
I am updating MS access tables with data in an xml document. I create two dataset, one for existing data and one for new data. I fill the first dataset with the records from MS Access, the second...
2
by: Neil | last post by:
I was wondering if anybody could shed some light on this scenario, maybe there is white paper somewhere ? I have a vb.net app that runs on a few machines storing data in a local access db, but...
2
by: Irvin | last post by:
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...
2
by: Alexey.Murin | last post by:
The application we are developing uses MS Access 2003 database (with help of ADO). We have noticed that during massive records updating the size of the mdb file increases dramatically (from 3-4 to...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
14
by: John T Ingato | last post by:
I have a contacts table with name address and such but are missing all phone numbers in the phone number fields. I have just received an updated customer list in Excel and have imported into a new...
6
by: mike11d11 | last post by:
I'm trying to create an application that will have multiple users working off a table on a SQL server. Since multi users will be updating different records at any given moment, how can i get those...
21
by: nihad.nasim | last post by:
Hi there, I have a database in Access that I need on the web. The web page should connect to the database and write records for certain tables and view records for others. I want to know a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.