Problem with updating data to MS Access. Need help

cachua9

Member
Joined
Feb 25, 2005
Messages
11
Programming Experience
Beginner
I tried to save/update my data from textbox to my access database. I don't know what wrong with my code. Nothing new record in my database, even though no error when i run my application.
Could somebody help me?
thanks


Private
Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim r As Data.DataRow

Me.OleDbConnection1.Open()

r = Me.CustomerDataset11.Tables("Customer").NewRow

Try

r.Item("Cust_ID") = Me.TextBox1.Text

r.Item("Address") =
Me.TextBox3.Text

r.Item("City") =
Me.TextBox4.Text

r.Item("State") =
Me.TextBox5.Text

r.Item("Zip") =
Me.TextBox6.Text

r.Item("Phone") =
Me.TextBox7.Text

r.Item("Fax") =
Me.TextBox8.Text

r.Item("Email") =
Me.TextBox9.Text

r.Item("Type") =
Me.TextBox10.Text

Me.CustomerDataset11.Tables("Customer").Rows.Add(r)

Try

Me.OleDbDataAdapter1.Update(Me.CustomerDataset11.GetChanges(DataRowState.Added))

Me.OleDbDataAdapter1.Update(Me.CustomerDataset11.GetChanges(DataRowState.Modified))

Me.CustomerDataset11.AcceptChanges()

Me.OleDbDataAdapter1.Fill(CustomerDataset11.Tables("Customer"))

Catch ex As Exception

End Try

Me.OleDbConnection1.Close()



Catch ex1 As Exception

Console.WriteLine(ex1.ToString())

End Try



End Sub

 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
You haven't created an Update statement for your dataAdapter.
The reason you don't get an error is that you have two Catch statements both catching the same kind of error. The first one is the only one being executed and in that first one, there is no message being displayed.
Have a look through this forum for examples (either this category or the Access category).
 

cachua9

Member
Joined
Feb 25, 2005
Messages
11
Programming Experience
Beginner
Thanks for your help. However, would you please tell me what update statement for my dataadapter is?

Is that my dataadapter.update(my dataset.getChanges(datarowstate.added)) ?
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
An update command is a form of the System.Data.OleDb.OleDbCommand. It's commandText property should be a valid SQL statement similar to "UPDATE DataTableName SET Field1 = ?, Field2 = ? .... Then you would add parameters (you can also manually specify the values for the fields instead of using the parameters with the question marks).

There are several examples in this forum. The search feature is most usefull. :)

As always, I suggest using the dataForm Wizard as a starting point for learning ADO.NET.
To use the DataForm Wizard, right click on your project in the solution explorer, click Add -> 'Add New Item'; in the dialog select 'Data Form Wizard' and answer the questions given by the Wizard.
 

cachua9

Member
Joined
Feb 25, 2005
Messages
11
Programming Experience
Beginner
Ouch, I already have those lines below, but I have another problem occurs that says "Could not lock file" even though i added full control for ASP.NET user

'OleDbUpdateCommand1
Me.OleDbUpdateCommand1.CommandText = "UPDATE Customer SET Address = ?, City = ?, Cust_ID = ?, Email = ?, Fax = ?, Name " & _

"= ?, Phone = ?, State = ?, Type = ?, Zip = ? WHERE (Cust_ID = ?) AND (Address = " & _

"? OR ? IS NULL AND Address IS NULL) AND (City = ? OR ? IS NULL AND City IS NULL)" & _

" AND (Email = ? OR ? IS NULL AND Email IS NULL) AND (Fax = ? OR ? IS NULL AND Fa" & _

"x IS NULL) AND (Name = ? OR ? IS NULL AND Name IS NULL) AND (Phone = ? OR ? IS N" & _

"ULL AND Phone IS NULL) AND (State = ? OR ? IS NULL AND State IS NULL) AND (Type " & _

"= ? OR ? IS NULL AND Type IS NULL) AND (Zip = ? OR ? IS NULL AND Zip IS NULL)"





Could not lock file.

[font=Arial, Helvetica, Geneva, SunSans-Regular, sans-serif]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.Data.OleDb.OleDbException: Could not lock file.

Source Error:

Line 183: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickLine 184: Dim r As Data.DataRowLine 185: Me.OleDbConnection2.Open()Line 186: r = Me.CustomerDataset11.Tables("Customer").NewRowLine 187:
Source File: C:\Inetpub\wwwroot\testing\WebForm1.aspx.vb Line: 185

Stack Trace:

[OleDbException (0x80004005): Could not lock file.] System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) System.Data.OleDb.OleDbConnection.InitializeProvider() System.Data.OleDb.OleDbConnection.Open() testing.WebForm1.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\testing\WebForm1.aspx.vb:185 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()[/font]
 

cachua9

Member
Joined
Feb 25, 2005
Messages
11
Programming Experience
Beginner
Thank you. It works now.
I just change fill syntax a little bit.
Instead off
Me.OleDbDataAdapter1.Fill(CustomerDataset11.Tables("Customer"))

I changed to
Me.OleDbDataAdapter1.Fill(CustomerDataset11)

somehow it works.
 
Top Bottom