Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Issue with having multiple list boxes with delete button going to 1 stored procedure

Posted on 2008-10-24
7
Medium Priority
?
199 Views
Last Modified: 2012-05-05
Hi,

I have an issue that concerns having multiple ASP.NET list boxes that each have a a delete button.  The Delete buttons all use the same stored procedure that has variables and conditions that will delete the correct entries in different tables

The issue that I have is that for some odd reason or another it seems to work once.  After that it I click on a delete button then I will get an error in a different delete method that I am not currently accessing

Below is my code and SQL Script

SQL Script:

ALTER procedure [dbo].[Delete_listItem]
@aID numeric = null,
@iID numeric = null

as

if @aID is not null
begin
delete from tbl_ActionType where id = @aID
end
else if @iID is not null
begin
delete from tbl_IssueTypes where id = @iID
end
VB.NET Code:
 
Imports System.Data
Imports System.Data.SqlClient
 
Partial Class admin
    Inherits System.Web.UI.Page
    Dim user As String
    Dim EditID As String
    Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        user = ListBox1.SelectedValue.ToString
        EditID = ListBox1.SelectedValue.ToString
    End Sub
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
 
    End Sub
 
    Protected Sub btnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        editID = ListBox1.SelectedValue.ToString
        If editID = "" Then
            Exit Sub
        Else
            ClientScript.RegisterStartupScript(Me.GetType(), _
             "Text", "<script language='javascript'>window.open('EditUser.aspx?editID=" & _
             editID & "', 'users','channelmode=no, left=400, top=100, height=389, width=247, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
        End If
    End Sub
 
    Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        ' editID = ""
        ClientScript.RegisterStartupScript(Me.GetType(), _
                     "Text", "<script language='javascript'>window.open('CreateSiteAdmin.aspx', 'users','channelmode=no, left=400, top=100, height=430, width=247, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
    End Sub
 
    Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If MsgBox("Are you sure you want to delete this user", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.Yes Then
            user = ListBox1.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_MrvUser"
            sqlCommand0.Connection = SqlCon1
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserName", user))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
           
        End If
 
 
    End Sub
 
    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
        Dim sConnSTr1 As String
        sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
        SqlCon1.ConnectionString = sConnSTr1
        SqlCon1.Open()
        Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
        sqlCommand0.CommandType = Data.CommandType.StoredProcedure
        sqlCommand0.CommandText = "Select_Users"
        sqlCommand0.Connection = SqlCon1
        Dim adapter As New SqlDataAdapter(sqlCommand0)
        Dim ds As New DataSet()
        adapter.Fill(ds)
        ListBox1.DataSource = ds.Tables(0)
        ListBox1.DataTextField = ds.Tables(0).Columns(1).ToString()
        ListBox1.DataValueField = ds.Tables(0).Columns(0).ToString()
        ListBox1.DataBind()
        ListBox2.DataSource = ds.Tables(1)
        ListBox2.DataTextField = ds.Tables(1).Columns(1).ToString()
        ListBox2.DataValueField = ds.Tables(1).Columns(0).ToString()
        ListBox2.DataBind()
        lstActionTypes.DataSource = ds.Tables(2)
        lstActionTypes.DataTextField = ds.Tables(2).Columns(1).ToString()
        lstActionTypes.DataValueField = ds.Tables(2).Columns(0).ToString()
        lstActionTypes.DataBind()
        lstIssueType.DataSource = ds.Tables(3)
        lstIssueType.DataTextField = ds.Tables(3).Columns(1).ToString()
        lstIssueType.DataValueField = ds.Tables(3).Columns(0).ToString()
        lstIssueType.DataBind()
        lstPriority.DataSource = ds.Tables(4)
        lstPriority.DataTextField = ds.Tables(4).Columns(1).ToString()
        lstPriority.DataValueField = ds.Tables(4).Columns(0).ToString()
        lstPriority.DataBind()
        lstFBtypes.DataSource = ds.Tables(5)
        lstFBtypes.DataTextField = ds.Tables(5).Columns(1).ToString()
        lstFBtypes.DataValueField = ds.Tables(5).Columns(0).ToString()
        lstFBtypes.DataBind()
        SqlCon1.Close()
 
    End Sub
 
    Protected Sub btnSCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSCreate.Click
        ClientScript.RegisterStartupScript(Me.GetType(), _
                     "Text", "<script language='javascript'>window.open('crSupplier.aspx', 'users','channelmode=no, left=400, top=100, height=510, width=315, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
    End Sub
 
    Protected Sub btnSEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSEdit.Click
        Dim sEditID As String = ListBox2.SelectedValue.ToString
        If sEditID = "" Then
            Exit Sub
        Else
            ClientScript.RegisterStartupScript(Me.GetType(), _
             "Text", "<script language='javascript'>window.open('edSupplier.aspx?sEditID=" & _
             sEditID & "', 'users','channelmode=no, left=400, top=100, height=400, width=325, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
        End If
    End Sub
 
    Protected Sub btnSDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSDelete.Click
        If MsgBox("Are you sure you want to delete this user", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.Yes Then
            user = ListBox2.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_Supplier"
            sqlCommand0.Connection = SqlCon1
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserName", user))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
            
        End If
 
    End Sub
 
    Protected Sub btnATadd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnATadd.Click
        ClientScript.RegisterStartupScript(Me.GetType(), _
                     "Text", "<script language='javascript'>window.open('crActionType.aspx', 'users','channelmode=no, left=400, top=100, height=215, width=210, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
    End Sub
 
    Protected Sub btnATedit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnATedit.Click
        Dim aEditID As String = lstActionTypes.SelectedValue.ToString
        If aEditID = "" Then
            Exit Sub
        Else
            ClientScript.RegisterStartupScript(Me.GetType(), _
             "Text", "<script language='javascript'>window.open('edActionType.aspx?aEditID=" & _
             aEditID & "', 'users','channelmode=no, left=400, top=100, height=215, width=210, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
        End If
    End Sub
 
    Protected Sub btnATdelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnATdelete.Click
        If MsgBox("Are you sure you want to delete this user", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.Yes Then
            Dim aID As String = lstActionTypes.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_listItem"
            sqlCommand0.Connection = SqlCon1
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@aID", aID))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
        End If
    End Sub
 
    Protected Sub btnITadd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnITadd.Click
        ClientScript.RegisterStartupScript(Me.GetType(), _
                     "Text", "<script language='javascript'>window.open('crIssueTypes.aspx', 'users','channelmode=no, left=400, top=100, height=215, width=210, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
    End Sub
 
    Protected Sub btnITedit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnITedit.Click
        Dim iEditID As String = lstIssueType.SelectedValue.ToString
        If iEditID = "" Then
            Exit Sub
        Else
            ClientScript.RegisterStartupScript(Me.GetType(), _
             "Text", "<script language='javascript'>window.open('edIssueType.aspx?iEditID=" & _
             iEditID & "', 'users','channelmode=no, left=400, top=100, height=215, width=210, menubar=no,status=yes,directories=no, resizable=no, scrollbars=no, titlebar=no');</script>")
        End If
    End Sub
 
    Protected Sub btnITdelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnITdelete.Click
        If MsgBox("Are you sure you want to delete this user", MsgBoxStyle.YesNo, "Delete?") = MsgBoxResult.Yes Then
            Dim iID As String = lstIssueType.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_listItem"
            sqlCommand0.Connection = SqlCon1
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iID", iID))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
        End If
    End Sub

Open in new window

0
Comment
Question by:thomasm1948
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Author Comment

by:thomasm1948
ID: 22797921
OK,

The error that I am getting is on the same method and it is

Error converting data type nvarchar to numeric.

And this only happens if I switch back and forth deleting items from the list boxes too fast..i think
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 22797930
try

SQL 
 
 
 
 
if @aID is not null or @aID is ''
begin
delete from tbl_ActionType where id = @aID
end
else if @iID is not null or @iID is ''
begin
delete from tbl_IssueTypes where id = @iID
end
 
 
 
 
 
 
 
   Protected Sub btnATdelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnATdelete.Click
            Dim aID As String = lstActionTypes.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_listItem"
            sqlCommand0.Connection = SqlCon1
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@aID", aID))
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iID", string.empty))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
        End If
    End Sub
 
    Protected Sub btnITdelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnITdelete.Click
            Dim iID As String = lstIssueType.SelectedValue.ToString
            Dim SqlCon1 As New System.Data.SqlClient.SqlConnection
            Dim sConnSTr1 As String
            sConnSTr1 = "server=tomkat\SQLEXPRESS;uid=CPDuser;pwd=Newpass01;database=CPD_Feedback"
            SqlCon1.ConnectionString = sConnSTr1
            SqlCon1.Open()
            Dim sqlCommand0 As New System.Data.SqlClient.SqlCommand
            sqlCommand0.CommandType = Data.CommandType.StoredProcedure
            sqlCommand0.CommandText = "Delete_listItem"
            sqlCommand0.Connection = SqlCon1
sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@aID", string.empty))
            sqlCommand0.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iID", iID))
            sqlCommand0.ExecuteNonQuery()
            SqlCon1.Close()
        End If

Open in new window

0
 

Author Comment

by:thomasm1948
ID: 22797971
OK,

I figured it out.  I was trying to convert a string to SQL numeric.  If I try to access the stored procedure too fast then it would not concvert.  I changed my variable in my VB code ans short and it seems to work

One last question.  Can someone check my stored procedure.  I am not sure if it is the most proficient way to do it

thank you
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:thomasm1948
ID: 22798012
thank you for your response
should the t-sql part be with not ' '

I am not sure on this one.  If the string is empty would it not still execute the part of the stored procedure that should not be accessed with adding:

" or is ' ' "

thank you for all of your help



if @aID is not null or @aID is not''
begin
delete from tbl_ActionType where id = @aID
end
else if @iID is not null or @iID is not ''
begin
delete from tbl_IssueTypes where id = @iID
end

Open in new window

0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 22798139
By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. Rarely is this information useful to the client. By turning off this default behavior, you can reduce network traffic between the server and the client, helping to boost overall performance of your server and applications.

There are two main ways to turn this feature off. You can also turn this feature off using a server trace setting, but it is unnecessary as there are easier ways, as described here.

To turn this feature off on at the stored procedure level, you can include the statement:

SET NOCOUNT ON

Good article -
http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx
0
 
LVL 7

Accepted Solution

by:
ASPSQLServerCOM earned 2000 total points
ID: 22798159
****should the t-sql part be with not ' '
yes, you are correct

many times if the number of SQL input paramerter are not same then some time its create problem, so its better to supply, that what I use to do, even if you are not using, because tomorrow you never know due to that it may affect somewhere and your SP fail
0
 

Author Closing Comment

by:thomasm1948
ID: 31509724
Thank you for all of your help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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