使用SQL读取读取表中字段的正确语法 [英] Proper syntax for reading a field in a table with a SQL read

问题描述

我不知道为什么示例代码的示例很少，显示如何使用后面的ASPX.VB代码从表中的字段读取值。以下示例不起作用，但我向您展示了这个想法。我意识到，如果它确实有效，它只会读取表格中的最后一条记录。我只想尝试正确的语法，现在经过一周的研究，我已经购买了2本书，但我找不到答案。



这是不正确的语法

 myreader（UserName）。value 

我只想猜它是如何让它工作的。



非常感谢任何帮助。



我尝试过：

受保护的子btnTest_Click（发送者为对象，e为EventArgs）处理btnTest.Click 
 
 Dim str As String =Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = C：\inetpub \wwwroot \ RedRock \ App_Data \ Database.mdf; Integrated Security = True
 Dim con As New Data.SqlClient.SqlConnection（str）
 Dim strAgentsName As String 
 
 con.Open（）
 Dim sql As String =SELECT * FROM tblAgents;
 Dim cmd As New SqlCommand（sql，con）
 Dim myreader由于SqlDataReader = c md.ExecuteReader（）
 
而myreader.Read（）
 
 
 strAgentsName = myreader（UserName）。value 
 
 
结束时
 
结束Sub 
 

解决方案

在您使用的代码中SqlDataReader类的Item属性。请查看 SqlDataReader.Item属性（字符串）（系统。 Data.SqlClient） [ ^ ]您会注意到该项目返回对象类（系统） ） [ ^ ]。



对象类然后再次不包含名为 value 的属性或函数，因此代码导致异常。



根据数据taype，您可以使用 Object.ToString方法（系统） [ ^ ]显示值。如果类型不是字符串，那么您可以使用例如 DirectCast运算符（Visual Basic） [ ^ ]或类型特定的获取功能如 SqlDataReader.GetInt32 Method（Int32）（ System.Data.SqlClient） [ ^ ]



只需记住使用SqlDataReader.IsDBNull Method（Int32）（System.Data.SqlClient ） [ ^ ]



考虑以下示例

 使用 connection1 作为 SqlClient.SqlConnection = 新 SqlClient.SqlConnection（ 连接字符串在这里）
 使用 command1 作为 SqlClient.SqlCommand = 新 SqlClient.SqlCommand 
 command1.Connection = connection1 
 command1.CommandText =  从sysobjects中选择名称 
 connection1.Open（）
 使用 reader1 = command1.ExecuteReader 
 < span class =code-keyword> while  reader1.Read（）
 如果 不 reader1.IsDBNull（ 0 ）然后 
 Console.WriteLine（reader1（  name） .ToString（））
 结束 如果 
 结束   
 结束 使用 
 connection1.Close（）
 结束 使用 
 结束 使用 

< blockquote>我终于找到了一个简单的解决方案。这个解决方案没有在SQL语句中使用@Parameters，因为我仍然在考虑这个问题。但这个解决方案就是我追求的目标。如何将SQL Query中的数据传递给可由程序后面的代码使用的局部变量。请随时提供有关如何进一步简化的任何建议。这是我试图开发的模型。如你所见，

 getUserName = rs（UserName）

就是我所追求的。如何访问数据字段。希望这有助于其他人并感谢你帮助我。

受保护的子Button1_Click（发件人为对象，e为EventArgs）处理btnTest2.Click 
 Dim getLoginID As String 
 Dim getUserName As String 
 
 
'测试SQL数据库的读取方法
 
'打开SQL连接cn
 Dim cn As System.Data.SqlClient.SqlConnection 
'设置cn连接位置的连接参数和连接信息，例如SQL登录ID和密码
 cn =新系统。 Data.SqlClient.SqlConnection（Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = C：\inetpub \wwwroot \ RedRock \ App_Data \ Database.mdf; Integrated Security = True）
 cn .Open（）
 
'将SQL搜索paramitiers定义为名为rs的记录集
 
 Dim getUserID As New System.Data.SqlClient.SqlCommand（Select LoginID，用户名，密码f rom tblAgents其中LoginID ='bmart101'，cn）
 Dim rs As System.Data.SqlClient.SqlDataReader 
 
'读取记录集。 
 rs = getUserID.ExecuteReader（Data.CommandBehavior.CloseConnection）
 
 
'使用从记录集中读取的信息将数据传递给本地变量。 
如果rs.HasRows（）则
而rs.Read（）
 getLoginID = rs（LoginID）
 getUserName = rs（UserName）
 
结束时
 
结束如果
'将结果作为标签传回网页，看是否有效。 
 Label1.Text = getUserName 
 
 
 End Sub 

I have no idea why there are so few examples for sample code showing how to read a value from a field in a table using ASPX.VB code behind. The example below does not work but I am showing you the idea. I realize that if it did work it would only read the last record in the table. I am just trying to get the syntax right and now after a week of research and I have purchased 2 books I have not found the answer.

This is the improper syntax "

myreader("UserName").value

that I was just guessing on how to get it to work.

Any help would be greatly appreciated.

What I have tried:

Protected Sub btnTest_Click(sender As Object, e As EventArgs) Handles btnTest.Click

       Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\inetpub\wwwroot\RedRock\App_Data\Database.mdf;Integrated Security=True"
       Dim con As New Data.SqlClient.SqlConnection(str)
       Dim strAgentsName As String

       con.Open()
           Dim sql As String = "SELECT * FROM tblAgents;"
           Dim cmd As New SqlCommand(sql, con)
           Dim myreader As SqlDataReader = cmd.ExecuteReader()

       While myreader.Read()


           strAgentsName = myreader("UserName").value


       End While

   End Sub

解决方案

In your code you're using the Item property of the SqlDataReader class. Have a look at the documentation at SqlDataReader.Item Property (String) (System.Data.SqlClient)[^] and you'll notice that Item returns a Object Class (System)[^].

The object class then again does not contain property or function called value so the code is causing an exception.

Depending on the data taype you can for example use the Object.ToString Method (System)[^] to show the value. If the type is something other than string then you can use for example DirectCast Operator (Visual Basic)[^] or type specific Get functions such as SqlDataReader.GetInt32 Method (Int32) (System.Data.SqlClient)[^]

Just remember to see if the value really exists by using SqlDataReader.IsDBNull Method (Int32) (System.Data.SqlClient)[^]

Consider the following example

Using connection1 As SqlClient.SqlConnection = New SqlClient.SqlConnection("connection string goes here")
   Using command1 As SqlClient.SqlCommand = New SqlClient.SqlCommand
      command1.Connection = connection1
      command1.CommandText = "select name from sysobjects"
      connection1.Open()
      Using reader1 = command1.ExecuteReader
         While reader1.Read()
            If Not reader1.IsDBNull(0) Then
               Console.WriteLine(reader1("name").ToString())
            End If
         End While
     End Using
     connection1.Close()
   End Using
End Using

I have finally worked out a simple solution. This solution is not using @Parameters in the SQL statement as I am still getting my head around that. But this solution was what I was after. How to pass data from the SQL Query into a local variable that can be used by the code behind program. Please feel free to offer any suggestions on how this could be further simplified. This is the model I was trying to develop. As you see the

getUserName = rs("UserName")

is what I was after. How to access a data field. Hope this helps others and thank you for helping me.

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles btnTest2.Click
        Dim getLoginID As String
        Dim getUserName As String


        'Testing the read methodology of the SQL database

        'Opening the SQL Connetion as "cn"
        Dim cn As System.Data.SqlClient.SqlConnection
        'setting rhe paramiters of "cn" connetion location and connection information such as SQL Login ID and Password
        cn = New System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\inetpub\wwwroot\RedRock\App_Data\Database.mdf;Integrated Security=True")
        cn.Open()

        'Definiing the SQL search paramitiers as a record set called "rs"

        Dim getUserID As New System.Data.SqlClient.SqlCommand("Select LoginID, UserName, Password from tblAgents where LoginID='bmart101'", cn)
        Dim rs As System.Data.SqlClient.SqlDataReader

        'Reads the record set.
        rs = getUserID.ExecuteReader(Data.CommandBehavior.CloseConnection)


        'Useds the information read from the record set to pass the data to local varables.
        If rs.HasRows() Then
            While rs.Read()
                getLoginID = rs("LoginID")
                getUserName = rs("UserName")

            End While

        End If
        'Passes the result back to the webpage as a label to see that it worked.
        Label1.Text = getUserName


    End Sub

这篇关于使用SQL读取读取表中字段的正确语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！