rcowen00
asked on
Procedure or function 'ReportTypeSelect' expects parameter '@ReportTypeKey', which was not supplied.
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.
"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
<%@ 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!@\.;:'"?-]{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">
</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>
Please post the contents of the Stored Procedure dbo.ReportTypeSelect or at the very least the signature.
ac,
ALTER PROCEDURE dbo.ReportTypeSelect( @ReportTypeKey int)...
https://www.experts-exchange.com/questions/27318081/Stored-Procedure-Results.html
I would have to guess that sReportTypeKey is Null. But the best way to find out is to step through the code.
ASKER
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.
acperkins - It is null, but I can't figure out why.
You will have to check out where you call the LoadReport method.
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:
A number of things:
Beyond that, just double check that the logic is not duplicative and makes sense.
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
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.Q ueryString ("ReportTy peKey"))
'http://msdn.microsoft.com/en-us/library/f02979c7.aspx
Int32.TryParse(Session("RT Key"), intRTKey)
Dim intRTKey As Integer
Session("RTKey") = Convert.ToString(Request.Q
'http://msdn.microsoft.com/en-us/library/f02979c7.aspx
Int32.TryParse(Session("RT
Beyond that, just double check that the logic is not duplicative and makes sense.
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)
:)
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)
:)
ASKER
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("@Repor tTypeKey", Data.SqlDbType.Int).Value = Int32.Parse("RTKey")
The RTKey is passing correctly everywhere else but here.
cmd.Parameters.Add("@Repor
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
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.