• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Getting data into a vb.net form from Access database

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
adamtrask
Asked:
adamtrask
  • 5
  • 4
  • 2
  • +1
1 Solution
 
rbgCODECommented:
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
 
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)
0
 
adamtraskAuthor Commented:
To tell you the truth I haven't tried it yet....
I'll do that now and get back to you
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rbgCODECommented:
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
 
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
0
 
Jini Jose.Net Team LeadCommented:
there is a spelling mistake in your code. change the nad to and before Response='A'"
0
 
adamtraskAuthor Commented:
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
 
Jini Jose.Net Team LeadCommented:
check the below link. you will get the basic idea

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


0
 
Jini Jose.Net Team LeadCommented:
you can get the values as below

rs.Fields(0).Value


0
 
adamtraskAuthor Commented:
I get the following error:

Fields is not a member of system.Data.Dataset
0
 
Jini Jose.Net Team LeadCommented:
sorry I posted the vb6 codes.
0
 
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)
con.Open()
Number =comm.ExecuteScalar
con.close()
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now