none
Display SQL data in text box upon clicking a button

    Question

  • I am trying to write a program that will display data in a text box from a SQL Database after a user enters an index number (which specifies the SQL entry to be selected) in a box, then clicks a button to grab the text data specific to that index number.

    Here is the VB I have so far:

    Imports System.Data.SqlClient

    Public Class ConfigBuilder
        Class TestTable
            Property SiteID As Integer
            Property SiteName As String
        End Class

        Function GetData(SiteID As Integer) As List(Of TestTable)

            Dim theResults = New List(Of TestTable)
            Dim connStr = "Data Source=WK009\SQLEXPRESS;Initial Catalog=TestData;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            Using conn = New SqlConnection(connStr)
                Dim sql = "SELECT SiteName, SiteID FROM TestTable WHERE [SiteID] = " & SiteIDtxt.Text

                Dim sqlCmd = New SqlCommand(sql, conn)

                sqlCmd.Parameters.AddWithValue(SiteIDtxt.Text, SiteID)
                conn.Open()

                Dim rdr = sqlCmd.ExecuteReader

                While rdr.Read
                    theResults.Add(New TestTable With {
                                   .SiteID = rdr.GetInt32(0),
                                   .SiteName = rdr.GetString(1)})
                End While

                conn.Close()

            End Using

            Return theResults

        End Function
        Private Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
            Dim SiteID As Integer = 0

            If Integer.TryParse(SiteIDtxt.Text, SiteID) Then
                Dim myResults = GetData(SiteID)
                If myResults.Count = 1 Then
                    ' we have a unique result, show information
                    ResultsTxt.Text = String.Format("{1}", myResults(0).SiteID, myResults(0).SiteName)

                Else
                    MsgBox(String.Format("Site ID {0} not found.", SiteID))
                End If

            Else
                MsgBox("Site ID must be a number!")
            End If


        End Sub

    If anyone could help, that would be greatly appreciated :)

    Harry

    Tuesday, March 21, 2017 4:56 PM

Answers

  • Hallo Harry,

    Yea so clever I'm not that I see this in a glance, but your SQL transact says: SiteName, SiteID  

    Or you should make from the zero a one and from the one a zero or change that transact.

    :-)


    Success
    Cor

    • Marked as answer by Harry Stout Wednesday, March 22, 2017 9:48 AM
    Tuesday, March 21, 2017 7:18 PM

All replies

  • Harry,

    I did not see much what is wrong, I changed the way the parameter is used. Also if using is used it closes automatically a connection. 

    I could not try it of course and I changed the class name to see it in Visual Studio. 

    Imports System.Data.SqlClient
    Public Class Form1
        Class TestTable
            Property SiteID As Integer
            Property SiteName As String
        End Class
        Function GetData(SiteID As Integer) As List(Of TestTable)
            Dim theResults = New List(Of TestTable)
            Dim connStr = "Data Source=WK009\SQLEXPRESS;Initial Catalog=TestData;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            Using conn = New SqlConnection(connStr)
                Dim sql = "SELECT SiteName, SiteID FROM TestTable WHERE [SiteID] = @SiteID"
                Dim sqlCmd As New SqlCommand(sql, conn)
                sqlCmd.Parameters.AddWithValue("@SiteID", SiteID)
                conn.Open()
                Dim rdr = sqlCmd.ExecuteReader
                While rdr.Read
                    theResults.Add(New TestTable With {
                                   .SiteID = rdr.GetInt32(0),
                                   .SiteName = rdr.GetString(1)})
                End While
            End Using
            Return theResults
        End Function
        Private Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
            Dim SiteID As Integer = 0
            If Integer.TryParse(SiteIDtxt.Text, SiteID) Then
                Dim myResults = GetData(SiteID)
                If myResults.Count = 1 Then
                    ' we have a unique result, show information
                    ResultsTxt.Text = String.Format("{1}", myResults(0).SiteID, myResults(0).SiteName)
                Else
                    MsgBox(String.Format("Site ID {0} not found.", SiteID))
                End If
            Else
                MsgBox("Site ID must be a number!")
            End If
        End Sub
    End Class


    Success
    Cor

    Tuesday, March 21, 2017 5:07 PM
  • Hi,

    Thanks for the prompt reply.

    I tried your amendments is Visual Studio, however, an exception is triggered around this line of code: 

    theResults.Add(New TestTable With {
                                   .SiteID = rdr.GetInt32(0),
                                   .SiteName = rdr.GetString(1)})

    Here is the system message:

    System.InvalidCastException: 'Specified cast is not valid.'

    If it helps, my SQL table looks like this:

    SiteID     SiteName

    170         Hull

    4             Harlow

    Thanks!

    H

    Tuesday, March 21, 2017 5:30 PM
  • Hallo Harry,

    Yea so clever I'm not that I see this in a glance, but your SQL transact says: SiteName, SiteID  

    Or you should make from the zero a one and from the one a zero or change that transact.

    :-)


    Success
    Cor

    • Marked as answer by Harry Stout Wednesday, March 22, 2017 9:48 AM
    Tuesday, March 21, 2017 7:18 PM
  • Yes this worked!

    Thank you very much

    H

    Wednesday, March 22, 2017 9:49 AM