Use SqlDataReader to return dates and times

[edogg23]

Hello. I'm trying to use SqlDataReader to return information from my database. It works fine until it comes to a datetime field. I'm getting an error saying: " Input string was not in a correct format." on the line where I'm declaring a variable to hold my field. Here is the line where the error is occuring: Dim waitDate As Date = reader.GetDateTime("WaitListDate")

Can someone tell me what I'm doing wrong?

[JorgeM]

Try DateTime instead.. However it assumes your DB table field is set to the appropriate data type..I used the index number instead of column name only because I dont know what your column names are in the db. replace 0 with the correct index number or use column names.

 Dim waitDate As DateTime = reader.GetDateTime(0)

[edogg23]

Hello. Thanks for the reply. I have pasted all of my code below. I tried using the index number and the column name and I still got errors. I'm not new to this but I'm not a programmer so please excuse my ignorace. And yes. My the WaitListDate column in sqlserver is datetime. I'm able to pull all the basic text columns but when the datatype changes I don't know what to do.

cmd = New SqlCommand("SELECT FirstName, LastName, StatusField, TypeOfApptField, WaitListDate FROM [PatientData] WHERE TypeOfApptField = 'MCC'", conn)

    Dim reader As SqlDataReader

    conn.Open()
    reader = cmd.ExecuteReader()

    Dim strBuilder As StringBuilder = New StringBuilder()

    Dim firstName As Int32 = reader.GetOrdinal("FirstName")
    Dim lastName As Int32 = reader.GetOrdinal("LastName")
    Dim apptype As Int32 = reader.GetOrdinal("TypeOfApptField")
    Dim waitDate As Datetime = reader.GetDateTime(0)

    While (reader.Read())
        strBuilder.Append( _
          reader.GetString(firstName) + " " + "     " + _
          reader.GetString(lastName) + " " + "   " + _
          reader.GetString(apptype) + " " + "   " + _
          "<br>")
    End While


    output.Text = strBuilder.ToString()

    reader.Close()
    conn.Close()

[edogg23]

I guess what I really need to know is how I could convert that datetime to a string to display it?

[JorgeM]

Are you sure your sample code above is working except for the date time problem?

Anyway with regard to converting to a string.. this should work..

reader.GetDateTime(columnIndex).ToString("dd MMM yyyy")

Here are some examples of Custom Date and Time Format Strings

[edogg23]

Yes. It worked before I tried to return the date column. That is when I got the error. Thanks for your help. I'm about to try your suggestion.

[edogg23]

Hi. I think I'm getting closer. I actaully tried that option once before. I'm just a little confused on what you mean by columnIndex. I'm trying to pull the data from the column named WaitListDate which is what I'm trying to store as waitDate. I don't know what that would equate to that as far as in Index goes. I tried just using 0 or 1 as the index # but I get an error that says: "Exception Details: System.InvalidOperationException: Invalid attempt to read when no data is present." But even though some of the records have a NULL value for that column a lot of them don't. Is there a way I can convert the date in the sql query and then just have SqlDataReader read it same as it reads the other fields? I was looking at the convert and cast functions in SQL but I'm not sure which one I should use or even if that would work. I've tried to search for way to read different datatypes using SqlDataReader but every example just shows how to read simple text records. I remember you asking if my code worked before. Do you need to see more of it? The only thing I left out was my connection string because it has my server password in it. But like I said when I ran the code without the line to pull the WaitListDate it returns everything just fine. Thanks agian for all your help. I know it is frustrating trying to help newbies like me.

[edogg23]

After sending my last question I have gotten even closer. I did decied to do the conversion in my sql query. It kind of worked. But on the records where that field has no date and has a NULL value I get this error when the SqlDataReader gets to a record with a NULL value: "This method or property cannot be called on Null values." Now I have no idea what to do about this. How are NULL values handled in this case?

[JorgeM]

With regard to columnIndex, what that refers to is basically the column number from your query. Say your query returns 5 columns. You can access the field (column) by its index number. The index number starts at 0. So if you have 5 fields you can reference Indexes 0-4. It's up to you how you do it but if you do it by name and you ever change the field name in SQL you have to go back to your code.

I wouldn't perform the casting/converting in SQL. If you are interested in just displaying date information as a string, then just convert it into a string and use the format method accordingly. That should solve the null value problem.

But if you do want to handle that in SQL, use the IsNULL() method in your query so you can tell SQL how to handle Nulls if it finds any.

http://technet.microsoft.com/en-us/library/ms184325.aspx

No worries on the frustration, I'm not frustrated and if you happen to get frustrated step away, take a moment, and get back to it after a break.

[edogg23]

Ok. Thanks. That makes a lot of sense, but if I use the index instead of the name will I have to use some kind of counter to go through all the records in the table?

I will try again to do it in the .net code and remove the conversion from the query statement. But I don't know how to make the SqlDataReader read the date. When it gets to this line

Dim waitDate As Int32 = reader.GetOrdinal("WaitListDate")

It gives me the error about being the incorrect format. Probably b'c the column is datetime type. I've also tried it this way and I get the same error:

Dim waitDate As date = reader.GetDateTime("WaitListDate")

That is the only reason I did the conversion in the query. I don't know how to read in the date using SqlDataReader. It doesn't matter to me how it displays the date. I just want it to work. I know that if I can get it to read the date and display it then formatting it the way I want will be easy. You sent me that information earlier in the thread. So am I getting that error because I'm using SqlDataReader wrong?

I want to learn using the best practices. So I will continue to try to do it using your suggestions. All the help you are giving is greatly appreciated. Thanks.

[JorgeM]

Ok. Thanks. That makes a lot of sense, but if I use the index instead of the name will I have to use some kind of counter to go through all the records in the table?

You could loop through them or reference them by number manually as you do with the name, or you can continue to do it as you are doing it already, or if the name works for you, thats fine too. I dont want to sway you one way or another, there's no right or wrong, only whats best for your app.

Try this...

 Dim waitDate As String = reader.GetDateTime("WaitListDate").ToString("dd MMM yyyy")

I want to learn using the best practices.

When I think of best practices, i simply try to implement simple, least complex solutions. Solutions that can be understood by someone else that may take over the project. However, I understand what you mean. aside from best practices, you also want to make sure you are doing it as effeciently as possible as well.

[edogg23]

Thanks for all your help. I finally got it working.