10 Replies - 3547 Views - Last Post: 21 September 2007 - 09:45 AM Rate Topic: -----

#1 RayAtCafeVittoria  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 07

Cannot update Access MDB [Resolved]

Post icon  Posted 17 September 2007 - 11:29 AM

I am getting balder by the minute!
I have posted below a function I wrote to import fields into an existing access mdb database from a text file.
Each line is correctly read and parsed, in fact it appears to run perfectly, I get no error messages, and yet the database is unchanged after running the code.

	  Private Sub ImportCatDescriptions()
		Dim sw As System.IO.StreamReader
		Dim IDescText, IDText, OrigID, OrigDESC As String
		Dim LinesImported, OrigIDKey, OrigLang As Integer
		Dim tText(1) As String
		Dim NewLine As String
		Dim LF, SkipLine As Boolean
		Dim dr As DataRowView
		Me.taTYPE_DESC.Fill(Me.PublicationsDataSet1.TYPE_DESC)
		Me.taITEM_TYPE.Fill(Me.PublicationsDataSet1.ITEM_TYPE)

		LinesImported = 0
		sw = New System.IO.StreamReader(Filename, System.Text.Encoding.GetEncoding("iso-8859-1"))
		While Not sw.EndOfStream
			NewLine = sw.ReadLine
			tText(0) = ""
			tText(1) = ""

			LF = (Not NewLine.Contains(","))
			SkipLine = NewLine.Contains("''") Or LF
			If Not SkipLine Then
				tText = NewLine.Split(",")
				IDescText = tText(0)
				IDText = tText(1)
				If IDText <> "" And IDescText <> "" Then
					Me.bsTYPE_DESC.Filter = "ID = '" & IDText & "' AND LANG = '" & Langtodo & "'" 'Filter to check if row exists already
					If Me.bsTYPE_DESC.Count = 0 Then 'Item does not already exist
						Try
							Me.taTYPE_DESC.Insert(IDText, IDescText, Langtodo)
							LinesImported += 1
						Catch ex As Exception
						End Try
					Else
						dr = Me.bsTYPE_DESC.Current
						OrigID = dr.Item("ID")
						OrigIDKey = dr.Item("IDKEY")
						OrigDESC = dr.Item("DESC")
						OrigLang = dr.Item("LANG")
						dr.Delete()
						
[color=#33CC00]						'Me.taTYPE_DESC.Update(IDText, IDescText, Langtodo, OrigID, OrigDESC, OrigLang, OrigIDKey)

						'Me.taTYPE_DESC.Delete(OrigID, OrigDESC, OrigLang, OrigIDKey)
						Me.bsTYPE_DESC.EndEdit()
						Me.taTYPE_DESC.Update(Me.PublicationsDataSet1.TYPE_DESC)
						Me.taTYPE_DESC.Insert(IDText, IDescText, Langtodo)[/color]						LinesImported += 1
					End If

				End If
			End If
			Me.bsTYPE_DESC.EndEdit()
			Me.taTYPE_DESC.Fill(Me.PublicationsDataSet1.TYPE_DESC)

		End While
		'at = Nothing
		sw.Close()

	End Sub



I have tried using a datarowview to delete rows, no go.
I have tried Updating the Datatable by retreiving the original values and including them in the update command, nothing
I have tried directly datatable.delete, followed by an insert command...
Nothing works!!! And yet I have a dozen other forms that update information in this database flawlessly.
Can someone point out a blinding error ... anything that would cause me such greif. :blink:
I am truly at a loss

*PsychoCoder: Added code blocks

This post has been edited by PsychoCoder: 17 September 2007 - 12:17 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Cannot update Access MDB [Resolved]

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Cannot update Access MDB [Resolved]

Posted 18 September 2007 - 04:36 AM

Try putting you code in a Try...Catch block and see if it will tell you maybe something was missed

Private Sub ImportCatDescriptions()
		Dim sw As System.IO.StreamReader
		Dim IDescText, IDText, OrigID, OrigDESC As String
		Dim LinesImported, OrigIDKey, OrigLang As Integer
		Dim tText(1) As String
		Dim NewLine As String
		Dim LF, SkipLine As Boolean
		Dim dr As DataRowView
		Me.taTYPE_DESC.Fill(Me.PublicationsDataSet1.TYPE_DESC)
		Me.taITEM_TYPE.Fill(Me.PublicationsDataSet1.ITEM_TYPE)
		Try
			LinesImported = 0
			sw = New System.IO.StreamReader(Filename, System.Text.Encoding.GetEncoding("iso-8859-1"))
			While Not sw.EndOfStream
				NewLine = sw.ReadLine
				tText(0) = ""
				tText(1) = ""

				LF = (Not NewLine.Contains(","))
				SkipLine = NewLine.Contains("''") Or LF
				If Not SkipLine Then
					tText = NewLine.Split(",")
					IDescText = tText(0)
					IDText = tText(1)
					If IDText <> "" And IDescText <> "" Then
						Me.bsTYPE_DESC.Filter = "ID = '" & IDText & "' AND LANG = '" & Langtodo & "'" 'Filter to check if row exists already
						If Me.bsTYPE_DESC.Count = 0 Then 'Item does not already exist
							Try
								Me.taTYPE_DESC.Insert(IDText, IDescText, Langtodo)
								LinesImported += 1
							Catch ex As Exception
							End Try
						Else
							dr = Me.bsTYPE_DESC.Current
							OrigID = dr.Item("ID")
							OrigIDKey = dr.Item("IDKEY")
							OrigDESC = dr.Item("DESC")
							OrigLang = dr.Item("LANG")
							dr.Delete()
							Me.bsTYPE_DESC.EndEdit()
							Me.taTYPE_DESC.Update(Me.PublicationsDataSet1.TYPE_DESC)
							Me.taTYPE_DESC.Insert(IDText, IDescText, Langtodo)
							LinesImported += 1
						End If

					End If
				End If
				Me.bsTYPE_DESC.EndEdit()
				Me.taTYPE_DESC.Fill(Me.PublicationsDataSet1.TYPE_DESC)

			End While
			'at = Nothing
			sw.Close()
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try


	End Sub


Was This Post Helpful? 0
  • +
  • -

#3 RayAtCafeVittoria  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 07

Re: Cannot update Access MDB [Resolved]

Posted 18 September 2007 - 05:07 AM

View PostPsychoCoder, on 18 Sep, 2007 - 04:36 AM, said:

Try putting you code in a Try...Catch block and see if it will tell you maybe something was missed


Already tried that, tho it's no longer in the code I posted. I popped up a message block in the try and the catch. Never saw the msgbox from the catch portion. Ergo the reason why I am so frustrated. It behaves as if it actually did something, but the mdb remains unchanged. I stepped through the entire process without error. :cry2:

A post elswhere suggested that maybe the program was updating the wrong mdb, or perhaps overwriting the one in the bin folder upon execution in debug mode. But that ain't it either. I am really stumped here as the other parts of the program correctly update, add, delete rows in other tables in this file. One function even adds to the current table that I am having such troubles with.... :blink: .... lemme just doublecheck this function w/the one that works.

Anyway if you have any suggestions I B happy 2 see them

Thanx
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Cannot update Access MDB [Resolved]

Posted 18 September 2007 - 05:16 AM

First, you have no BeginEdit in your function, second you don't AcceptChanges in your application. Try changing part of your function like this

Me.bsTYPE_DESC.BeginEdit
dr = Me.bsTYPE_DESC.Current
OrigID = dr.Item("ID")
OrigIDKey = dr.Item("IDKEY")
OrigDESC = dr.Item("DESC")
OrigLang = dr.Item("LANG")
'Dont selete this yet
'dr.Delete()
Me.bsTYPE_DESC.EndEdit()
'Why are you doing an Update and an Insert??
Me.taTYPE_DESC.Update(Me.PublicationsDataSet1.TYPE_DESC)
Me.taTYPE_DESC.Insert(IDText, IDescText, Langtodo)
Me.bsTYPE_DESC.AcceptChanges()
dr.Delete
dr.Dispose
dr = Nothing



Let me know if that helps you as Ive never worked with the drag and drop controls in .Net, I always create them at run-time :)

This post has been edited by PsychoCoder: 18 September 2007 - 05:16 AM

Was This Post Helpful? 0
  • +
  • -

#5 RayAtCafeVittoria  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 07

Re: Cannot update Access MDB [Resolved]

Posted 20 September 2007 - 05:56 AM

Quote

First, you have no BeginEdit in your function, second you don't AcceptChanges in your application. Try changing part of your function like this


This is in a local mdb file not on an SQL server. There are no Begin edit and AcceptChanges commands available to the Tableadapters.

Quote

'Why are you doing an Update and an Insert??


There is a lot of excess code in here as I have tried a number of different approaches. Some of it is actually commented out, (dr.delete for instance). Sorry I should have removed the commented out parts.
All I want to do in reality is update the existing field with the text in the IDescText field

In any event I think we may be barking up the wrong tree. I say this because I noticed that the data is updated in memory because if I run the function a second time without quitting the program, the updated data is there in the TableAdapter. It just doesn't get written to the original file. What I don't understand is that I Insert and Delete rows in other Tables in a hundred other functions in this program, but this one just refuses to work. :crazy:

I am wondering if perhaps there is a known bug in visual studio that would cause this kind of behavior. I have Googled the question with a number of different keywords, and just can't seem to find anyone else who has hit this kind of problem.

I suspect the answer is staring me in the face, and when I finally see it I will be kicking myself.

Thanks for your help. If you can think of anything else... or anyone else here I would be much obliged!
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Cannot update Access MDB [Resolved]

Posted 20 September 2007 - 06:05 AM

In the code you posted dr.Delete isn't commented out, you need to make sure it is in your application, this could be whats causing your error.
Was This Post Helpful? 0
  • +
  • -

#7 RayAtCafeVittoria  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 07

Re: Cannot update Access MDB [Resolved]

Posted 20 September 2007 - 10:12 AM

View PostPsychoCoder, on 20 Sep, 2007 - 06:05 AM, said:

In the code you posted dr.Delete isn't commented out, you need to make sure it is in your application, this could be whats causing your error.


Nope, Ah but if it could be so simple. :(
Was This Post Helpful? 0
  • +
  • -

#8 m2s87  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 390
  • Joined: 28-November 06

Re: Cannot update Access MDB [Resolved]

Posted 20 September 2007 - 10:43 AM

Quote

This is in a local mdb file ...

Excuse me, but is your
project-properties-compile-build output path="bin\Release\"?

If so, then every change you make, will be made on local copy that is located in your project bin folder! As files in bin folder are temporary and will be replaced by the real ones on each build, you will see no changes.

So if you find a copy of the database in bin folder after build, change output path or release your application.
Was This Post Helpful? 1

#9 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Cannot update Access MDB [Resolved]

Posted 20 September 2007 - 11:05 AM

View Postm2s87, on 20 Sep, 2007 - 10:43 AM, said:

Excuse me, but is your
project-properties-compile-build output path="bin\Release\"?

If so, then every change you make, will be made on local copy that is located in your project bin folder! As files in bin folder are temporary and will be replaced by the real ones on each build, you will see no changes.

So if you find a copy of the database in bin folder after build, change output path or release your application.


I didn't even notice that! Ive been wracking my brain over this one and couldn't figure out for the life of my why it wasn't updating.
Was This Post Helpful? 0
  • +
  • -

#10 RayAtCafeVittoria  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 17-September 07

Re: Cannot update Access MDB [Resolved]

Posted 21 September 2007 - 09:40 AM

View PostPsychoCoder, on 20 Sep, 2007 - 11:05 AM, said:

View Postm2s87, on 20 Sep, 2007 - 10:43 AM, said:

Excuse me, but is your
project-properties-compile-build output path="bin\Release\"?

If so, then every change you make, will be made on local copy that is located in your project bin folder! As files in bin folder are temporary and will be replaced by the real ones on each build, you will see no changes.

So if you find a copy of the database in bin folder after build, change output path or release your application.


I didn't even notice that! Ive been wracking my brain over this one and couldn't figure out for the life of my why it wasn't updating.


:huh: :stupid:

I knowed it hadda bee sumpin simple.

Somebody help me pin this Kick Me sign on my back!

Thank You both for your help
Of course I had been using the mdb in the debug folder to verify that things were working correctly and I had deleted the mdb in my project leaving only the one in the debug so the changes never made it into the mdb that I wanted!

Wonderbar...
Now how do we mark this thread as solved?
Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Cannot update Access MDB [Resolved]

Posted 21 September 2007 - 09:45 AM

I can do that for you :)

Glad you solved it
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1