taking individual fields from a database

[rickilambert]

hey peeps, im creating a database link in vb.ent with my programme which is a libary system,

on the main page i have a search box for the user to type in a name of a book. if the book name is correct i want the detials from the database to be listed in a box below, such as

author
name
genre
condition

these are all present fields in my database but how do i get them to display?

ty
rik

[PeejAvery]

With what database are you working? Also, have you searched Google yet for tutorials on VB.NET and your specify database? It will return hundreds of results.

[rickilambert]

hey, thanks for the rpely, the databse i am using is microsoft access! and i have tried looking on google but i cannot fidn the solution, ive been lokking for weeks! shall i copy and paste my code in here? i realsie it wont work without the databasse but what the heck, u may get a clearer pictire of what im trying to achieve?

[PeejAvery]

shall i copy and paste my code in here?

That is always helpful. Remember to use code tags.

[rickilambert]

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Main
    Dim bookconnection As OleDbConnection
    Dim BooktableCommand As OleDbCommand
    Dim BooktableAdapter As OleDbDataAdapter
    Dim BooktableTable As DataTable
    Dim bookManager As CurrencyManager

    Private Sub Main_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        bookconnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + Application.StartupPath + "\\..\\..\\Unnormalised database.mdb")
        bookconnection.Open()
        BooktableCommand = New OleDbCommand("SELECT * FROM Booktable ORDER BY BookName", bookconnection)
        ' establish Booktable data adapter/data table
        BooktableAdapter = New OleDbDataAdapter()
        BooktableAdapter.SelectCommand = BooktableCommand
        BooktableTable = New DataTable()
        BooktableAdapter.Fill(BooktableTable)
        With Me

            .booksearchtxtb.DataBindings.Add("Text", BooktableTable, "BookName")
            .MaskedTextBox1.DataBindings.Add("Text", BooktableTable, "BookID")
            .MaskedTextBox1.Focus()

        End With

    End Sub
    Private Sub findButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles findButton.Click
        'Check BookID validity (at least that it has enough characters)

        BooktableTable.DefaultView.Sort = "BookName"

        Dim foundRow As Integer = BooktableTable.DefaultView.Find(booksearchtxtb.Text)
        
        If (foundRow <> -1) Then

            MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in Booktable.", _
            ("Author"), _
            ("Quantity"), _
            ("previous owners"), _
            ("condition"), _
            ("genre")))


        End If

    End Sub
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bookdetailsbtn.Click
        bookdetails.Show()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles newclientbtn.Click
        newcliententry.Show()

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles overduebooksbtn.Click
        overduebooks.Show()

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitbtn.Click
        End
    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()
    End Sub

    Private Sub VToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles VToolStripMenuItem.Click

        With Me.FontDialog1
            .Font = Me.loginnamelbl.Font
            .ShowDialog()
            Me.bookdetailsbtn.Font = .Font
            Me.Bookdetailslbl.Font = .Font
            Me.Booksbacklbl.Font = .Font
            Me.booksbacklstbox.Font = .Font
            Me.booksearchlbl.Font = .Font
            Me.exitbtn.Font = .Font
            Me.loginnamelbl.Font = .Font
            Me.newclientbtn.Font = .Font
            Me.overduebooksbtn.Font = .Font
            Me.booksearchtxtb.Font = .Font
            Me.Quantitytxtb.Font = .Font

        End With
    End Sub

    Private Sub TextColourToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextColourToolStripMenuItem.Click

        With Me.ColorDialog1

            .Color = Me.loginnamelbl.ForeColor
            .ShowDialog()
            Me.bookdetailsbtn.ForeColor = .Color
            Me.Bookdetailslbl.ForeColor = .Color
            Me.Booksbacklbl.ForeColor = .Color
            Me.booksbacklstbox.ForeColor = .Color
            Me.booksearchlbl.ForeColor = .Color
            Me.exitbtn.ForeColor = .Color
            Me.loginnamelbl.ForeColor = .Color
            Me.newclientbtn.ForeColor = .Color
            Me.overduebooksbtn.ForeColor = .Color
            Me.booksearchtxtb.ForeColor = .Color
            Me.Quantitytxtb.ForeColor = .Color
            Me.availabilitylbl.ForeColor = .Color

        End With

    End Sub

    Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click
        Aboutscreen.Show()
    End Sub

    Private Sub HowToUseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HowToUseToolStripMenuItem.Click
        Howtouse.Show()
    End Sub

    Private Sub booksbacklstbox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles booksbacklstbox.SelectedIndexChanged

    End Sub

    Private Sub BooktableBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Me.BooktableBindingSource.EndEdit()
        Me.BooktableTableAdapter.Update(Me.Unnormalised_databaseDataSet1.Booktable)

    End Sub

    Private Sub booksearchlbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles booksearchlbl.Click

    End Sub

    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub
    
    Private Sub MaskedTextBox1_MaskInputRejected(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MaskInputRejectedEventArgs) Handles MaskedTextBox1.MaskInputRejected

    End Sub
End Class

hopefully you understand what i am trying to do?

[Thread1]

there are ways of doing it.. here is one..

Dim foundRow As Integer = BooktableTable.DefaultView.Find(booksearchtxtb.Text)
        
        If (foundRow <> -1) Then

            Dim drv As DataRowView = BooktableTable.DefaultView(foundRow)

            MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in Booktable.", _
            drv("Author").ToString(), _
            drv("Quantity").ToString(), _
            drv("previous owners").ToString(), _
            drv("condition").ToString(), _
            drv("genre").ToString()))


        End If

you may check out also FindRows method.

hope that helps.

[rickilambert]

thanks very much for that, ill give it a go and ill reply sometime soon to let you no how it goes

[sultan_tipu]

this source code will also helpfull for you.

[rickilambert]

ok, im back, ive tried, and again i have failed badly, i really cannot get my head round this at all!


basically on my main page there is a search box and i wish the search box to allow the user to type in the name of a book and then it displays the book details below (the book details will be fields from the database such as, author, genre, condition)

i am really struggling, this is the last thing to do and ive finished and i cant do it

ive attatched the file, with the database included, if you do take a look u will need to log in to the programme with the user name and password

user = user
password = password!

thanks very much

regards

rl

[aniskhan]

Perform search using datatables Select() method.

        Dim foundRows() As DataRow = BooktableTable.Select("BookName='" & booksearchtxtb.Text.Trim & "'")
        
        If (foundRows.Length > 0) Then
            MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in Booktable.", _
            foundRows(0)("Author"), _
            foundRows(0)("Quantity"), _
            foundRows(0)("previousowners"), _
            foundRows(0)("condition"), _
            foundRows(0)("genre")))
        End If

[rickilambert]

Perform search using datatables Select() method.

        Dim foundRows() As DataRow = BooktableTable.Select("BookName='" & booksearchtxtb.Text.Trim & "'")
        
        If (foundRows.Length > 0) Then
            MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in Booktable.", _
            foundRows(0)("Author"), _
            foundRows(0)("Quantity"), _
            foundRows(0)("previousowners"), _
            foundRows(0)("condition"), _
            foundRows(0)("genre")))
        End If

the thing with this method however is that it just pops up a display messgage, i want the values to appear in a list box? how can this be done?

[aniskhan]

Use dataView to get only filtered rows from books datatable.

Dim dv As New DataView(BooktableTable, "BookName='" & booksearchtxtb.Text.Trim & "'", "BookID", DataViewRowState.CurrentRows)

Simply bind the controls in the same way to dataView like u did with datatable.

[rickilambert]

i really appreciate u helping me, but the thing is im a year 1 student and have only just started, ireally domt undersand what you mean, sorry :s