MySQL Issue: SQL query fails on unrepresentable date/time

TheWizardofInt

TheWizardofInt

Well-known member
Joined
Dec 31, 1969
Messages
333
Location
Orlando, FL
The error is: Specified argument was out of the range of valid values, parameters year month and day describe an unrepresentable datetime.

The situation is that I am querying MySQL with a VB.Net application, and the SQL Query pulls a date field. The format for their date is yyyy-MM-dd, and apparently .Net doesnt understand it.

Any known work around? I am reasonably sure I need to do some sort of convert
 
or maybe your code is whats causing the problem.
.net doesnt support mysql out of the box, the mysql odbc drivers need to be installed, unless youre using some other data provider such as the one provided bytefx.
if you can, plz post reproduceable code.
 
HJB417 said:
or maybe your code is whats causing the problem.
.net doesnt support mysql out of the box, the mysql odbc drivers need to be installed, unless youre using some other data provider such as the one provided bytefx.
if you can, plz post reproduceable code.
Click to expand...

Code: 
        Dim sConn As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                            "SERVER=adminserver;" & _
                            "DATABASE=scatteredlots;" & _
                            "UID=bob;" & _
                            "PASSWORD=intwiz;" & _
                            "OPTION=3"
        Dim cmd As New OdbcCommand
        Dim oConn As OdbcConnection = New OdbcConnection(sConn)
        oConn.Open()
        all parameters
        sSQL = "Select TaxIDParcelID, OwnersName, Address1, Address2, Address3, City, " & _
            "State, Zip, Country, Phone, Community, ShortLegal, ShortLegal2, ID, Price, " & _
            "WillingSellerStatus, WillingPrice, SellableRange, CommissionRate, CommissionGross, " & _
            "ClosingFeeAmount, NetProceeds, ContractNotes, LeadSource, LeadNotes, LeadDateAquired, " & _
            "Agent, LeadStatus, BuilderID, ActionID, Neighborhood, SentToCallCenter, AnticipatedClosingDate, " & _
            "UnitID, ContractRecievedDate, TitleID, ContractExpirationDate from MasterList where TaxIDParcelID IS NOT NULL"
        no dates
        sSQL = "Select TaxIDParcelID, OwnersName, Address1, Address2, Address3, City, " & _
            "State, Zip, Country, Phone, Community, ShortLegal, ShortLegal2, ID, Price, " & _
            "WillingSellerStatus, WillingPrice, SellableRange, CommissionRate, CommissionGross, " & _
            "ClosingFeeAmount, NetProceeds, ContractNotes, LeadSource, LeadNotes, " & _
            "Agent, LeadStatus, BuilderID, ActionID, Neighborhood, SentToCallCenter, " & _
            "UnitID,  TitleID, ContractExpirationDate from MasterList where TaxIDParcelID IS NOT NULL"
        cmd.Connection = oConn

        cmd.CommandText = sSQL

        Dim odDA As New OdbcDataAdapter(cmd)
        Try
            odDA.Fill(dt)

        Catch ex As Exception
            MsgBox(ex.Message)
            oConn.Close()
            Exit Sub
        End Try

The version with dates doesnt work, the version with dates does

The MyODBC object is installed
 
If you execute the following, what is the value of parameter value. And what is the type, is it System.DateTime, System.String, or something else?
[VB]
Dim sConn As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=adminserver;" & _
"DATABASE=scatteredlots;" & _
"UID=bob;" & _
"PASSWORD=intwiz;" & _
"OPTION=3"
Dim oConn As OdbcConnection = New OdbcConnection(sConn)
oConn.Open()

Dim oCmd As OdbcCommand = oConn.CreateCommand()
oCmd.CommandText = "SELECT ContractRecievedDate FROM MasterList where TaxIDParcelID IS NOT NULL"

Dim value As Object = oCmd.ExecuteScalar()
[/VB]
 
Back
Top