output parameter values not displaying properly

  • Thread starter Thread starter DC Gringo
  • Start date Start date
D

DC Gringo

I have a SQL Server stored procedure that outputs 4 parameters and a
recordset. They are outputting just fine in Query Analyzer, but come out as

[@gcLatestNav] = 1/1/0001
[@pubLatestNav] = 0
[@gcPreviousNav] = 1/1/0001
[@pubPreviousNav] = 0



-- Here is my stored procedure --

Create Procedure fn3MainNav

@gcLatestNav int = 1 output,
@pubLatestNav smalldatetime = '5/5/2002' output,
@gcPreviousNav int = 1 output,
@pubPreviousNav smalldatetime = '5/5/2002' output

as


SELECT TOP 1
@gcLatestNav = gc1.gc_id,
@pubLatestNav = gc1.published


SELECT TOP 1
@gcPreviousNav = gc1.gc_id,
@pubPreviousNav = gc1.published
FROM general_content gc1






-- Here's the relevant section of my .aspx code --

<% = pubLatestNav %>
<% = gcLatestNav %>
<% = pubPreviousNav %>
<% = gcPreviousNav %>





-- Here's my code behind --

Public Class mainNav
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
"server=myserver_2;database=mdb;uid=sa;pwd=mypwd;"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "fn3MainNav"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatestNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output, False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubLatestNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcPreviousNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output, False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubPreviousNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
End Sub

Public gcLatestNav As Integer
Public pubLatestNav As String
Public gcPreviousNav As Integer
Public pubPreviousNav As String

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
gcLatestNav = Me.SqlCommand1.Parameters("@gcLatestNav").Value
pubLatestNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubLatestNav").Value,
DateFormat.ShortDate)
gcPreviousNav = Me.SqlCommand1.Parameters("@gcPreviousNav").Value
pubPreviousNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubPreviousNav").Value,
DateFormat.ShortDate)


End Sub

End Class


from database right but showing
 
I see where you're creating the Command object, but not where it's executed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

DC Gringo said:
I have a SQL Server stored procedure that outputs 4 parameters and a
recordset. They are outputting just fine in Query Analyzer, but come out
as

[@gcLatestNav] = 1/1/0001
[@pubLatestNav] =
[@gcPreviousNav] = 1/1/0001
[@pubPreviousNav] = 0



-- Here is my stored procedure --

Create Procedure fn3MainNav

@gcLatestNav int = 1 output,
@pubLatestNav smalldatetime = '5/5/2002' output,
@gcPreviousNav int = 1 output,
@pubPreviousNav smalldatetime = '5/5/2002' output

as


SELECT TOP 1
@gcLatestNav = gc1.gc_id,
@pubLatestNav = gc1.published


SELECT TOP 1
@gcPreviousNav = gc1.gc_id,
@pubPreviousNav = gc1.published
FROM general_content gc1






-- Here's the relevant section of my .aspx code --

<% = pubLatestNav %>
<% = gcLatestNav %>
<% = pubPreviousNav %>
<% = gcPreviousNav %>





-- Here's my code behind --

Public Class mainNav
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
"server=myserver_2;database=mdb;uid=sa;pwd=mypwd;"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "fn3MainNav"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatestNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubLatestNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcPreviousNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubPreviousNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
End Sub

Public gcLatestNav As Integer
Public pubLatestNav As String
Public gcPreviousNav As Integer
Public pubPreviousNav As String

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
gcLatestNav = Me.SqlCommand1.Parameters("@gcLatestNav").Value
pubLatestNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubLatestNav").Value,
DateFormat.ShortDate)
gcPreviousNav = Me.SqlCommand1.Parameters("@gcPreviousNav").Value
pubPreviousNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubPreviousNav").Value,
DateFormat.ShortDate)


End Sub

End Class


from database right but showing
Click to expand...
 
I got it, thanks Bill...

I was missing

SqlConnection1.Open()

SqlCommand1.ExecuteNonQuery()

SqlConnection1.Close()





William (Bill) Vaughn said:
I see where you're creating the Command object, but not where it's executed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

DC Gringo said:
I have a SQL Server stored procedure that outputs 4 parameters and a
recordset. They are outputting just fine in Query Analyzer, but come out
as

[@gcLatestNav] = 1/1/0001
[@pubLatestNav] =
[@gcPreviousNav] = 1/1/0001
[@pubPreviousNav] = 0



-- Here is my stored procedure --

Create Procedure fn3MainNav

@gcLatestNav int = 1 output,
@pubLatestNav smalldatetime = '5/5/2002' output,
@gcPreviousNav int = 1 output,
@pubPreviousNav smalldatetime = '5/5/2002' output

as


SELECT TOP 1
@gcLatestNav = gc1.gc_id,
@pubLatestNav = gc1.published


SELECT TOP 1
@gcPreviousNav = gc1.gc_id,
@pubPreviousNav = gc1.published
FROM general_content gc1






-- Here's the relevant section of my .aspx code --

<% = pubLatestNav %>
<% = gcLatestNav %>
<% = pubPreviousNav %>
<% = gcPreviousNav %>





-- Here's my code behind --

Public Class mainNav
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
"server=myserver_2;database=mdb;uid=sa;pwd=mypwd;"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "fn3MainNav"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatestNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubLatestNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcPreviousNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubPreviousNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
End Sub

Public gcLatestNav As Integer
Public pubLatestNav As String
Public gcPreviousNav As Integer
Public pubPreviousNav As String

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
gcLatestNav = Me.SqlCommand1.Parameters("@gcLatestNav").Value
pubLatestNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubLatestNav").Value,
DateFormat.ShortDate)
gcPreviousNav = Me.SqlCommand1.Parameters("@gcPreviousNav").Value
pubPreviousNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubPreviousNav").Value,
DateFormat.ShortDate)


End Sub

End Class


from database right but showing
Click to expand...
Click to expand...
 
details ... details... ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

DC Gringo said:
I got it, thanks Bill...

I was missing

SqlConnection1.Open()

SqlCommand1.ExecuteNonQuery()

SqlConnection1.Close()





William (Bill) Vaughn said:
I see where you're creating the Command object, but not where it's executed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

DC Gringo said:
I have a SQL Server stored procedure that outputs 4 parameters and a
recordset. They are outputting just fine in Query Analyzer, but come out
as

[@gcLatestNav] = 1/1/0001
[@pubLatestNav] =
[@gcPreviousNav] = 1/1/0001
[@pubPreviousNav] = 0



-- Here is my stored procedure --

Create Procedure fn3MainNav

@gcLatestNav int = 1 output,
@pubLatestNav smalldatetime = '5/5/2002' output,
@gcPreviousNav int = 1 output,
@pubPreviousNav smalldatetime = '5/5/2002' output

as


SELECT TOP 1
@gcLatestNav = gc1.gc_id,
@pubLatestNav = gc1.published


SELECT TOP 1
@gcPreviousNav = gc1.gc_id,
@pubPreviousNav = gc1.published
FROM general_content gc1






-- Here's the relevant section of my .aspx code --

<% = pubLatestNav %>
<% = gcLatestNav %>
<% = pubPreviousNav %>
<% = gcPreviousNav %>





-- Here's my code behind --

Public Class mainNav
Inherits System.Web.UI.UserControl
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString =
"server=myserver_2;database=mdb;uid=sa;pwd=mypwd;"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "fn3MainNav"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcLatestNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubLatestNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@gcPreviousNav",
System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Output,
False,
CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current,
Nothing))
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@pubPreviousNav",
System.Data.SqlDbType.SmallDateTime, 0,
System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0,
Byte), "", System.Data.DataRowVersion.Current, Nothing))
End Sub

Public gcLatestNav As Integer
Public pubLatestNav As String
Public gcPreviousNav As Integer
Public pubPreviousNav As String

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web
Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
gcLatestNav = Me.SqlCommand1.Parameters("@gcLatestNav").Value
pubLatestNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubLatestNav").Value,
DateFormat.ShortDate)
gcPreviousNav = Me.SqlCommand1.Parameters("@gcPreviousNav").Value
pubPreviousNav =
FormatDateTime(Me.SqlCommand1.Parameters("@pubPreviousNav").Value,
DateFormat.ShortDate)


End Sub

End Class


from database right but showing
Click to expand...
Click to expand...
Click to expand...
 
Back
Top