ASP.net - VB NET如何排了SQL查询计数 [英] ASP.net - VB NET how to row count for SQL query

问题描述

乡亲您好我很新的ASP.net，并尝试建立了我认为是一个简单的重定向页面。我有一个IIS基本身份验证设置，并已在成功登录用户达到低于我的Default.aspx页面。我有我的地方创建了用户的登录的副本，并创造了他们GROUP_ID的数据库。我想要做的是在成功登录的用户将达到下面的默认页面，并检查用户是GROUP_ID 1001的一部分，答案是肯定的，以他们重定向到URL1如果没有它们重定向到URL2。

我一直让人们从这个网站帮助帮助了很多，让我在不同的编码语言思维的例子，我可以很容易地进行翻译，但保持陷入在不同的步骤。现在，我得到下面的错误，并一直拉我的头发试图理解这个逻辑。展望从VB网专家得到一些建议在那里。真的AP preciate帮助，但停留在这几天，现在笑。

更新code - 3月1日

 ％@页面语言=VB调试=真正的％GT;
＆LT;％@导入命名空间=System.Data这％GT;
＆LT;％@导入命名空间=MySql.Data.MySqlClient％GT;
＆LT; SCRIPT LANGUAGE =VB=服务器＆GT;
小组的Page_Load（发送者为对象，E作为EventArgs的）
    昏暗的用户名作为字符串= Convert.ToString（User.Identity.Name.Substring（User.Identity.Name.IndexOf（\\）+ 1））
    昏暗的MyConnection的作为的MySqlConnection
    昏暗myDataAdapter作为MySqlDataAdapter的
        昏暗STRSQL作为字符串
        昏暗的MySqlCommand作为的MySqlCommand
        昏暗的计数器作为整数
        昏暗isInGroup由于布尔        MyConnection的=新的MySqlConnection（服务器=本地主机;用户ID = Directory_Admin;密码= IMCisgreat2014;数据库= imc_directory_tool;池=假;）
        STRSQL =SELECT COUNT（*）FROM tbl_staff其中username = @用户名和'GROUP_ID'='1001';
        myDataAdapter =新MySqlDataAdapter的（STRSQL，MyConnection的）        的MySqlCommand =新的MySqlCommand（STRSQL）
        mySqlCommand.Parameters.AddWithValue（@用户名的用户名）
        计数器= mySqlCommand.ExecuteScalar（）
        myConnection.Open（）
        如果isInGroup =柜台＆GT; 0，则
            的Response.Redirect（http://www.w3schools.com）
        其他
            的Response.Redirect（http://www.google.ca）
        万一
        myConnection.Close（）
    结束小组＆LT; / SCRIPT＆GT;＆LT; HTML和GT;
＆LT; HEAD＆GT;
＆LT;标题＆GT;简单的MySQL数据库查询＆LT; /标题＆GT;
＆LT; /头＆GT;
＆LT;身体GT;主页 ...＆LT; /身体GT;
＆LT; / HTML＆GT;
 

我收到以下错误：

 说明：当前Web请求的执行过程中发生未处理的异常。请查看有关错误的详细信息的堆栈跟踪以及它起源于code。异常详细信息：System.InvalidOperationException：连接必须是有效的和开放的。第18行：MySqlCommand的=新的MySqlCommand（STRSQL）
第19行：mySqlCommand.Parameters.AddWithValue（@用户名的用户名）
第20行：柜台= mySqlCommand.ExecuteScalar（）
第21行：如果isInGroup =柜台＆GT; 0，则
第22行：的Response.Redirect（http://www.w3schools.com）
 

解决方案

您创建的MySqlConnection但从未连接你的MySqlCommand它。

  mySqlCommand.Connection =的MySqlConnection
mySqlCommand.Connection.Open（）
 

另外，你以后要调用的Ex​​ecuteScalar不要忘记关闭与连接

  mySqlCommand.Connection.Close（）
 

，否则你将有一个开路且晃来晃去的连接。

Hi folks I am very new to ASP.net and trying to build what I thought was a simple redirect page. I have an IIS basic authentication setup already and upon a successful logon user reach my default.aspx page below. I have a database where I created a copy of user's logon and created them a group_id. What I wanted to do is upon a successful logon users will reach the default page below and check if the user is part of the group_ID 1001 and the answer is yes to redirect them to URL1 and if not redirect them to URL2.

Ive been getting help alot of help from people on this site and giving me examples on different coding language thinking I could easily translate them but keep getting stuck at different steps. Now I am getting the below error and been pulling my hair trying to understand the logic on this. Looking to get some advice from the VB Net experts out there. Would really appreciate help but stuck on this for days now lol.

updated code - March 1ST

%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
    Dim username As String = Convert.ToString(User.Identity.Name.Substring(User.Identity.Name.IndexOf("\") + 1))
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
        Dim strSQL As String
        Dim mySqlCommand As MySqlCommand
        Dim counter As Integer
        Dim isInGroup As Boolean

        myConnection = New MySqlConnection("server=localhost; user id=Directory_Admin; password=IMCisgreat2014; database=imc_directory_tool; pooling=false;")
        strSQL = "SELECT COUNT(*) FROM tbl_staff WHERE username = @username AND 'group_id' = '1001';"
        myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)

        mySqlCommand = New MySqlCommand(strSQL)
        mySqlCommand.Parameters.AddWithValue("@username", username)
        counter = mySqlCommand.ExecuteScalar()
        myConnection.Open()
        If isInGroup = counter > 0 Then
            Response.Redirect("http://www.w3schools.com")
        Else
            Response.Redirect("http://www.google.ca")
        End If
        myConnection.Close()
    End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

Main page ...

</body>
</html> 

I am getting the following error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Connection must be valid and open.

Line 18:             mySqlCommand = New MySqlCommand(strSQL)
Line 19:             mySqlCommand.Parameters.AddWithValue("@username", username)
Line 20:             counter = mySqlCommand.ExecuteScalar()
Line 21:             If isInGroup = counter > 0 Then
Line 22:                 Response.Redirect("http://www.w3schools.com")

解决方案

You created a MySqlConnection but never connected your MySqlCommand to it.

mySqlCommand.Connection = mySqlConnection
mySqlCommand.Connection.Open()

Also, after your call to ExecuteScalar don't forget to close the connection with

mySqlCommand.Connection.Close()

or else you will have a connection that is left open and dangling.

这篇关于ASP.net - VB NET如何排了SQL查询计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！