How can I stop deleting all the records?

Question

How can I stop deleting if there is only one remaining records

For example : I add 5 new admin accounts. I delete the other four. When I delete the last one it will stop deleting?

    commandtext = "select * from tbluser where username = '" & admin & '"" 

    rd = excutereader
      dim count as integer count = 0
         while rd.read 
        count + 1 
        if count <= 1  then 
      messagebox  (" ") 
          end if  

  end while 
    con.close
     con.open 
     cmd.commadtext = delete from tbl useraccount where 
      user_id  = @id 
       with cmd.parameters
      .clear()
     .addwith value (" id " ,txtid.text) 
       Cmd.executenonquery()
   end with 
   con.close

Welcome to Stack Overflow! What have you tried so far? Share your code and please review [how-to-ask](https://stackoverflow.com/help/how-to-ask) to help you to ask a good question, and thus get a good answer — Selim Yildiz, Nov 14 '19 at 12:17
How are you deleing them now? in one mass delete? or one by one? Are you deleting them by ID? What's the criteria for the deletion? What makes the other 4 different from the 1 you want to keep? After keeping the one, you add another three, and then delete again, do you delete the new three, and keep the original one, or delete the original one and delete 2 of the new 3? — TechGnome, Nov 14 '19 at 12:27
I try it using this code commandtext = "select * from tbluser where username = '" & admin & '"" rd = excutereader dim count as integer count = 0 while rd.read count + 1 if count <= 1 then messagebox (" ") end if end while con.close con.open cmd.commadtext = delete from tbl useraccount where user_id = @id with cmd.parameters .clear() .addwith value (" id " ,txtid.text) cmd.executenonquery() end with con.close — Lyka Monique, Nov 14 '19 at 12:48
Hmmm, something seems off. You said it's deleting all records, even though there is a WHERE clause in there? — JohnPete22, Nov 14 '19 at 13:14
I try to catch all username that usertype as an admin and count it — Lyka Monique, Nov 14 '19 at 13:16
I would suggest first posting valid VB.NET code that compiles. It would make it easier to help. — HardCode, Nov 14 '19 at 17:10

Jon Roberts · Answer 1 · 2019-11-14T16:34:20.053

0

Are you trying to remove duplicate user names? So finding all matching the same name and then delete all but one? Does it matter which one you keep?

if not, try changing your SQL Command to something like this:

"Delete from tbluser  Where username = '" & admin & "'  and User_Id <> (select top 1 user_Id from tbluser where username <> '" & admin & "')"

edited Nov 14 '19 at 16:34

answered Nov 14 '19 at 15:53

Jon Roberts

2,262
2
13
17

1

Please don't encourage people to concatenate strings to build Sql commands. It is too dangerous. – Mary Nov 14 '19 at 16:11
`<> =` doesn't look valid. – Mary Nov 14 '19 at 16:11

score 0 · Answer 2 · answered Nov 14 '19 at 16:32

Your question is in essence about how to do a SQL query that would delete all but one row in a set of rows that are seen as duplicates.

Your code suggests that table "tbluser" has columns "username" and "user_id" in it. If each row has a unique user_id, then something like this might work:

DELETE FROM tbluser WHERE
    username='some_acct_name'
    AND user_id <> 
        (SELECT MIN(user_id) FROM tbluser WHERE
            username='some_acct_name')

If you are using SQL Server 2012 or later (not sure about earlier), and especially if there is nothing to distinguish one row from another (i.e. nothing like a unique user_id), then something like this could work:

DELETE TOP (4) FROM tbluser WHERE username='some_acct_name'

That will delete up to the first four rows it finds, then stops. See https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql for more details.

As has been mentioned above, you can take a look at T-SQL: Deleting all duplicate rows but keeping one for more ideas.

Once you decide on the sort of query to use, you can then code it into your VB.NET program. When you do, though, NEVER do it this way!:

' Do NOT do it like this! 
commandtext = "select * from tbluser where username = '" & admin & '"" 
' Use parameters instead! Like you did for the DELETE part of your code!

Search the web (google, bing, etc.) on "vb net sql server examples parameterized query" for examples on using parameters. E.g., https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx.

Finally, here's a query to get a count of all your duplicate rows, which is nice (and fast) - no need to do loops!:

SELECT COUNT(*) FROM tbluser WHERE username='some_acct_name'

score 0 · Answer 3 · answered Nov 14 '19 at 16:49

Since you didn't say what database you are using I used Sql Server. You can substiture whatever provider you are using.

Using...End Using blocks will close and dispose your database objects even if there is an error.

You can pass your connection string directly to the constructor of the connection. Likewise pass the command text and connection directly to the constructor of the command.

Most databases have a Count function which will do the counting for you. Then you can use .ExecuteScalar to retrieve the single value that the Count function returns.

Don't do this dance of closing and opening the connection. Just open is just before the .Execute... and close it as soon as posible. The End Using does the closing and disposing.

Don't use .AddWithValue. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

I had to guess at the datatype of the id field. Check your database for the actual type.

There is no need to clear the Parameters collection when your cmd is local. You can see that the only parameter added is the id.

Private Sub OPCode()
    Dim count As Integer
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("Select Count(*) From tbluser where usertype = 'admin';", cn)
        cn.Open()
        count = CInt(cmd.ExecuteScalar)
    End Using
    If count > 1 Then
        Using cn As New SqlConnection("Your connection strin"),
                cmd As New SqlCommand("Delete From tbluser Where user_id = @id;", cn)
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = CInt(txtid.text)
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End If
End Sub

How can I stop deleting all the records?

3 Answers3