Stored result of Query in a Variable

Question

0.00/5 (No votes)

See more:

Hello friends
I am facing a problem on my very old project where now the client is asking for something like if there are 100 records in a table and i should be able to display the number of employees whose designation is like "supervisors" on a data report.
I am trying but can not display the number of records. I try something like this:
I am using Access database with ado.

VB

dim temp as string
temp = "select count(*) from employees where designation='" & cmbodesig.Text & "'", Con, adOpenDynamic, adLockPessimistic
DataReport1.Sections("Section2").Controls("Label5").Caption =temp

I searched a lot on the web but no results
Please help me
Thank you
Sarfaraz

Posted 21-Jun-13 17:09pm

sarfarazbhat

Updated 21-Jun-13 21:24pm

v3

Add a Solution

2 solutions

Solution 1

You define the query, but you never run it on a database or data source. You need to run the query in order to get the data. Temp is only the SQL string, you need to make a connection to the database, then create the SQL command object, and run it. This will return a data set with a single row and a single column. That column will be the number of employees.

If you tell us what kind of database you are connecting to (SQL Server, Access, MySQL, SQLite, etc), we can help you identify the connection strings and objects to run this query.

Posted 21-Jun-13 17:52pm

Ron Beyer

Comments

sarfarazbhat 22-Jun-13 3:21am

I am using Access database with ado.
i was using like

temp = "select count(*) from employees where designation='" & cmbodesig.Text & "'", Con, adOpenDynamic, adLockPessimistic

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sarfarazbhat · Accepted Answer · 2013-06-21T23:00:00

Finally solved the problem Thanks to Ron Beyer for valuable idea.

<pre lang="vb">Con.CursorLocation = adUseClient
   Set cmd = New ADODB.Command
        cmd.ActiveConnection = Con

        cmd.CommandText = &amp;amp;quot;select * from employees where Designation=&amp;amp;#39;&amp;amp;quot; &amp;amp;amp; cmbodesig.Text &amp;amp;amp; &amp;amp;quot;&amp;amp;#39;&amp;amp;quot;
        cmd.CommandType = adCmdText

        Set rs = cmd.Execute
&amp;amp;#39;rs.Open &amp;amp;quot;SELECT COUNT(*) AS Posts FROM employees WHERE  designation=&amp;amp;#39;&amp;amp;quot; &amp;amp;amp; cmbodesig.Text &amp;amp;amp; &amp;amp;quot;&amp;amp;#39;&amp;amp;quot;, Con, adOpenDynamic, adLockPessimistic
   temp = rs.RecordCount
DataReport1.Sections("Section2").Controls("Label5").Caption = temp & Me.cmbodesig.Text & "@"

Thank you