Visual Basic

23-Feb-07 8:34

I am trying to insert some date columns into a MySQL database using vb.net. When trying, I get the error "Incorrect date value: '02/07/2006' for column 'Orderdate' at row 1. MySQL has a date format like YYYY-MM-DD and will not let me insert those dates because of the format. I am inserting text files and the date columns are formatted like MM/DD/YYYY. When I change the data type in the database to varchar, the dates will insert just fine but when I change the data type to date they won't insert in which the date data type is preferred. I have tried so many different things trying to format this including parameterized queries. Any ideas on how to fix this problem?

Here is some code were I tried using the CDate function and got the error "Conversion from string "" to type 'Date' is not valid."

Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & CDate(OrderDate).ToString("yyyy-MM-dd") & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & CDate(OrderItemQtyDtRec).ToString("yyyy-MM-dd") & "','" & OrderItemBO & "','" & CDate(OrderItemBODt).ToString("yyyy-MM-dd") & "','" & OrderItemCanceled & "','" & CDate(OrderItemCanceledDt).ToString("yyyy-MM-dd") & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")

jds1207

Marcus J. Smith23-Feb-07 8:44

23-Feb-07 8:44

jds1207 wrote:
CDate(OrderItemQtyDtRec).ToString("yyyy-MM-dd")

That should be CDate(OrderItemQtyDtRec). I thought this was already answered.

CleAkO

jds120723-Feb-07 8:53

23-Feb-07 8:53

What?

Marcus J. Smith23-Feb-07 8:57

23-Feb-07 8:57

Dont convert it to date then convert it back to string, just leave it as a date object when you insert it into the DateTime column. The database knows what to do with a Date object as well as a correctly formatted Date String object.

CleAkO

Marcus J. Smith23-Feb-07 9:01

23-Feb-07 9:01

It also helps if the variable isnt blank when you try to convert it. If it is blank you should probably pass DBNull.Value, which you may or may not be able to set to a string before hand.

CleAkO

jds120723-Feb-07 9:10

23-Feb-07 9:10

What variable?

Marcus J. Smith23-Feb-07 9:16

23-Feb-07 9:16

Did you even write this code???

CleAkO

jds120723-Feb-07 9:19

23-Feb-07 9:19

yeah

Marcus J. Smith23-Feb-07 9:26

23-Feb-07 9:26

jds1207 wrote:
Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & CDate(OrderDate).ToString("yyyy-MM-dd") & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & CDate(OrderItemQtyDtRec).ToString("yyyy-MM-dd") & "','" & OrderItemBO & "','" & CDate(OrderItemBODt).ToString("yyyy-MM-dd") & "','" & OrderItemCanceled & "','" & CDate(OrderItemCanceledDt).ToString("yyyy-MM-dd") & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")

The stuff in bold, just leave it as CDate(VariableName). Before you run this command do something like If OrderDate.Trim = "" Then OrderDate = DBNull.Value to insert a NULL value instead of trying to convert a blank value to a date.

CleAkO

jds120723-Feb-07 9:53

23-Feb-07 9:53

I am still getting the same error. I am beginning to believe this can't be done with vb.net and MySQL.

Marcus J. Smith23-Feb-07 10:04

23-Feb-07 10:04

Try a Parameterized statement, search Google for Parameterized Query. There you would setup your statement like the following.

Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?, ?, ?, ?)<br />
<br />
If OrderDate.Trim <> "" Then<br />
MyCommand.Parameters.Add("@OrderDate", CDate(OrderDate))<br />
Else<br />
MyCommand.Parameters.Add("@OrderDate", DBNull.Value)<br />
End If

Repeat the above for all dates and for the rest just use the line inside the If part.

CleAkO

Dave Kreskowiak23-Feb-07 10:27

Dave Kreskowiak

23-Feb-07 10:27

Oh it can be done. It's just a matter of doing it correctly. Concatenting strings together like your doing is not it. I've already told you how to do it with a parameterized query.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Caret Size

Anybloodyid23-Feb-07 7:54

23-Feb-07 7:54

I have a rich text box, when I change the font size and type something, everything is ok, then when I hit return and go to the next line the Caret goes back to the default size then when I start to type again it returns to the new font size, how can I get the Caret to stay the same size as the chosen font size.

Re: Caret Size [modified]

Dave Kreskowiak23-Feb-07 10:25

Dave Kreskowiak

23-Feb-07 10:25

You've got no control over the caret size in the RTB, or any other standard textbox for that matter.

The reason it's doing this because there is no text on that line with the size of the font your using. It won't change to the new size, as you've found out, until there is something on that line in the new size.

I don't know how your going to change the size of the caret, and more importantly, keep it that size when the RTB is resizing it to what it wants every time the cursor moves.

Dave Kreskowiak
Microsoft MVP - Visual Basic

TwoFaced23-Feb-07 11:32

23-Feb-07 11:32

Does the caret actually change or is it just the spacing that changes. I tried it by setting a large font as the default font. I ran the program and typed some stuff and then clicked a button which altered the selectionfont to a smaller font. When I hit enter the caret was the appropriate height but the space for the line appeared to fit the larger default font. Once I typed something the spacing was set correctly but the issue persisted with every new line I made.

I messed around with overriding wndproc and I think I guessed correctly. I seem to have fixed the issue. You'll have to create your own class that inherits the richtextbox. The fix is a simple one. However, you'll have to use your 'new' control instead of the standard richtextbox. Here is the code. Let me know if you have any issues.

Public Class MyRTB
    Inherits RichTextBox
    Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)
        If m.Msg = &H444 AndAlso m.WParam.ToInt32 = 1 Then m.WParam = New System.IntPtr(3)

        MyBase.WndProc(m)
    End Sub
End Class

-- modified at 18:10 Friday 23rd February, 2007

Dave Kreskowiak23-Feb-07 14:02

Dave Kreskowiak

23-Feb-07 14:02

Hmmm... Interesting. Which message is it looking for?

Dave Kreskowiak
Microsoft MVP - Visual Basic

TwoFaced23-Feb-07 14:31

23-Feb-07 14:31

The message is EM_SETCHARFORMAT. I noticed that when you set the Font property for the RichTextBox this message is sent. So I looked for it when I set the SelectionFont property. Sure enough it was sent then as well. I looked for a difference and noticed the messages only differ by the wparam value. A value of 4 was being sent when you set the Font and a value of 1 was being sent when you set SelectionFont. So I figured why not change it and see what happens. I tried a 2 and then a 3. 3 seemed to fix the problem. Although I never fully tested the consequences so some thorough testing should be done.

Anybloodyid23-Feb-07 22:53

23-Feb-07 22:53

I tried using the code to make a New control, but I get a squiggley under
Inherits RichTextBox with the message RichTextBox not defined.

I am also currently using an extended RichTextBoxPrintCtrl so ideally I would like to keep this control with what you have suggested placed in there, if that's possible?

TwoFaced23-Feb-07 23:51

23-Feb-07 23:51

Maybe try the full path "Inherits System.Windows.Forms.RichTextBox". The fix should work with your extended class. As long as it's a RichTextBox at the heart. All you should have to do is copy and paste the WndProc method.

Anybloodyid24-Feb-07 1:22

24-Feb-07 1:22

I tried pasting the code into the RTB Class, which already has the Inherits RichTextBox

But still no luck Frown | :(

I have pasted all of the code for the RTB below.

<br />
Option Explicit On<br />
<br />
Imports System<br />
Imports System.Windows.Forms<br />
Imports System.Drawing<br />
Imports System.Runtime.InteropServices<br />
Imports System.Drawing.Printing<br />
<br />
<br />
''' <summary><br />
''' The rich text box print control class was developed by Microsoft, information about<br />
''' this control can be found in your help files at:  <br />
''' ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.KB.v10.en/enu_kbvbnetkb/vbnetkb/811401.htm<br />
''' In general, their intent was to create a rich text box control with print capability<br />
''' embedded into the control.<br />
''' </summary><br />
''' <remarks>This control class replaces the use of the regular RichTextBox control; the<br />
''' purpose of this extension was specifically to facilitate printing the contents<br />
''' of a rich text box control.</remarks><br />
<br />
    Public Class RichTextBoxPrintCtrl<br />
    Inherits RichTextBox<br />
    Protected Overrides Sub WndProc(ByRef m As System.Windows.Forms.Message)<br />
        If m.Msg = &H444 AndAlso m.WParam.ToInt32 = 1 Then m.WParam = New System.IntPtr(3)<br />
        MyBase.WndProc(m)<br />
    End Sub<br />
        ' Convert the unit that is used by the .NET framework (1/100 inch) <br />
        ' and the unit that is used by Win32 API calls (twips 1/1440 inch)<br />
        Private Const AnInch As Double = 14.4<br />
<br />
        <StructLayout(LayoutKind.Sequential)> _<br />
         Private Structure RECT<br />
            Public Left As Integer<br />
            Public Top As Integer<br />
            Public Right As Integer<br />
            Public Bottom As Integer<br />
        End Structure<br />
<br />
        <StructLayout(LayoutKind.Sequential)> _<br />
        Private Structure CHARRANGE<br />
            Public cpMin As Integer          ' First character of range (0 for start of doc)<br />
            Public cpMax As Integer          ' Last character of range (-1 for end of doc)<br />
        End Structure<br />
<br />
        <StructLayout(LayoutKind.Sequential)> _<br />
        Private Structure FORMATRANGE<br />
            Public hdc As IntPtr             ' Actual DC to draw on<br />
            Public hdcTarget As IntPtr       ' Target DC for determining text formatting<br />
            Public rc As Rect                ' Region of the DC to draw to (in twips)<br />
            Public rcPage As Rect            ' Region of the whole DC (page size) (in twips)<br />
            Public chrg As CHARRANGE         ' Range of text to draw (see above declaration)<br />
        End Structure<br />
<br />
        Private Const WM_USER As Integer = &H400<br />
        Private Const EM_FORMATRANGE As Integer = WM_USER + 57<br />
<br />
        Private Declare Function SendMessage Lib "USER32" Alias "SendMessageA" (ByVal hWnd As IntPtr, ByVal msg As Integer, ByVal wp As IntPtr, ByVal lp As IntPtr) As IntPtr<br />
<br />
        ' Render the contents of the RichTextBox for printing<br />
        '	Return the last character printed + 1 (printing start from this point for next page)<br />
        Public Function Print(ByVal charFrom As Integer, ByVal charTo As Integer, ByVal e As PrintPageEventArgs) As Integer<br />
<br />
            ' Mark starting and ending character <br />
            Dim cRange As CHARRANGE<br />
            cRange.cpMin = charFrom<br />
            cRange.cpMax = charTo<br />
<br />
            ' Calculate the area to render and print<br />
            Dim rectToPrint As RECT<br />
            rectToPrint.Top = e.MarginBounds.Top * AnInch<br />
            rectToPrint.Bottom = e.MarginBounds.Bottom * AnInch<br />
            rectToPrint.Left = e.MarginBounds.Left * AnInch<br />
            rectToPrint.Right = e.MarginBounds.Right * AnInch<br />
<br />
            ' Calculate the size of the page<br />
            Dim rectPage As RECT<br />
            rectPage.Top = e.PageBounds.Top * AnInch<br />
            rectPage.Bottom = e.PageBounds.Bottom * AnInch<br />
            rectPage.Left = e.PageBounds.Left * AnInch<br />
            rectPage.Right = e.PageBounds.Right * AnInch<br />
<br />
            Dim hdc As IntPtr = e.Graphics.GetHdc()<br />
<br />
            Dim fmtRange As FORMATRANGE<br />
            fmtRange.chrg = cRange                 ' Indicate character from to character to <br />
            fmtRange.hdc = hdc                     ' Use the same DC for measuring and rendering<br />
            fmtRange.hdcTarget = hdc               ' Point at printer hDC<br />
            fmtRange.rc = rectToPrint              ' Indicate the area on page to print<br />
            fmtRange.rcPage = rectPage             ' Indicate whole size of page<br />
<br />
            Dim res As IntPtr = IntPtr.Zero<br />
<br />
            Dim wparam As IntPtr = IntPtr.Zero<br />
            wparam = New IntPtr(1)<br />
<br />
            ' Move the pointer to the FORMATRANGE structure in memory<br />
            Dim lparam As IntPtr = IntPtr.Zero<br />
            lparam = Marshal.AllocCoTaskMem(Marshal.SizeOf(fmtRange))<br />
            Marshal.StructureToPtr(fmtRange, lparam, False)<br />
<br />
            ' Send the rendered data for printing <br />
            res = SendMessage(Handle, EM_FORMATRANGE, wparam, lparam)<br />
<br />
            ' Free the block of memory allocated<br />
            Marshal.FreeCoTaskMem(lparam)<br />
<br />
            ' Release the device context handle obtained by a previous call<br />
            e.Graphics.ReleaseHdc(hdc)<br />
<br />
            ' Return last + 1 character printer<br />
            Return res.ToInt32()<br />
        End Function<br />
<br />
    End Class<br />
<br />

TwoFaced24-Feb-07 8:28

24-Feb-07 8:28

So the code didn't change anything at all? What version of Visual Studio are you using? I'm using 2005 and that worked great for me. If we're using different versions of the .net framework then maybe our RTB's are also slightly different. Is the code even executing? Try breaking the if then statement up and putting a break point on the code that executes. Also add in a line that prints the message. You can output it however you like I usually just send it to the console or debug window.

If m.Msg = &H444 AndAlso m.WParam.ToInt32 = 1 Then
console.writeline(m.tostring) 
m.WParam = New System.IntPtr(3)
End IF

This code should execute whenever the font or selectionfont is changed. If it doesn't execute then that's good to know and clearly something is different, and if it does execute maybe the message is slightly different. If it does execute then post the output and we can see if there is a solution that will work for you. Please post the message you get when you change the font property as well as when you change the selectionfont property.

Anybloodyid24-Feb-07 9:42