Solved

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

Posted on 2008-10-24
7
192 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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