Cant understand No problem in code but not deleting from db

  • (2 Pages)
  • +
  • 1
  • 2

23 Replies - 810 Views - Last Post: 06 December 2013 - 03:54 PM Rate Topic: -----

#1 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 06:57 PM

the code which i have produced has no error but it is not deleting the fields from the database i dont understand why


        strsql = "delete from  cg_security_user_right  where user_id=@field1 & right_id=@field2 "
        Dim objcmd As New System.Data.OleDb.OleDbCommand(strsql, acsconn) ' the oledbcommand
        With objcmd
            .Parameters.AddWithValue("@field1", TextBox1.Text)
            .Parameters.AddWithValue("@field2", TextBox2.Text)

        End With
        objcmd.ExecuteNonQuery()
        objcmd.Dispose()
        MsgBox("deleted")
    End Sub




please help been scratching my head for a while trying to figure this one out. it run fines just does not delete

Is This A Good Question/Topic? 0
  • +

Replies To: Cant understand No problem in code but not deleting from db

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,981
  • Joined: 08-June 10

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:05 PM

What type of database are you using?

My first guess would be that you are using & (bitwise AND) in your WHERE clause where you should be using AND (boolean AND).
Was This Post Helpful? 0
  • +
  • -

#3 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:08 PM

i am using ms access. i will try the & conversion i have not writen a code for boolean yet so i am not sure how it would b

still the same issue when i change the & to and it runs the whole script with no errors but doesnt delete the record : :sad2:
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:15 PM

IF you followed atli's advice and it's not deleting then what you are putting in textbox1 and textbox2 are not found in the database.
Was This Post Helpful? 0
  • +
  • -

#5 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:19 PM

in my database i am picking the user_id and the right_id

i have put test data in the table. and it is still not deleting it

How would i write the code for Boolean if i am using parameters
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:23 PM

I think you're mis-understanding what Atli was saying. You used delete from table where this = this & that = that. In that case you used a bitwise check. Whereas SQL syntax require a boolean result meaning that you want both conditions to be true therefore you would use AND in place of &. That's all he as saying.

You Delete statement (with Atli's correction of AND instead of &) will delete a row as long as BOTH conditions are found. Apparently, both conditions are not being found to be true.
Was This Post Helpful? 0
  • +
  • -

#7 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:27 PM

ok thats good to no that i am on the right lines and it has made more sence what you are saying. is there a structre that i would follow

like i have done this = this and that = that

would with a boolean would it be this =this and true & that =that and true

would the be similiar to this
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:32 PM

No. Just follow the DELETE syntax is all.

What you can do is this:
...
  With objcmd
	        .Parameters.AddWithValue("@field1", TextBox1.Text)
	        .Parameters.AddWithValue("@field2", TextBox2.Text)
	 
  End With
dim result as integer = 0
  result = objcmd.ExecuteNonQuery()
    objcmd.Dispose()
    MsgBox(result & " Row Deleted")
End Sub



Now run your program with the same entries and see if it doesn't respond with a messagebox that reads:
0 Row Deleted.

If So then you are not finding a row that contains both values to be true.
Was This Post Helpful? 1
  • +
  • -

#9 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:40 PM

Thank you that has helped a bit but i am strugling to understand why it is saying 0 row deleted.

The reason i am confused is because i have a gridview in my form which is showing me all my user_id and my role_ids

so i am selecting from user_ids & roles_ids from within the table how can that not be true
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:48 PM

Hard to say but there is obviously something not matching which is why I can confidently tell you that the result is 0 without ever seeing any of the information you are inputting.

So maybe show us the information you are displaying in your grid and the values entered into textbox1 and textbox2
Was This Post Helpful? 0
  • +
  • -

#11 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 07:53 PM

ok

in the gridview


----------------------------
user_id ///////// role_id
2 1
3 2
_____________________________

now wen i type in the text box 2 and 1. it still returns the value 0 rows deleted which is confusing
Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 08:03 PM

So (under the influence of alcoholic beverages) I'm going to use my super memory powers and remember earlier today that you asked how to join two tables so that you could display both the user_id and the role_id in a datagridview. I'm then going to use those same powers to make a huge assumption. That assumption would be that User_ID resides in a totally separate table than role_id. Am I right so far?

By now, you should be making a determination that since you joined two tables to get this result that you can in no way compare those two result in a single table which DELETE FROM is limited to doing.

You will need two separate delete statements that delete the appropriate information from each table.

Oh, and don't forget to refresh your dataset after doing this so that the datagridview displays the updated "Joined" information.
Was This Post Helpful? 0
  • +
  • -

#13 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 08:07 PM

yes charlie may you right about my earlier question but this delete function is within one table. and i dont understand why it is not working what could be the cause

 strsql = "delete from  cg_security_user_right  where user_id=@field1 &  right_id=@field2 "
        Dim objcmd As New System.Data.OleDb.OleDbCommand(strsql, acsconn) ' the oledbcommand
        With objcmd
            .Parameters.AddWithValue("@field1", TextBox1.Text)
            .Parameters.AddWithValue("@field2", TextBox2.Text)

        End With
        Dim result As Integer = 0
        result = objcmd.ExecuteNonQuery()
        objcmd.Dispose()
        MsgBox(result & " Row Deleted")
    End Sub

Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 08:12 PM

Show me the Fields you have in your cg_security_user_right table.
Also, show me what data is contained in the 1st row of that table for each field. (field = column)
Was This Post Helpful? 0
  • +
  • -

#15 farhan_b  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 133
  • Joined: 26-February 12

Re: Cant understand No problem in code but not deleting from db

Posted 02 March 2012 - 08:18 PM

this link is to a picture of what is in my cg_security _user_right table

http://www.flickr.co...N06/6947997321/
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2