Access Database simple customer lookup app.

[davidaperry69]

I am trying to write a very simple web application that can lookup a name in a simple access database that has about 500 records. I want to be able to type a name and then click on a lookup button and then the application can display the rest of the fields for that name. eg name, address, phone and email.

The access database only has four fields, name, address, phone number and email address. How do I do this. I recenlty took an ASP class online, and ASP has some built in forms like grid view that do everything anutomatically.

But that is for a web application, I need to write something in the regular Visual Studio.net. So that I can compile it and send it to other co-workers as an executable application.

I just downloaded Visual Studio and I setup a new form called form1 and I added a simple texbox for input of the name and a button called lookup.

Next I added a datagrid view box and connected it to my database to pull up the information for whichever name that is entered in the textbox. I am not sure if this is the best method, but I think I remember doing something simular when I took my VB class.

My Database is called DB1 and the table is called customers.
So now when I click on the button, I am not sure what code I need to write. Honestly I took a VB class almost 5 years ago. So forgot how to start writing the code for the button click event.

Any help will be appreciated. I wish I still had my old textbook.

 

[Geert Verhoeven]

Hi,

In the following article (http://www.akadia.com/services/dotnet_dbaccess.html) they explain how to connect to a database using .NET. The only change you need to do is in case of writing SqlCommand, SqlDataReader, SqlConnection you need to use OleDbCommand, OleDbDataReader and OleDbConnection.

Since you only have 1 record, I would advise to use the OleDbDataReader in case of a dataset.

To show the results, I wouldn't go for a datagrid but would add textboxes for the four fields. With the reader.GetString() function, you can then set the text value of the textboxes.

Greetz,

Geert

 

[cjard]

I wish I still had my old textbook.

Except, the world has moved on since then. Significantly.

Take a read of the dw2 link in my signature

 

[davidaperry69]

Help to Link a combobox to the datagrid?

Ok,

I learned how to use the fillby control. This works pretty good with the datagrid, but how would I do it if I wanted to add a combobox on the same form to query the distinct values from one field and then use whatever value is selected to query the datagrid and then show only those items that have that particualar value.

In my case I am trying to list only the names of stores that are in one region, So if I were to pick Florida on the dropdownlist of the combobox, I want the datagrid to only show the stores that are in Florida.
Where Region Like ? and ? being the combobox selected value.

I did this in ASP 2.0 very easy but visual studio is a little bit tricky, because when you click add query on the datasource it forces you to select fillby.

my Access table has field like: Store_Number, Name of Store, Location and Region_Nm

 

[cjard]

standard master detail relationship. read dw2 link in sig, (section on master/detail, displaying related data)

 

[davidaperry69]

Walkthrough: Displaying Related Data on a Form in a Windows Application

I went to
Walkthrough: Displaying Related Data on a Form in a Windows Application
they show you how to relate 2 tables, but in my case, i am only working with one table eg.

customer (table)
name
address
phone
state

I have over 500 records in the database, at least 20 are in florida.
so I want to drag the customer table onto the form and then
add a dropdown list where I can chose only the state, eg florida
and then only show me the 20 records from customer that are from florida

I thought, or would like to use a combo box, I also want my combo box to not show duplicates, eg florida 20 times, texas 10 times etc.

 

[cjard]

add query on the datasource it forces you to select fillby

mm.. yes? But that's what you want?

Ideally, you have a table with all your states in and their codes. You can then ensure noone lives in a state that doesnt exist (foreign key) and you have something to select from to fill your combo (rather than SELECT DISTINCT state FROM custoemrs)

then you put a query on the customers Table Adapter, looking like:
SELECT * FROM customers WHERE state = ?

then when the SelectedItem of the combo changes, you run that query to fill into the necessary table.

If you give me your db i'll do a sample project. I dont have any access databases with data in..

 

[AIS4U]

Here are a couple of examples that should get you what you want.

http://www.devcity.net/Articles/85/1/ssteps2_addressbook.aspx

http://www.homeandlearn.co.uk/NET/nets12p4.html

They both give pretty complete and easy to follow directions

Good Luck

 

[davidaperry69]

My database file

If you give me your db i'll do a sample project. I dont have any access databases with data in..

I have attached a zip file with my entire project. It includes the access database and it is already conected to the db.

Form 4 is the one that 1 want to fix. It works, but take a look at the drop down list. It lists the region names too many times.

Also in form 3 I wanted to do a lookup based on the first 5 or 6 characters
of a store name. in other works a wild card.. how can I do that>

Thanks in advance.

 

[cjard]

I have attached a zip file with my entire project.

Where?

Try deleting the BIN and OBJ folders, then remake the zip

 

[davidaperry69]

RE: Zip File

I removed the file.

Is there any way I can just email it to you or just one person. I did not want the file to be accessed by the whole world.

 

[Arg81]

Form 4 is the one that 1 want to fix. It works, but take a look at the drop down list. It lists the region names too many times.

Are you able to edit the database in anyway? Because you are working off 1 table, you are not using any lookup tables. Ideally you want a lookup table for your states, and then the ID value is stored in your main table.

I'm assuming you are getting regions listed multiple times because your SQL query is worded wrong. I'm again assuming that your main table has customer details and in the state field it names the states.
What you want to do is create a dataTable in your app called States, and the SQL query would be

SELECT DISTINCT stateName from Customer

^^ where stateName is your State column name (you will obviously have to rename in this statement to what your column is called) and Customer is your main table name.
DISTINCT means it will only list each region / state once in the retrieved data, even if that region / state appears multiple times in the column.

Also in form 3 I wanted to do a lookup based on the first 5 or 6 characters
of a store name. in other works a wild card.. how can I do that

This is where you use the wildcard symbol. For access I think it is *

So...you're select statement for the table would be

SELECT * FROM Customer WHERE StoreName LIKE @StoreName

On your form you then have a textbox and a button.
The code for the button_click event would be

Public Sub button1_click................
 
me.tableadapter1.fillbystorename(me.dataset1.table1, textbox1.text & "*")
 
End Sub

You would need to change the above to your settings. TableAdapter1 would be your tableadapter from the customer table, fillbystorename is the name you give your query, dataset1 is the name of your dataset and .table1 would probably be customer...

Make a note of after dataset1.table1 the , and the variable.

This takes the textbox1.text, adds a * to the end of it (so if you type flor in the texbox this makes it flor*) and then sets it to be the variable (@StoreName) for the query.

This will then fill your grid / whatever you are using to display the data with only customers that have a storename matching what is LIKE the textbox.text.

Hopefully you can make some sense out of that lot!

 

[cjard]

I removed the file.

Is there any way I can just email it to you or just one person.

That's what passworded zip files are for (and PM me the password)

can you email me direct? *******@yahoo.com

Dont put your email address on the web, it makes you a spam target

 

[Arg81]

yeah I forgot to add that to my post - don't put your email address in a post like that. A bot farmer can grab it and either send you a lot of spam, or send others a lot of spam using your address....

 

[davidaperry69]

Sample apps and database

Ok, I made up some fake data, so as not to compromise anything.

I attached the app that I created so that you can see where I am having the issues. Please let me know how you would fix forms 3 and 4 based upon the other posts in this forum

Thanks in advance.