Solved

Getting data into a vb.net form from Access database

Posted on 2010-08-17
12
429 Views
Last Modified: 2012-05-10
Hello,

Would this code work? I am trying to count the number of "A" entered in the Response field.
the database is called Survey.
   
Dim number As Integer
        Dim con As OleDbConnection
        Dim comm As OleDbCommand
        Dim reader As OleDbDataReader
        con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Survey.accdb;")
        comm = New OleDbCommand("Select count(Response)as number from Answers where SurveyID = 1 and QuestionID =1 and Response = 'A'", con)
        ' Open Connection
        con.Open()
        ' reader read comm
        reader = comm.ExecuteReader
        ' Get from the reader the UserName and UserPasswrod
        While reader.Read
            number = reader.Item("Response")

        End While
        con.Close()
       
       
       
0
Comment
Question by:adamtrask
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 6

Expert Comment

by:rbgCODE
Comment Utility
Because you are using Count this should only return one value, and you don't need execute reader, what is the error you are getting?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
add a space before "as"

comm = New OleDbCommand("Select count(Response) as number from Answers where SurveyID = 1 and QuestionID =1 and Response = 'A'", con)

or in access vba, just not sure if it will work on .net

comm = New OleDbCommand("Select count("*") as number from Answers where SurveyID = 1 and QuestionID =1 and Response = 'A'", con)
0
 

Author Comment

by:adamtrask
Comment Utility
To tell you the truth I haven't tried it yet....
I'll do that now and get back to you
0
 
LVL 6

Expert Comment

by:rbgCODE
Comment Utility
or you can try this...

 Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\survey.accdb;Persist Security Info=False"
        Dim cmd As String = "Select Count(response) as iCount from Answers Where surveyID=1 and questionID=1 nad Response='A'"
        Dim adapter As New OleDbDataAdapter(cmd, conn)
        Dim rs As New DataSet
        adapter.Fill(rs)

rs.Count as well as rs("iCount") should tell you the total number of people who chose a.
0
 

Author Comment

by:adamtrask
Comment Utility
This the error i am getting

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect
0
 
LVL 10

Expert Comment

by:Jini Jose
Comment Utility
there is a spelling mistake in your code. change the nad to and before Response='A'"
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:adamtrask
Comment Utility
rbgCODE:

It looks like it's working. The thing is I don't know how to extract the value contained in "rs".

Is it possible to do some thing like:
dim count as integer = rs.value....???
0
 
LVL 10

Expert Comment

by:Jini Jose
Comment Utility
check the below link. you will get the basic idea

http://www.devdos.com/vb/lesson4.shtml


0
 
LVL 10

Expert Comment

by:Jini Jose
Comment Utility
you can get the values as below

rs.Fields(0).Value


0
 

Author Comment

by:adamtrask
Comment Utility
I get the following error:

Fields is not a member of system.Data.Dataset
0
 
LVL 10

Expert Comment

by:Jini Jose
Comment Utility
sorry I posted the vb6 codes.
0
 

Accepted Solution

by:
adamtrask earned 0 total points
Comment Utility
Thanks to every one who responded. I found the solution using the ExecuteScalar method which executes the SQL statement associated with the Command object and returns a single value. So in the case of the code above it would be some thing like that:

Dim Number as integer
comm = New OleDbCommand("Select count(Response)from Answers where SurveyID = 1 and QuestionID =1 and Response = 'A'", con)
con.Open()
Number =comm.ExecuteScalar
con.close()
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now