Solved

Get lastname from database and populate textboxes no binding

Posted on 2014-10-10
2
138 Views
Last Modified: 2014-10-13
I wrote a form in which you enter lastname and then should return data and populate textboxes I know that I created a sub that grab's lastname but I don't know how to return that dataset into a new function

app look
here is what I wrote
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Data.SqlDbType
Public Class LastnameSelectNCCA
    Public sql As String
    Public ds As New DataSet
    Public stgnccapath As String = "\\tsnfps01\webdev\branding\tompowers\College Football logos\"
    Public connetionString As String
    Public connection As SqlConnection
    Public adapter As SqlDataAdapter
    Public objDataReader As SqlDataReader
    Public SelectCommand As New SqlCommand
    Public intLast As Integer
    Public Sub GetNccaplayers()

        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=XXXXXXXXX"
        connection = New SqlConnection(connetionString)



        sql = "SELECT FirstName, LastName,Position,College,Class,TeamID,PlayerID"

        sql += " FROM NCAA_FB_PLAYERS  WHERE"
        sql += " STATUS ='A' AND FIRSTNAME <>'Team' AND Lastname like '%" & _
                txtLastname.Text & "%'" & "ORDER BY LastName DESC"

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@Lastname", txtLastname.Text)

            adapter.Fill(ds)
            connection.Close()

        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        LoadData()
    End Sub
    Public Sub LoadData()


        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)
        Dim intLast As Integer = txtPlayerID.Text

        Try
            Dim SqlCommand = New SqlCommand
            Dim objCommand = New SqlCommand

            'Set the objCommand object properties
            objCommand.CommandText = "SelectID"
            objCommand.CommandType = CommandType.StoredProcedure
            objCommand.Connection = connection

            'Add the required parameter for the query
            '        objCommand.Parameters.Add("@ID", OleDbType.Guid, 16).Value = _
            '           New Guid(ComboBox1.SelectedValue.ToString)
            objCommand.Parameters.Add("@PlayerID", SqlDbType.Int, 32).Value = CInt(intLast)
            Dim objDataReader As SqlDataReader
            'Execute the Query
            objDataReader = objCommand.ExecuteReader()

            'If we have data then display the project description
            If objDataReader.HasRows Then
                objDataReader.Read()
                txtFirst.Text = objDataReader.Item("Firstname")
                txtLast.Text = objDataReader.Item("Lastname")
                Position.Text = objDataReader.Item("Position")
                txtCollege.Text = objDataReader.Item("College")
                txtClass.Text = objDataReader.Item("Class")
                txtTeamID.Text = objDataReader.Item("TeamID")
                txtPlayerID.Text = objDataReader.Item("PlayerID")
               

            End If
        Catch sqlExceptionErr As SqlException
            MessageBox.Show(sqlExceptionErr.Message)
        End Try

        objDataReader.Close()
        connection.Close()

       
    End Sub

    Private Sub btnexit_Click(sender As Object, e As EventArgs) Handles btnexit.Click
        Me.Close()
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
       
        picNCAATeam.ImageLocation = stgnccapath & txtTeamID.Text & ".jpg"
    End Sub

    
    Private Sub btnselectLastname_Click(sender As Object, e As EventArgs) Handles btnselectLastname.Click
        If txtLast.Text = "" Then
            Exit Sub
        Else

            GetNccaplayers()
        End If
    End Sub
End Class

Open in new window

Thanks EE
0
Comment
Question by:powerztom
2 Comments
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
ID: 40377123
After call to
            GetNccaplayers()

Put the ds values in the text boxes.

if ds isnot nothing andalso ds.tables(0).rows.count>0 then
        dim row as DataRow=ds.Tables(0).Rows(0)
       
                txtFirst.Text = row.item(0).toString()
                txtLast.Text = row.item(1).toString()

'etc.
'increment the item index 0,1,2,3, etc.
end if
0
 

Author Closing Comment

by:powerztom
ID: 40377609
SStory Thank you just what I needed to see. Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question