I need help with ADO.NET
-
Hi I have trouble with ADO.Net. I don't understand when to use what. I'm using Visual Basic in Visual Studio 2005. In my program I have a dataadapter-,dataset and a bindingsource control. When I try to use the code
Dim cnNwind As New OleDb.OleDbConnection(Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = Nothing
Dim strSQL As String = "DELETE FROM " + table + " WHERE ID = " + cbo.SelectedValue.ToString
cmOrder = New OleDb.OleDbCommand(strSQL, cnNwind)
cmOrder.ExecuteNonQuery()nothing happens, the record isn't deleted. I have also tried using the adapters Delete method
Me.FilmTableAdapter.Delete(cbo.SelectedValue.ToString)
Me.FilmTableAdapter.Update(Me.FilmerDataSet)and nothing happens. I don't know if I had to use the update method, but it didn't mattered if I did or didn't. I have also tried to add parameters when I used the Delete method, but it didn't helped.
cmOrder.Parameters.AddWithValue("@Original_ID", cbo.SelectedValue.ToString)
Me.FilmTableAdapter.Delete(cbo.SelectedValue.ToString)So I have know clue what to do or when I should use either of the methods to delete a record. If there is somone who can explain when to use either of these examples, please explain that for me. And when should I use the update method, because I don't know. And what should I do in my program to delete a record. PLEASE HELP ME!!!! FIA :confused:
If the rest of the code you haven't show around these small sections is correct, the ID you're supplying to the SQL DELETE statement isn't matching any records.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
If the rest of the code you haven't show around these small sections is correct, the ID you're supplying to the SQL DELETE statement isn't matching any records.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008I'll give it another try. I have in my program a dataadapter, a dataset and a databindingsource control. I have tried the code below
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection (Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = Nothing
Dim strSQL As String = "DELETE FROM " + table + " WHERE ID = " + cbo.SelectedValue.ToString
cmOrder = New OleDb.OleDbCommand(strSQL, cnNwind)
'cmOrder.Parameters.AddWithValue("@Original_ID", cbo.SelectedValue.ToString)cnNwind.Open() cmOrder.ExecuteNonQuery() cnNwind.Close()End Sub
and nothing happens. It doesn't matter if I add the parameters or not. And the ID is in the database. I have also tried the dataadapters Delete method
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection(Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = NothingcnNwind.Open() Me.FilmTableAdapter.Delete(cbo.SelectedValue.ToString) Me.FilmTableAdapter.Update(Me.FilmerDataSet) cnNwind.Close()End Sub
but nothing happens here either. I don't know when to use either of these examples or when to add the parameters or use the Update method. And now when neither of them working with parameters and Update or not, I need some explanation on when to use either of the examples I have and when to use parameter or Update. And of course what to do in my program. I hope this code explains my trouble more.
-
I'll give it another try. I have in my program a dataadapter, a dataset and a databindingsource control. I have tried the code below
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection (Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = Nothing
Dim strSQL As String = "DELETE FROM " + table + " WHERE ID = " + cbo.SelectedValue.ToString
cmOrder = New OleDb.OleDbCommand(strSQL, cnNwind)
'cmOrder.Parameters.AddWithValue("@Original_ID", cbo.SelectedValue.ToString)cnNwind.Open() cmOrder.ExecuteNonQuery() cnNwind.Close()End Sub
and nothing happens. It doesn't matter if I add the parameters or not. And the ID is in the database. I have also tried the dataadapters Delete method
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection(Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = NothingcnNwind.Open() Me.FilmTableAdapter.Delete(cbo.SelectedValue.ToString) Me.FilmTableAdapter.Update(Me.FilmerDataSet) cnNwind.Close()End Sub
but nothing happens here either. I don't know when to use either of these examples or when to add the parameters or use the Update method. And now when neither of them working with parameters and Update or not, I need some explanation on when to use either of the examples I have and when to use parameter or Update. And of course what to do in my program. I hope this code explains my trouble more.
And have you checked the value of
cbo.SelectedValuewhen you put it in the SQL string?? Does this value actually exist in the table?A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
And have you checked the value of
cbo.SelectedValuewhen you put it in the SQL string?? Does this value actually exist in the table?A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
Hi again Yes the value exists, so I have no idea why it's not working. Can it maybe be that the combobox is bound to fields in the database. I have also changed the disabled mode in the Access database.
Wait a minute. You're using an Access database?? Is this database part of your project? If so, then the code might actually be working. If your database is in the project folder and you compile the program, the database file (*.mdb) will be copied to the bin folder of your project. Now, every time you compile and run your app the database is being copied to the bin folder AGAIN, overwriting any changes you've made since the database was first added to the project.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
Wait a minute. You're using an Access database?? Is this database part of your project? If so, then the code might actually be working. If your database is in the project folder and you compile the program, the database file (*.mdb) will be copied to the bin folder of your project. Now, every time you compile and run your app the database is being copied to the bin folder AGAIN, overwriting any changes you've made since the database was first added to the project.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008Yes, the database is included. Doesn't it have to be if I'm using dataadapter, dataset controls and bound conrols? I tried to exclude the database, but then I got an InvalidOperationException that said it couldn't find the database. What do I have to do now?
-
Yes, the database is included. Doesn't it have to be if I'm using dataadapter, dataset controls and bound conrols? I tried to exclude the database, but then I got an InvalidOperationException that said it couldn't find the database. What do I have to do now?
fiaolle wrote:
Doesn't it have to be if I'm using dataadapter, dataset controls and bound conrols?
If you've used the dataset designer, yes, it does. Put the database back in the proejct like you had it. You just need to understand that any changes you make to the database will be undone because the database is being recopied to the Release or Debug folders every time you run it. Your code is making changes to that copy of the database, not the copy you see in the project.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
fiaolle wrote:
Doesn't it have to be if I'm using dataadapter, dataset controls and bound conrols?
If you've used the dataset designer, yes, it does. Put the database back in the proejct like you had it. You just need to understand that any changes you make to the database will be undone because the database is being recopied to the Release or Debug folders every time you run it. Your code is making changes to that copy of the database, not the copy you see in the project.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008I don't understand the point of that. Because when I delete an item in the comboBox I want the item to be removed from the comboBox. I really don't know how to do that the easiest way. But should I remove my controls and my database and connect to my database another way. Are there no connection controls in Visual Studio 2005, because I didn't get one when I added a new DataSource.
-
I don't understand the point of that. Because when I delete an item in the comboBox I want the item to be removed from the comboBox. I really don't know how to do that the easiest way. But should I remove my controls and my database and connect to my database another way. Are there no connection controls in Visual Studio 2005, because I didn't get one when I added a new DataSource.
fiaolle wrote:
Because when I delete an item in the comboBox I want the item to be removed from the comboBox.
I'm assuming you databound the combo to a dataset or datatable. In that case, you don't delete anything from the Combo. You delete it from the data, then either rebind the Combo, or refresh the datasource that the combo is bound to. This will automatically take the item out of the list in the Combo.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
fiaolle wrote:
Because when I delete an item in the comboBox I want the item to be removed from the comboBox.
I'm assuming you databound the combo to a dataset or datatable. In that case, you don't delete anything from the Combo. You delete it from the data, then either rebind the Combo, or refresh the datasource that the combo is bound to. This will automatically take the item out of the list in the Combo.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008I'm using the dataadapter to update the dataset and then I refresh the combobox. The combobox is bound to FilmerDataSet. I have tried this
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Me.FilmTableAdapter.DeleteQuery(cbo.SelectedValue.ToString)
Me.FilmTableAdapter.Update(Me.FilmerDataSet)
cbo.Refresh()
End Suband this
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection (Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = Nothing
Dim strSQL As String = "DELETE FROM " + table + " WHERE ID = " + cbo.SelectedValue.ToString
cmOrder = New OleDb.OleDbCommand(strSQL, cnNwind)
'cmOrder.Parameters.AddWithValue("@Original_ID",cbo.SelectedValue.ToString)
cnNwind.Open()
cmOrder.ExecuteNonQuery()
Me.FilmTableAdapter.Update(Me.FilmerDataSet)
cnNwind.Close()
cbo.Refresh()
End Suband neither works. Also when do I have to add parameters, that line is a comment.
-
I'm using the dataadapter to update the dataset and then I refresh the combobox. The combobox is bound to FilmerDataSet. I have tried this
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Me.FilmTableAdapter.DeleteQuery(cbo.SelectedValue.ToString)
Me.FilmTableAdapter.Update(Me.FilmerDataSet)
cbo.Refresh()
End Suband this
Sub deleteRecords(ByVal cbo As ComboBox, ByVal table As String)
Dim cnNwind As New OleDb.OleDbConnection (Me.FilmTableAdapter.Connection.ConnectionString)
Dim cmOrder As OleDb.OleDbCommand = Nothing
Dim strSQL As String = "DELETE FROM " + table + " WHERE ID = " + cbo.SelectedValue.ToString
cmOrder = New OleDb.OleDbCommand(strSQL, cnNwind)
'cmOrder.Parameters.AddWithValue("@Original_ID",cbo.SelectedValue.ToString)
cnNwind.Open()
cmOrder.ExecuteNonQuery()
Me.FilmTableAdapter.Update(Me.FilmerDataSet)
cnNwind.Close()
cbo.Refresh()
End Suband neither works. Also when do I have to add parameters, that line is a comment.
OK. It's obvious that you're really, really confused and a beginner at programming in general. You're passing ComboBox's and table names when you don't need either. All you need to pass is the ID selected in the ComboBox. The method you call to delete the record should already know which table it's dealing with. You also don't know how DataAdapters work and your SQL also needs work to support parameters. Describing all of this is way beyond the focus of a forum post and would take a large amount of space. I couldn't tell you how to fix this because, really, it would involved scraping the code you have entirely, and starting over from scratch. I seriously recommend picking up a beginners book on VB.NET and ADO.NET.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
OK. It's obvious that you're really, really confused and a beginner at programming in general. You're passing ComboBox's and table names when you don't need either. All you need to pass is the ID selected in the ComboBox. The method you call to delete the record should already know which table it's dealing with. You also don't know how DataAdapters work and your SQL also needs work to support parameters. Describing all of this is way beyond the focus of a forum post and would take a large amount of space. I couldn't tell you how to fix this because, really, it would involved scraping the code you have entirely, and starting over from scratch. I seriously recommend picking up a beginners book on VB.NET and ADO.NET.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008You don't have to be insolent. I have more than one table and combobox, which are doing the same thing. I know I have trouble understanding ADO.NET, that is why I ask questions. Because I don't see the differences and understand wich method I should use in the books I have read. I'm greatefull for the answers you gave me, but now I have to ask somewhere else to answer my questions.