Solved

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

Posted on 2008-10-24
7
183 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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