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

SQL queries using VB 2010

Although OK with VB, I have never coded anything to connect to SQL and run queries.

I have my connection already as "conn"

What I want to do is run a query and store the result in a variable.

My query is:-

SELECT SUM(timetaken) FROM dbo.ACTIONS where who='roybridge' and Whe_ >= '03/01/2011'

Open in new window


How do I do this in VB 2010?

Thanks
0
Chris Millard
Asked:
Chris Millard
  • 3
  • 3
3 Solutions
 
bitrefCommented:
        'Declare Connection
        Dim conn As New SqlConnection

        'Set connection string
        conn.ConnectionString = ""
        'Declare command and set its connection, type and SQL test (Query)
        Dim cmd As New SqlCommand
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = ""

        'Declare output string
        Dim strOutput As String

        'Open the connection, fill the output string and close the connection
        conn.Open()
        strOutput = cmd.ExecuteScalar
        conn.Close()

Open in new window

0
 
Chris MillardAuthor Commented:
When the code gets to

strOutput = cmd.ExecuteScalar

I get an error "ExecuteScalar: CommandText property has not been initialized"

0
 
bitrefCommented:
Did you insert your query in the command CommandText property?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris MillardAuthor Commented:
This is the full code:-

        Dim conn As New SqlConnection()
        conn.ConnectionString = "Data Source=RCSRV01;Initial Catalog=NetHDRedcatSQL;Persist Security Info=True;User ID=sa;Password=letmein"

        Dim cmd As New SqlCommand
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = ""

        Dim strOutput As String

        conn.Open()
        strOutput = cmd.ExecuteScalar
        conn.Close()

Open in new window

0
 
bitrefCommented:
Put your query in the command CommandText:
cmd.CommandText = "SELECT SUM(timetaken) FROM dbo.ACTIONS where who='roybridge' and Whe_ >= '03/01/2011'"

Open in new window

0
 
Chris MillardAuthor Commented:
D'oh! Stupidly I didn't see that!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now