Solved

Getting data into a vb.net form from Access database

Posted on 2010-08-17
12
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 6

Expert Comment

by:rbgCODE
ID: 33458069
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33458075
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
ID: 33458078
To tell you the truth I haven't tried it yet....
I'll do that now and get back to you
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 6

Expert Comment

by:rbgCODE
ID: 33458121
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
ID: 33458441
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
ID: 33458506
there is a spelling mistake in your code. change the nad to and before Response='A'"
0
 

Author Comment

by:adamtrask
ID: 33458708
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
ID: 33458793
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
ID: 33458806
you can get the values as below

rs.Fields(0).Value


0
 

Author Comment

by:adamtrask
ID: 33458974
I get the following error:

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

Expert Comment

by:Jini Jose
ID: 33459057
sorry I posted the vb6 codes.
0
 

Accepted Solution

by:
adamtrask earned 0 total points
ID: 33461125
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 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