Member Avatar for raidenx44

I'm working on my capstone project.
I really having a bad time thinking for the correct query for the search button function.
VB.Net 2008 is my front end while I'm using MySQL as my back end database.

 Private Sub BtnSearch_Material_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch_Material.Click
        Try
            mysqlconn = New MySqlConnection
            mysqlconn.ConnectionString = "server = localhost; username = root ; password = ; database = ilib "

            mysqlconn.Open()

            'Trial Query 1
            'sqlstr = "SELECT alname as 'Author Lastname', afname as 'Author Firstname', aminitial as 'Author Middle Initial',bname as 'Book Title',dissertationtitle as 'Dissertation Title', jname as 'Journal Title', thesestitle as 'Theses Title', materialclass as 'Material Class', subjectname as 'Subject', lsname as 'Library Section'" & _
            '         "FROM material,author,book,dissertation,journal,theses,subject,materialclass,librarysection " & _
            '         "WHERE (material.authorno = author.authorno AND material.bookno = book.bookno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
            '         "AND (material.authorno = author.authorno AND material.journalno = journal.journalno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
            '         "AND (material.authorno = author.authorno AND material.dissertationno = dissertation.dissertationno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
            '         "AND (material.authorno = author.authorno AND material.thesesno = theses.thesesno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
            '         "OR author.alname = " & txtAcquisitionSearch.Text & " OR author.afname = " & txtAcquisitionSearch.Text & " OR book.bname = " & txtAcquisitionSearch.Text & " OR dissertation.dissertationtitle = " & txtAcquisitionSearch.Text & " OR journal.jname = " & txtAcquisitionSearch.Text & _
            '         " OR theses.thesestitle = " & txtAcquisitionSearch.Text & " OR book.bcallno = " & txtAcquisitionSearch.Text & " OR journal.jcallno = " & txtAcquisitionSearch.Text & " OR dissertation.dissertationcallno = " & txtAcquisitionSearch.Text & " OR theses.thesescallno = " & txtAcquisitionSearch.Text

            'Trial Query 2
            sqlstr = "SELECT alname as 'Author Lastname', afname as 'Author Firstname', aminitial as 'Author Middle Initial',bname as 'Book Title',dissertationtitle as 'Dissertation Title', jname as 'Journal Title', thesestitle as 'Theses Title', materialclass as 'Material Class', subjectname as 'Subject', lsname as 'Library Section'" & _
                     "FROM material,author,book,dissertation,journal,theses,subject,materialclass,librarysection " & _
                     "WHERE (material.authorno = author.authorno AND material.bookno = book.bookno AND material.journalno = journal.journalno AND material.dissertationno = dissertation.dissertationno AND material.thesesno = theses.thesesno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
                     "OR author.alname LIKE '" & txtAcquisitionSearch.Text & "%' OR author.afname LIKE '" & txtAcquisitionSearch.Text & "%' OR book.bname LIKE '" & txtAcquisitionSearch.Text & "%' OR dissertation.dissertationtitle LIKE '" & txtAcquisitionSearch.Text & "%' OR journal.jname LIKE '" & txtAcquisitionSearch.Text & _
                     "%' OR theses.thesestitle LIKE '" & txtAcquisitionSearch.Text & "%' OR book.bcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR journal.jcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR dissertation.dissertationcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR theses.thesescallno LIKE '" & txtAcquisitionSearch.Text & "%' "

            sqlcmd = New MySqlCommand(sqlstr, mysqlconn)
            sqlda = New MySqlDataAdapter
            sqlda.SelectCommand = sqlcmd

            sqltable = New DataTable
            sqlda.Fill(sqltable)
            Acquisition_DataGridView1.DataSource = sqltable

            mysqlconn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
fadsff.png 17.13 KB
  • 1 Contributor
  • 1 Reply
  • 310 Views
  • 19 Minutes Discussion Span
  • Latest Post Latest Post by raidenx44
Member Avatar for raidenx44

I solved the problem using this code.
lacking of open and close parenthesis.

Private Sub BtnSearch_Material_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch_Material.Click
        Try
            mysqlconn = New MySqlConnection
            mysqlconn.ConnectionString = "server = localhost; username = root ; password = ; database = ilib "

            mysqlconn.Open()

            sqlstr = "SELECT alname as 'Author Lastname', afname as 'Author Firstname', aminitial as 'Author Middle Initial',bname as 'Book Title',dissertationtitle as 'Dissertation Title', jname as 'Journal Title', thesestitle as 'Theses Title', materialclass as 'Material Class', subjectname as 'Subject', lsname as 'Library Section'" & _
                     "FROM material,author,book,dissertation,journal,theses,subject,materialclass,librarysection " & _
                     "WHERE (material.authorno = author.authorno AND material.bookno = book.bookno AND material.journalno = journal.journalno AND material.dissertationno = dissertation.dissertationno AND material.thesesno = theses.thesesno AND material.materialclassno = materialclass.materialclassno AND material.subjectno = subject.subjectno AND material.librarysectionno = librarysection.librarysectionno) " & _
                     "AND (author.alname LIKE '" & txtAcquisitionSearch.Text & "%' OR author.afname LIKE '" & txtAcquisitionSearch.Text & "%' OR book.bname LIKE '" & txtAcquisitionSearch.Text & "%' OR dissertation.dissertationtitle LIKE '" & txtAcquisitionSearch.Text & "%' OR journal.jname LIKE '" & txtAcquisitionSearch.Text & _
                     "%' OR theses.thesestitle LIKE '" & txtAcquisitionSearch.Text & "%' OR book.bcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR journal.jcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR dissertation.dissertationcallno LIKE '" & txtAcquisitionSearch.Text & "%' OR theses.thesescallno LIKE '" & txtAcquisitionSearch.Text & "%') "

            sqlcmd = New MySqlCommand(sqlstr, mysqlconn)
            sqlda = New MySqlDataAdapter
            sqlda.SelectCommand = sqlcmd

            sqltable = New DataTable
            sqlda.Fill(sqltable)
            Acquisition_DataGridView1.DataSource = sqltable

            mysqlconn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
fasdfasdf.png 18.49 KB
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.