Data table only updates once...why is this happening!!!!!!!!!!!!!!
Hi there,
I have a dataset that i am updating during runtime of my program. To do this, I first delete all of the data in a datatable using "DELETE * FROM Table1" command, and then update the dataset. To initiate this process, i double click on a datagrid. The first time i double-click on the data grid, the program works exactly as planned--i step through the code and verify that first all the records in the datatable delete, and then is updated with all the new records. The problem i'm having is that the next time i double-click on the datagrid, the table doesn't delete! And then i get an error that i am updating a table with a non-unique record. If my table deletes like its supposed to, i shouldn't ever get this error...why does this happen? Here's my code:
Sub UpdateTeamTable()
Dim dt As New DataTable
Dim strSQL As String
Dim int1 As Integer, int2 As Integer
dt = me.dataset1.table1
Dim oCon As New OleDb.OleDbConnection (My.Settings.FantasyConnectionString)
Dim oCommand As New OleDb.OleDbCommand
Dim oAdpt As New OleDbDataAdapter
oCon.Open()
strSQL = "DELETE * FROM table1"
oCommand = New OleDbCommand(strSQL, oCon)
oAdpt = New OleDbDataAdapter(oCommand)
oAdpt.Fill(dt) 'the second time i try to update dt, nothing happens!!!!
oAdpt.Update(dt)
oCon.Close()
'routine for updating dt goes here and seems to be working just fine....
Main.Table1TableAdapter.Update(dt)
End Sub
Thanks in advance for the help!
Pete M
p.s. I've also tried to use ocommand.executenonquery(), and the same thing happens!
[1960 byte] By [
Pete_M] at [2007-12-28]
Ok I made a discovery. The delete part of my procedure works when i comment out the update portion...so i believe that somehow my update routine prevents me from modifying the table1 later on in the program
dt = Main.dataset1.table1
For int1 = 1 To 15
Dim dr As DataRow = dt.NewRow()
dr(0) = int1
For int2 = 1 To 15
dr(int2) = Roster(int2, int1)
Next int2
dt.Rows.Add(dr)
Next int1
Main.TeamsTableAdapter.Update(dt)
Can anyone please weigh in on this!
Thanks in advance!
Pete
I might be wrong but you are using a DELETE to FILL a dataset, only SELECT fills so it may ignore the command or put it into the delete command of the dataset/adapter, though technically it should just execute as the SELECT command.
You do not show how you attempted ExecuteNonQuery so here goes:
I use ODBC which is similar and to execute SQL commands like a tidyup-delete just execute it as a nonquery, Oledb must be similar or have the execute facility, here is an outline using your example:
Dim oCon As New OleDb.OleDbConnection (My.Settings.FantasyConnectionString)
Dim oCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand("", oCon)oCommand.CommandText =
"DELETE * FROM table1"
oCommand.ExecuteNonQueryThe table should now be empty. Whether the dataset memory copy follows it I am not so sure or if the dataset holds a lock on the table for its contents I am also not sure, if it does it may prevent update.
A DataAdapter has a separate DeleteCommand member, example from help file:
' Create the DeleteCommand.
command = New OleDbCommand( _
"DELETE * FROM Customers WHERE CustomerID = ?", _
connection)
parameter = command.Parameters.Add( _
"CustomerID", OleDbType.Char, 5, "CustomerID")
parameter.SourceVersion = DataRowVersion.Original
dataAdapter.DeleteCommand = command
Slightly complicated by separate parameters but readable. Intended for deleting rows when told to not really for deleting entire contents of a table, Execute...is a better method.
Make sure you have closed any datasets/adapters using table1 as they may have locks outstanding.
Well, i still haven't figured this one out. Here's everthing i have, any ideas?
Sub
UpdateTable1()
Dim dt As New DataTableDim int1 As Integer, int2 As IntegerDim dr As DataRowDim oCon As New OleDb.OleDbConnection(My.Settings.ProjectConnectionString)Dim oCommand As New OleDb.OleDbCommandoCommand.CommandType = CommandType.Text
oCommand.CommandText =
"DELETE * FROM Table1"oCommand.Connection = oCon
oCon.Open()
oCommand.ExecuteNonQuery()
oCon.Close()
dt = Main.Dataset1.Table1
For int1 = 1 To 15dr = dt.NewRow()
dr(0) = int1
For int2 = 1 To loopdr(int2) = Array1(int2, int1)
Next int2dt.Rows.Add(dr) ' The program has an error here: Column 'Pick' is constrained to be unique. Value '1' is already present.
Next int1Main.Table1TableAdapter.Update(dr)
End SubIs Pick = dr(0) and is Pick an auto-increment field, if so you do not need to set a value in it, need to see the table definition to be of help.
The trouble is, at the point in my that i get this error, the dt shouldn't have any records. The executenonquery command should have deleted the records, but it doesn't.
Has the memory copy maintained in the dataset flushed, having issued the DELETE command display a datagrid of the dataset and see what is present in it, if the records are still there then that will be the problem and you need to flush out the dataset as it is not in sync with the table after the delete.
I got it to work!!!!! I cannot thank you enough. I've been struggling with this for at least a week...You are the man!
For anyone else who is having a tough time updating data. here's what's worked for me:
oCommand.CommandType = CommandType.Text
oCommand.CommandText =
"DELETE * FROM Table1"oCommand.Connection = oCon
oCon.Open()
oCommand.ExecuteNonQuery()
oCon.Close()
strSQL =
"SELECT * FROM Table1"oCommand =
New OleDbCommand(strSQL, oCon)Dim oAdpt As New OleDbDataAdapter(oCommand)oAdpt.Fill(dt)
For int1 = 1 To 15dr = dt.NewRow()
dr(0) = int1
For int2 = 1 To 12dr(int2) = Array1(int2, int1)
Next int2dt.Rows.Add(dr)
Next int1Main.Table1TableAdapter.Update(dr)
