How to write a scalar query in VB.NET from a SQL database?

Question

I am trying to write VB.NET query without writing SQL stored procs. I have already gotten many dataset text queries to work. But I am having trouble with getting this scalar query to work. All I want to do is to get the result of this T-SQL query. How can I code this successfully?

select (DateDiff(w, '1/1/' + '2011', getdate())) / 7 AS SELECTED_WEEK

(What this code does is it returns the current week of the year). And will this query above return the value as an integer datatype or string?

I tried your answers but it gave me this exception error:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed.

Do you know what this means?

score 1 · Accepted Answer · answered Jul 11 '11 at 01:03

1

Dim result As Integer
Dim sql As String = "select (DateDiff(w, '1/1/' + '2011', getdate())) / 7 AS SELECTED_WEEK"

Using cn As New SqlConnection("your connection string here"), _
      cmd As New SqlCommand(sql, cn)

    cn.Open()
    result = Convert.ToInt32(cmd.ExecuteScalar())
End Using

answered Jul 11 '11 at 01:03

Joel Coehoorn

399,467
113
570
794

Joel, can you look at my revised description above? – user371819 Jul 11 '11 at 12:17
@user Don't forget the cn.Open() call I put in there. – Joel Coehoorn Jul 11 '11 at 13:40

How to write a scalar query in VB.NET from a SQL database?

1 Answers1

Linked