0

I am trying to delete rows in database in sql from datagridview. Can you please help?

Private Sub cmdDelete_Click(sender As Object, e As EventArgs) Handles cmdDelete.Click

    SQL.DataUpdate("DELETE FROM Exp_Table2 WHERE ExpInd_ID = ??")

End Sub
Improve this question
7
  • I"m not familiar with method DataUpdate. Is it this one:msdn.microsoft.com/en-us/library/aa302325.aspx. Commented Mar 17, 2017 at 11:57
  • @Nick.McDermaid Thank you for your response. I am trying to delete rows in database. How do I delete by referencing ID of rows in database table to id of selected rows in datagridview (where ExpInd_ID = ???) Commented Mar 17, 2017 at 12:02
  • @Nick.McDermaid I have created DataUpdate as sql control function/method to delete and update the database. Commented Mar 17, 2017 at 12:06
  • 1
    Basically your question is: how do I replace the question marks with something from my datagrid. Does your table have a primary key, and is that primary key captured on the datagrid? Is it a string or a number? You can just concatenate it into the string, although that invites sql injection (a bad thing) Commented Mar 17, 2017 at 12:15
  • @Nick.McDermaid Yes, my question is how do I replace the question mark with something from my datagridview. My table has primary key, which is ExpInd_ID and it is an integer. Can you please share one sample code for concatenation. Commented Mar 17, 2017 at 12:21

1 Answer 1

Reset to default
2

I'll be that "someone less grumpy who will probably just give you all the code you need" then I guess.

As Nick said, ideally you should never concatenate strings into SQL, as any value with a single quote (') in it will break it, but not only that, hackers can do all sorts of things to your database too. I'd suggest researching SQL parameters.

But, for the sake of this question, once you've followed the link that Nick provided to get the value out of the DataGridView (There are many ways to do this, you could also use the row index along with the column index/column name, but whatever way you want to do it is fine), you'll need to assign this value to a variable of the same datatype as the value you've extracted.

The final part of that would to then use

SQL.DataUpdate("DELETE FROM Exp_Table2 WHERE ExpInd_ID = '" & yourVariable & "'")

Again, as I said - I do not advocate concatenation in SQL statements, but I used to do it too when I did my first ever project for college, so I understand we all need to start somewhere.

In future, though, do use parameters and then ask for help when you encounter a specific problem.

Parameters help to prevent against hackers using commands like DROP TABLE after inserting a single quote, which would delete the entire table (and all data in it) that the query is operating on, so always best to use parameters, which prevent this.

An example of how to use them would be as below (This is one query I have in a project I'm working on currently)

For Each dr As DataRow In dt.Rows
   sql = "SELECT * FROM [Sales Headers] WHERE [Order_Number] = ? AND [Order_Cancelled] = ? AND [Stage] < ?"
   cmd = New OleDbCommand(sql, con)
   cmd.Parameters.Add("@num", OleDbType.Integer).Value = dr.Item("Order_Number")
   cmd.Parameters.Add("@can", OleDbType.Boolean).Value = False
   cmd.Parameters.Add("@stage", OleDbType.Integer).Value = 7
   da = New OleDbDataAdapter(cmd)
   da.Fill(commTable)
Next
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This would be a great answer if you demonstrated how to use parameters here instead of just stating that they should. This type of query is incredibly dangerous. I know we all started somewhere but unless they understand the dangers AND how to overcome them they will continue writing vulnerable code like this until bobby tables comes to visit.
@SeanLange Okay, thanks, point noted. I've updated the answer with a little more information in to help the OP.
@mallan1121 thank you so much for taking time in explaining and helping me out. As you suggested I used the variable and worked for now. I will try to use parameters when I have time. Thanks a lot.
@Sonam David provided the answer I just put in some edits.
@David sorry for the late response. Thank you very much for the late response.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.