adamtrask
asked on
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=
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()
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=
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()
rbgCODE
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?
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)
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)
ASKER
To tell you the truth I haven't tried it yet....
I'll do that now and get back to you
I'll do that now and get back to you
or you can try this...
Dim conn As String = "Provider=Microsoft.Jet.OL
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.
Dim conn As String = "Provider=Microsoft.Jet.OL
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.
ASKER
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
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect
there is a spelling mistake in your code. change the nad to and before Response='A'"
ASKER
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....???
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....???
you can get the values as below
rs.Fields(0).Value
rs.Fields(0).Value
ASKER
I get the following error:
Fields is not a member of system.Data.Dataset
Fields is not a member of system.Data.Dataset
sorry I posted the vb6 codes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.