getting the count value from ms access database

[keerthi]

i am getting error in this code
I have a problem in getting a count value .
my database has three fields like Selectedtime,weather,drink,medicine

part of the code:
Dim DCount As Integer
cmd.CommandText = "SELECT COUNT(*) FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"
cmd.CommandText = "SELECT Drink FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"

'execute retrieve command
' DCount = cmd.ExecuteScalar()
DCount = cmd.ExecuteScalar()
MessageBox.Show(DCount

error: conversion from string "Tea" to type "Integer" is not valied
can you say where i am wrong

complete code also i kept at the end

my database has three fields like Selectedtime,weather,drink,medicine

i am trying to find how many times the drink field is tea and selected time is morning and weather is cold

I am keeping my code here.

I am waiting for some help


Dim strPathFileName As String = "C:\myAccess\UserPreferences.mdb"
Dim strCmdTxt As String = ""

Dim CN As New System.Data.OleDb.OleDbConnection
Dim ObjDA As New System.Data.OleDb.OleDbDataAdapter
Dim InsertCmd As New System.Data.OleDb.OleDbCommand

CN.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Data Source=""" & strPathFileName.Trim & """;Mode=Share Deny None;Jet OLED" & _
"B:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Je" & _
"t OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Com" & _
"pact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Cre" & _
"ate System Database=False;Jet OLEDBon't Copy Locale on Compact=False;User ID=A" & _
"dmin;Jet OLEDB:Global Bulk Transactions=1"

strCmdTxt = "INSERT INTO UserPreferences("
strCmdTxt &= "SelectedTime,"
strCmdTxt &= "Weather,"
strCmdTxt &= "Drink,"
strCmdTxt &= "Medicine"
strCmdTxt &= ") VALUES ("
strCmdTxt &= "'" & DTiming & "',"
strCmdTxt &= "'" & DWeather & "',"
strCmdTxt &= "'" & DDrink & "',"
strCmdTxt &= "'" & DMedicine & "'"
strCmdTxt &= ")"

'strCmdTxt = "INSERT INTO UserPreferences(Time) VALUES('TESTTTTT')"

'strCmdTxt = "INSERT INTO UserPreferences(Time,Weather,Drink)VALUES('DTiming ','DWeather','DDrink','DMedicine')"
'strCmdTxt = "INSERT INTO UserPreferences(Time,Weather,Drink) VALUES('uTimimg','uWeather','uDrink','DMedicine')"

InsertCmd.CommandText = strCmdTxt
InsertCmd.Connection = CN
'InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Time", System.Data.OleDb.OleDbType.VarWChar, 30, "DTime"))
'InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Weather", System.Data.OleDb.OleDbType.VarWChar, 30, "DWeather"))

'InsertCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Drink", System.Data.OleDb.OleDbType.VarChar, 0, "DDrink"))

InsertCmd.CommandText = strCmdTxt.Trim
InsertCmd.Connection = CN
'ObjDA.SelectCommand = InsertCmd
CN.Open()
'execute insert command
InsertCmd.ExecuteNonQuery()

CN.Close()


Dim ProbCMC As Integer

Dim strPathFileName As String = "C:\myAccess\UserPreferences.mdb"
Dim strCmdTxt As String = ""

Dim CN As New System.Data.OleDb.OleDbConnection
Dim ObjDA As New System.Data.OleDb.OleDbDataAdapter
Dim InsertCmd As New System.Data.OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand
Dim count As Integer
CN.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Data Source=""" & strPathFileName.Trim & """;Mode=Share Deny None;Jet OLED" & _
"B:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Je" & _
"t OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Com" & _
"pact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Cre" & _
"ate System Database=False;Jet OLEDBon't Copy Locale on Compact=False;User ID=A" & _
"dmin;Jet OLEDB:Global Bulk Transactions=1"


'InsertCmd.Connection = CN
cmd = New OleDb.OleDbCommand
cmd.Connection = CN
'ObjDA.SelectCommand = InsertCmd

CN.Open()

Dim DCount As Integer
cmd.CommandText = "SELECT COUNT(*) FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"
cmd.CommandText = "SELECT Drink FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"

'execute retrieve command
' DCount = cmd.ExecuteScalar()
DCount = cmd.ExecuteScalar()
MessageBox.Show(DCount)

'Dim DCountMorCold As Integer
'cmd.CommandText = "SELECT COUNT(*) FROM UserPreferences where SelectedTime='Morning' and Weather='Cold'"
'cmd.CommandText = "SELECT Drink FROM UserPreferences where SelectedTime='Morning' and Weather='Cold'"

''execute retrieve command
'DCountMorCold = cmd.ExecuteScalar()
'MessageBox.Show(DCountMorCold)
CN.Close()

'ProbCMC = DCount / DCountMorCold
'MessageBox.Show(ProbCMC)

 

[jshultz]

Your problem is:

cmd.CommandText = "SELECT COUNT(*) FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"
cmd.CommandText = "SELECT Drink FROM UserPreferences where SelectedTime='morning' and Weather='cold' and Drink='tea'"

You first set it to the select statement that would give you the count, then you set it to select the drink.

Also, use code tags next time.