从SQL DB表读取数据时出错 [英] Getting errors when reading data from SQL DB Tables

问题描述

从SQL表读取时出现错误。

我需要在Visual Basic中构建一个表单来读取和写入数据到SQL DB。  
我首先编写了一个非常简单的程序来从一个简单的示例数据库表中获取数据但是当我尝试从表中读取时，我的结果仍然是"-1"。<跨度>&NBSP;
简单数据库有两个表。  我想要读取的一个表有3列，我已经用数据填充了3行。 
列是：

•   ArtID（PK，bigint）
• ArtName（Varchar（50））
•   ArtAliasID（bigint）

以下是我用于从表中获取数据的代码部分：

 Public Class Form1 
 Dim conn As New SqlConnection 
 Dim cmds As New SqlCommand 
 
 Private Sub btnGetRecord_Click（sender as Object，e As EventArgs）处理btnGetRecord.Click 
 Dim ArtistName As String =" This is default Text" 
 Dim AliasNumber为Int64 = 0 
 Dim ArtID为Int64 = 1 
 
尝试
 conn.ConnectionString ="数据源= BRUTE \ SQLEXPRESS;初始目录= TestingDB; Integrated Security = True; Connect Timeout = 15; Encrypt = False; TrustServerCertificate = True; ApplicationIntent = ReadWrite; MultiSubnetFailover = False" 
 conn.Open（）
 cmds.Connection = conn 
 cmds.CommandText =" SELECT ArtName FROM ARTIST WHERE ArtID = 1" 
 ArtistName = cmds.ExecuteNonQuery（）
 cmds.CommandText =" SELECT ArtAliasID FROM ARTIST WHERE ArtID =" &安培; ArtID 
 AliasNumber = cmds.ExecuteNonQuery（）
 MsgBox（" Artist is"& ArtistName&" Artist Alias is"& AliasNumber）
 Catch ex As Exception 
 MessageBox.Show（"从表中读取记录时出错..."& ex.Message，"读取记录"）
最后
 conn.Close（）
结束试试
结束次级
 

以下是我从此代码中获得的信息：

正如你所看到我回来了-1。  ; 我对这里发生的事情感到茫然，因为我无法监控数据库内部的查询结果。 
我一直在努力确保SQL表和VB程序之间的数据类型匹配（SQL中的bigint和VB中的int64，SQL中的Varchar（50）和VB中的字符串）。
  有没有人能够了解我做错了什么？

此外，有谁知道-1的含义（我假设这意味着没有什么可以返回或格式不匹配或沿着那条线的东西）？

解决方案

使用ExecuteReader，而不是ExecueNonQuery，例如Dim reader = cmds.ExecuteReader然后询问是否有行，例如如果是reader.HasRows，如果为true则执行reader.Read然后获取值，例如

reader.Read

Dim artistName As String = reader.GetString（0）'用于第一个查询

或ExecuteScalar

https://msdn.microsoft.com /en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

I am getting an error when reading from SQL tables.

I need to build a form in Visual Basic to read and write data to a SQL DB.  I have started out by writing a very simple program to fetch data from a simple sample database table but I keep getting a "-1" back as the result when I try and read from a table.  The simple database has two tables.  The one table I am trying to read from has 3 columns and I have populated 3 rows with data.  The columns are:

•  ArtID (PK, bigint)
• ArtName (Varchar(50))
•  ArtAliasID (bigint)

Here is the part of the code that I am using for getting data from the table:

Public Class Form1
    Dim conn As New SqlConnection
    Dim cmds As New SqlCommand

    Private Sub btnGetRecord_Click(sender As Object, e As EventArgs) Handles btnGetRecord.Click
        Dim ArtistName As String = "This is default Text"
        Dim AliasNumber As Int64 = 0
        Dim ArtID As Int64 = 1

        Try
            conn.ConnectionString = "Data Source=BRUTE\SQLEXPRESS;Initial Catalog=TestingDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            conn.Open()
            cmds.Connection = conn
            cmds.CommandText = "SELECT ArtName FROM ARTIST WHERE ArtID = 1"
            ArtistName = cmds.ExecuteNonQuery()
            cmds.CommandText = "SELECT ArtAliasID FROM ARTIST WHERE ArtID = " & ArtID
            AliasNumber = cmds.ExecuteNonQuery()
            MsgBox("Artist is " & ArtistName & "    Artist Alias is " & AliasNumber)
        Catch ex As Exception
            MessageBox.Show("Error while reading record from table..." & ex.Message, "  Read Records")
        Finally
            conn.Close()
        End Try
    End Sub

Here is what I get from this code:

As you can see I am getting back a -1.  I am at a loss as to what is happening here since I can’t monitor what is going on inside the DB when it gets a query.  I have endeavored to make sure the data types match between the SQL table and my VB program (bigint in SQL and int64 in VB, Varchar(50) in SQL and string in VB).  Does anyone have insight as to what I am doing wrong?

Also, does anyone know what the -1 means (I am assuming it means that there is nothing to return or the formats don't match or something along that line)?

解决方案

Use ExecuteReader, not ExecueNonQuery e.g. Dim reader = cmds.ExecuteReader then ask if there are rows e.g. if reader.HasRows, if true execute reader.Read then to get values e.g.

reader.Read

Dim artistName As String = reader.GetString(0) ' for the first query

Or ExecuteScalar

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

这篇关于从SQL DB表读取数据时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！