Delete a row in a dynamic table - place help

[Newbie81]

hello all,

I have created a dynamic table that displays data from a sql table. I have added delete links beside each row. Everytime the delete link is pressed i want it to delete that particular row. I know that you need to have a key field, which i have already created.

Here is what i have done:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = SqlConnection1.CreateCommand[/SIZE]
[SIZE=2]cmd.Connection = SqlConnection1[/SIZE]
[SIZE=2]cmd.CommandType = CommandType.Text[/SIZE]
[SIZE=2]cmd.CommandText = "SELECT * " [/SIZE]
[SIZE=2]cmd.CommandText &= "FROM Schoolmates "[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dsAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dsDataset [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]dsAdapter.SelectCommand = cmd[/SIZE]
[SIZE=2]dsAdapter.Fill(dsDataset)[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowNumber [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int16[/SIZE]
[SIZE=2][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] rowNumber = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] dsDataset.Tables(0).Rows.Count - 1[/SIZE]
 
[SIZE=2][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] rowNumber = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] 3[/SIZE]
[SIZE=2]Response.Write("<tr>")[/SIZE]
[SIZE=2]Response.Write("<td>" & dsDataset.Tables(0).Rows(rowNumber).Item("Forename") & "</td>")[/SIZE]
[SIZE=2]Response.Write("<td>" & dsDataset.Tables(0).Rows(rowNumber).Item("Surname") & "</td>")[/SIZE]
[SIZE=2]Response.Write("<td>" & dsDataset.Tables(0).Rows(rowNumber).Item("Telephone") & "</td>")[/SIZE]
[SIZE=2]Response.Write("<td>" & dsDataset.Tables(0).Rows(rowNumber).Item("Email") & "</td>")[/SIZE]
[SIZE=2]Response.Write("<td><a href='webform1.aspx?delete=" & dsDataset.Tables(0).Rows(rowNumber).Item("Email") & "'>Delete</a></td></tr>")[/SIZE]
[SIZE=2]Response.Write("<td><a href='frmupdate.aspx?update=" & dsDataset.Tables(0).Rows(rowNumber).Item("Email") & "'>Update</a></td></tr>")[/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] rowNumber[/SIZE]
[SIZE=2]Response.Write("</table>")[/SIZE]

I am wondering how i would implement this delete query or stored procedure.

I thank all you experts in advance.
[/SIZE][/SIZE][/SIZE]

 

[Paszt]

There are some good video tutorials on MSDN: Video training for absolute beginner Web developers.
Lessons 7 & 8 are specific to data access.

 

[Berry-tan]

You could create a stored procedure to delete one specific record, but that might be overkill. Here are a few of my recommendations:

• Use an AutoNumber as a primary key (if you're not already doing so).
• Use the primary key to refer to a row to delete. That way, you can prevent SQL injection attacks by simply parsing an integer in a Try...Catch statement; if an exception is thrown, it's not valid.

The SQL code, if you're interested, would be:

DELETE TOP 1 FROM Schoolmates WHERE PrimaryKey=[B][Number][/B]

(The "TOP 1" speeds up the query a bit by specifying you only want to delete one row. It's completely optional, of course.)