PRoblems Deleting and Uodating Records with Access

[awleese]

I Am using a prgram to udate and control a small database for my company. I have the code written and working, however my database will not take certian updates. The odd thing is certian lines of code will add in rows to the database where as others will not. At the same time I am having no sucess in deleting a line from the databse. I can delete the line from the Data Source but the change will not transfer.

This is the code that adds a record to the table and updates the data properly.

MyDS.Clear()
myAdapt.Fill(MyDS)
. . . code to get new data . . .
myRow = myTable.NewRow()
myRow("Buildings") = newbuilding
MyDS.Building_List.ImportRow(myRow)
myAdapt.Update(MyDS)

Where as on the same form to remove a record this code is used . . .


MyDS.Clear()
myAdapt.Fill(MyDS)
myRow = MyDS.Building_List.Rows.Item(V)
MyDS.Building_List.Rows.Remove(myRow)
myAdapt.Update(MyDS)

this code will remove the Row from the DS , but the code will not make the change to the database itself. The code gives me no syntatical errors so I assume the problem is in my logic. IF anymore data is needed to help me with this please let me know. The probleem is that the changes will not carry over. I have a similar problem on another form in the same program.

[mar_zim]

use the commandbuilder to update your data in your database.

[CyberHawke]

Before I give you my answer, I need to know if you have already created Insert, Update, and Delete command objects and associated them with your DataAdapter?

I ask this because if you have then your code should work. I am assuming for the moment that you have not.

There are three ways you can approach this problem. The first is to take Mar_Zim's advice and use the command builder. While this option will work, there are two problems with it that I can think of right off the top of my head. 1. Command builders are intrensically slow when compared to execute commands. 2. It takes the programming away from the programmer. If you want to trust that Microsoft has managed to allow for every eventuality in their development of the command builder and that they know exactly what your solution needs, then by all means, do use the command builder. The second approach is to code your command objects by hand, associate them with your data adapter and run your code as is, and it will fix your problem. While this approach puts the programming back in the hands of the programmer, in a high volume production system, this approach is again quite slow (I've learned the hard way.) The third option is to code command objects on your own and execute them directly against a connection. A delete command might appear thus:

Imports System
Imports System.Data
Imports System.Data.OleDb
 
Public Class frmTest : Inherits System.Windows.Forms.Form
    Private cnNorthwind As OleDbConnection = New OleDbConnection("ConnectionString")
    Private cmdDelete As OleDbCommand = New OleDbCommand("DELETE * FROM Customers WHERE CustomerID = ?", cnNorthwind)
    Private prmID As OleDbParameter = New OleDbParameter
 
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        prmID.ParameterName = "@CustID"
        prmID.Value = MyDS.Building_List.Rows.Item(V)
        cmdDelete.Parameters.Add(prmID)
        cnNorthwind.Open()
        cmdDelete.ExecuteNonQuery()
        cnNorthwind.Close()
    End Sub
End Class

This approach is scalable, and puts the management of your inserts, updates and deletes completely under your control. If you need any more help with this, feel free to ask.

John

btw, Mar_Zim, love the movie gif

[awleese]

I have created the Commands Insert, update and such when I created the DS and Datat adapter objects. An oddly enough one piece of code will work. I can add records to the one database. None of the other commands seem to work. the data is found and I can work with it. I am also preventing the user from altering the database when it is displayed. Only by inputting the data into prompts or forms. I doubt that is important. So I generate the commands just for simplicity and lack of familiarity in dealing with databases. I am told that they were created successfully and since some of it works I am trying to figure out what is going on. I doubt anything I am doing is complex enough for the Command builder to not work with it. The Database in question is only one field and not even link to anyhting else. I use others in the program but figure if I solve this one the others should be obvious as well. The form inquestion only hase on connection, Datasource, and adapter. Only needs one table from a database.

here is the relevent code generated by VB
'
'MyConnect
'
Me.MyConnect.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Data Source=""C:\StartTimes Sheet\Sheetsdb.mdb"";Jet OLEDB:Engine Ty" & _
"pe=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP" & _
"=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet" & _
" OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB" & _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False" & _
";User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'
'myAdapt
'
Me.myAdapt.DeleteCommand = Me.OleDbDeleteCommand1
Me.myAdapt.InsertCommand = Me.OleDbInsertCommand1
Me.myAdapt.SelectCommand = Me.OleDbSelectCommand1
Me.myAdapt.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Building List", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Buildings", "Buildings")})})
Me.myAdapt.UpdateCommand = Me.OleDbUpdateCommand1
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT Buildings FROM [Building List]"
Me.OleDbSelectCommand1.Connection = Me.MyConnect
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO [Building List] (Buildings) VALUES (?)"
Me.OleDbInsertCommand1.Connection = Me.MyConnect
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Buildings", System.Data.OleDb.OleDbType.VarWChar, 50, "Buildings"))
'
'OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = "UPDATE [Building List] SET Buildings = ? WHERE (Buildings = ?)"
Me.OleDbUpdateCommand1.Connection = Me.MyConnect
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Buildings", System.Data.OleDb.OleDbType.VarWChar, 50, "Buildings"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Buildings", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Buildings", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM [Building List] WHERE (Buildings = ?)"
Me.OleDbDeleteCommand1.Connection = Me.MyConnect
Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Buildings", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Buildings", System.Data.DataRowVersion.Original, Nothing))
'

the oddest thing is the record I try to delete is removed from the data displayed and from the DS I would think. However it is not removed from the database when I exit the program. I have another section where it won't even add the record but thats after the this problem is fixed. If any other code or questions would be relevent please let me know.

Yeah Mar you do have a nice little gif there.