none
Getting errors when reading data from SQL DB Tables

    Question

  • 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)?


    Thursday, December 07, 2017 4:25 PM

All replies

  • 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


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, December 07, 2017 4:39 PM
    Moderator
  • I would prefer in this case ExecuteScalar

    @Karen, the forum has again issues with login


    Success
    Cor

    Thursday, December 07, 2017 5:00 PM
  • @Cor, I will report the issue.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 07, 2017 5:37 PM
    Moderator
  • 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



    I must not understand your recommendation.  When I insert "Dim reader = cmds.ExecuteReader" into my code I get a unhanded exception error for an invalid operation and it states that "Additional information: ExecuteReader: Connection property has not been initialized." on that statement.

    So I guess I don't understand.

    Friday, December 08, 2017 4:49 AM
  • Hi TJBlues,

    According to your description, you just want to acquire ArtistName and AliasNumber data when ArtID=1 Am I right? If yes, I think you could need to use SqlCommand.ExecuteReader Method () instead of SqlCommand.ExecuteNonQuery Method (), because SqlCommand.ExecuteNonQuery Method () executes a Transact-SQL statement against the connection and returns the number of rows affected, for example insert, delete or update.

    I have use SqlCommand.ExecuteReader Method () to do one example, you can take a look:

     Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Sample\Data1.mdf;Integrated Security=True"
            Dim conn As New SqlConnection(str)
            conn.Open()
            Dim sql As String = "select * from Test9 where Id=" & TextBox1.Text
            Dim cmd As New SqlCommand(sql, conn)
            Dim read As SqlDataReader = cmd.ExecuteReader()
            If read.Read Then
                Console.WriteLine("The productname is {0}, and the price is {1}", read(0), read(1))
            End If

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, December 08, 2017 6:23 AM
    Moderator
  • Okay, first off it's best to 

    • Use a class to do data operations
    • Not re-use a connection or command
    • Use parameters rather than embed or concatenate for the where condition
    • Have structured exception handling that is non intrusive

    In the following class I use a method for ExecuteScalar, one for ExecuteReader. Also one that intentionally raises a runtime error so to demo the last bullet above.

    Public Class DataOperations
        Private Server As String = "KARENS-PC"
        Private Catalog As String = "CustomerDatabase"
    
        Private mHasError As Boolean
        ''' <summary>
        ''' Indicates a runtime exception occured
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasError As Boolean
            Get
                Return mHasError
            End Get
        End Property
        Private mException As Exception
        ''' <summary>
        ''' Get last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
    
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        ''' <summary>
        ''' Get a company name given a primary key value to locate
        ''' </summary>
        ''' <param name="pIdentifier"></param>
        ''' <returns></returns>
        Public Function GetCompanyNameByIdentifierScalar(ByVal pIdentifier As Integer) As String
            mHasError = False
            Dim companyName As String = ""
    
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT CompanyName FROM Customer WHERE Identifier = @Identifier"
                    }
    
                    Try
                        cmd.Parameters.AddWithValue("@Identifier", pIdentifier)
                        cn.Open()
    
                        companyName = CStr(cmd.ExecuteScalar)
                    Catch ex As Exception
                        mHasError = True
                        mException = ex
                    End Try
    
                End Using
            End Using
    
            Return companyName
    
        End Function
        Public Function GetCompanyNameByIdentifierScalarWithError(ByVal pIdentifier As Integer) As String
            mHasError = False
            Dim companyName As String = ""
    
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT CompanyName FROM Customer WHERE Identifier = @Identifier"
                    }
    
                    Try
                        'cmd.Parameters.AddWithValue("@Identifier", pIdentifier)
                        cn.Open()
    
                        companyName = CStr(cmd.ExecuteScalar)
                    Catch ex As Exception
                        mHasError = True
                        mException = ex
                    End Try
    
                End Using
            End Using
    
            Return companyName
    
        End Function
        Public Function GetCompanyNameByIdentifierWithReader(ByVal pIdentifier As Integer) As String
            mHasError = False
            Dim companyName As String = ""
    
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT CompanyName FROM Customer WHERE Identifier = @Identifier"
                    }
    
                    Try
                        cmd.Parameters.AddWithValue("@Identifier", pIdentifier)
                        cn.Open()
    
                        Dim reader As SqlDataReader = cmd.ExecuteReader
                        If reader.HasRows Then
                            reader.Read()
                            companyName = reader.GetString(0)
                        End If
    
                    Catch ex As Exception
                        mHasError = True
                        mException = ex
                    End Try
    
                End Using
            End Using
    
            Return companyName
    
        End Function
    End Class
    
    

    Form code

    Public Class Form1
        ''' <summary>
        ''' Using Scalar
        ''' 
        ''' Find by company identifier
        ''' Identifiers in this case range from 1 to 7 so the second call will not
        ''' find a record but the first does find a record
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DataOperations
            Dim companyName As String = ops.GetCompanyNameByIdentifierScalar(2)
            If Not ops.HasError Then
                If Not String.IsNullOrWhiteSpace(companyName) Then
                    MessageBox.Show(companyName)
                Else
                    MessageBox.Show("Not found")
                End If
            Else
                MessageBox.Show($"The following error was encountered: {ops.LastException.Message}")
            End If
    
            companyName = ops.GetCompanyNameByIdentifierScalar(100)
            If Not ops.HasError Then
                If Not String.IsNullOrWhiteSpace(companyName) Then
                    MessageBox.Show(companyName)
                Else
                    MessageBox.Show("Not found")
                End If
            Else
                MessageBox.Show($"The following error was encountered: {ops.LastException.Message}")
            End If
    
        End Sub
        ''' <summary>
        ''' Using reader
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim ops As New DataOperations
            Dim companyName As String = ops.GetCompanyNameByIdentifierWithReader(2)
            If Not ops.HasError Then
                If Not String.IsNullOrWhiteSpace(companyName) Then
                    MessageBox.Show(companyName)
                Else
                    MessageBox.Show("Not found")
                End If
            Else
                MessageBox.Show($"The following error was encountered: {ops.LastException.Message}")
            End If
    
            companyName = ops.GetCompanyNameByIdentifierWithReader(100)
            If Not ops.HasError Then
                If Not String.IsNullOrWhiteSpace(companyName) Then
                    MessageBox.Show(companyName)
                Else
                    MessageBox.Show("Not found")
                End If
            Else
                MessageBox.Show($"The following error was encountered: {ops.LastException.Message}")
            End If
        End Sub
        ''' <summary>
        ''' Demo with error
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim ops As New DataOperations
            Dim companyName As String = ops.GetCompanyNameByIdentifierScalarWithError(2)
            If Not ops.HasError Then
                If Not String.IsNullOrWhiteSpace(companyName) Then
                    MessageBox.Show(companyName)
                Else
                    MessageBox.Show("Not found")
                End If
            Else
                MessageBox.Show($"The following error was encountered: {ops.LastException.Message}")
            End If
    
        End Sub
    End Class
    

    Our database

    Results


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 08, 2017 11:09 AM
    Moderator