to those of you who are familiar with the coding, could you write a simple code example so we can understand,give us simple syntax examples please of;
deleete command
accept changes command
that way those of us who are beginners can read the syntax and get a better view of how it works.
syntax code
PremZ
dear ahmed
you say you have given me info before which you have, but being a beginner, and one that sits for hours at the coding trying to slove the issue on my own, you answers didnt really help in as much as they are not simplistic enough for us beginners to grasp. i have viewed scores of your answers to others and i have to say just the answer confuses me. what we beginners need is for you skilled people to tell us in the way you would explain it to a starter you might be teaching. for example;
<name>tableadapter.update(dataset.tablename)
this is the syntax of the update command, what we need to know what is it doing so we can follow its progress, in this example as i undretsand it, the TA is going to recieve the new data, and update the TABLE in the DS with the new data it got from ealier coding.
i hope this is correct, ans therfore i need the same kind of expalantion with the delete commands which i havbe used and all return errors.
even the errors, when explaned can lead us to the soution, but no one even yourself wseems to take the time to explain what the error was called for and give some reasons why this error was called.
so while there are scores of viewers to mine and other postings, few take the time to give a more detailed /simplistic breakdown of the problem.
i dont want to bother you with emails all the time, i dont feel that is right unless you say otherwise, but other beginners need to see the poroblems and solutions, which is what i thioyght these forums were here for
thanks chaza
Jas90
I did give you this code a while back. When dealing with the DataAdapter, you shouldnt call acceptChanges really as it will tell the dataset to accept any changes, so when time comes to update the real database from the dataset, it will return "false" that there are no updates to be made but actually there are. So forget about acceptchanges.
As for the delete command the syntax is usually this in either the DeleteCommand property of the DataAdapter or the SqlCommand/OleDbCommand object class
Delete all records from a table:
DELETE FROM tableName
Delete a certain record based on a field and its value:
DELETE FROM tableName WHERE [field] = 'yourValue'
Full example using SqlCommand:
Dim theSQLCommand as new SqlCommand("DELETE FROM [TableName] WHERE [ID] = 2")
theSQLCommand.Connection = new SqlConnection(connectionString)
theSQLCommand.Connection.Open()
Dim theResult as Integer = theSQLCommand.ExecuteNonQuery()
theSQLCommand.Connection.Close()
MessageBox.Show("Rows affected: " & theResult.ToString())
you may also wish to visit the SQL Forums here to ask T-SQL questions as well as perhaps invest in some beginners guide to SQL Server or perhaps a quick search on the internet which can give you examples on the syntax of SQL Server and how to do a specific thing that is database related
remember, any database action is done and created using its own database class, like for example if we want to operate on the SQL Server database, we use the Sql classes in the System.Data.SqlClient namespace
the DataAdapter is pretty much the same. Here's more info about the DeleteCommand in the dataAdapter:
OleDb:
http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.deletecommand.aspx
SQL:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx
I hope this gives you a start :-)
Kai123
ok....
now if you are giving a method a string (newsong) to a method which only takes a certain type (integer) you will get this error. It's like, remember those "fit the shapes into the correct holes" Where you have a triangle which fits in a triangle shaped hole Pretty much the same thing here, a method which expects a parameter (value you are giving) must be of the same "shape" (value/type) of the one it requires. Without doing so you will get this error, it won't fit that hole...
you need to take a look at the parameters, of any method, it requires before proceeding to do your inputs/code otherwise you will get compiler errors or maybe runtime errors.
There are many ways of doing a task, it depends on what approach you are using. My example was a simplistic raw example of directly deleting a record from the database with a given condition (the WHERE clause). The syntax still applies if you are needing to use the DeleteCommand of the dataAdapter.
So since you are using the wizard approach to generate the dataset/dataadapter, it can get sometimes tricky to understand what's going on since everything is done for you without having knowing what's happening, how its getting done etc....
Now I'm going through my own dataset wizard/binding source wizard and trying to mimic whats going on at your end. Where does this "records" property come from
How did you create this dataadapter and dataset
Justin Pyfrom
i did drag the binding souce as yiou did as well as the dadatset, i dont know waht has gone wrong, so lii probably start againg, but to go back o my original statement about questions being answered in a simple fashion, that is what will help us beginers more.
what i am doing as my knowledge grows is to write them down in a book in idiots language so anyone who is at the point i am can undertsand what things are doing.
i have posted huge amounts of code as you say, but as i watch the posts i get huge amounts of viewers but few interact. thats life i suppose. i am not beaten yet, ill still plug away, till the help dries up then im at a stand still, but im still going and we'll see how far i get.
just as a point though, your delete commands you sent, can you wite one with the code you wil have or did use so i can see what data it contains and how it looks.
thanks ahmed
sampi
sure.
Say for example if you have a datagridview and you bound this to a dataset/datatable as a datasource, and you modify (edit/add/remove) a row/cell of data in the datagridview, it will automatically update the datasource (the bound source, dataset/datatable) then all you need to do is the Update(datatable) command in the dataAdapter, which will then (hopefully) update your database for you with the modified items.
However if this is not the case then you can delete a specific record in the datatable by removing a DataRow or a certain position of the rows collection in the datatable.
now, from what I understand, you wish to delete a value, from the datatable, from the value given from a textbox correct if so, you can either directly delete this using the code given in the initial response (raw direct delete) but means that your datatable would be out of sync so any operations you perform on it will be invalid because you did not update the datatable.
getting to the problem you are having....looking at my dataset here (wizard generated) you can use, as you have pretty much nearly have, the Delete method in the Generated Table Adapter. For me, the syntax will be different, or rather the parameters, since I'm using my database here. But doing some guess work, you would need to pass the value of the item you wish to delete.
Now, when you do this:
theTableAdapter.Delete(
as soon as you open that bracket, you should get a tooltip up showing you the parameters expected correct can you post that here so we can try to help you delete that specific record you want from the value of the textbox
Bobby110uk
it will take me a bit of time to read and digest all that, i have tried to send you my project.
as an extra, even when i use the binding navigater whih is soposed to delete and add as you go it doesnt, i can add a record but i cant delete one. that is strange.
but let me take in all thiis stuff for now.
many thanks ahmed
bluefocs
hi ahmed
i did what you said.
recordstableadapter1.delete()
that is waht i got, i never got any other help menus or drop downs, all it said was that delete was not a member of my dataset.
somewhere i must have upset the whole thing and its getting confused, i might have to wipe it clean and start again, but as i said earlier without clear simple examples of the various commands ill get nowhere. im at the point where im going to stop doing it for lack of help.
but thanks again
RickHan
thanks for your time ahmed, i have a few questions for clification;
1 how does the code below relatte to my ,recordstableadapter,musicdataset2,and records(tablename)
approach 1, raw "dirty" delete
2 do i have to create a NEW sql command or do i have on now as i have already got a batabase
3 is the conection string i got when i created my database not the same thing.
4 i havent grasped where the users input into the textbox came into your example. it seems as i need several lines of code just to delete one record.
im about to view the link you gave
thanks very much
chaza
papadi
indeed but it has been explained on what things are doing at the time and how to fix it. It's hard to just explain where the problem is if you have any errors and not explaining in depth on what code you have, where the errors are coming from etc...
What exactly, in this case, do you need help with I understand for this thread you are wanting to know how to use a Delete command correct I have supplied code for that and explained what the syntax does. Do you still not understand What don't you understand :-)
In your small code snippet applied above, it tells the DataAdapter to update the database with the values which are modified in some way of the data table.
http://msdn2.microsoft.com/en-us/library/z1z2bkx2.aspx
"
When an application calls the Update method, the DbDataAdapter examines the RowState property, and executes the required INSERT, UPDATE, or DELETE statements iteratively for each row, based on the order of the indexes configured in the DataSet. For example, Update might execute a DELETE statement, followed by an INSERT statement, and then another DELETE statement, due to the ordering of the rows in the DataTable.
"
As also stated, any operations to be performed on the database must be used using its classes, like the Sql classes or the DataAdapter classes so it knows what to do and how to operate it. These classes in .NET are developed to do a specific task so if you just put in the VB IDE:
DELETE FROM tableName
you will get a syntax error. This is meaningless to VB so you need to use the appropriate class to perform the action required, as shown in my initial post
Onur_cel
i undertsand about the pasing a string when it should be an integer, but the bit od code i gave you was newsong was a string field of my records, that the bit i dont seem the grasp.
the records is my table name,the data set and adapter wre created from the toolbox. in my table i have artist, song, year. all are string exept yr which is integer.
the update i undertsand more now, but i am having great problems understanding the delete function of the process. why cant i just tel the tableadapter to delete the row in the dataset which containd the vaule of the textbox, that basically is what i want it to do.
if i have to stop using the wixzzard to acomplish what i want i will do, but the understanding of the various commands is vital, as you expalined about the string and integer situation.
viswaug
We are trying our best to help but apologies if you are feeling you are not getting any where. Starting off with something new is hard, always, but you must never be beaten by the challange :-)
The way I had created my version here, was to drag a binding source on the form, and bind it to a database table, then I was able to access the table adapters in code and able to get the delete command. The delete command is in the table adapter as it looks like you have done.
If you can, by all means, feel free to post a link to the entire project for everyone to help out and would make understanding that bit easier.
Eugene Katz
dear ahmed
no i still do not u ndertsand sufficiently, for example,
newsong = TextBox1.Text
MusicDataSet21.records(newsong).Delete()
this code although has no errors until runtime, come back with a strin to integer error, you can see as you are experienced in this area what it is doing although you dont know the exact aplication, and this is just one which i keep changing tio gather a further understanding which i am not getting on thei forum.
can you explain why im getting a string to integer error, that would at least teach me that part of my failure, and thus my experience grows.
the delete exampkles you give ive tried them and they never worked, perhaps because i havent undertsood what they are doing wwhich yours dont explain.
its the simpkle explanations that will teach us more then telling us a= b, tell us what a and b are doing and why.
thank chaza
JDHDevelopement
it's good you are doing this idiots guide thing, something that people may well need.
As for the delete command well, the code posted initially is the code. So lets do a couple of example.
using a Sql database, and we wish to delete a record which has an ID of 2, which has been given by the user in a textbox for example:
'we need to create an SqlCommand. This allows us to create SQL database related statements so we can execute it on the SQL Server database and perform our action. Without it, we can't do anything programmatically in the database
Dim theSqlCommand as new SqlCommand("DELETE FROM myTable WHERE
'We also need a connection string - details on how and where to connect to the SQL Server database
theSqlCommand.Connection = new SqlConnection(connectionStringHere)
'Open the connection
theSqlCommand.Connection.Open()
'Execute the non query, to perform the action we have said in the SqlCommand
theSqlCommand.ExecuteNonQuery()
'Close the connection
theSqlCommand.Connection.Close()
and that's it.
well, we need to give the dataAdapter the DeleteCommand so it knows what to delete when performing an update on the database, giving it the dataset/datatable. So something like this:
theDataAdapter.DeleteCommand = new SqlCommand("DELETE FROM tableName WHERE field1=@p1")
'again, we create another SqlCommand, same reason as in option 1. This time we give it a parameter (@p1) so we can do some "mapping" to the original data so it can get the correct value to delete the record.
We need to of course give it the connection to connect to:
theDataAdapter.DeleteCommand.Connection = new SqlConnection(connectionStringHere)
'We need to create this parameter and add it to the SqlCommand (rather the delete command) parameter collection so when it executes, it needs to know what this parameter is otherwise it will throw an error stating that the parameter was not supplied. So in this example, the field in question is a data type of Integer and the field name is ID
Dim theParameter as new SqlParameter("@p1", SqlDbType.Int, 4, "ID")
theParameter.SourceVersion = DataRowVersion.Original
So now we need to add this parameter into the DeleteCommand collection of parameters, so it can take a look and get the right parameter in the query to obtain the correct values:
theDataAdapter.DeleteCommand.Parameters.Add(theParameter)
'we open the connection
theDataAdapter.DeleteCommand.Connection.Open()
'now perform the update!
theDataAdapter.Update(theDataTable)
'the dataTable has all the data it needs which the dataAdapter looks at and executes the correct command (insertCommand, UpdateCommand, DeleteCommand) depending on how the data was modified in the datatable from the user or some other form/source. This will now hopefully automatically do the work for you after creating the correct commands, like above.
'close the connection
theDataAdapter.DeleteCommand.Connection.Close()
I hope this helps you in some way and gets you going. I was also following an example from the link:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx
Patrik J
I'll try my best here.
well you can avoid the first approach altogether, since you are using a DataAdapter.
2) depends. In order to correctly insert, update or delete records in the database when we are doing an update from the dataAdapter, it needs to have the correct commands created, otherwise you will get an exception thrown that the correct command was not found/generated. So for the delete command, as shown in my example, you will need to create this.
3) yes it is the same thing
4) well as suggested earlier but you had stated that you could not get the tooltip to appear, this would not be valid (For now)...the 2nd approach shown (delete command etc...) are commands created for the dataAdapter so if for example you bind the dataset or datatable to the datagridview, and you modify the datagridview, it will automatically update the dataset or datatable, so if you finally want to commit changes to the database, then you need to create the appropriate commands (Insertcommand, deletecommand, updatecommand) for the dataAdapter so it can successfully execute them correctly and commit the changes made from the dataset or datatable to the database.
So to me, if you want to delete a specific record and its been bound to a datagridview then you need to do the above, otherwise I believe again that you need to give it the correct parameter to the Delete() command (TableAdapter.Delete()) the value/record to delete. This value is given from the textbox into this command here (Delete) but it was stated by yourself that you don't get the method at all (Delete)
Are you able to link us to your project so we can look at it and comment further you can upload it to www.yousendit.com for example then get the link once uploaded so we can all then take a look from the link