help needed searching ms access database using vb.net

Question

0.00/5 (No votes)

See more:

i'm creating a stock control system and i am at a stage where i need helping searching the database to return the data i want. For e.g i have a form called check stock now in this for i wish to enter the product id and hit search, and when i press search it should check against the ms access database if the data exists if not return an error message.

So far i have tried this but what happens is that i enter the correct product id it displays "data not found" but still displays the data, but incorrect product id says not found which is what it is meant to do.

Please can someone help me fix the error i get

Here is my code:

VB

Dim b As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & fn
        If txtsearch.Text = "" Then
            MsgBox("Incorrect!", MsgBoxStyle.Critical)
        Else

            Dim a As New OleDb.OleDbConnection(b)
            a.Open()
            sql = ("SELECT[Product ID], [Product Name],[Qunatity in stock], [Quantity Ordered], [Date of order], [Supplier ID] FROM STOCK")


            Dim dscheckstock As New DataSet("Stock")
            Dim dacheckstock As New OleDb.OleDbDataAdapter

            dacheckstock = New OleDb.OleDbDataAdapter(sql, a)
            dacheckstock.Fill(dsstock, "stock")

            For index = 0 To dscheckstock.Tables("Stock").Rows.Count - 1
                Dim tblstock As DataTable
                tblstock = dscheckstock.Tables("Stock")


  If txtsearch.Text = dscheckstock.Tables("stock").Rows(index).Item("Product ID") Or _txtsearch.Text.Tolower = dscheckstock.Tables("stock").Rows(index).Item("Product Name") Then
                    txtsearch.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product ID")
                    TextBox2.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product Name")
                    TextBox3.Text = dscheckstock.Tables("Stock").Rows(index).Item("Qunatity in stock")
                    TextBox4.Text = dscheckstock.Tables("Stock").Rows(index).Item("Quantity Ordered")
                    TextBox5.Text = dsstock.Tables("Stock").Rows(index).Item("Date Of Order")
                    TextBox6.Text = (dscheckstock.Tables("Stock").Rows(index).Item("Supplier ID"))

                ElseIf index = dscheckstock.Tables("stock").Rows.Count - 1 Then


                    MsgBox("Not found!"  & "" & vbNewLine & Me.txtsearch.Text, MsgBoxStyle.Critical)

                End If

            Next

Thanks,

Posted 17-Feb-12 12:27pm

alom_93

Updated 18-Feb-12 0:27am

CRDave1988

v2

Add a Solution

Comments

ledtech3 18-Feb-12 1:03am

your code does not contain the words "data not found" so I would think it is returning from the database. Verify your Select Statement items are Spelled correctly and all of the names are actually in the data base.
And if you are wanting to just check if a Product ID exist then cut down what you are searching for.
("Qunatity in stock")is misspelled , that could possibly raise the "data not found" error.

alom_93 18-Feb-12 18:26pm

thanks for your reply, the actual error i get is the line of code Not Found!
but the product ID that do exist i get the same error message which is not meant to happen.
The aim is i enter the product ID and the press search and all data is displayed on the product ID so cant really cut down, also i know quantity is misspelled but thats how it is on the actual database its self.

Any other suggestions why i get the error?

ledtech3 18-Feb-12 18:47pm

Looking at your code again i see that you are checking for either the product ID or the product name , if they exist then return all of the info.
the line at the bottom
"ElseIf index = dscheckstock.Tables("stock").Rows.Count - 1 Then"
you might try
"ElseIf index = Nothing or -1 or < 0 Then" something like that.

alom_93 18-Feb-12 19:39pm

i get an error if i try this
"ElseIf index = Nothing or -1 or < 0 Then"

it dont allow the <0

ledtech3 18-Feb-12 19:18pm

I also just noticed that you are using your input text box as an output text box also. you may consider keeping them as seperate textboxes to avoid any problems there.So you would need to add anonther text box.

alom_93 18-Feb-12 19:35pm

how can i achieve this? im a little bit confused

ledtech3 18-Feb-12 19:36pm

I was trying to think of how to word this. What this line of code is saying is.
"ElseIf index = dscheckstock.Tables("stock").Rows.Count - 1 Then"
No matter what the index count is then return the error.

alom_93 18-Feb-12 19:41pm

how can i fix this? where is the problem

ledtech3 18-Feb-12 19:45pm

trty this.
ElseIf index < 0 then

ledtech3 18-Feb-12 19:53pm

If you was to use all of the examples i showed then, I should have listed it as .
ElseIf index = nothing or index = 0 or index < 0 Then
but the "index < 0" (Zero) should filter out anything that exist and not throw the error.

alom_93 18-Feb-12 19:57pm

thanks,
now what is happening is that if i enter the first product id from the db it displays the data without any errors BUT those after the first one says not found and still displays the data..
what am i doing wrong?

ledtech3 18-Feb-12 19:57pm

you can add a temp. text box to return the index count, that could shed a little light on why it is not working.

alom_93 18-Feb-12 19:58pm

what do you mean? how will it function?
can you kindly explain.

Thanks for all help so far

ledtech3 18-Feb-12 20:05pm

all you have to do is add another textbox to your form and name it what ever you want,I'll use indexCount here then just above the error code add a line
indexCount.text = index.count.tostring
that will give us an idea what the index count is , or how many items it found.
or you can set a break point and step thru the code.
I will try and recreate your code here and see if i can figure out where it goes wrong.

alom_93 18-Feb-12 20:30pm

i did and when the program run this is what appears in the new textbox:
System.Windows.Forms.TextBox, Text:

ledtech3 18-Feb-12 20:24pm

What version of visual studio are you using? Or are you using a different IDE to build the code ?

alom_93 18-Feb-12 20:31pm

i'm using Visual Basic 2008 express edition

ledtech3 18-Feb-12 20:37pm

Ok i'm trying to resolve several errors that popped up as soon as i copied your code into a new Forms application.
I'm using visual studio 2008 Standard.will take a bit to recreate your app.

alom_93 18-Feb-12 20:42pm

ok thanks,

ledtech3 18-Feb-12 21:00pm

look at this line here.
tbDateOfOrder.Text = dsstock.Tables("Stock").Rows(index).Item("Date Of Order")
change dsstock.Tables to dscheckstock.Tables and see what that does

alom_93 18-Feb-12 21:09pm

no change still the same

ledtech3 18-Feb-12 21:17pm

is part of the code missing, dsstock is not declared in my test and the "Sql = " comes up with the blue squiggleys under it and says it is a namespace and cannot be used as a expression. Just wondering how you are returning anything like that?
what do the sql and dsstock represent , just so i'm clear.

ledtech3 19-Feb-12 0:56am

Ok i got it to work, I made several changes, and added some comments.
you can download my test solution at
https://skydrive.live.com/?cid=9c6526ae56cfdfa8#cid=9C6526AE56CFDFA8&id=9C6526AE56CFDFA8%21116
File name is MSDatabaseCodeProjectSample.zip , the test database is also included in the solution, you can use the code in the solution to get idea on how to get yours to work.Let me know if there are any problems or something I did isn't clear.

alom_93 19-Feb-12 11:42am

thanks for all your help i will have a look..

in my code i got squiggly line for dsstock but then i realised i forgot to write check in front of it and the sql was set up as a variable.

anyway will have a look at your code.

ledtech3 19-Feb-12 11:49am

That code is a complete solution you can compare them side by side.
The solution works. Part of the problem of the error message may have been the upper and lower case. See the code on how I handled that.
If you typed in Widget and the database has it as lower case then the error pops up. But not when you type it in with upper case. The sample forces both the input text and the database text to lower case before it compares them.

alom_93 19-Feb-12 18:26pm

many thanks for all your help so far but still cant solve it, for e.g in my db my forst record has product id a1 now if i enter that results appear. the second record is a2 now if i enter that the program says not found but still displays the data.

i have seen same happens on the one u created, but only on the first one, if i enter 12414 it says not found but displays data however the others are fine.

dont understand what the problem is

ledtech3 19-Feb-12 18:46pm

ok ,i see what it is doing now,I didn't run acroos that when i tested it, sorry about that. let me look into it more,will have to step thru the code to see why it triggers the error code.

alom_93 19-Feb-12 18:58pm

ok thanks

ledtech3 19-Feb-12 19:04pm

the reason my code sample still threw the errror is because the index value was Zero until it found a match , then it was one,so what needs to be done is to move the error handling up, first iterate thru the database and see if it finds a match, if true then continue on and load the data if the match found is false then throw the error. Does that make any sense ?

alom_93 19-Feb-12 19:20pm

but how can edit the code to say that? index =0?

ledtech3 19-Feb-12 19:33pm

no it needs to search the database for the string first, and if it is found, then return what is there.
What it is doing to me now is it will throw the error for every item that does not match. then will display the data once a match is found.

alom_93 19-Feb-12 19:37pm

i understand what your saying but how do i edit it?
thanks

ledtech3 19-Feb-12 20:23pm

My head is stuck in vb mode i'm thinking it needs a sql statement to parse the data. But i'm haveing brain lock at the moment, I tried a looping statement but it got stuck, so back to the drawing board.

ledtech3 19-Feb-12 20:58pm

Ok it finally dawned on me that the error handeling is inside of the for next loop , which means it will pop up a not found for for every row in the database it dosen't find the search string. If you wanted to do it the lazy way you can just check if an output for the ID is something or nothing after the "next"

alom_93 20-Feb-12 19:08pm

THANK YOU !!!!! for everything really thanks you have taught me few things

alom_93 21-Feb-12 11:48am

sorry to disturb you, but i am require some help from you.. Using the data i already have i now wish to update some records etc and when i press update i want the actual db to be updated aswell.. can you help me, i have been spending alot of time trying to figure it out but i cant get my head around it.

e.g from you sample db i wish to change some details of say product id 12414 and when i press update i want the actual MS access database to be updated.

Can you help me?

Thanks

ledtech3 21-Feb-12 13:31pm

A problem I just found was trying to build the search form Like in the Video using an Access Data Base Earlier Access databases do no support the newer filtering.
http://msdn.microsoft.com/en-us/library/ms187072.asp
Watch the videos then try building a new Form in the sample App you downloaded and then set that form as the start up form.
Let me know how it goes,still looking for a work around for the filter for access databases. Also what is the file extension of the database you need to end up working with ?

alom_93 21-Feb-12 13:40pm

.mdb file

ledtech3 21-Feb-12 11:53am

you should be able to do that thru the dataset.
Have you watched any of these Forms over Data, Videos Yet ?
http://msdn.microsoft.com/en-us/vstudio/bb466226.aspx?wt.slv=topsectionsee
Beth Massi does a pretty good job of explaining alot of things.

alom_93 21-Feb-12 14:03pm

thanks for the link, however none of these will help me as i wish to connect and update through coding and not through the use of wizards etc.

ledtech3 21-Feb-12 17:18pm

In order Not to use the wizards to automatically generate the components it uses.
It Would take allott more coding, which is beyond what I have tired so far.
What you could do is create a Form with the wizard then view the code behind the table adapter ,binding source etc.. So you can create and modify the code you want. Sorry, that is the only help that I can offer at the moment.

alom_93 21-Feb-12 18:13pm

ok thnaks, will have a look at this!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ledtech3 · Accepted Answer · 2012-02-19T15:49:00

This is what I have at the moment. It would not be the correct way to do this, but it does work. It will also throw a null error if a data item is returns is null. This error handeling type also depends on the textbox it is checking to be cleared each time you click the button.

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        tbDateOfOrder.Clear()
        tbProductID.Clear()
        tbProductName.Clear()
        tbQuantityOrdered.Clear()
        tbQunatityInStock.Clear()
        tbSupplierID.Clear()
        tbItemCount.Clear()
        Try
           
            Dim b As String = My.Settings.dsstockConnectionString 'this location can be edited in the settings or the app.config file
            Dim InputSearch As String = tbInput.Text
            If InputSearch = " " Or InputSearch = Nothing Then
                MsgBox("Nothing was input to search for", MsgBoxStyle.Critical, "No Input")
            Else

                Dim a As New OleDb.OleDbConnection(b)
                a.Open()

                Dim mySelect As String = ("SELECT[Product ID], [Product Name],[Qunatity in stock], [Quantity Ordered], [Date of order], [Supplier ID] FROM dsstock.STOCK")

               
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(mySelect, b)
                Dim dscheckstock As DataSet = New DataSet
                adapter.Fill(dscheckstock, "Stock")


                For index = 0 To dscheckstock.Tables("Stock").Rows.Count - 1
                    Dim tblstock As DataTable
                    tblstock = dscheckstock.Tables("Stock")

                    ' below we set out input text .tolower and we also set the product id .tolower. and the same with the product name to handle case issues.
                    If dscheckstock.Tables("stock").Rows(index).Item("Product ID").ToString.ToLower.Contains(InputSearch.ToLower) Or _
                        dscheckstock.Tables("stock").Rows(index).Item("Product Name").ToString.ToLower.Contains(InputSearch.ToLower) Then

                        tbProductID.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product ID")
                        tbProductName.Text = dscheckstock.Tables("Stock").Rows(index).Item("Product Name")
                        tbQunatityInStock.Text = dscheckstock.Tables("Stock").Rows(index).Item("Qunatity in stock")
                        tbQuantityOrdered.Text = dscheckstock.Tables("Stock").Rows(index).Item("Quantity Ordered")
                        tbDateOfOrder.Text = dscheckstock.Tables("Stock").Rows(index).Item("Date Of Order")
                        tbSupplierID.Text = (dscheckstock.Tables("Stock").Rows(index).Item("Supplier ID"))
                        'ElseIf tbProductID.Text = Nothing Then
                        '    MsgBox("Item Not found!" & "" & vbNewLine & InputSearch.ToString, MsgBoxStyle.Critical, "Item Not Found")
                    End If

                Next

                If tbProductID.Text = Nothing Then
                    MsgBox("Item Not found!" & "" & vbNewLine & InputSearch.ToString, MsgBoxStyle.Critical, "Item Not Found")
                End If

                a.Close() ' close the database
                tbItemCount.Text = dscheckstock.Tables("stock").Rows.Count 'count the number of rows
            End If

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub