Hi there
I what to test a stored procedures on a web page using the normal connection
string.
the controls: Server, username,Password, database allow to select the
appropriate Sql server database.
Now I would use checkbox = Trusted connection as option to logon to the
database.
I guess the question that I have is where am I going wrong in the code?
How can I step through the script before or up until the page is loaded?
Any hints are greatly appreciated, the same will be made available after
completion.
Regards
Norman
Here is the Code:-----------------
<%@dotnet.itags.org. Page Language="VB" AutoEventWireup="True" %>
<%@dotnet.itags.org. import Namespace="System.Data" %>
<%@dotnet.itags.org. import Namespace="System.Data.SqlClient" %>
<%@dotnet.itags.org. import Namespace="System.Web.UI.WebControls" %>
<HTML>
<HEAD>
<script runat="server"
' code to check if the tickbox was ticked
Sub Check_Clicked(sender As Object, e As EventArgs)
If WinTrusted.checked then
'Dim conn As New SqlConnection( _
' "Data source=" & DatabaseServer.Text & _
' ";Trusted_Connection=true" & _
' ";Initial catalog=" & Database.Text)
'else
'Dim conn As New SqlConnection( _
' "Data source=" & DatabaseServer.Text & _
' ";Trusted_Connection=true" & _
' ";Initial catalog=" & Database.Text)
end if
End Sub
'
Sub LoginButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim ds As New DataSet
Dim conn As New SqlConnection( _
"Data source=" & DatabaseServer.Text & _
";Trusted_Connection=true" & _
";Initial catalog=" & Database.Text)
Dim cmd As New SqlCommand("sp_stored_procedures", conn) ' << when Sub
Check_Clicked and the code is run Asp courses an error at this line saying
that "conn" is not defined??
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
adpt.Fill(ds, "SPs")
SPs.DataSource = ds.Tables("SPs")
SPs.DataTextField = "PROCEDURE_NAME"
SPs.DataBind()
Catch ex As SqlException
Status.Text = ex.Message
End Try
End Sub
Sub GetParametersButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim ds As New DataSet
'If Wintrusted.checked = True Then
' Dim conn As New SqlConnection( _
' "Data source=" & DatabaseServer.Text & _
' ";Trusted_Connection=true" & _
' ";Initial catalog=" & Database.Text)
'Else
Dim conn As New SqlConnection( _
"Data source=" & DatabaseServer.Text & _
";User id=" & Userid.text & _
";Password=" & Password.Text & _
";Initial catalog=" & Database.Text)
'End If
Dim cmd As New SqlCommand("sp_sproc_columns", conn) '' << when Sub
Check_Clicked and the code is run Asp courses an error at this line saying
that "conn" is not defined??
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@dotnet.itags.org.procedure_name", SqlDbType.NVarchar, 390).Value = _
SPs.SelectedItem.Value
adpt.Fill(ds, "Parameters")
ParametersDataGrid.DataSource = ds.Tables("Parameters")
ParametersDataGrid.DataBind()
ResultsDataGrid.Visible = False
Catch ex As SqlException
Status.Text = ex.Message
End Try
End Sub
Sub AddParameters(ByVal cmd As SqlCommand)
'works ok
End Sub
Sub UpdateParameters(ByVal cmd As SqlCommand)
'Works Ok
End Sub
Sub ExecuteQueryButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim ds As New DataSet
'If WinTrusted.checked then
'Dim conn As New SqlConnection( _
' "Data source=" & DatabaseServer.Text & _
' ";Trusted_Connection=true" & _
' ";Initial catalog=" & Database.Text)
'else
Dim conn As New SqlConnection( _
"Data source=" & DatabaseServer.Text & _
";Trusted_Connection=true" & _
";Initial catalog=" & Database.Text)
'end if
Dim cmd As New SqlCommand(SPs.SelectedItem.Value, conn) ' ' << when Sub
Check_Clicked and the code is run Asp courses an error at this line saying
that "conn" is not defined??
Dim adpt As New SqlDataAdapter(cmd)
Try
Status.Text = ""
cmd.CommandType = CommandType.StoredProcedure
AddParameters(cmd)
adpt.Fill(ds, "Results")
UpdateParameters(cmd)
ResultsDataGrid.DataSource = ds.Tables("Results")
ResultsDataGrid.DataBind()
ResultsDataGrid.Visible = True
Catch ex As SqlException
Status.Text = ex.Message
End Try
End Sub
</script>
</HEAD>
----------End of
Code--------------You problem i think is happening because you are defining the connection in
a if statement
that would make it a local variable of
if(conditional statement)
{
// you code and your variables.
// any variables declared here are local to this if statement..
}
try
SqlConnection conn;
if(WinTrusted.checked == true)
con = new SqlConnection("con paramas")
else
con = new SqlConnection("second set of param")
}
SqlDataAdapter myCommand = new SqlDataAdapter("sp_storedproc", myCon)
myCommand.SelectCommand.CommandType = CommandType.StoredProc
This should work,
Hermit Dave
"Norman Fritag" <mtp.net@.ozemail.com.au> wrote in message
news:cY6Cb.10$Tq.1083@.nnrp1.ozemail.com.au...
> Hi there
> I what to test a stored procedures on a web page using the normal
connection
> string.
> the controls: Server, username,Password, database allow to select the
> appropriate Sql server database.
> Now I would use checkbox = Trusted connection as option to logon to the
> database.
> I guess the question that I have is where am I going wrong in the code?
> How can I step through the script before or up until the page is loaded?
> Any hints are greatly appreciated, the same will be made available after
> completion.
> Regards
> Norman
>
> Here is the Code:-----------------
> <%@. Page Language="VB" AutoEventWireup="True" %>
> <%@. import Namespace="System.Data" %>
> <%@. import Namespace="System.Data.SqlClient" %>
> <%@. import Namespace="System.Web.UI.WebControls" %>
> <HTML>
> <HEAD>
> <script runat="server">
> ' code to check if the tickbox was ticked
> Sub Check_Clicked(sender As Object, e As EventArgs)
> If WinTrusted.checked then
> 'Dim conn As New SqlConnection( _
> ' "Data source=" & DatabaseServer.Text & _
> ' ";Trusted_Connection=true" & _
> ' ";Initial catalog=" & Database.Text)
> 'else
> 'Dim conn As New SqlConnection( _
> ' "Data source=" & DatabaseServer.Text & _
> ' ";Trusted_Connection=true" & _
> ' ";Initial catalog=" & Database.Text)
> end if
> End Sub
> '
> Sub LoginButton_Click(ByVal sender As Object, ByVal e As EventArgs)
> Dim ds As New DataSet
> Dim conn As New SqlConnection( _
> "Data source=" & DatabaseServer.Text & _
> ";Trusted_Connection=true" & _
> ";Initial catalog=" & Database.Text)
> Dim cmd As New SqlCommand("sp_stored_procedures", conn) ' << when Sub
> Check_Clicked and the code is run Asp courses an error at this line saying
> that "conn" is not defined??
> Dim adpt As New SqlDataAdapter(cmd)
> Try
> Status.Text = ""
> adpt.Fill(ds, "SPs")
> SPs.DataSource = ds.Tables("SPs")
> SPs.DataTextField = "PROCEDURE_NAME"
> SPs.DataBind()
> Catch ex As SqlException
> Status.Text = ex.Message
> End Try
> End Sub
> Sub GetParametersButton_Click(ByVal sender As Object, ByVal e As
EventArgs)
> Dim ds As New DataSet
> 'If Wintrusted.checked = True Then
> ' Dim conn As New SqlConnection( _
> ' "Data source=" & DatabaseServer.Text & _
> ' ";Trusted_Connection=true" & _
> ' ";Initial catalog=" & Database.Text)
> 'Else
> Dim conn As New SqlConnection( _
> "Data source=" & DatabaseServer.Text & _
> ";User id=" & Userid.text & _
> ";Password=" & Password.Text & _
> ";Initial catalog=" & Database.Text)
> 'End If
> Dim cmd As New SqlCommand("sp_sproc_columns", conn) '' << when Sub
> Check_Clicked and the code is run Asp courses an error at this line saying
> that "conn" is not defined??
> Dim adpt As New SqlDataAdapter(cmd)
> Try
> Status.Text = ""
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@.procedure_name", SqlDbType.NVarchar, 390).Value = _
> SPs.SelectedItem.Value
> adpt.Fill(ds, "Parameters")
> ParametersDataGrid.DataSource = ds.Tables("Parameters")
> ParametersDataGrid.DataBind()
> ResultsDataGrid.Visible = False
> Catch ex As SqlException
> Status.Text = ex.Message
> End Try
> End Sub
> Sub AddParameters(ByVal cmd As SqlCommand)
> 'works ok
> End Sub
> Sub UpdateParameters(ByVal cmd As SqlCommand)
> 'Works Ok
> End Sub
> Sub ExecuteQueryButton_Click(ByVal sender As Object, ByVal e As EventArgs)
> Dim ds As New DataSet
> 'If WinTrusted.checked then
> 'Dim conn As New SqlConnection( _
> ' "Data source=" & DatabaseServer.Text & _
> ' ";Trusted_Connection=true" & _
> ' ";Initial catalog=" & Database.Text)
> 'else
> Dim conn As New SqlConnection( _
> "Data source=" & DatabaseServer.Text & _
> ";Trusted_Connection=true" & _
> ";Initial catalog=" & Database.Text)
> 'end if
> Dim cmd As New SqlCommand(SPs.SelectedItem.Value, conn) ' ' << when Sub
> Check_Clicked and the code is run Asp courses an error at this line saying
> that "conn" is not defined??
> Dim adpt As New SqlDataAdapter(cmd)
> Try
> Status.Text = ""
> cmd.CommandType = CommandType.StoredProcedure
> AddParameters(cmd)
> adpt.Fill(ds, "Results")
> UpdateParameters(cmd)
> ResultsDataGrid.DataSource = ds.Tables("Results")
> ResultsDataGrid.DataBind()
> ResultsDataGrid.Visible = True
> Catch ex As SqlException
> Status.Text = ex.Message
> End Try
> End Sub
> </script>
> </HEAD>
> ----------End of
> Code--------------
Sorry bout the typos... too knackered... and too hungry and i would do with
some food and some sleep...
Your problem i think is happening because you are defining the connection in
a if statement
that would make it a local variable of
if(conditional statement)
{
// you code and your variables.
// any variables declared here are local to this if statement..
}
try
SqlConnection conn;
if(WinTrusted.checked == true)
conn = new SqlConnection("con paramas")
else
conn = new SqlConnection("second set of param")
}
SqlDataAdapter myCommand = new SqlDataAdapter("sp_storedproc", conn)
myCommand.SelectCommand.CommandType = CommandType.StoredProc
This should work,
Hermit Dave
"Hermit Dave" <hermitd.REMOVE@.CAPS.AND.DOTS.hotmail.com> wrote in message
news:evdi6$DwDHA.2372@.TK2MSFTNGP09.phx.gbl...
> You problem i think is happening because you are defining the connection
in
> a if statement
> that would make it a local variable of
> if(conditional statement)
> {
> // you code and your variables.
> // any variables declared here are local to this if statement..
> }
> try
> SqlConnection conn;
> if(WinTrusted.checked == true)
> con = new SqlConnection("con paramas")
> else
> con = new SqlConnection("second set of param")
> }
> SqlDataAdapter myCommand = new SqlDataAdapter("sp_storedproc", myCon)
> myCommand.SelectCommand.CommandType = CommandType.StoredProc
> This should work,
> Hermit Dave
> "Norman Fritag" <mtp.net@.ozemail.com.au> wrote in message
> news:cY6Cb.10$Tq.1083@.nnrp1.ozemail.com.au...
> > Hi there
> > I what to test a stored procedures on a web page using the normal
> connection
> > string.
> > the controls: Server, username,Password, database allow to select the
> > appropriate Sql server database.
> > Now I would use checkbox = Trusted connection as option to logon to the
> > database.
> > I guess the question that I have is where am I going wrong in the code?
> > How can I step through the script before or up until the page is loaded?
> > Any hints are greatly appreciated, the same will be made available after
> > completion.
> > Regards
> > Norman
> > Here is the Code:-----------------
> > <%@. Page Language="VB" AutoEventWireup="True" %>
> > <%@. import Namespace="System.Data" %>
> > <%@. import Namespace="System.Data.SqlClient" %>
> > <%@. import Namespace="System.Web.UI.WebControls" %>
> > <HTML>
> > <HEAD>
> > <script runat="server">
> > ' code to check if the tickbox was ticked
> > Sub Check_Clicked(sender As Object, e As EventArgs)
> > If WinTrusted.checked then
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'else
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > end if
> > End Sub
> > '
> > Sub LoginButton_Click(ByVal sender As Object, ByVal e As EventArgs)
> > Dim ds As New DataSet
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";Trusted_Connection=true" & _
> > ";Initial catalog=" & Database.Text)
> > Dim cmd As New SqlCommand("sp_stored_procedures", conn) ' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > adpt.Fill(ds, "SPs")
> > SPs.DataSource = ds.Tables("SPs")
> > SPs.DataTextField = "PROCEDURE_NAME"
> > SPs.DataBind()
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > Sub GetParametersButton_Click(ByVal sender As Object, ByVal e As
> EventArgs)
> > Dim ds As New DataSet
> > 'If Wintrusted.checked = True Then
> > ' Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'Else
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";User id=" & Userid.text & _
> > ";Password=" & Password.Text & _
> > ";Initial catalog=" & Database.Text)
> > 'End If
> > Dim cmd As New SqlCommand("sp_sproc_columns", conn) '' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > cmd.CommandType = CommandType.StoredProcedure
> > cmd.Parameters.Add("@.procedure_name", SqlDbType.NVarchar, 390).Value = _
> > SPs.SelectedItem.Value
> > adpt.Fill(ds, "Parameters")
> > ParametersDataGrid.DataSource = ds.Tables("Parameters")
> > ParametersDataGrid.DataBind()
> > ResultsDataGrid.Visible = False
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > Sub AddParameters(ByVal cmd As SqlCommand)
> > 'works ok
> > End Sub
> > Sub UpdateParameters(ByVal cmd As SqlCommand)
> > 'Works Ok
> > End Sub
> > Sub ExecuteQueryButton_Click(ByVal sender As Object, ByVal e As
EventArgs)
> > Dim ds As New DataSet
> > 'If WinTrusted.checked then
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'else
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";Trusted_Connection=true" & _
> > ";Initial catalog=" & Database.Text)
> > 'end if
> > Dim cmd As New SqlCommand(SPs.SelectedItem.Value, conn) ' ' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > cmd.CommandType = CommandType.StoredProcedure
> > AddParameters(cmd)
> > adpt.Fill(ds, "Results")
> > UpdateParameters(cmd)
> > ResultsDataGrid.DataSource = ds.Tables("Results")
> > ResultsDataGrid.DataBind()
> > ResultsDataGrid.Visible = True
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > </script>
> > </HEAD>
> > ----------End of
> > Code--------------
Thanks Hermit Dave
I picked up the thought an it works Ok.
Regards
Norman
"Hermit Dave" <hermitd.REMOVE@.CAPS.AND.DOTS.hotmail.com> wrote in message
news:evdi6$DwDHA.2372@.TK2MSFTNGP09.phx.gbl...
> You problem i think is happening because you are defining the connection
in
> a if statement
> that would make it a local variable of
> if(conditional statement)
> {
> // you code and your variables.
> // any variables declared here are local to this if statement..
> }
> try
> SqlConnection conn;
> if(WinTrusted.checked == true)
> con = new SqlConnection("con paramas")
> else
> con = new SqlConnection("second set of param")
> }
> SqlDataAdapter myCommand = new SqlDataAdapter("sp_storedproc", myCon)
> myCommand.SelectCommand.CommandType = CommandType.StoredProc
> This should work,
> Hermit Dave
> "Norman Fritag" <mtp.net@.ozemail.com.au> wrote in message
> news:cY6Cb.10$Tq.1083@.nnrp1.ozemail.com.au...
> > Hi there
> > I what to test a stored procedures on a web page using the normal
> connection
> > string.
> > the controls: Server, username,Password, database allow to select the
> > appropriate Sql server database.
> > Now I would use checkbox = Trusted connection as option to logon to the
> > database.
> > I guess the question that I have is where am I going wrong in the code?
> > How can I step through the script before or up until the page is loaded?
> > Any hints are greatly appreciated, the same will be made available after
> > completion.
> > Regards
> > Norman
> > Here is the Code:-----------------
> > <%@. Page Language="VB" AutoEventWireup="True" %>
> > <%@. import Namespace="System.Data" %>
> > <%@. import Namespace="System.Data.SqlClient" %>
> > <%@. import Namespace="System.Web.UI.WebControls" %>
> > <HTML>
> > <HEAD>
> > <script runat="server">
> > ' code to check if the tickbox was ticked
> > Sub Check_Clicked(sender As Object, e As EventArgs)
> > If WinTrusted.checked then
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'else
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > end if
> > End Sub
> > '
> > Sub LoginButton_Click(ByVal sender As Object, ByVal e As EventArgs)
> > Dim ds As New DataSet
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";Trusted_Connection=true" & _
> > ";Initial catalog=" & Database.Text)
> > Dim cmd As New SqlCommand("sp_stored_procedures", conn) ' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > adpt.Fill(ds, "SPs")
> > SPs.DataSource = ds.Tables("SPs")
> > SPs.DataTextField = "PROCEDURE_NAME"
> > SPs.DataBind()
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > Sub GetParametersButton_Click(ByVal sender As Object, ByVal e As
> EventArgs)
> > Dim ds As New DataSet
> > 'If Wintrusted.checked = True Then
> > ' Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'Else
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";User id=" & Userid.text & _
> > ";Password=" & Password.Text & _
> > ";Initial catalog=" & Database.Text)
> > 'End If
> > Dim cmd As New SqlCommand("sp_sproc_columns", conn) '' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > cmd.CommandType = CommandType.StoredProcedure
> > cmd.Parameters.Add("@.procedure_name", SqlDbType.NVarchar, 390).Value = _
> > SPs.SelectedItem.Value
> > adpt.Fill(ds, "Parameters")
> > ParametersDataGrid.DataSource = ds.Tables("Parameters")
> > ParametersDataGrid.DataBind()
> > ResultsDataGrid.Visible = False
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > Sub AddParameters(ByVal cmd As SqlCommand)
> > 'works ok
> > End Sub
> > Sub UpdateParameters(ByVal cmd As SqlCommand)
> > 'Works Ok
> > End Sub
> > Sub ExecuteQueryButton_Click(ByVal sender As Object, ByVal e As
EventArgs)
> > Dim ds As New DataSet
> > 'If WinTrusted.checked then
> > 'Dim conn As New SqlConnection( _
> > ' "Data source=" & DatabaseServer.Text & _
> > ' ";Trusted_Connection=true" & _
> > ' ";Initial catalog=" & Database.Text)
> > 'else
> > Dim conn As New SqlConnection( _
> > "Data source=" & DatabaseServer.Text & _
> > ";Trusted_Connection=true" & _
> > ";Initial catalog=" & Database.Text)
> > 'end if
> > Dim cmd As New SqlCommand(SPs.SelectedItem.Value, conn) ' ' << when Sub
> > Check_Clicked and the code is run Asp courses an error at this line
saying
> > that "conn" is not defined??
> > Dim adpt As New SqlDataAdapter(cmd)
> > Try
> > Status.Text = ""
> > cmd.CommandType = CommandType.StoredProcedure
> > AddParameters(cmd)
> > adpt.Fill(ds, "Results")
> > UpdateParameters(cmd)
> > ResultsDataGrid.DataSource = ds.Tables("Results")
> > ResultsDataGrid.DataBind()
> > ResultsDataGrid.Visible = True
> > Catch ex As SqlException
> > Status.Text = ex.Message
> > End Try
> > End Sub
> > </script>
> > </HEAD>
> > ----------End of
> > Code--------------
