Format Query Result

[euel]

Hi guys! I'm kinda new with Visual Basic.
So I created this simple function to get all DATES in a field(Date) from mysql database.

Public Function tblSelect(ByVal selectString As String)
        Dim myCmd As MySqlCommand
        Dim myReader As MySqlDataReader


        SQLConnection.Open()
        myCmd = New MySqlCommand(selectString, SQLConnection)
        myReader = myCmd.ExecuteReader()
        Return myReader
        SQLConnection.Close()
    End Function

then add it into my Combo Box

        Dim participationRows As Object
        Dim dateStart As Date



        rows = tblSelect(selectString)
        While (rows.Read())
            dateStart = Format(rows.GetDateTime(1).ToString("m"))
            cmbProjects.Items.Add(dateStart)
        End While
        rows.close()

What im trying to do is convert the date (ex. 2012-11-23 to November 23).
But I'm getting an error with this line--> dateStart = Format(rows.GetDateTime(1).ToString("m")) saying that "Conversion from string "m" to type 'Integer' is not valid."
Before i made it to a function (when it was just one whole code in my form) it works perfectly..
I'm guessing its the data type of the row or the value that i returned, but i can't seem to find some references on how to do this visual basic way..
I'm a php programmer so its easy to it in php..
Any ideas? Help really appreciated!


Thanks!!!

 

[Menthos]

Try the ToString("m") with a capital M... the lowercase m usually denotes minutes, not months

 

[euel]

Thanks for the reply!

Changed it to capital still i got error, "Conversion from string "M" to type 'Integer' is not valid."

Is my declaration here correct?

Dim rows As Object
Dim dateStart As  Date

 

[Menthos]

You would be better off declaring your rows object as a datareader, and you might need to cast retrieved data to a date object to get it to work.

If you step through your code, are you getting the results you expect from that field?

 

[euel]

Thanks Menthos,
yep, i'm getting the correct results from my field when not converting them...
Well might as well make it as a datareader like before then though i'm not yet giving up hope.
Still open for any ideas!

Thanks!!

 

[Menthos]

Last crack at this - if your field is coming back correctly as a datetime, it should format with:

dateStart = rows.GetDateTime(1).ToString("MMMM dd")

Your dateStart will want to be a string too.

 

[euel]

Last crack at this - if your field is coming back correctly as a datetime, it should format with:

dateStart = rows.GetDateTime(1).ToString("MMMM dd")

Your dateStart will want to be a string too.

Nope, still the same error,
Tried both :

dateStart = rows.GetDateTime(1).ToString("MMMM dd")

and

dateStart = Format(rows.GetDateTime(1).ToString("MMMM dd"))

with my dateStart As String...

Kinda getting the impression its hard to make this simple things in VB or i just missed something important.
Thanks Anyways!

 

[Menthos]

I'm starting to think that your field isn't getting back as a date, or that you're not getting back anything at all. Have you debugged your code and taken a look at what rows.getDateTime(1) is returning?

 

[euel]

Sorry for the late reply,I tried debugging and watching dateStart & dateEnd like you said..At breakpoint the value of both is #12:00:00 AM# , when i step into it changes to the correct value (ex. #11/23/2012#)..

Anyways i tried this...

Dim rows As Object 
Dim dateStart, dateEnd As Date        

conn(connectionString)        
rows = tblSelect(selectString)        
While (rows.Read())            
dateStart = rows.GetString(1)            
dateEnd = rows.GetString(2)            
cmbProjects.Items.Add(Format(dateStart.ToString("m")) & " - " & Format(dateEnd.ToString("m")))        
End While        
rows.close()        
SQLConnection.Close()

And it worked! But is this the correct way to do it?
Thanks!

 

[Menthos]

It's one way to do it

Personally I'd be happier seeing those database fields cast to type when you assign them to your vars - plus I'd drop the Format and just go with the ToString formatting.

So something along the lines of:

dateStart = cDate(rows.GetString(1))
dateEnd = cDate(rows.GetString(2))
cmbProjects.Items.Add(dateStart.ToString("MMMM") & " - " & dateEnd.ToString("dd"))

 

[euel]

Kinda like ur's better, much cleaner looking. :tennis:

Anyway's thanks for everything, gonna mark this as solve then..

Edit:
Dunno how to mark this as solved. LOL :loyal:
Can't find it or is it from the other forums.

 

[Menthos]

Hehe - no idea myself, I only answer questions here (or try, anyway)