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

how to read a value from a query in asp.net

i have the following (see below) what I need to do is set some parameters from this query. it will bring only one record set back.

dim weekid as int
dim qtrid as int
dim yearid as int
dim monthid as int
dim startweekdate as date

how do I set these values ???? from my query asp no problem but this is new to me






        Dim querystring As String = ("select weekval, monthval,qtrval, yearval, [stdt] as stareweekdate from cal2005year where '" & Now & "' between stdt and eddt")
        Dim sqlcommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(querystring, SqlConnection1)
        sqlcommand.CommandTimeout = 0


        Dim myCommand As New SqlCommand(querystring, SqlConnection1)

        Dim adapter As New SqlDataAdapter(myCommand)
        Dim ds As New DataSet

        SqlConnection1.Open()
        adapter.Fill(ds)
        SqlConnection1.Close()


0
LeeHopkins
Asked:
LeeHopkins
  • 2
1 Solution
 
NauticalNonsenseCommented:
Greetings,

I assume your problem is getting the data OUT of the dataset to use it. First, you don't need to use a dataset and adapter, you can just use the datareader. It's more efficient to boot. Try something along these lines:

C#
======================================================
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connCommon"]);
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = "select weekval, monthval,qtrval, yearval, [stdt] as stareweekdate from cal2005year where '" & Now & "' between stdt and eddt";
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Connection = myConnection;
myConnection.Open();
SqlDataReader dr = sqlCommand.ExecuteReader();

x=0;
while(dr.Read())
 {
//   ' Put whatever you need to get out here.. it's just like an old skool ASP recordset really, movenext not required
//   ' access the data inside the datareader by using dr("fieldname")
   weekid = Convert.ToInt32(dr("weekval"));
//   ' et cetera
}
dr.Close();
sqlCommand.Connection.Close();
sqlCommand.Dispose();
myConnection.Close();

VB (converted)
==========================================================
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connCommon"))
Dim sqlCommand As New SqlCommand()
sqlCommand.CommandText = "select weekval, monthval,qtrval, yearval, [stdt] as stareweekdate from cal2005year where '" And Now And "' between stdt and eddt"
sqlCommand.CommandType = CommandType.Text
sqlCommand.Connection = myConnection
myConnection.Open()
Dim dr As SqlDataReader = sqlCommand.ExecuteReader()

x = 0
While dr.Read()
   '   ' Put whatever you need to get out here.. it's just like an old skool ASP recordset really, movenext not required
   '   ' access the data inside the datareader by using dr("fieldname")
   weekid = Convert.ToInt32(dr("weekval"))
End While '   ' et cetera
dr.Close()
sqlCommand.Connection.Close()
sqlCommand.Dispose()
myConnection.Close()



You say you're getting only one recordset back, did you mean row? If it's just one row, this'll work fine. If you have more than one row, then you have to put your logic in there or set stuff up in arrays.

Hope this helps!

Cheers,

aaron
0
 
NauticalNonsenseCommented:
Incidentally, you can use the dr.HasRows property to see if you got something back.
0
 
LeeHopkinsAuthor Commented:
thanks works great.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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