Stored Procedure and SQL String????

[Tim::..]

Can someone please tell me if it is possible to get the SQL statement from a
stored procedure in ASP.NET?

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???
EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")
...CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue(iOrdinal).ToString )
iOrdinal = oDataReader.GetOrdinal("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub

[Kostadin Kostov]

Instead of replacing use this:

cmd.Parameters.Add("@ParentID", SqlDbType.Integer);
cmd.Parameters["@ParentID"].Value = 0;

"Tim::.." wrote:

Can someone please tell me if it is possible to get the SQL statement from a
stored procedure in ASP.NET?

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???
EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")
..CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue(iOrdinal).ToString )
iOrdinal = oDataReader.GetOrdinal("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub

[Eliyahu Goldin]

No, there is no such a thing. You can use SQL Query Analyzer do modify the
sp to accommodate more parameters if you wish and can.

Eliyahu

"Tim::.." <myatix_at_hotmail.com> wrote in message
news:1B**********************************@microsof t.com...

Can someone please tell me if it is possible to get the SQL statement from a stored procedure in ASP.NET?

Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("s trConn")) Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

I would like to beable to get the SQL statement of "MenuItems" as a string
and use it in another part of my code... How do I do this???
EG:
Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")
..CODE...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then

Dim Myconn As New
SqlConnection(ConfigurationSettings.AppSettings("s trConn"))
Dim cmd As New SqlCommand("MenuItems", Myconn)
cmd.CommandType = CommandType.StoredProcedure

Myconn.Open()

Dim baseSQL As String = 'meuItems value
Dim SQL As String = baseSQL.Replace("@ParentID", "0")

'OK
Dim oDataReader As SqlDataReader = cmd.ExecuteReader()
Dim oMenuItem As skmMenu.MenuItem = Nothing
Dim iOrdinal As Integer = -1
Dim myID As Long = 0
While oDataReader.Read
oMenuItem = New skmMenu.MenuItem
iOrdinal = oDataReader.GetOrdinal("Text")
oMenuItem.Text = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ToolTip")
oMenuItem.ToolTip = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("Url")
oMenuItem.Url = oDataReader.GetString(iOrdinal)
iOrdinal = oDataReader.GetOrdinal("ID")
myID = Long.Parse(oDataReader.GetValue(iOrdinal).ToString )
iOrdinal = oDataReader.GetOrdinal("SubMenuItemsCount")
If oDataReader.GetInt32(iOrdinal) > 0 Then
LoadSubMenu(oMenuItem, baseSQL, myID)
End If
mnuMain.Items.Add(oMenuItem)
End While
mnuMain.CssClass = "menustyle"
mnuMain.HighlightTopMenu = True
mnuMain.Opacity = "100"
mnuMain.zIndex = 1000
mnuMain.Cursor = skmMenu.MouseCursor.Pointer

oDataReader.Close()
Myconn.Close()
End If
End Sub