How to check if Row exists in MS Acess for validation

Question

0.00/5 (No votes)

See more:

Can anyone kindly help me with my problem:

Through the use of VB.NET i would like to know if a row in ms access exists or not. If it does exist then it should set the variable pass = true else should be false.

Im doing a school application program and what user needs to select is the year they wish to start now the database has the previous 10 years etc and in October every year it adds the new year to accept application for the next year.

but the problem i have is if someone selects to start for e.g 2013 before october i needed the program to check the database to see if the row of 2013 (row 12) exits if it does then accept application if row 12 (2013) does not exist give error message.

How can i achieve this??

I just need to know how to check the row information..
Please can someone kindly help

Help needed urgently!!!

Thanks

code used to get data:

VB

 sql = "SELECT * FROM App"
   
        

             Dim dsapp As New DataSet("App")
                Dim daapp As New OleDb.OleDbDataAdapter

                daapp = New OleDb.OleDbDataAdapter(sql, cnn)
                daapp.Fill(dsapp, "app")

if x = 2010 then
curyear=0
Elseif x=2011 then
curyear=1
else if x=2012 then
curyear =2

        Try
 Dim tblapp As DataTable
                    tblapp = dsapp.Tables("App")

           cnn.open
            textbox1.text = dsapp.tables("app").rows(curyear).item(1)
textbox2.text= dsapp.tables("app").rows(curyear).item(2)
textbox3.text= dsapp.tables("app").rows(curyear).item(3)


        Catch ex As Exception
            MsgBox(ex.ToString)

        End Try

      

   

    End Function

Posted 4-Apr-12 14:30pm

alom_93

Updated 10-Apr-12 15:17pm

v2

Add a Solution

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)

Kschuler · Answer 1 · 2012-04-05T04:05:00

Solution 1

What code have you tried? You seem to already have the basic concept down. What you need to do is query the database, then check if any rows returned. It's that simple. Your SQL would look something like this:

SELECT year FROM table WHERE year=2013

You could use this to fill a table and then check if the table has any rows (dtTable.Count = 0). Or you could use this with an execute scalar and see if it equals DBNull.Value or Nothing which would mean it wasn't found in the database.

Or you could use the SQL function COUNT() like this:
SELECT COUNT(*) FROM table WHERE year=2013
and execute scalar. Then you'd just have to check if it was zero.

********* UPDATE *********

Now that you've posted your SQL code, the above will only work if you change your sql line to something like this:

VB

sql = "SELECT * FROM App WHERE yearColumn=" & x

Where the "yearColumn" is whatever name your App table has for the column that contains the year.

If you want to continue to use your old sql line, that's fine...you'll just want to search it for the year in question, which you appear to be storing in the variable named x. (I'd recommend changing that variable name to something more meanningful, such as intYear if it's an integer value). To do that, you can simply do a .Select() on the table after you have filled it....it would look something like this:

VB

If tblapp.Select("yearColumn=" & x).Count > 0 Then
   'There is at least one record with the year we are looking for
End If

Posted 5-Apr-12 4:05am

Kschuler

Updated 11-Apr-12 3:30am

v3

Comments

alom_93 5-Apr-12 13:26pm

i have tried to execute the code to see if the row exists by using IF function and making = nothing so if the row doesnt exist the error message occurs.. But the problem i seem to face is that for e.g system accepts 2012 and accepts the application, the next user puts 2013 it skips the if function and then later throws a run time error.

Also you said try this SQL: "SELECT year FROM table WHERE year=2013" but the year could be 2014 or 2015 how do i edit this.

Could you explain the last one using the count? didnt fully understand that one

Kschuler 5-Apr-12 13:39pm

It would be a lot easier to help you if you post the code you are using and tell us where you encounter the error and the exact error message. Click the Improve Question link and add that info to your question.

As for year, you can just replace 2013 with a variable that holds which ever year the user selected. And the COUNT() function is an SQL function that will return a count of all the records that you are looking for. So if it returns zero you know there aren't any records that match the WHERE statement. Otherwise it will tell you how many records there are for the year you put in the WHERE. There are a lot of things you can do with SQL...if you are interested you may want to research it more. I would suggest starting here: http://www.w3schools.com/sql/sql_intro.asp

alom_93 5-Apr-12 18:33pm

i would have posted the code but it wasnt working and i deleted it... But i set up the data adapter and dataset and then put if function to see if the row exists or not. Ill try to put the code back up.

Thanks for the link

alom_93 5-Apr-12 19:22pm

here is the code i was using:

Try
sql = ("SELECT * FROM Application")

Dim dsx As New DataSet("app")
Dim dax As New OleDb.OleDbDataAdapter

dax = New OleDb.OleDbDataAdapter(sql, con)
dax.Fill(dsx, "app")

Dim tblapplications As DataTable
tblapplication = dsx.Tables("app")

If dsx.Tables("app").Rows(year).Item(0) = nothing Then

msgbox("Year does not exist!")
else
call add()

End If

Catch ex As Exception
MsgBox(ex.ToString)

End Try

End Sub

i have tried to use what u have said with the dbnull but i get blue squiggly lines.. Also if i set the SQL like this "SELECT year FROM App WHERE year=cb.text"
i also get an error saying no values given for parameters

Kschuler 6-Apr-12 9:10am

Instead of this:
If dsx.Tables("app").Rows(year).Item(0) = nothing Then

Try
If dsx.Tables("app").Rows.Count = 0 Then

alom_93 6-Apr-12 19:54pm

Nope doesnt work just continues as normal and that is expected because there is already 3 rows in the table.. And your saying if =0 then write message but there is 3 rows already..

Could you show me how i can use the SQL count function or which ever one will help me to achieve what im after.

Thanks

Kschuler 9-Apr-12 9:16am

What SQL are you currently using? And what data is coming back? I thought you said there ISN'T anything for 2013...so what data are you getting?

alom_93 9-Apr-12 19:16pm

the code u suggested is in logically right because it counts how many rows there are and there are 3... but in the code ur saying if it = 0 then do something but it does not = 0 it =3..

I need something to check if that row exists.

What do u mean what SQL am i using, im using MS access?

thanks

Kschuler 10-Apr-12 9:13am

I don't understand why there are three rows returning because I don't understand how you are pulling your data. Can you please post that code so I can understand? I'm obviously imagining something different in my head than what you are actually doing.

alom_93 10-Apr-12 21:10pm

In the table app there are 3 rows 2010,2011, 2012... Now your saying if the rows.count=0 then do something BUT it doesnt = 0 as it has 2010,2011,2012 (3 rows) and not 0..

The code im using to get data is displayed now added to the orginal post

Kschuler 11-Apr-12 9:30am

I was assuming that your sql included a WHERE clause that tried to get only the year in question, in this case 2013. I have updated my solution with some more information.

alom_93 11-Apr-12 20:37pm

ok thanks, got it working.... Put it on the basis of what u provided and changed it around and now working..