Populate textboxes from the listbox selection

Question

0.00/5 (No votes)

See more:

I have a small program and when it runs the MDB database populates the Names into the ListBox. I have wrecked my brain trying to select a name in the Listbox and the full information to then populate the text Boxes. Eventually I wish to use the text Boxes to add new records to the database. Any help appreciated, here is my code, the commented out code is what won't work.

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Hospital.mdb;"
        Dim objConnection As New OleDbConnection(strConnection)
        Dim objConnection2 As New OleDbConnection(strConnection)

        'Open the connection with error handling
        Try
            objConnection.Open()
        Catch OleDbExceptionErr As OleDbException
            MessageBox.Show(OleDbExceptionErr.Message)
        Catch InvalidOperationErr As InvalidOperationException
            MessageBox.Show(InvalidOperationErr.Message)
        End Try

        'Create a command object with the SQL statement needed to select the first names
        Dim strSQL As String = "SELECT * FROM Patients"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)

        'Create a data adapter and data table then fill the data table
        Dim objDataAdapter As New OleDbDataAdapter(objCommand)
        Dim objDataTable As New DataTable("PatientID")
        objDataAdapter.Fill(objDataTable)

        'Create connection and release resources
        objConnection.Close()
        objConnection.Dispose()
        objConnection = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing

        'Fill names into the listbox
        For Each Row As DataRow In objDataTable.Rows
            lstNames.Items.Add(Row.Item("Patient Name"))
        Next

        'Release resources
        objDataTable.Dispose()
        objDataTable = Nothing
    End Sub

    Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        'Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Hospital.mdb;"

        'Dim DR As DataRow = Select * From Patients  Where PatientID = "'" & lstNames.SelectedValue & "'"
        'txtName.Text = DR("Patient Name")
        'txtWard.Text = DR("Ward")
        'txtNotes.Text = DR("Notes")

    End Sub

What I have tried:

'Dim DR As DataRow = Select * From Patients  Where PatientID = "'" & lstNames.SelectedValue & "'"
       'txtName.Text = DR("Patient Name")
       'txtWard.Text = DR("Ward")
       'txtNotes.Text = DR("Notes")

Posted 12-Jan-17 1:39am

sebalius

Updated 23-Jan-17 1:42am

Add a Solution

Comments

[no name] 12-Jan-17 8:35am

Of course it won't work. You are trying to assign a string to a DataRow which tells me that you do not understand the fundamental basics of programming. And even if you could do that, you are asking for an SQL injection attack.

Richard MacCutchan 12-Jan-17 8:38am

That code makes no sense. you already have all the data in your DataTable so you just need to copy the items from the relevant cells to your text boxes.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thregarth · Answer 1 · 2017-01-23T01:42:00

Which you need is to read from Patients table by lstnames selected value and pass others fields value to textboxes

'Create Connection from connectionString

Dim objConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Hospital.mdb;")
'Create a command

Dim objCommand As New OleDbCommand("Select [Patient Name],[Ward],[Notes] From Patients  Where PatientID ='" & lstNames.SelectedValue & "'", objConnection)
Dim reader As OleDbDataReader
'Open connection

objConnection.open()
'Execute query

reader = command.ExecuteReader()

' Always call Read before accessing data.
While reader.Read()
    txtName.Text=reader.GetValue(0)
    txtWard.Text = reader.GetValue(1)
    txtNotes.Text = reader.GetValue(2)
End While
reader.close

For more documentation please read MSDN Documentation - OleDbDataReader.Read Method