Cannot Delete record from SQL Database

[abathurst]

Hi,

I am new to ASP.NET and i have been thrown into the deep end on a project, the person who created this code is no longer available. The website allows an Admin to login and edit, update or delete information in an SQL database referring to PDFs located on the server. the issue comes when trying to delete a record from the database.

This is the error i get:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Procedure or Function 'Delete_Publication' expects parameter '@UID', which was not supplied. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'Delete_Publication' expects parameter '@UID', which was not supplied.

Source Error: 


Line 92: 
Line 93:         myConnection.Open()
Line 94:         myCommand.ExecuteNonQuery()
Line 95:         myConnection.Close()
Line 96:

This is the code that is causing grief:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic


Partial Class Delete_Exist_Publication
    Inherits System.Web.UI.Page

    Dim a, b, c, d As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        a = Request.QueryString("Title")
        b = Request.QueryString("RefNo")
        c = Request.QueryString("Rowguid")

        Delete_Publication(b, c)
        Delete_File()
        Response.Write("<script>window.opener.location.href = window.opener.location.href;</script>")
        Response.Write("<script>window.close();</script>")

        'If Not Me.IsPostBack Then
        '    If Session("UserID") = "" Or Session("Active") = "False" Then
        '        Response.Redirect("Login.aspx")

        '    ElseIf Session("UserID") <> "" And Session("Active") = "True" Then
        '        'Me.lblLoginId.Text = "You are logged in as: " + Session("UserID")
        '        'Me.lblWorkType.Text = "Your Work Type is " + Session("WorkType")

        '        Approve_EventCalendar(b, c)
        '        Response.Write("<script>window.opener.location.href = window.opener.location.href;</script>")
        '        Response.Write("<script>window.close();</script>")



        '    End If
        'Else
        '    If Session("UserID") = "" Or Session("Active") = "False" Then
        '        Response.Redirect("Login.aspx?Name=true")

        '    ElseIf Session("UserID") <> "" Then
        '        'PopulateDetails(Session("UserID"))
        '        'Me.lblLoginId.Text = "You are logged in as: " + Session("UserID")
        '        'me.lblWorkType.Text = "Your Work Type is " + Session("WorkType")
        '        'populate_Worktype()
        '        ' populate_SubType()
        '        'populate_JobDetail(c)

        '    End If
        'End If


    End Sub

    Private Sub ExpirePageCache()

        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.Cache.SetExpires(DateTime.Now - New TimeSpan(1, 0, 0))
        Response.Cache.SetLastModified(DateTime.Now)
        Response.Cache.SetAllowResponseInBrowserHistory(False)

    End Sub



    Private Sub Delete_Publication(ByVal RefNO As String, ByVal Rowguid As String)

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        Dim myCommand As SqlCommand = New SqlCommand("Delete_Publication", myConnection)

        ' Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure

        ' Add Parameters to SPROC
        'Session("UserID") = "Admin"

        '@UID varchar(20)
        Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.VarChar, 20)
        parameterUID.Value = Session("UserID")
        myCommand.Parameters.Add(parameterUID)

        '@RefNo varchar(20)
        Dim parameterRefNO As SqlParameter = New SqlParameter("@RefNo", SqlDbType.VarChar, 20)
        parameterRefNO.Value = RefNO
        myCommand.Parameters.Add(parameterRefNO)

        '@Rowguid varchar(30)
        Dim parameterRowguid As SqlParameter = New SqlParameter("@Rowguid", SqlDbType.VarChar, 40)
        parameterRowguid.Value = Rowguid
        myCommand.Parameters.Add(parameterRowguid)

        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()

    End Sub

    Private Sub Delete_File()

        Try

            Dim TheFile As FileInfo = New FileInfo(MapPath(".") & "\pdf\" & b + ".pdf")
            If TheFile.Exists Then

                File.Delete(MapPath(".") & "\pdf\" & b + ".pdf")
                'Me.lblFileInfo.Text = "You have successfully deleted the " + a + ".pdf file"
                'Me.lblStatus.Visible = False

            Else

                Throw New FileNotFoundException()
            End If

        Catch ex As FileNotFoundException

            lblStatus.Text += ex.Message
        Catch ex As Exception

            lblStatus.Text += ex.Message
        End Try

    End Sub



End Class

Any help would be great.
Thanks
Aaron

[abathurst]

Sorry that's not the right error. It's this:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Conversion failed when converting from a character string to uniqueidentifier. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.

Source Error: 


Line 92: 
Line 93:         myConnection.Open()
Line 94:         myCommand.ExecuteNonQuery()
Line 95:         myConnection.Close()
Line 96:

[Akash Saikia]

Hi,the problem seems to be in conversion.

The stored Procedure expects parameters of type Int or Bigint or numeric that is too defined in Autoincrement mode within the database.

The problem is here
1 ) SqlDbType.VarChar for parameterUID -- define it as int or int32 or something in numeric
2 ) SqlDbType.VarChar for parameterRefNO -- as above
3 ) While passing it like this myCommand.Parameters.Add(parameterUID)
convert it into integer type in this way myCommand.Parameters.Add(Convert.ToInt32 (parameterUID))

Here is your changed code...

'@UID varchar(20)
Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.Int32)
parameterUID.Value = Session("UserID")
myCommand.Parameters.Add(Convert.ToInt32(parameterUID))
'@RefNo varchar(20)
Dim parameterRefNO As SqlParameter = New SqlParameter("@RefNo", SqlDbType.Int32)
parameterRefNO.Value = RefNO
myCommand.Parameters.Add(Convert.ToInt32(parameterRefNO))
'@Rowguid varchar(30)
Dim parameterRowguid As SqlParameter = New SqlParameter("@Rowguid", SqlDbType.Int32)
parameterRowguid.Value = Rowguid
myCommand.Parameters.Add(Convert.ToInt32(parameterRowguid))

Hope it helps :)

[abathurst]

I've made those changes but is now coming up with this error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30456: 'Int32' is not a member of 'System.Data.SqlDbType'.

Source Error:

 

Line 77: 
Line 78:         '@UID varchar(20)
Line 79:         Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.Int32)
Line 80:         parameterUID.Value = Session("UserID")
Line 81:         myCommand.Parameters.Add(Convert.ToInt32(parameterUID))

[Akash Saikia]

Ohh,sorry for that,replace SqlDbType.Int32 with this

SqlDbType.Int

[abathurst]

I have changed the code as you said to this:

'@UID varchar(20)
        Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.Int)
        parameterUID.Value = Session("UserID")
        myCommand.Parameters.Add(Convert.ToInt32(parameterUID))

        '@RefNo varchar(20)
        Dim parameterRefNO As SqlParameter = New SqlParameter("@RefNo", SqlDbType.Int)
        parameterRefNO.Value = RefNO
        myCommand.Parameters.Add(Convert.ToInt32(parameterRefNO))

        '@Rowguid varchar(30)
        Dim parameterRowguid As SqlParameter = New SqlParameter("@Rowguid", SqlDbType.Int)
        parameterRowguid.Value = Rowguid
        myCommand.Parameters.Add(Convert.ToInt32(parameterRowguid))

It comes up with this error

Server Error in '/' Application.
--------------------------------------------------------------------------------

Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'System.IConvertible'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'System.IConvertible'.

Source Error: 


Line 79:         Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.Int)
Line 80:         parameterUID.Value = Session("UserID")
Line 81:         myCommand.Parameters.Add(Convert.ToInt32(parameterUID))
Line 82: 
Line 83:         '@RefNo varchar(20)

[Akash Saikia]

Hey,did you check that at the backend what is the data type of UID and all in your Stored procedure???
Check it out and let me know.
And also try this..

myCommand.Parameters.AddWithValue ("@UID",Convert.ToInt32(parameterUID))

and replace these two lines

Dim parameterUID As SqlParameter = New SqlParameter("@UID", SqlDbType.Int)
parameterUID.Value = Session("UserID")

[abathurst]

UID is varchar

also made those changes and still coming up with same error

[Akash Saikia]

Do one thing,post your stored procedure code here,and let me try to find for any bug there.

But before doing that,try to execute that stored procedure in your Sql server 2005 or any version,by right clicking on your sql procedure for deletion,and pass proper values for deleting it.

[abathurst]

To be honest I'm not sure what or where the stored procedure is. Im very new to all this and have been thrown in the deep end.

If you can give me some ideas of what im looking for I should be able to find it.

[Akash Saikia]

Ohh k,
So do one thing write a hard coded code there instead

Let me know first what is Refno,UserId and RowGuid in your table??
Does all these three values are needed to delete a single record???
If yes
then change your code to like this :

Private Sub Delete_Publication(ByVal RefNO As String, ByVal Rowguid As String)
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("Delete_Publication", myConnection)
myCommand .commandtext="delete from yourTablename where RefNo='" & RefNO & "' and RowGuid='" & Rowguid & "' and UserId='" & Session("UserID") & "'"  
 cmd.ExecuteNonQuery()
'Then rest of the lines at the bottom

Remember the fieldname may differ in the code suggested above
for example if have used wher RefNo=
but it may be written as something differenc in your database

[abathurst]

From what i can tell, the UserId is just making sure you're login as Admin it's not actually in the table. RefNo and RowGuid are just unqiue characters used to identify each row, so it should only need one, RowGuid, to locate the file to delete.

[Akash Saikia]

So try the the method i wrote above,with a bif of change with your fieldname,and it will sure delete the records.

[abathurst]

I tried with this code:

Private Sub Delete_Publication(ByVal RefNO As String, ByVal Rowguid As String)

        Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
        Dim myCommand As SqlCommand = New SqlCommand("Delete_Publication", myConnection)

        myCommand.commandtext = "DELETE from Publications where Rowguid='" & Rowguid & "'"

        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()

    End Sub

But still get the error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Conversion failed when converting from a character string to uniqueidentifier. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.

Source Error: 


Line 73: 
Line 74:         myConnection.Open()
Line 75:         myCommand.ExecuteNonQuery()
Line 76:         myConnection.Close()
Line 77: