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

 

[vis781]

If you are using .Net 2003 can you change your primary platform to reflect that please. Is this the whole code you are using? Where is the SELECT query? I'm a bit concerned about that parameter too, but we can get down to it when we see the rest of the code.

 

[ckeezer]

VIS,
The select query is in the data adapter. I am still learning this stuff, so be gentle.

I think that I may be taking the wrong approach to trying to get this to work. What I am thinking, based on your question, is that I need to add a select statement to the btn_click function instead of on the data adapter. Am I on the right track with this???!

I do not have the project with me, as I am at work right now.

I do not want for anyone to just give me the code, that teaches me nothing, except how to copy and paste.

 

[vis781]

Good for you , and you are right you will learn nothing from copying and pasting. Ok we'll start with the error you are getting. Can you post that, and i'll try to give you and idea of what it means.

 

[vis781]

It's probably easier if you just post the code you are using and i'll take a look, or you can post the whole project it's up to you. To upload files click thre 'Manage Attachments' button and you can upload it form there. Please do not include the Bin and debug folders, just the project.

 

[ckeezer]

do you want just the .exe or all of the code except the bin and obj folders? Just making sure I dont give you more than you want.

 

[ckeezer]

here is the code that I am using...

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

that is it... I think that I am missing a bunch of stuff. I will attach a zip to my next post with the whole project so you can run through it.

 

[ckeezer]

whole project

 

[vis781]

Ok i've had quick look. This was produced by the dataform wizard? Have you modified the SELECT query by any chance? Currently It looks like this....

Me.OleDbSelectCommand1.CommandText = "SELECT CompanyName, ContactFirstName, ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = '*editCompanyName') AND (Contact" & _
"FirstName = '*obcContactFirstName') AND (ContactLastName = '*obcContactLastName'" & _
") AND (PhoneNumber = '*obcPhoneNumber') AND (EmailAddress = '*obcEmailAddress')"

So when you click the button it will do a search on all the values in the the textboxes on your form. (Actually it will throw an exception, which is what you are getting) I'd advise at this point to have a go at writing your own SQL SELECT statement. This is why i don't like using the wizard, it can go way too far, also if you write it yourself you know whats going on under the hood. So here's a pointer in the right direction. For button 1 the SQL will want to look a bit like this....

Me.OleDbSelectCommand1.CommandText = "SELECT CompanyName, ContactFirstName, ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = ?)"

Ok so i've removed most of the original SQL and added a ? this will be the placeholder for the parameter we are going to add. So have a go at adding a parameter to the select commands parameter collection. Try it out, and if you are still having trouble post back and we'll take another look.

 

[ckeezer]

here is a little different question... as I try what you have already given me. When I go to deploy this app on the new computers, I am going to copy the .mdb file to a share drive, this will generate errors when trying to open the forms. Will I have to rewrite the data connectors, adapters and datasets when I do this?

 

[vis781]

You'll need to provide a way to change the connection string, but the datsets/dataadapter will remain the same. If you move the .mdb file the only thing you are going to need to alter is the data source part of the connection string. You just need to decide where to store it.

 

[ckeezer]

one more off the topic question....
how do I get code to stop if an error is detected?
I have a procedure that checks through every field to make sure that it is formatted right, and throws errors if it is not.
problem:
when I apply that procedure to my navigational menu items, it throws the code but still goes to the next, last, previous or first record in the db.

thanks.

 

[ckeezer]

here is what I put in for the procedure:

Dim customer AsNew frmCommercialCustomers
customer.Show()
OleDbConnection1.Open()
Me.OleDbSelectCommand1.CommandText = "SELECT CompanyName, ContactFirstName, ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = editCompanyName)"
Me.OleDbDataAdapter1.Fill(DsCustomer.Customers)
OleDbConnection1.Close()
IfMe.DsCustomer.Customers.Rows.Count = 0 Then
MessageBox.Show("No data found", "Error")
customer.Hide()
EndIf

attached is the error that I got.

The problem I think I am having is identifing the Where clause. I want (ComapnyName = "MyForm.TxtBox1") right? or is the syntax wrong?
Thanks,
Chuck

 

[ckeezer]

I have changed things around a little. I want to eliminate the search form, and have the users search on the current form. I set it to not load when the form is opened. This leaves me the option to search for a customer or enter a new one.
Problem:
I am still getting the same oledb errors as above. here is the new code:

PrivateSub frmCommercialCustomers_mnuSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles frmCommercialCustomers_mnuSearch.Click
If frmCommercialCUstomers_txtCompanyName.Text.Length < 1 Then
MessageBox.Show("Please enter a Company Name to search for", "Error")
Else
Me.OleDbConnection1.Open()
Me.OleDbSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactTitle, " & _
" ContactFirstName, ContactLastName, BillingAddress, City, StateOrProvince, PostalCode, Country/Region, " & _
" PhoneNumber, FaxNumber, EmailAddress, FROM Customers WHERE (CompanyName = frmCommercialCUstomers_txtCompanyName)"
Me.OleDbDataAdapter1.Fill(dsOrdersByCustomer.Customers)
Me.OleDbDataAdapter2.Fill(dsOrdersByCustomer.Orders)
Me.OleDbConnection1.Close()
IfMe.dsOrdersByCustomer.Customers.Rows.Count = 0 Then
MessageBox.Show("No data found", "Error")
EndIf
EndIf
EndSub

 

[vis781]

A little earlier on in this thread i posted a SQL statement that will help you out. Did you see it?...

Dim MySelectCommand as new OleDbCommand("SELECT CompanyName, ContactFirstName, 
ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = [B][I][U][COLOR=blue]?[/COLOR][/U][/I][/B])")

Notice the question mark, that is how you add a placeholder for a parameter.

Then you will add the parameters to the collection....

MySelectCommand.Parameters.Add("@CompanyName"),OleDbType.VarWChar,255,"CompanyName").Value = me.Textbox1.Text

With me?