Searching access

C

ckeezer

Well-known member
Joined
Jan 16, 2006
Messages
100
Programming Experience
1-3
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:
VB.NET: 
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
 
Last edited by a moderator:
Sort by date Sort by votes
You lost me a little on that one... guess I should stop using those darn wizards so much..

So the whole code will look something like:
VB.NET: 
Dim MySelectCommand as new OleDbCommand("SELECT CompanyName, ContactFirstName, 
ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = [B][I][U][COLOR=#0000ff]frmMain.txtbx1.txt[/COLOR][/U][/I][/B])")
 
MySelectCommand.Parameters.Add("@CompanyName"),OleDbType.VarWChar,255,"CompanyName").Value = me.Textbox1.Text
MySelectCommand.Parameters.Add("@ContactFirstName"),OleDbType.VarWChar,255,"ContactFirstName").Value = me.Textbox2.Text
MySelectCommand.Parameters.Add("@ContactLastName"),OleDbType.VarWChar,255,"ContactLastName").Value = me.Textbox3.Text
AND SO ON

Is that even close? Does not look right but then again, I have only written one of these statements since I have been using .net

By the way, you have been a wonderful help to me. I owe you big time.
 
Upvote 0 Downvote
No, but don't worry. Writing your own parameterised queries can be very confusing if you haven't done it before. This is how your SELECT statement should look....

VB.NET: 
Dim MySelectCommand as new OleDbCommand("SELECT CompanyName, ContactFirstName, 
ContactLastName, PhoneNumber, EmailAddress," & _
" CustomerID FROM Customers WHERE (CompanyName = ?)")

Exactly like that.

Then you add a parameter. This is what i'm trying to get accross to you. When you want to input a parameter you put a question mark. For example..

VB.NET: 
SELECT Myid FROM MyTable WHERE Myid = ?

So when we want to add a parameter to your select command, we do so by the following..

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


Then when that SELECT command executes the question mark that we have put in will be replaced by the current value of TextBox1 because that is what we have supplied in the parameter above. We only need one parameter because there is only one question mark in our SELECT statement, and that parameter has the value of TextBox1.Text because we have specified it here...

VB.NET: 
MySelectCommand.Parameters.Add("@CompanyName"),OleDbType.VarWChar,255,"CompanyName[B][COLOR=blue]").Value = me.Textbox1.Text[/COLOR][/B]

Is this getting any clearer?
 
Upvote 0 Downvote
No one ever said I was quick!!!:D

I have seen the light. I will give this a shot this evening when I get home and have a chance to mess with it. Thanks again for the help. I will post if I still hitting the wall (with my head)!

Chuck
 
Upvote 0 Downvote
I keep getting an "End of Statement Expected" error in the following code:
VB.NET: 
[SIZE=2][COLOR=#0000ff]
Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand1.Parameters.Add(("@CompanyName"), System.Data.OleDb.OleDbType.VarWChar, 255, "CompanyName").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].frmCommercialCUstomers_txtCompanyName.Text)
[/SIZE]
 
Upvote 0 Downvote
Got it... no need for the .value after ("CompanyName")
VB.NET: 
[COLOR=#0000ff]Me[/COLOR][SIZE=2].OleDbSelectCommand1.Parameters.Add(("@CompanyName"), " & _ System.Data.OleDb.OleDbType.VarWChar, 255, ("CompanyName") = [/SIZE][SIZE=2][COLOR=#0000ff]" & _ [/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].frmCommercialCUstomers_txtCompanyName.Text)
[/SIZE]

I am getting there. Thank you for helping me out. I am sure that I will have lots more questions... maybe in just a few minutes.

Chuck
 
Upvote 0 Downvote
Yes there is a need for the .value you just put some brackets in the wrong place....

VB.NET: 
Me.OleDbSelectCommand1.Parameters.Add("@CompanyName", 
System.Data.OleDb.OleDbType.VarWChar, 255, "CompanyName").Value = 
Me.frmCommercialCUstomers_txtCompanyName.Text
 
Upvote 0 Downvote
Did I mess something up? I am getting oledb errors again:

VB.NET: 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnSearchCommCust_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnSearchCommCust.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] err [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.ComponentModel.CancelEventArgs
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] frmCommercialCUstomers_txtCompanyName.Text.Length < 1 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]MessageBox.Show("Cannot leave Company Name blank")
err.Cancel = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbConnection1.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MySelectCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommand("SELECT CompanyName, ContactFirstName, ContactLastName, PhoneNumber, EmailAddress, CustomerID FROM Customers WHERE (CompanyName = ?)")
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand1.Parameters.Add("@CompanyName", System.Data.OleDb.OleDbType.VarWChar, 255, "CompanyName").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].frmCommercialCUstomers_txtCompanyName.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbDataAdapter1.Fill(DsOrdersCustomers1.Customers)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbDataAdapter2.Fill(DsOrdersCustomers1.Orders)
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbConnection1.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsOrdersCustomers1.Customers.Rows.Count = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]MessageBox.Show("No data found", "Error")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].btnSearchCommCust.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE]
 
Upvote 0 Downvote
I got rid of the error, seems I forgot that I changed connection string when I took it to work. Changed it back, but now I dont get any errors or data on the form.
 
Upvote 0 Downvote
how do I populate the datagrid with the proper records, if there are any?

I thought that is what the:
Me.OleDbDataAdapter2.Fill(DsOrdersCustomers1.Orders)

was supposed to do?

 
Upvote 0 Downvote
Can you, or anyone else, take a look at the attached project and try and help me with why I can no longer get the search to work. The form is CCustomer.

I do not want to load the form unitl something happens.. like the user searching, adding a new record, click on next, etc. Not sure how I get it to do that.
 

Attachments

  • Project.zip
    518.1 KB · Views: 20
Upvote 0 Downvote
ckeezer said:
how do I populate the datagrid with the proper records, if there are any?

I thought that is what the:
Me.OleDbDataAdapter2.Fill(DsOrdersCustomers1.Orders)

was supposed to do?
Click to expand...

No that code will fill a datatable, with the info relative the to SELECT query you have provided it with. If you want to see those reords in a datagrid then..

VB.NET: 
Me.DataGrid1.DataSource = YourDatatable

As for the rest, i think you are still struggling with the concepts of ADO.Net, there's no shame in that, but so you get a better understanding i will create a demo application/tutorial tonight and post it on the forums.

In the mean time have a good read of these

http://www.devcity.net/PrintArticle.aspx?ArticleID=215
http://www.devcity.net/Articles/240/1/article.aspx

In that second link there is five pages of info.
 
Upvote 0 Downvote
Walking through the code I keep getting the following error:
"An unhandled exception of type 'System.Data.OleDbException'
occurred in System.Data.dll" it is being thrown for command.ExecuteNonQuery()

Here is the code... it is exactly what the author wrote so I am a little confused as to why this is happening:

VB.NET: 
[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.OleDb[/SIZE]
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] ManageData[/SIZE]
[SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Form[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] _contactID [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int32 = 0[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE][SIZE=2] ContactID() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int32[/SIZE]
[SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Return[/COLOR][/SIZE][SIZE=2] _contactID[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Get[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Set[/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Value [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Int32)[/SIZE]
[SIZE=2]_contactID = Value[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Set[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Property[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btnSave_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btnSave.Click[/SIZE]
[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 & "\AddressBook.mdb")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2].Empty[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] _contactID = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2]sql = "INSERT INTO Contacts(Title, FirstName, LastName) & VALUES (" & txtTitle.Text & "," & txtFirstName.Text & "," & txtLastName.Text & ")"[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(sql, conn)[/SIZE]
[SIZE=2][COLOR=red]command.ExecuteNonQuery()[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]
 
Upvote 0 Downvote
Your problem is here...

VB.NET: 
sql = "INSERT INTO Contacts(Title, FirstName, LastName) [COLOR=red]& [/COLOR]VALUES 
(" & txtTitle.Text & "," & txtFirstName.Text & "," & txtLastName.Text & ")"

The & i've highlighted. That shouldn't be there, probably a typo in the
original article. It should look like this...

VB.NET: 
sql = "INSERT INTO Contacts(Title, FirstName, LastName) VALUES 
(" & txtTitle.Text & "," & txtFirstName.Text & "," & txtLastName.Text & ")"

Also, didn't realise at the time but the author of that article hasn't used parameters. Whoever the author is, was a bit naughty there.
 
Upvote 0 Downvote

Similar threads

luca89
  • Question
Question Unable to populate RDLC from DataSet
Replies
3
Views
475
luca89
luca89
RobertAlanGustafsonII
  • Solved
Question How do I host an arbitrary custom control in a DataGridView cell?
Replies
6
Views
834
jdelano
J
S
  • Question
Resolved Can't get ReportViewer working
Replies
1
Views
1K
zepter47
Z
M
  • Question
Function in class library throwing run-time error '91'
Replies
1
Views
2K
jmcilhinney
jmcilhinney
J
  • Question
Importing Excel spreadsheet into DataGridView and completing search
Replies
1
Views
2K
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top