Solved

Procedure or function 'ReportTypeSelect' expects parameter '@ReportTypeKey', which was not supplied.

Posted on 2011-09-23
11
439 Views
Last Modified: 2012-05-12
I have a page that lists all Report Types in the system.  The user clicks the name of the report type to edit an existing record or clicks a link add New Report Type to enter a new report type.  When I click the Report Name I am getting the following error.

"Procedure or function 'ReportTypeSelect' expects parameter '@ReportTypeKey', which was not supplied."

Can anyone tell me what I am doing wrong?  I am a newbie and self taught so be gentle.
Public Class AddEditReportType
    Inherits System.Web.UI.Page

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

        If Not IsPostBack Then
            'LoadReportGroup()
            If Request.QueryString("RTKey") Is Nothing Then
                Session("RTKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
                'LoadReportGroup(True)
                lblMessage.Text = "Report Details"
                cmdUpdateReport.Text = "Add Report Type"
                cmdDeleteReport.Visible = False
                Session("ReportTypeKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
            Else
                'LoadReportGroup(True)
                cmdDeleteReport.Visible = True
                lblMessage.Text = "Modify Report Type"
                cmdUpdateReport.Text = "Update Report Type"
                Session("ReportTypeKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
                LoadReport(Convert.ToString(Request.QueryString("ReportTypeKey")))
            End If

        End If

        If Not Session("ReportTypeKey") Is Nothing Then
            LoadReport("sReportTypeKey")
        End If
    End Sub
    Private Sub LoadReport(ByVal sReportTypeKey As String)
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand

            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportTypeSelect"
            cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int).Value = sReportTypeKey
            cnn.Open()
            Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
            dr.Read()
            If dr.HasRows Then
                txtReportName.Text = dr("ReportTypeName").ToString.Trim
                txtShortDesc.Text = dr("ReportTypeDesc").ToString.Trim
                cboReportTypeGroup.SelectedValue = dr("ReportTypeGroupKey")
                cboOrderType.SelectedValue = dr("OrderTypeKey")
            End If
        Finally
            cnn.Close()
        End Try
    End Sub
    Private Sub LoadReportGroup()
        cboReportTypeGroup.DataSource = Nothing
        cboReportTypeGroup.DataBind()
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportGroupSelect"
            cmd.Parameters.Add("@DropDown", Data.SqlDbType.Bit).Value = True
            cnn.Open()
            Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
            If dr.HasRows Then
                cboReportTypeGroup.DataTextField = "ReportTypeGroupName"
                cboReportTypeGroup.DataValueField = "ReportTypeGroupKey"
                cboReportTypeGroup.DataSource = dr
                cboReportTypeGroup.DataBind()
            End If
        Finally
            cnn.Close()
        End Try
    End Sub

    Private Sub AddReport()
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportTypeInsert"
            cmd.Parameters.Add("@ReportTypeGroupKey", Data.SqlDbType.Int).Value = ReportTypeGroupKey.Text
            cmd.Parameters.Add("@OrderTypeKey", Data.SqlDbType.Int).Value = OrderTypeKey.Text
            cmd.Parameters.Add("@ReportName", Data.SqlDbType.Char).Value = txtReportName.Text
            cmd.Parameters.Add("@ReportDesc", Data.SqlDbType.Char).Value = txtShortDesc.Text
            cnn.Open()
            cmd.ExecuteScalar()
            Dim sRedirect As String = "ReportType.aspx"
            Response.Redirect(sRedirect)
        Finally
            cnn.Close()
        End Try
    End Sub
    Private Sub UpdateReport()
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportTypeUpdate"
            cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int).Value = Int32.Parse(Session("sReportTypeKey").ToString)
            cmd.Parameters.Add("@ReportTypeGroupKey", Data.SqlDbType.Int).Value = ReportTypeGroupKey.Text
            cmd.Parameters.Add("@OrderTypeKey", Data.SqlDbType.Int).Value = OrderTypeKey.Text
            cmd.Parameters.Add("@ReportName", Data.SqlDbType.Char).Value = txtReportName.Text
            cmd.Parameters.Add("@ReportDesc", Data.SqlDbType.Char).Value = txtShortDesc.Text
            cnn.Open()
            cmd.ExecuteScalar()
            Dim sRedirect As String = "ReportType.aspx"
            Response.Redirect(sRedirect)
        Finally
            cnn.Close()
        End Try

    End Sub

    Protected Sub UpdateReport_Click(sender As Object, e As EventArgs) Handles cmdUpdateReport.Click
        If lblMessage.Text = "Report Details" Then
            AddReport()
        Else
            UpdateReport()
        End If
    End Sub

End Class

Open in new window

<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="StaffSettings.Master" CodeBehind="AddEditReportType.aspx.vb" Inherits="ProVal.AddEditReportType" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    <style type="text/css">
        .style2
        {
            width: 100%;
        }
        .style3
        {
            width: 131px;
        }
        .style4
        {
            width: 456px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    
        <h1>
            <asp:Label ID="lblMessage" runat="server" Text="Label"></asp:Label></h1>
    <table class="style2">
        <tr>
            <td class="style3">
                Report Type Name:</td>
            <td class="style4">
               <asp:HiddenField
                    ID="ReportTypeKey" runat="server" /> <asp:TextBox ID="txtReportName" 
                    runat="server" Width="228px"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                    ErrorMessage="Report Name Required" ControlToValidate="txtReportName" 
                    Font-Bold="True" Font-Size="Small" ForeColor="Red"></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td class="style3">
                Short Description:</td>
            <td class="style4">
                <asp:TextBox ID="txtShortDesc" runat="server" Width="228px"></asp:TextBox>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
                    ControlToValidate="txtShortDesc" ErrorMessage="Limit to 20 Characters" 
                    ValidationExpression="^[0-9A-Za-z!@\.;:'&quot;?-]{1,20}$" 
                    Font-Bold="True" Font-Size="Small" ForeColor="Red"></asp:RegularExpressionValidator>
            </td>
        </tr>
        <tr>
            <td class="style3">
                Group Name:</td>
            <td class="style4">
                <asp:TextBox ID="ReportTypeGroupKey" runat="server" Width="228px"></asp:TextBox>
                <asp:DropDownList ID="cboReportTypeGroup" runat="server">
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td class="style3">
                Order Type:</td>
            <td class="style4">
                <asp:TextBox ID="OrderTypeKey" runat="server" Width="228px"></asp:TextBox>
                
                <asp:DropDownList ID="cboOrderType" runat="server">
                </asp:DropDownList>
                
            </td>
        </tr>
        <tr>
            <td class="style3">
                &nbsp;</td>
            <td class="style4">
                <asp:Button ID="cmdUpdateReport" runat="server" Text="Update Report" />
                <asp:Button ID="cmdDeleteReport" runat="server" Text="Inactivate Report" />
            </td>
        </tr>
    </table>
</asp:Content>

Open in new window

0
Comment
Question by:rcowen00
[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
  • 5
  • 3
  • 3
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590456
Hi.

What is the exact Session() variable name? In some places you have as sReportTypeKey and in others just ReportTypeKey.

Further, you check to make sure the key is in the session, but then pass along a literal string "sReportTypeKey".
If Not Session("ReportTypeKey") Is Nothing Then
            LoadReport("sReportTypeKey")
        End If

So, you will want to have the parameter to LoadReport be an integer. Once you check that the session key exists, I would parse it to an integer then pass it to LoadReport.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36590909
Please post the contents of the Stored Procedure dbo.ReportTypeSelect or at the very least the signature.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590921
ac,
ALTER PROCEDURE dbo.ReportTypeSelect( @ReportTypeKey int)...

Open in new window

http://www.experts-exchange.com/Q_27318081.html
0
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!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36591000
I would have to guess that sReportTypeKey is Null.  But the best way to find out is to step through the code.
0
 

Author Comment

by:rcowen00
ID: 36591249
mwvisa1 - the actual Session variable is RTKey and it is being passed in the URL as expected.  Regarding, " I would parse it to an integer then pass it to LoadReport.", how would I do that?

acperkins - It is null, but I can't figure out why.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36592768
You will have to check out where you call the LoadReport method.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593228
So, first change:
Private Sub LoadReport(ByVal sReportTypeKey As String) ...

To:
Private Sub LoadReport(ByVal intRTKey As Integer)...

Then take a look at this code:
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then
            'LoadReportGroup()
            If Request.QueryString("RTKey") Is Nothing Then
                Session("RTKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
                'LoadReportGroup(True)
                lblMessage.Text = "Report Details"
                cmdUpdateReport.Text = "Add Report Type"
                cmdDeleteReport.Visible = False
                Session("ReportTypeKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
            Else
                'LoadReportGroup(True)
                cmdDeleteReport.Visible = True
                lblMessage.Text = "Modify Report Type"
                cmdUpdateReport.Text = "Update Report Type"
                Session("ReportTypeKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
                LoadReport(Convert.ToString(Request.QueryString("ReportTypeKey")))
            End If

        End If

        If Not Session("ReportTypeKey") Is Nothing Then
            LoadReport("sReportTypeKey")
        End If
    End Sub

Open in new window


A number of things:
You have a mix of Session("ReportTypeKey") and Session("RTKey"). If iti s RTKey, keep that consistent OR you will end up pulling NULL/Nothing accidentally. i.e., if you check for existence of RTKey, but then pull ReportTypeKey you will likely get Nothing.
Why is the last If Not ... statement there? May be correct, but since you are If Not IsPostBack Then earlier with LoadReport(), just make sure this belongs here as it may be calling report twice ...
Along with above, the second call will surely fail as you are passing a literal string "sReportTypeKey" instead of the value of the session variable.
For parsing to an Integer, I would do something like:

Dim intRTKey As Integer
Session("RTKey") = Convert.ToString(Request.QueryString("ReportTypeKey"))
'http://msdn.microsoft.com/en-us/library/f02979c7.aspx
Int32.TryParse(Session("RTKey"), intRTKey)

Beyond that, just double check that the logic is not duplicative and makes sense.


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36593242
And the last statement is important, so I will reiterate. The duplication of LoadReport() calls is why I only made my initial comment about string as I did not see the other call to LoadReport which looks a little better, but again I would take string from URL/Session and then deal with Integers in LoadReport. Keep it simple.

In that vein, if LoadReport() is ultimately called regardless, see if your logic should really be:

Dim intRTKey As Integer

If ...
  'prep some fields, including intRTKey if appropriate
Else ...
   'prep some other fields, including intRTKey if appropriate
End If

LoadReport(intRTKey)

:)
0
 

Author Comment

by:rcowen00
ID: 36593763
mwvisa1 - I have really made a mess of this.  I have taken out the unnecessary lines but I still am having a problem that you have probably already answered.  I get the following error (Input string was not in a correct format.) when calling LoadReport regarding line:

cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int).Value = Int32.Parse("RTKey")

The RTKey is passing correctly everywhere else but here.  



 

Public Class AddEditReportType
    Inherits System.Web.UI.Page

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

        If Not IsPostBack Then
            If Request.QueryString("RTKey") Is Nothing Then
                lblMessage.Text = "Report Details"
                cmdUpdateReport.Text = "Add Report Type"
                cmdDeleteReport.Visible = False
            Else
                cmdDeleteReport.Visible = True
                lblMessage.Text = "Modify Report Type"
                cmdUpdateReport.Text = "Update Report Type"
                LoadReport(Convert.ToString(Request.QueryString("RTKey")))
            End If
        End If
        If Not Session("RTKey") Is Nothing Then
            LoadReport(Convert.ToString(Request.QueryString("RTKey")))
        End If
    End Sub
    Private Sub LoadReport(ByVal intRTKey As Integer)
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportTypeSelect"
            cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int).Value = Int32.Parse("RTKey")
            cnn.Open()
            Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
            dr.Read()
            If dr.HasRows Then
                txtReportName.Text = dr("ReportTypeName").ToString.Trim
                txtShortDesc.Text = dr("ReportTypeDesc").ToString.Trim
                cboReportTypeGroup.SelectedValue = dr("ReportTypeGroupKey")
                cboOrderType.SelectedValue = dr("OrderTypeKey")
            End If
        Finally
            cnn.Close()
        End Try
    End Sub

Open in new window

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36593876
cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int).Value = Int32.Parse("RTKey")

This is passing a literal string "RTKey" to the Int32.Parse() method versus the value contained in a variable called RTKey. Invalid data is the reason I showed you Int32.TryParse() which will default to 0 if fails. Figuring most integer keys auto-increment from 1 up, 0 will return no rows but does not break your code.

Try like the attached.



Public Class AddEditReportType
    Inherits System.Web.UI.Page

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

        If Not IsPostBack Then
            If Request.QueryString("RTKey") Is Nothing Then
                lblMessage.Text = "Report Details"
                cmdUpdateReport.Text = "Add Report Type"
                cmdDeleteReport.Visible = False
            Else
                cmdDeleteReport.Visible = True
                lblMessage.Text = "Modify Report Type"
                cmdUpdateReport.Text = "Update Report Type"
                Session("RTKey") = Convert.ToString(Request.QueryString("RTKey"))
            End If
        End If

        If Not Session("RTKey") Is Nothing Then
            Dim intRTKey As Integer
            Int32.TryParse(Session("RTKey"), intRTKey)
            LoadReport(intRTKey)
        End If
    End Sub

    Private Sub LoadReport(ByVal intRTKey As Integer)
        Dim cnn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("myprovalConnectionString").ConnectionString)
        Try
            Dim cmd As New System.Data.SqlClient.SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "dbo.ReportTypeSelect"
            cmd.Parameters.Add("@ReportTypeKey", Data.SqlDbType.Int)
            cmd.Parameters("@ReportTypeKey").Value = intRTKey
            cnn.Open()
            Dim dr As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader
            dr.Read()
            If dr.HasRows Then
                txtReportName.Text = dr("ReportTypeName").ToString.Trim
                txtShortDesc.Text = dr("ReportTypeDesc").ToString.Trim
                cboReportTypeGroup.SelectedValue = dr("ReportTypeGroupKey")
                cboOrderType.SelectedValue = dr("OrderTypeKey")
            End If
        Finally
            cnn.Close()
        End Try
    End Sub
End Class

Open in new window

0
 

Author Closing Comment

by:rcowen00
ID: 36595684
Thanks!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

732 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