DateTime questions And SQL Data Objects ?

rsearing · January 11th, 2007, 07:54 PM

a) I have to admit, I am very knew at creating SQL data objects. I get a problem when trying to page with my data source. I know it has to do with my use of a Data Reader. I am pretty sure I need to use a DataSet--but I am not sure how to rewrite the following code to do that---could someone help?

Dim knightsDBConn As New SqlConnection(conString)
        Dim sqlString As String = "GetEventsByDate"
        Dim sqlCmd As New SqlCommand(sqlString, knightsDBConn)
        sqlCmd.CommandType = CommandType.StoredProcedure
        sqlCmd.Parameters.AddWithValue("@EventFromDate", fromDate)
        sqlCmd.Parameters.AddWithValue("@EventToDate", toDate)
        knightsDBConn.Open()
        Dim diaryEventSQLDR As SqlDataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnecti on)
        sqlCmd = Nothing
        Return diaryEventSQLDR

b) second question is--does anyone know how to put code as a default value of a Gridview. I am trying to do something like "DefaultValue=datetime.now" && "DefaultValue=datetime.now.addmonths(1)"
Basically, I have a GridView that is using an ObjectDataSource that grabs parameters from two text boxes I have....and I am wanting to set the defaultvalue to above--not sure how to do that without the control converting to string.

THANKS SO MUCH!
Rob

Imar · January 12th, 2007, 04:51 PM

Hi Rob,

There are a couple of ways to do this. Probably the easiest way is to use the Load method on the DataSet or DataTable class:

Code:

private DataTable GetDataTable()
{
  string sql = "YourSelectStatement";
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
    {
      myConnection.Open();
      using (SqlDataReader myReader = myCommand.ExecuteReader())
      {
        DataTable myTable = new DataTable();
        myTable.Load(myReader);
        myConnection.Close();
        return myTable;
      }
    }
  }
}

(Taken from http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=406 and therefore in C#, but I think you get the idea)Alternatively, you can use the "old" way and use an Adapter to fill the DataSet:

Code:

Using myConnection As SqlConnection = New SqlConnection(connectionString)
  Dim myAdapter As SqlDataAdapter = New SqlDataAdapter()
  myConnection.Open()

  Dim myCommand As SqlCommand = New SqlCommand(yourSqlStatement, myConnection)
  myCommand.CommandType = CommandType.Text ' Or StoredProcedure for a procedure

  myAdapter.SelectCommand = myCommand

  Dim myDataSet As DataSet = New DataSet("TableName")
  myAdapter.Fill(myDataSet)

  myConnection.Close()
End Using

Finally, to set a parameters value, use the OnSelecting event. First, let's assume you have a single parameter in the <SelectParameters>:

Code:

<SelectParameters>
  <asp:Parameter Name="today" Type="Object" />
</SelectParameters>

Then you can handle the OnSelecting event like this:

Code:

  Protected Sub ObjectDataSource1_Selecting( _
        ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) _
        Handles ObjectDataSource1.Selecting
    e.InputParameters(0) = DateTime.Now
  End Sub

By the way, if you just want to pass today's date, I think you're better using that date in the object directly.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.