Procedure Expects Paramter...

[putrtek]

Im trying to pass a paremeter to a SQL stored procedure. I'm using a drop down box for the user to select a Time Zone. I'm then tryting to pass a TimeZone OFFSET to mt stored procedure in order to adjust the Dates and times displayed by the query.

Right now I get an error "System.Data.SqlClient.SqlException: Procedure 'usp_incidentsummary_SELECT_TZ_OffSet' expects parameter '@StrOffSet', which was not supplied."

I've inserted Trace statments in my code to be sure that the value is being recognized. All 4 of the TRACE statements shown are showing the value that was selected from the dropdown, however it doesn't seem to be passing to the SPROC.. I'm not sure why at this point. Can anyone give me a shove in the right direction?

Thanks in advance for any assistance that anyone can give me..

Code for this is listed below.

-MARK-


SQL

ALTER proc [dbo].[usp_incidentsummary_SELECT_TZ_OffSet]

@StrOffSet int 
--Number of Hours to OffSet Time - NULL value makes NO adjustment
AS

BEGIN
SELECT top 10

  IncidentID
, OutageShortDescription
, @StrOffSet as OffSet
, LastUpdated
, dateadd(hh, @StrOffSet, LastUpdated) as LastUpdated_TZ_OffSet
, DateCreated
, dateadd(hh, @StrOffSet, DateCreated) as DateCreated_TZ_OffSet
    
   
FROM  incidentsummary

WHERE IncidentID = 459
END

ASPX

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Test_TZ.aspx.vb" Inherits="Test_TZ" title="Untitled Page" %>

<%@ Register Assembly="PetersDatePackage" Namespace="PeterBlum.PetersDatePackage" TagPrefix="Date" %>


<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" />


<br />
Select your time Zone
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack=true>

    <asp:ListItem value="0" Text="-- Choose a Time Zone --"> </asp:ListItem> 
    <asp:ListItem value="-4" Text="EDT -  Eastern Daylight Time  UTC - 4 hours"> </asp:ListItem> 
    <asp:ListItem Value="-5" Text="EST Eastern Standard Time  UTC - 5 hours"> </asp:ListItem> 
    <asp:ListItem Value="-5" Text="CDT Central Daylight Time  UTC - 5 hours"> </asp:ListItem> 
    <asp:ListItem Value="-6" Text="CST Central Standard Time  UTC - 6 hours"> </asp:ListItem> 
    <asp:ListItem Value="-6" Text="MDT Mountain Daylight Time  UTC - 6 hours"> </asp:ListItem> 
    <asp:ListItem Value="-7" Text="MST Mountain Standard Time  UTC - 7 hours"> </asp:ListItem> 
    <asp:ListItem Value="-7" Text="PDT Pacific Daylight Time HAP  - 7 hours"> </asp:ListItem> 
    <asp:ListItem Value="-8" Text="PST Pacific Standard Time HNP  - 8 hours"> </asp:ListItem> 
    <asp:ListItem Value="-9" Text="HDT Hawaii Daylight Time  UTC - 9 hours"> </asp:ListItem> 
    <asp:ListItem Value="-10" Text="HST Hawaii Standard Time  UTC - 10 hours"> </asp:ListItem> 
</asp:DropDownList>
   <br />    <br /> 
    <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/test_TZ.aspx">Clear All</asp:HyperLink>
    
                         
<asp:Panel ID="Panel1" runat="server" Height="120px" Width="525px" BorderStyle="Dotted" BackColor="rosyBrown" >
    
The current  UTC Time is   : <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label><br />
Your Local Time is  : <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /><br />

Your Selected time Zone you selected is: <asp:Label ID="Label10" runat="server" Text="Label"></asp:Label><br />
Your Selected time Zone Offset is: <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label><br />
The time in Your Selected time Zone is: <asp:Label ID="Label11" runat="server" Text="Label"></asp:Label><br />
<br />

  </asp:Panel>
<asp:Datagrid ID="dg_TZ_OffSet" runat="server"
                  BackColor="#eeeeee" 
                  Width="80%"
                  HorizontalAlign="Center" 
                  Font-Names="Verdana" 
                  CellPadding="4"
                  HeaderStyle-BackColor="Black" 
                  HeaderStyle-ForeColor ="White" 
                  HeaderStyle-Font-Bold ="true" 
                  HeaderStyle-HorizontalAlign ="Center" 
                  AlternatingItemStyle-BackColor="White"
                  Font-Size="10pt" 
                  DataKeyField="IncidentID"
                 AutoGenerateColumns="False" >
  
    <Columns>
  
        <asp:BoundColumn DataField="IncidentID" HeaderText="ID" />
          <asp:BoundColumn DataField="OutageShortDescription" HeaderText="OutageShortDescription" />
        <asp:BoundColumn DataField="LastUpdated" HeaderText="LastUpdated" ItemStyle-Wrap="false" />
        <asp:BoundColumn DataField="LastUpdated_TZ_OffSet" HeaderText="LastUpdated_TZ_OffSet" ItemStyle-Wrap="false" />
        <asp:BoundColumn DataField="DateCreated" HeaderText="DateCreated" ItemStyle-Wrap="false" />
        <asp:BoundColumn DataField="DateCreated_TZ_OffSet" HeaderText="DateCreated_TZ_OffSet" ItemStyle-Wrap="false"  />
      <%--  <asp:BoundColumn DataField="StatusName" HeaderText="Status" ItemStyle-Wrap="false"  />--%>
    </Columns>        
</asp:Datagrid>
</asp:Content>

ASPX.VB

Option Explicit On
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls

Partial Class Test_TZ

    Inherits System.Web.UI.Page

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        If Request.QueryString("trace") = "true" Then
            Trace.IsEnabled = True
        End If

        If Not Page.IsPostBack Then
            Panel1.Visible = False
            OutageStartTime.xDateTimeBindable = Date.UtcNow
        End If

        If Page.IsPostBack Then
            OutageStartTime.xDateTimeBindable = Date.UtcNow.AddHours(DropDownList1.SelectedValue).ToString()
            Panel1.Visible = True
            Dim LocalNow As Date
            Dim UtcNow As Date
            Dim NewUtcNow As Date
            UtcNow = Date.UtcNow
            LocalNow = UtcNow.ToLocalTime()
            NewUtcNow = Now.ToUniversalTime()

            Label1.Text = UtcNow.ToString
            Label2.Text = LocalNow.ToString
            Label3.Text = DropDownList1.SelectedValue
            Label10.Text = DropDownList1.SelectedItem.ToString
            Label11.Text = Date.UtcNow.AddHours(DropDownList1.SelectedValue).ToString()
            Trace.Warn("Label3 : ", Label3.Text)
            Trace.Warn("Dropdown1: ", DropDownList1.SelectedValue)
            BindData(DropDownList1.SelectedValue.ToString)
                    End If

    End Sub



    Private Sub BindData(ByVal StrOffSet As String)

        Try
            ' Get Connection string from Web Config
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("IncidentConnectionString").ToString)
            ' Create the command object, passing in the SQL string (Stored Procedure), Open the connection
            Const strSQL As String = "usp_incidentsummary_SELECT_TZ_OffSet" 'Using a Stored Procedure
            Dim myCommand As New SqlCommand(strSQL, myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim Parm_StrOffSet As New SqlParameter("@StrOffSet", SqlDbType.Int, 4)
            Trace.Warn("Parm_StrOffSet:  ", StrOffSet)
            Parm_StrOffSet.Value = StrOffSet
            myCommand.Parameters.AddWithValue("@StrOffSet", StrOffSet)

            'Set the datagrid's datasource to the datareader and databind
            Dim resultsDataSet As New DataSet()
            Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)
            myDataAdapter.Fill(resultsDataSet, "Results")
            'RecordCount = resultsDataSet.Tables("Results").Rows.Count.ToString()
            'lblRecordCount.Text = "<br/> <b>" & RecordCount & " </b> records found"
            dg_TZ_OffSet.DataSource = resultsDataSet
            dg_TZ_OffSet.DataBind()

        Catch exSQL As SqlException
            Trace.Warn("*** a SQL Error has Occured ***")
            Trace.Write(exSQL.ToString)
            Trace.Warn("*** End Of SQL Error ***")
        Catch ex As Exception
            Trace.Warn("*** a Error has Occured ***")
            Trace.Write(ex.ToString)
            Trace.Warn("*** End Of Error ***")
        Finally
            '   myConnection.Close()
        End Try
    End Sub

    End Class

Mark Buckley

http://www.swiftriverphotos.com

 

[AlexCuse]

You aren't using your command object (where the parameter has been set) within your data adapter.

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)

I believe you just need this:

Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCommand)

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jbenson001]

I don't see where you are adding the parameter to the sql string. Try this:

myDataAdapter.SelectCommand.Parameters.Add(Parm_StrOffSet)

Jim

 

[ca8msm]

            Dim myCommand As New SqlCommand(strSQL, myConnection)
...
            Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)

It looks like although you create a command and setup some parameters, that you later setup a DataAdaptor which then executes the original procedure (and therefore without the parameter).


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[putrtek]

Thanks for the assistance.. Alright I've cleaned up my BindData function and now I get a differant error...

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'usp_incidentsummary_SELECT_TZ_OffSet'


Appreciate any help...

-MARK-

Private Sub BindData(ByVal StrOffSet As String)
        Try
            ' Get Connection string from Web Config
            Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("IncidentConnectionString").ToString)
            Const strSQL As String = " usp_incidentsummary_SELECT_TZ_OffSet " 'Using a Stored Procedure
            Dim MyDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)
            Dim Parm_StrOffSet As New SqlParameter("@StrOffSet", SqlDbType.Int, 4)
            Trace.Warn("Parm_StrOffSet:  ", StrOffSet)
            Parm_StrOffSet.Value = StrOffSet
            Trace.Warn("Parm_StrOffSet:  ", Parm_StrOffSet.Value)
            MyDataAdapter.SelectCommand.Parameters.Add(Parm_StrOffSet)
            Dim MyDataSet As DataSet = New DataSet
            MyDataAdapter.Fill(MyDataSet)
            dg_TZ_OffSet.DataSource = MyDataAdapter
            dg_TZ_OffSet.DataKeyField = "RecID"
            dg_TZ_OffSet.DataBind()

        Catch exSQL As SqlException
            Trace.Warn("*** a SQL Error has Occured ***")
            Trace.Write(exSQL.ToString)
            Trace.Warn("*** End Of SQL Error ***")
        Catch ex As Exception
            Trace.Warn("*** a Error has Occured ***")
            Trace.Write(ex.ToString)
            Trace.Warn("*** End Of Error ***")
        Finally

        End Try
    End Sub

Mark Buckley

http://www.swiftriverphotos.com

 

[AlexCuse]

You are still not using your SQLCommand in your DataAdapter, so it doesn't know you are using a stored procedure anymore...

Either add "execute " to the beginning of strSQL or use a command object.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[yosie2007]

Hope this give you a clue how to work with Ps

Dim connectionString As String = ConnectionStrings("asdffff").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()

Dim BiddersData As OracleDataReader
Dim cmdBiddersData As OracleCommand = New OracleCommand()
With cmdBiddersData
.Connection = oOracleConn
.CommandText = "LettingInformation.BiddersInformation"
.CommandType = CommandType.StoredProcedure
.Parameters.Clear()
.Parameters.Add(New OracleParameter("p_letting", OracleType.VarChar)).Value = lettId
.Parameters.Add(New OracleParameter("p_contid", OracleType.VarChar)).Value = contId
.Parameters.Add(New OracleParameter("p_call", OracleType.VarChar)).Value = calId
.Parameters.Add(New OracleParameter("p_result", OracleType.Cursor)).Direction = ParameterDirection.Output
End With
BiddersData = cmdBiddersData.ExecuteReader()
gvvendors.DataSource = BiddersData
gvvendors.DataBind()
BiddersData.Close()

 

[putrtek]

Thanks for the input / guidance / direction. I have modifed my code again and it now works. Again thank you for the "push" in the right direction.

-MARK-

Private Sub BindData(ByVal StrOffSet As String)
        Dim dt As New DataTable
        Dim Conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("IncidentConnectionString").ToString)
        Try
            Dim cmd As SqlCommand = New SqlCommand("usp_incidentsummary_SELECT_TZ_OffSet", Conn)
            With cmd
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@StrOffSet", StrOffSet)
            End With
            Conn.Open()
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            dt.Load(dr)
            dg_TZ_OffSet.DataSource = dt
            dg_TZ_OffSet.DataKeyField = "IncidentID"
            dg_TZ_OffSet.DataBind()
        Catch exSQL As SqlException
            Trace.Warn("*** a SQL Error has Occured ***")
            Trace.Write(exSQL.ToString)
            Trace.Warn("*** End Of SQL Error ***")
        Catch ex As Exception
            Trace.Warn("*** a Error has Occured ***")
            Trace.Write(ex.ToString)
            Trace.Warn("*** End Of Error ***")
        Finally
            Conn.Close()
        End Try
    End Sub

Mark Buckley

http://www.swiftriverphotos.com