none
how to display data sql in text box in vb RRS feed

  • Question

  • I'm just learning Visual Basic
      Now I have a project and I can not display my database information with me
    please direct me

      Private Sub okbtn_Click(sender As Object, e As EventArgs) Handles okbtn.Click
            con = New SqlConnection
            con.ConnectionString = ("Data Source=FARAHANI-PC;Initial Catalog=pc_info;Integrated Security=True")
            Dim dadapter As New SqlDataAdapter
            Try
                con.Open()
                Dim Query As String
                Query = "select * from  [dbo].[SYS-INFO] where [system num]='" & sysnotxtbox.Text.ToString & "' "
                cmd = New SqlCommand(Query, con)
                Dim reader As SqlDataReader
                reader = cmd.ExecuteReader
                Dim count As Integer

                While reader.Read


                End While

                If count = 1 Then

                    OSTETXBOX.Text = reader.GetInt16("os")

                ElseIf count > 1 Then
                    MessageBox.Show("شماره سیستم را وارد کنید")

                Else
                    MessageBox.Show("شماره وارد شده در لیست موجو نمیباشد")
                End If
                conn.Close()


            Catch ex As SqlException
                MessageBox.Show(ex.Message)
            Finally
            End Try

    Saturday, January 20, 2018 5:52 AM

All replies

  • The quickest way and also with parameters on our website.

    http://www.vb-tips.com/ExecuteScalarText.ASPX


    Success Cor

    Saturday, January 20, 2018 8:25 AM
  • Check this too:


       . . .
      
    reader = cmd.ExecuteReader

       If reader.Read Then
          OSTETXBOX.Text = reader.GetInt16("os")
       Else
          OSTETXBOX.Text = ""
          MessageBox.Show("شماره وارد شده در لیست موجو نمیباشد")
       End If




    The first message box is probably better to use at the beginning:

    Private Sub okbtn_Click(sender As Object, e As EventArgs) Handles okbtn.Click
       if String.IsNullOrWhiteSpace( sysnotxtbox.Text ) Then
          MessageBox.Show(
    "شماره سیستم را وارد کنید")
          Return
       End If
       . . .

    End Sub

    Saturday, January 20, 2018 9:00 AM
  • When starting off as you are now it's easy to fall into writing "easy" code which means write as little as possible along with writing this code in the form.

    What Cor presented will give you what you are after and for someone who has done a decent amount of code would take Cor's code sample and place the code into a class, call a method from the class from a form.

    The following is one level up, meaning not only does it do the data operation from a class (and this is overkill for one method but safe to say you will have more data operations down the road so it's not overkill).

    The first class is a base class which the class below it uses. This class provides properties for if there was an exception thrown and if so is propagated back to the form

    Public Class BaseExceptionsHandler
        Protected mHasException As Boolean
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        Public ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return mLastException.Message
            End Get
        End Property
    End Class
    

    This class which inherits the above class provides the connection string. I hard coded the server name as most developers will work with one server and left the catalog empty as this will most likely change and we will set it in the data class below this class.

    Public Class BaseSqlServerConnections
        Inherits BaseExceptionsHandler
    
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = ""
        Public ReadOnly Property ConnectionString As String
            Get
                Return $"Data Source={DatabaseServer};" &
                    $"Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    

    Here we have the data class which we will call in the form from a button click event. Note this class inherits from the class above so we have our connection string and now we set the catalog.

    We pass in a integer, use the integer to locate any records (in this case there is only one as we are looking for a primary key) a specific record. I use a parameter which properly sets up the value for the WHERE condition. If we were doing a string the value would be wrapped in apostrophes and if the value was a string with something like 

    Karen's coffee shop

    The parameter would be wrapped like 'Karen''s coffee shop'

    Note the try/catch, if something unexpected happens two properties are set and we can query HasException, if try show the issue e.g. MessageBox.Show($"Encountered a problem {ops.LastException.Message}")

    Imports System.Data.SqlClient
    
    Public Class SqlServerOperations
        Inherits BaseSqlServerConnections
        Public Sub New()
            DefaultCatalog = "NorthWindAzure"
        End Sub
        Public Function GetCatagoryByIdentifier(ByVal pIdentifier As Integer) As String
    
            Dim selectStatement As String =
                "SELECT CategoryName FROM Categories WHERE CategoryId = @CategoryId"
    
            Dim categoryName As String = Nothing
    
            Using cn = New SqlConnection(ConnectionString)
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                    cmd.Parameters.AddWithValue("@CategoryId", pIdentifier)
                    Try
                        cn.Open()
                        categoryName = CStr(cmd.ExecuteScalar)
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
                End Using
            End Using
    
            Return categoryName
    
        End Function
    End Class
    

    In the form we would write  the following

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
        Dim categoryId As Integer = 0
        Dim categoryName As String = ""
    
        If Integer.TryParse(txtCategoryId.Text, categoryId) Then
            Dim ops As New SqlServerOperations
            txtCategoryName.Text = ops.GetCatagoryByIdentifier(categoryId)
        End If
    End Sub

    In the above if the value was not located we see nothing but we can check for no value located e.g.

    Dim categoryId As Integer = 0
    Dim categoryName As String = ""
    
    If Integer.TryParse(txtCategoryId.Text, categoryId) Then
        Dim ops As New SqlServerOperations
        txtCategoryName.Text = ops.GetCatagoryByIdentifier(categoryId)
        If String.IsNullOrWhiteSpace(txtCategoryName.Text) Then
            MessageBox.Show($"Category id {categoryId} not located")
        End If
    End If

    Suppose they entered nothing or a non numeric value, we can tell them this happened

    Dim categoryId As Integer = 0
    Dim categoryName As String = ""
    
    If Integer.TryParse(txtCategoryId.Text, categoryId) Then
        Dim ops As New SqlServerOperations
        txtCategoryName.Text = ops.GetCatagoryByIdentifier(categoryId)
        If String.IsNullOrWhiteSpace(txtCategoryName.Text) Then
            MessageBox.Show($"Category id {categoryId} not located")
        End If
    Else
        MessageBox.Show("Please enter a valid number for category id")
    End If

    This example checks for errors

    Dim categoryId As Integer = 0
    Dim categoryName As String = ""
    
    If Integer.TryParse(txtCategoryId.Text, categoryId) Then
        Dim ops As New SqlServerOperations
        txtCategoryName.Text = ops.GetCatagoryByIdentifier(categoryId)
        If ops.HasException Then
            MessageBox.Show($"Encountered issues: {ops.LastException.Message}")
            Exit Sub
        End If
        If String.IsNullOrWhiteSpace(txtCategoryName.Text) Then
            MessageBox.Show($"Category id {categoryId} not located")
        End If
    Else
        MessageBox.Show("Please enter a valid number for category id")
    End If
    How can we improve this for the above examples? Do not use a TextBox for the number but instead use a NumericUpDown control or a custom TextBox that only takes numbers.

    In closing, you can take the easy way by doing everything in the form or use what I presented.

    For a complete example (with full code) see my MSDN code sample.


    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

    Saturday, January 20, 2018 11:12 AM
    Moderator
  • Hi electra740,

    Please take a look the following code, you need to use reader.Int16("os") in reader.read() range.

     Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Integrated Security=True"
            Dim sql As String = "Select * from Test4 where Id=@Id"
            Dim count As Integer = 0
            Dim character As String = ""
            Using conn As New SqlConnection(str)
                conn.Open()
                Using cmd As New SqlCommand(sql, conn)
                    cmd.Parameters.AddWithValue("@Id", 1)
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    While reader.Read()
                        count = +1
                        character += "The picture name is " & reader("PictureName") & Environment.NewLine
                    End While
                    If count = 1 Then
                        TextBox1.Text = character
                        MessageBox.Show("correct Id number")
                    ElseIf count > 1 Then
                        MessageBox.Show("Please enter Id number")
                    Else
                        MessageBox.Show("The Id you enter is not in the list")
                    End If
                End Using
                conn.Close()
            End Using

    About SqlCommand.ExecuteReader Method (), please refer to:

    https://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx

    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.

    • Proposed as answer by Stanly Fan Friday, January 26, 2018 8:21 AM
    Monday, January 22, 2018 6:16 AM
    Moderator