Searching access

[ckeezer]

I need to allow my users to search, via different text boxes on a form each with its own search button, and show the results on a different form.
I currently cannot even get it to search, I keep getting oledb errors. Here is my code:

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OleDbConnection1.Open()
Me.OleDbDataAdapter1.SelectCommand.Parameters("*editCompanyName").Value = editCompanyName.Text
Me.OleDbDataAdapter1.Fill(DsCustomer.Customers)
OleDbConnection1.Close()
IfMe.DsCustomer.Customers.Rows.Count = 0 Then
MessageBox.Show("No data found for that criteria!", "Error")
EndIf
EndSub

This is only my attempt to get it to search through access. I am using access 2003 with VS.NET 2003.

Thanks,
chuck

 

[ckeezer]

Now that works great for binding to a dg... my problem is that I am getting overloaded with code.

Starting at the begining.. I have recreated the form, added new controls and new code right now I am working with the following to populate the form:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\SmartHouse.mdb")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter("SELECT * FROM Customers", conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable("Customers")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] temp [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Binding
adapter.Fill(dt)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "CustomerID")
txtCustomerID.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "CompanyName")
txtCompanyName.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "ContactTitle")
txtTitle.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "ContactFirstName")
txtFirstName.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "ContactLastName")
txtLastName.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "BillingAddress")
txtBillingAddress.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "City")
txtCity.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "StateOrProvince")
txtState.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "PostalCode")
txtZipCode.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "Country/Region")
txtCountry_Region.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "PhoneNumber")
txtPhoneNumber.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "FaxNumber")
txtFaxNumber.DataBindings.Add(temp)
temp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Binding("Text", dt, "EmailAddress")
txtEmailAddress.DataBindings.Add(temp)

Now this does nothing with the dg at this time, beacuse I am trying to better understand this.. I have you guys and a book, and between you guys telling me how to do it and the book wanting me to use the wizards, I am getting confused. No more BOOK!!!

Now the above code works wonderfully for populating the controls.
I want to populate the dg with the corresponding records from my orders table. that should just be a matter of creating the following:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter("SELECT (OrderID, OrderDate, ShipDate) FROM Orders", conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable("Orders")
adapter2.Fill(dt2)
dgOrders.DataSource = dt2

Right?! Please tell me I am making some head way and not just banging it against the wall.
[/SIZE][/SIZE]

 

[vis781]

Ok, you have a made a great advancment in your understanding of ADO.Net, congrats!! Everything you have done there is ok because it works. I've always said that there is nothing wrong with using wizards, but it is blind programming if you have no idea about what the wizard is doing, that is what i was trying to get across to you.
If you want to retrieve the corresponding data from the orders table, i'm assuming that you mean orders with matching orderId and customer Id you will need to include a 'Where' clause in your orders select statement.

WHERE OrderId = ?

Then add a parameter to that select command to 'tell' it that you want all the orders with an orderId that matches the value you given to the parameter.

(There is a way to do this with Joins and such, but we'll leave that for another time) Don't be afraid to experiment.

 

[ckeezer]

For GP I created a different Procedure to handle the datagrid, just for testing, here it is:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] RetrieveOrders()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\SmartHouse.mdb")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter("SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE CustomerID = Customer.CustomerID", conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable("Orders")
conn.Open()
adapter2.Fill(dt2)
conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

I get the following error thrown on 'adapter2.Fill(dt2)':

 

[ckeezer]

tried to add a oledbparamater.add, but can not get it to but in the ".add" here is what I have:

Dim sel AsNew OleDbParameter("@CustomerID", OleDbType.VarWChar, 255, "CompanyName").value = txtCustomerID.Text

 

[vis781]

You are trying to select the order form the order table where the order id is the same as the customerID, so...

Dim adapter2 AsNew OleDbDataAdapter("SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE CustomerID = Customer.CustomerID", conn)

Here where you have put 'WHERE customerID = Customer.CustomerID' It should be..

WHERE OrderID = ?

Then the parameter...

SelectCommand.Parameters.Add("@OrderID", OleDbType.Integer, 0, "OrderID").value = txtCustomerID.Text

So we are trying to select all the OrderId's in the orders table where OrderID is the same as the customerID

 

[ckeezer]

on both tables I have a CustomerID field, which is the field I am trying to pull, so in the parameter should it not be :

Dim sel AsNew OleDbParameter("@CustomerID", OleDbType.Integer, 0, "CustomerID").value = txtCustomerID.Text

Wouldn't that pull the records from the Orders table only if the CustomerID matched that which was in txtCustomerID.Text?

 

[vis781]

Yep, i mis-read what your were trying to do, you are correct.

But the query should look like this...

Dim adapter2 AsNew OleDbDataAdapter("SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE CustomerID = ? ", conn)

Remember the whole question mark thing?

 

[ckeezer]

I am getting an End of Statement expected error on the following, underlinning from ".value = txtCustomerID.Text":

[SIZE=2][COLOR=#0000ff]dim[/COLOR][/SIZE][SIZE=2] sel [/SIZE][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][SIZE=2] OleDbParameter("@CustomerID", OleDbType.Integer, 0, "CustomerID").value = txtCustomerID.Text[/SIZE]
[SIZE=2]

[/SIZE]

 

[vis781]

It's because you have created a new parameter rather than adding a parameter like we did before.

SelectCommand.Parameters.add(....).value = ...

If you create a 'New' parameters as you have you will then have to add it another way..

Sel.Value = text whatever
SelectCommand.Parameters.Add(sel)

This thread is beginning to become very long, i want to help you but i can't help but think that you are just posting straight back here at the slightest hurdle. I know you could have worked out this last problem without my help, you just need to slow down and think about what the error message is saying. You've got all the ingredients you need to be able to make your program work. If your not sure what the code you are using does then read up on it at MSDN. It's very difficult to teach ADO.Net from the ground up over a forum so for the most part you need to be able to work it out for yourself. Keep trying, and post a question when you are totally stuck and myself and everyone else on this forum will be happy to help you.

 

[ckeezer]

This is getting on my nerves... I can get it to populate the dg as long as I do not set the params.. after more research here is what I am working with now:

Dim conn AsNew OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\SmartHouse.mdb")
Dim OleString AsString = "SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE (CustomerID = ?), conn)"
Dim cmdCustomerID AsNew OleDbCommand(OleString, conn)
cmdCustomerID.Parameters.Add(New OleDbParameter("@CustomerID", OleDbType.VarWChar, 255, "CustomerID")).Value = txtCompanyName.Text
conn.Open()
dgOrders.DataSource = cmdCustomerID.ExecuteReader
conn.Close()

It is still throwing an exception on the dgorders.datasource

 

[vis781]

dgOrders.DataSource = cmdCustomerID.ExecuteReader

Why are you setting the datasource to an OleDbCommand Object? Fill a datatable with the info from your select command and then set the datasource of the datagrid to the datatable.

dgOrders.DataSource = Yourdatatable

 

[ckeezer]

I knew you were getting me there. I understand it now... it only took 30 something posts, hours of surfing google and a few bangs against the wall. I was missing your point on how to fill the datatable/dataset. I understand now that I need to fill the datatable with the select command.... YEAH HE CAN BE TAUGHT....

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\SmartHouse.mdb")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] OleString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT OrderID, OrderDate, ShipDate FROM Orders WHERE (CustomerID = ?), conn)"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmdCustomerID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(OleString, conn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable(OleString)[/SIZE]
[SIZE=2]cmdCustomerID.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("@CustomerID", OleDbType.VarWChar, 255, "CustomerID")).Value = txtCompanyName.Text[/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2]dgOrders.DataSource = dt[/SIZE]
[SIZE=2]conn.Close()[/SIZE]