asked on

VB.Net with Access 2007 - runing a query for a give search criteria

Hi,
I am working with vb.net 2012 an access 2007 database to develop a simple DMS (Document Management System).
I have resolved how to create new entries and update the database with all the document properties, including its original path.
Now, what I need to do is to create a second form where users can select some searching criteria and click the button for a custom result. The results has to be displayed in a 'user friendly' form, as much as possible, this means that additionally to the query, the user has to be able to click somewhere in the form so he can 'open' the selected document from the resulting list (using the 'original_path' property).

Initially I am trying to use a DataGridView control because it looks simple and allows me to preview the results but I dont know how to pass a new query from my search form to the 'result form' (the one that has de DataGridView control).

I am using DataGridView control but if someone tells me there is a better control or a better way to do it, I am open to acccept his or her answer if it works.

The following code is the one I am using to update the access database:

                dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
                Dim folderPath As String
                folderPath = My.Computer.FileSystem.CurrentDirectory
                Dim filePath As String
                filePath = "\main_db.accdb"
                dbSource = "Data Source=" & folderPath & filePath
                con.ConnectionString = dbProvider & dbSource
                con.Open()

                sql = "SELECT * FROM documents"
                da = New OleDb.OleDbDataAdapter(sql, con)
                da.Fill(ds, "documents")

Open in new window

Datagridview is the suitable control for this.

You can add a Where clause to your Select query to do the searching.

ASKER

ok but how should I do to display it in the ]DataGridView control on the Form2?

You can either construct the query and pass it to form2 or pass the search criteria to form2 which can then construct the query.

ASKER

Code?

I don't have your forms so this is just an example

Dim frm As New Form2
frm.Query = SqlQuery
frm.Show

*Query is a public string variable declared in Form2
*SqlQuery is a string variable in current form which has been populated with a Select and Where clause.

ASKER

The search form is called "FindDocs". This form is where the user selectes the criteria. This form has a command button that prepares (concatenates) the query to send it to the DataGriedView1 component on the results form that is called "ResultsTechnical".

I tried "ResultsTechnical.query" but it does not recognizes the property ".Query"

As I said, you will need to add a

Public Query as String

at class level in FindDocs.

ASKER

Error: "Object Reference not set to an instance of an object"

This is the code I am using:

'this is the search form
Public Class BuscaDocs
    Public query As String
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim frm As BuscaDocs
        Dim SqlQuery As String = "SELECT * FROM documents"
        frm.query = SqlQuery
        ResultsTechnical.Show()
    End Sub
End Class

Open in new window

The results should be displayed in a form with a DataGridView control called "ResultsTechnical"

Then the SqlQuery needs to be passed to ResultsTechnical and

public query as string

needs to be in ResultsTechnical.

ASKER

If I move "Public query As String" to the ResultsTechnical form is displays an error in the "BuscaDocs" form:

at line "frm.query"
"query is not a member of BuscaDocs"

This

Dim frm As BuscaDocs

also needs changing to

Dim frm As New ResultsTechincal

ASKER

ok, it appears to display the query but the result is not filtered by the query itself.
The result is the DataGridView control updated with a "SELECT * FROM table" but I am using a different query:

I have tried my query in SqlView in Access and it displays just 1 results, the one that matches the select criteria, but when I try to use it from vb.net it displays the whole table.

Public Class BuscaDocs

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim frm As New ResultsTechnical
        Dim SqlQuery As String = "SELECT new_document_number, document_title, revision_number, issue_for FROM documents WHERE area_wbs_code='3155';"
        frm.query = SqlQuery
        ResultsTechnical.Show()
    End Sub
End Class

Open in new window

You then need to change the code in ResultsTechnical so that it uses the query variable that you passed to it to populate the grid.

ASKER

it fails to recognize 'SqlQuery'

This is ResultsTechnical

Public Class ResultsTechnical
    Public query As String
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.DocumentsTableAdapter.Fill(Me.Main_dbDataSet.documents)
        Me.DataGridView1.DataSource = sqlQuery 'here it fails
    End Sub
End Class

Open in new window

Change following

Me.DocumentsTableAdapter.Fill(Me.Main_dbDataSet.documents)
Me.DataGridView1.DataSource = sqlQuery 'here it fails

to

Me.DocumentsTableAdapter.SelectCommand.CommandText = query
Me.DocumentsTableAdapter.Fill(Me.Main_dbDataSet.documents)
Me.DataGridView1.DataSource = Main_dbDataSet.documents

ASKER

displays an error "SelectCommand is not a member of Me.DocumentsTableAdapter"

Me.DocumentsTableAdapter.SelectCommand.CommandText = query

Open in new window

It is a typed table adapter so you will need to browse through the list of properties in intellisense to find what the select command is called. I suspect it is called DocumentsSelectCommand.

ASKER

These are the available ones:

.Adapter
.ClearBeforeFill
.Connection
.Container
.Delete
.Dispose
.Fill
.GetDate
.Insert
.Site
.ToString
.Transaction
.Update

And what is available in .Adapter?

ASKER

.AcceptsChangeDuringFill
.AcceptsChangeDuringUpdate
.Container
.ContinueUpdateOnError
.DeleteCommand
.Dispose
.Fill
.FillLoadOption
.FillSchema
.InsertCommand
.MissingMappingAction
.MissingSchemaAction
.ReturnProviderSpecificType
.SelectCommand
.Site
.TableMappings
.ToString
.Update
.UpdateBatchSize
.UpdateCommand

So it has the selectcommand so this

Me.DocumentsTableAdapter.SelectCommand.CommandText = query

becomes

Me.DocumentsTableAdapter.Adapter.SelectCommand.CommandText = query

ASKER

it runs but it displays nothing, probably because 'query' has no value

Public Class ResultsTechnical
    Public query As String
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.DocumentsTableAdapter.Adapter.SelectCommand.CommandText = query
        Me.DocumentsTableAdapter.Fill(Me.Main_dbDataSet.documents)
        Me.DataGridView1.DataSource = Main_dbDataSet.documents
    End Sub
End Class

Open in new window

You are passing it a value in your other code aren't you? To test it, you can remove the Where clause from query

Public Class BuscaDocs

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim frm As New ResultsTechnical
        Dim SqlQuery As String = "SELECT new_document_number, document_title, revision_number, issue_for FROM documents"
        frm.query = SqlQuery
        ResultsTechnical.Show()
    End Sub
End Class

Open in new window

ASKER

Nothing happens, same result, the DataGridView control still displays empty :(

Ok try following code

Public Class ResultsTechnical
Public query As String
Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim dbadp As New SqlClient.SqlDataAdapter (query, "your connection string here")
Dim dTable As New DataTable
dbadp.Fill(dTable)
Me.DataGridView1.DataSource = dTable
End Sub
End Class

ASKER

Nothing happens.

Show relevant code from both forms.

ASKER

Public Class BuscaDocs

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim frm As New ResultsTechnical
        Dim SqlQuery As String = "SELECT new_document_number, document_title, revision_number, issue_for FROM documents WHERE area_wbs_code='3155';"
        frm.query = SqlQuery
        ResultsTechnical.Show()
    End Sub
End Class

Open in new window

Public Class ResultsTechnical
    Public query As String
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dbadp As New SqlClient.SqlDataAdapter(query, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ogarin\Documents\Visual Studio 2012\Projects\DCS_vbnet\DCS_vbnet\bin\Debug\main_db.accdb")
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        Me.DataGridView1.DataSource = dTable
    End Sub
End Class

Open in new window

In first code section, replace ResultsTechnical.Show()

With

frm.Show()

ASKER

same, no changes... but I have a suspect...

I am not seeing the value asigned to 'query' in ResultsTechnical form:

Public Class ResultsTechnical
    Public query As String 'we define 'query' variable but where we assign a value?
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dbadp As New SqlClient.SqlDataAdapter(query, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ogarin\Documents\Visual Studio 2012\Projects\DCS_vbnet\DCS_vbnet\bin\Debug\main_db.accdb")
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        Me.DataGridView1.DataSource = dTable
    End Sub
End Class

Open in new window

>we define 'query' variable but where we assign a value?

We do it here

Dim frm As New ResultsTechnical
Dim SqlQuery As String = "SELECT new_document_number, document_title, revision_number, issue_for FROM documents WHERE area_wbs_code='3155';"
frm.query = SqlQuery
frm.Show()

ASKER

mmm true... any other idea to test?

Show the full (relevant) code again.

ASKER

I have a form that helps me to search for document numbers in an access 2007 database.
The data exists in the access database.
The form currently uses the following code to execute the search (provided by you):

Public Class BuscaDocs

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        Dim frm As New ResultsTechnical
        Dim SqlQuery As String = "SELECT new_document_number, document_title, revision_number, issue_for FROM documents WHERE area_wbs_code='3152';"
        frm.query = SqlQuery
        frm.Show()
    End Sub
End Class

Open in new window

The results have to be displayed in a form that contains a dataGridView control.
This control is the unique control in the form. This control was configured using the DataGridView wizard (created the string connection and the initial query SELECT * FROM documents).

The form that contains the control is called 'ResultsTechnical' and this this is the form code:

Public Class ResultsTechnical
    Public query As String
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dbadp As New SqlClient.SqlDataAdapter(query, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ogarin\Documents\Visual Studio 2012\Projects\DCS_vbnet\DCS_vbnet\bin\Debug\main_db.accdb")
        Dim dTable As New DataTable
        dbadp.Fill(dTable)
        Me.DataGridView1.DataSource = dTable
    End Sub
End Class

Open in new window

You code seems fine and should work. As a test, you can add following line at the end of above Load method

msgbox "Rows: " & dTable.Rows.Count

Is the grid on form called DataGridView1? Does it show column headers or is it totally blank?

ASKER

mmm when I added the msgbox line and run nothing happened... very weird... When I traced it what I saw was that when loading the ResultsTechnical form (the one that have the DataGridView1 control) it read the first 3 lines (until first Dim) as displayed above then at the end of line 3 it went to the "BuscaDocs" form, wtihout runing the rest of the ResultsTechnical form and run the frm.Show() from that form :(

I have attached an image for your referrence.
trace.png

Enclose the code in resultstechnical load event in Try...Catch and see if it is throwing any exceptions.

ASKER

it is in the ResultsTechnical load event.
I modified the code to Catch any exception... nothing changes :(

Public Class ResultsTechnical
    Public query As String
    Private Sub ResultsTechnical_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim result As String
        Try
            Dim dbadp As New SqlClient.SqlDataAdapter(query, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\ogarin\Documents\Visual Studio 2012\Projects\DCS_vbnet\DCS_vbnet\bin\Debug\main_db.accdb")
            Dim dTable As New DataTable 'not runing
            dbadp.Fill(dTable) 'not runing
            Me.DataGridView1.DataSource = dTable 'not runing
            MsgBox("Rows: " & dTable.Rows.Count) 'not runing

        Catch ex As Exception
            ' display error
            result = "Generic exception caught" & ex.Message
        End Try

    End Sub
End Class

Open in new window

In the Catch section, add

msgbox "ERROR: " & ex.Message

ASKER

It now displays a message:
Error: Keyword not supported: 'provider'

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

ASKER

Perfect!

Thanks a lot, I am so happy it os now working!
God Bless you and your family.