Solved

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

Posted on 2008-10-24
7
177 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
  • 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

11 Experts available now in Live!

Get 1:1 Help Now