Solved

Delete records in repeater

Posted on 2004-10-18
11
260 Views
Last Modified: 2011-09-20
Hello,
I have a data repeater that displays database records in a table.  I want to add a "delete" button in the repeater so that a user can press the button to delete the corresponding record but I do not know how to pass that value back to the SQL "DELETE" statement.  Can someone help?

Thanks.
0
Comment
Question by:liquid22
  • 6
  • 5
11 Comments
 
LVL 20

Expert Comment

by:jitganguly
ID: 12340345
onclick of Delete call a procedure and isse Delete statement on that

Post code
0
 
LVL 1

Author Comment

by:liquid22
ID: 12340601
Yeah that's what I tried to do but didn't know how to pass the record ID value into the SQL statement.

Here is the code that builds the repeater table:
Sub Page_Load(Source As Object, E As EventArgs)
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
Dim sSQL As String = "SELECT FILE_ID, FILE_NAME FROM tblMgmtReview ORDER BY FILE_NAME"
Dim objCmd As New SqlDataAdapter(sSQL,objConn)
Dim objDS As New DataSet()
objCmd.Fill(objDS, "tblMgmtReview")
MgmtOutput.DataSource = objDS.Tables("tblMgmtReview").DefaultView
MgmtOutput.DataBind()
objConn.Close
End Sub
------DELETE CODE HERE----------
Sub cmdDelete_OnClick(Source As Object, E As EventArgs)
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = @FileID"      
Dim objCmd As SqlCommand = New SqlCommand(sSQL,objConn)
objCmd.Parameters.Add(New SqlParameter("@FileID", <not sure what to put here>))
Try
objConn.Open()
objCmd.ExecuteNonQuery
lblMessage.Text = "Record successfully deleted.<br />"
Catch SqlEx As exception
lblMessage.Text = "There was an error:&nbsp;" & SqlEx.ToString()
Finally
objConn.Close()
End Try
End Sub
------------------ REPEATER TABLE -------------------
<form runat="server">
<asp:Repeater ID="MgmtOutput" OnItemCommand="onClick_Delete" runat="server">
<headertemplate>
<table width="100%" style="border:1px solid #000000;" cellpadding="0" cellspacing="0">
<tr>
<td style="font-weight:bold;background-color:#666666;color:#FFFFFF;">ID:</td>
<td style="font-weight:bold;background-color:#666666;color:#FFFFFF;">File Name:</td>
<td style="font-weight:bold;background-color:#666666;color:#FFFFFF;">&nbsp;</td>
</tr>
</headertemplate>
<itemtemplate>
<tr>
<td style="border:1px solid #000000;"><%#DataBinder.Eval(Container.DataItem,"FILE_ID")%></td>
<td style="border:1px solid #000000;"><%#DataBinder.Eval(Container.DataItem,"FILE_NAME")%></td>
<td style="border:1px solid #000000;"><asp:Button ID="btnDelete" OnClick="cmdDelete_OnClick" Text="Delete" runat="server" /></td>
</tr>
</itemtemplate>
<footertemplate>
</table>
</footertemplate>
</asp:Repeater>
</form>

I'm new to ASP.NET so please excuse my code if it's "messy".
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 12340860
<td style="border:1px solid #000000;"><asp:Button ID="btnDelete" OnClick="cmdDelete"  CommandArgument='<%#Container.DataItem(FILE_ID")%>' Text="Delete" runat="server" /></td>


Sub cmdDelete(Source As Object, E As EventArgs)
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = "  & FILE_ID
Dim objCmd As SqlCommand = New SqlCommand(sSQL,objConn)
objCmd.Parameters.Add(New SqlParameter("@FileID", <not sure what to put here>))
Try
objConn.Open()
objCmd.ExecuteNonQuery
lblMessage.Text = "Record successfully deleted.<br />"
Catch SqlEx As exception
lblMessage.Text = "There was an error:&nbsp;" & SqlEx.ToString()
Finally
objConn.Close()
End Try
End Sub
0
 
LVL 1

Author Comment

by:liquid22
ID: 12341080
I get this error now:
Method 'Public Sub onClick_Delete(Source As Object, e As System.EventArgs)' does not have the same signature as delegate 'Delegate Sub RepeaterCommandEventHandler(source As Object, e As System.Web.UI.WebControls.RepeaterCommandEventArgs)'.
0
 
LVL 1

Author Comment

by:liquid22
ID: 12341122
Actually disregard that last error.  Here is the error I get now:

There was an error: System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'FILE_ID' to a column of data type int. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 20

Expert Comment

by:jitganguly
ID: 12341140
Oh put it within quotes

Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = '"  & FILE_ID & "'"

This has
1 single quote after =
then a double quote
Then & File_ID & and then a double quoute
then a single quote
then a double quote

0
 
LVL 1

Author Comment

by:liquid22
ID: 12341189
Hi again,
I had some syntax wrong but when I re-tried the code it delete everything in the entire table, not just the record I wanted to delete.  Not sure what happened there but here was the delete code I used:

Sub onClick_Delete(Source As Object, e As EventArgs)
Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = " & "FILE_ID" '
Dim objCmd As SqlCommand = New SqlCommand(sSQL,objConn)
Try
objConn.Open()
objCmd.ExecuteNonQuery
lblMessage.Text = "Record successfully deleted.<br />"
Catch SqlEx As exception
lblMessage.Text = "There was an error:&nbsp;" & SqlEx.ToString()
Finally
objConn.Close()
End Try
End Sub
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 12341207
Again
Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = " & "FILE_ID" '
is wrong
do exactly as I described above

The reason for deleteting all records is all of them got File_ID = passed id. Check that
0
 
LVL 1

Author Comment

by:liquid22
ID: 12341302
Okay I corrected the syntax but still receive this error:

There was an error: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'FILE_ID' to a column of data type int. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I'll be sure to increase my points if we can get this fixed.
0
 
LVL 20

Accepted Solution

by:
jitganguly earned 200 total points
ID: 12341321
Whats the data type of File_ID

If its int then use

Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = " & FILE_ID

if its char use
Dim sSQL As String = "DELETE FROM tblMgmtReview WHERE FILE_ID = '" & FILE_ID & "'"


0
 
LVL 1

Author Comment

by:liquid22
ID: 12341404
That did it - thanks.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now