Re: Need help updating access db

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 09:57:56 -0400

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" <iamoraal@hotmail.com> wrote in message
news:d19ac02b.0404210423.44a7884c@posting.google.com...
> 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>

Relevant Pages

  • Re: cmdFind_Click
    ... Private Sub FillCustomer() ... Private Sub cbo1_Click ... The global flag lets the click event be exited as it is not neede to fire ... > by the time Form_Load runs the command buttons are there. ...
    (microsoft.public.vb.general.discussion)
  • Tool to get MCP ODT at Windows CMD prompt
    ... You basically send a series of ODT commands of your choosing, once, or in a loop, and get the output right in the command ... Dim sHostname ... Sub WriteIt ... WriteIt "" ...
    (comp.sys.unisys)
  • Re: Afterupdate event
    ... I managed to solve my problem by using the beforeupdate event and modifying ... Private Sub Form_BeforeUpdate ... place your code in the AfterUpdate event of an appropriate text control. ... So I shifted it to the afterupdate event but as I say doesn't fire. ...
    (microsoft.public.access.formscoding)
  • FYI: Delete still shows the deleted record
    ... "kit" wrote: ... new SQL Server DB and I'm borrowing code from the previous SQL Server DB ... Put this in the click event of your "DELETE" command button: ... Public Sub DelCurrentRec ...
    (microsoft.public.access.forms)
  • Re: How Does One Determine If a Command Button Has Been Pressed?
    ... I create a Public variable in the module that calls the UserForm and then ... options buttons), a text box, and two command buttons. ... Private Sub CommandButton1_Click ...
    (microsoft.public.word.vba.general)