Getting data into a form from Access database


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
        ' reader read comm
        reader = comm.ExecuteReader
        ' Get from the reader the UserName and UserPasswrod
        While reader.Read
            number = reader.Item("Response")

        End While
Who is Participating?
adamtraskAuthor Commented:
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)
Number =comm.ExecuteScalar
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?
Rey Obrero (Capricorn1)Commented:
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)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

adamtraskAuthor Commented:
To tell you the truth I haven't tried it yet....
I'll do that now and get back to you
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

rs.Count as well as rs("iCount") should tell you the total number of people who chose a.
adamtraskAuthor Commented:
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
Jini JoseSenior .Net DeveloperCommented:
there is a spelling mistake in your code. change the nad to and before Response='A'"
adamtraskAuthor Commented:

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....???
Jini JoseSenior .Net DeveloperCommented:
check the below link. you will get the basic idea

Jini JoseSenior .Net DeveloperCommented:
you can get the values as below


adamtraskAuthor Commented:
I get the following error:

Fields is not a member of system.Data.Dataset
Jini JoseSenior .Net DeveloperCommented:
sorry I posted the vb6 codes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.