INSERT and DELETE Problem

[adi_uk]

I have the following code for inserting and deleting information from a database named CDList.mdb. Im having problems in that, when i try and add or delete nothing happens. Have i got something wrong?

The user basically enters the information into a number of text boxes, and hits the button, which then inserts or deletes the information depending on what they have entered.

DELETE

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strTitle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strArtist [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intTracks [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intRunningTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intId [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2]strTitle = TextBox1.Text
strArtist = TextBox2.Text
intTracks = TextBox3.Text
intRunningTime = TextBox4.Text
intId = TextBox5.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]OleDbDataAdapter1.DeleteCommand.CommandText = "DELETE FROM CDList WHERE (ID='intId') AND (RunningTime='intRunningTime') AND (Tracks='intTracks') AND (Artist='strArtist') AND (Title='strTitle')"
OleDbDataAdapter1.Update(MyDataSet1)
Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

INSERT

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strTitle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strArtist [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intTracks [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intRunningTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intId [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2]strTitle = TextBox1.Text
strArtist = TextBox2.Text
intTracks = TextBox3.Text
intRunningTime = TextBox4.Text
intId = TextBox5.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]MyDataSet1.Clear()
OleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO CDList (Title, Artist, Tracks, RunningTime, ID) VALUES (strTitle,strArtist,intTracks,intRunningTime, intId)"
OleDbDataAdapter1.Update(MyDataSet1)
Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

 

[Administrator]

Please post your questions in the most fitting forum. The VS.NET forum is not the most fitting forum for your post as it is a Database topic. Your post has been moved to the appropriate forum.

Thank you.

 

[vis781]

That is what i would call an SQN

'Structured Query Nightmare'!!

I don't mean to be harsh or nasty. But that is all wrong i'm afraid.

Have a search on this forum, for a fellow called 'TechGnome' he's got some ADO.Net tutorials in his signature. That would be the best place to start.

P.s sorry for taking the mick

 

[TechGnome]

Speak of the devil and I shall appear. The problem is the SQL itself, and is a classic rookie mistake I see all too often. I'm still trying to figure out why people think it should work, but that's a topic for another time.

Bottom line is this: You've used the names of your variables directly in your SQL and set them as literal strings. When the query is passed to the database, it's going to be passed exactly as it is. As such, the database knows nothing about your variables. This means that you have to insert the values of the variables into the right place before sending it off.

There's two ways to do this. The first is the simplest and easiest way. It also happens to be the most unsecure way and leaves you open to potential hacking.

The second is what's known as a parameterized queries. It's safer, easier to read and makes reading the query easier.

I have my prefferences, but I'll demonstrate both.

To use concatenation, it's as simple as stringing the items together into one long string:

OleDbDataAdapter1.DeleteCommand.CommandText = "DELETE FROM CDList WHERE (ID=" & intId.ToString & ") AND (RunningTime=" & intRunningTime.ToString & ") AND (Tracks=" & intTracks.ToString & ") AND (Artist='" & strArtist & "') AND (Title='" & strTitle &"')"

Note that I took off the ' around the numeric fields. It's not needed. The only time you need the ' is when deailing with string date, like the artist and title.

As you can see, it's very easy to set up.... and it can also be easy to be hacked. Enter in the correct info into one of your string variables and things can be broken. If you aren't worried about that, then this may be the quickest way to go.

As I said, using parameterized queries takes a bit more setup, but is safer in the long run.

A parameterized version of your query looks like this:

OleDbDataAdapter1.DeleteCommand.CommandText = "DELETE FROM CDList WHERE (ID=?) AND (RunningTime=?) AND (Tracks=?) AND (Artist=?) AND (Title=?)"

The ? in the query are place holders that will be replaced when the query is run.

To create and insert the data where it belongs:

Dim YourParameter As OLEDBClient.OLEDBParameter = your_data_adaptor.CreateParameter 
        YourParameter.ParameterName = "@ID" 
        YourParameter.Value = intID
        your_data_adaptor.Parameters.Add(YourParameter)

Just be sure to add the parameters in the same order that you want them to be replaced in your SQL.

It's also possible to resue the same object once it's been added.

Dim YourParameter As OLEDBClient.OLEDBParameter
YourParameter = your_data_adaptor.CreateParameter 
        YourParameter.ParameterName = "@ID" 
        YourParameter.Value = intID
        your_data_adaptor.Parameters.Add(YourParameter) 
YourParameter = your_data_adaptor.CreateParameter 
        YourParameter.ParameterName = "@RunningTime" 
        YourParameter.Value = intRunningTime
        your_data_adaptor.Parameters.Add(YourParameter)

Hope this helps, as Vis pointed out, there's a couple of ADO.NET tutorials in my sig that also cover a lot of this.

-tg

 

[adi_uk]

I have doen what you said, although im still having problems. It just doesnt do anything, despite me setting the permissions on the folder and everything else i can think of.

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strTitle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strArtist [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intTracks [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intRunningTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intId [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2]strTitle = TextBox1.Text
strArtist = TextBox2.Text
intTracks = TextBox3.Text
intRunningTime = TextBox4.Text
intId = TextBox5.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]MyDataSet1.Clear()
OleDbDataAdapter1.DeleteCommand.CommandText = "DELETE FROM CDList WHERE (ID=" & intId.ToString & ") AND (RunningTime=" & intRunningTime.ToString & ") AND (Tracks=" & intTracks.ToString & ") AND (Artist='" & strArtist & "') AND (Title='" & strTitle & "')"
OleDbDataAdapter1.Update(MyDataSet1)
Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

 

[Neeti]

updation without sql

it is very important to make one primary key when u r updating the data.

dim dr as datarow
dr = objdataset.tables(00.newrow
dr.item(0)=1
dr.item(1)="abs"
dr.item(2)="ghjk"
objdataset.tables(0).row.add(dr)

use commandbuilder when u r using without sql , becoz it helps to generate an sql query.

 

[adi_uk]

Im still having problems with this! It just doesnt do anything - no error messages or anthing.

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Title [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = TextBox1.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Artist [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = TextBox2.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Tracks [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = TextBox3.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] RunningTime [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = TextBox4.Text
[/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("INSERT INTO CDList (Title,Artist,Tracks,RunningTime) VALUES ('" & Title & "','" & Artist & "'," & [/SIZE][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][SIZE=2](Tracks) & "," & [/SIZE][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][SIZE=2](RunningTime) & ")", OleDbConnection1)
OleDbDataAdapter1.InsertCommand = cmd
OleDbDataAdapter1.InsertCommand.Connection = OleDbConnection1
OleDbDataAdapter1.Update(MyDataSet1)
Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

 

[TechGnome]

It's just ocurred to me what it is you are trying to do..... and it won't work like that. Sorry I didn't pick up on it sooner.

Using the Insert and Delete commands is for doing updates from the dataSet to the database (where all edits and inserts & deletes are done directly to the dataSet.) In your case, how ever you are inserting data directly to the DB, bypassing the dataset.

Sooo..... what you should be doing is creating the OLEDBCommand object, set the SQL, add your parameters (or string it all together into one big SQL), and set it as the CommandText ofthe command object.

THEN.... execute the .ExecuteNonQuery method of the COMMAND......

That I think will solve your problem.....

-tg

 

[Pace]

Im still having problems with this! It just doesnt do anything - no error messages or anthing.

[SIZE=2]
[/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[COLOR=red]Me[SIZE=2].BindingContext(YourDataset, "YourTable").EndCurrentEdit()[/SIZE][/COLOR][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("INSERT INTO CDList (Title,Artist,Tracks,RunningTime) VALUES ('" & Title & "','" & Artist & "'," & [/SIZE][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][SIZE=2](Tracks) & "," & [/SIZE][SIZE=2][COLOR=#0000ff]CStr[/COLOR][/SIZE][SIZE=2](RunningTime) & ")", OleDbConnection1)
OleDbDataAdapter1.InsertCommand = cmd
OleDbDataAdapter1.InsertCommand.Connection = OleDbConnection1
OleDbDataAdapter1.Update(MyDataSet1)
Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.ToString())
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

Just try adding the red piece of code also.