Visual Basic

3-Jan-07 10:07

I need to format the date when the values are inserted to the MySQl db. All other columns work fine but all of the date columns are inserted as zeroes and in a different format, for example 0000-00-00. Any ideas?

Code:
Dim myConnString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=192.168.1.27;" & _
"DATABASE=testship;" & _
"USER=;" & _
"PASSWORD=;" & _
"OPTION=3;"

Dim MyConnection As New Odbc.OdbcConnection(myConnString)
MyConnection.Open()
Dim MyCommand As New Odbc.OdbcCommand
MyCommand.Connection = MyConnection
MyCommand.CommandText = "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 ('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "'" & ")" '& "','" & 'TimeStamp & "'" & ")"

MyCommand.ExecuteNonQuery()
MyConnection.Close()

Re: format date in MySql

Dave Kreskowiak3-Jan-07 11:40

3-Jan-07 11:40

Change this to a parameterized query. The parameter objects take care of the translation for you. You can read why they are a good thing here[^].

Dave Kreskowiak
Microsoft MVP - Visual Basic

Re: format date in MySql

jds12074-Jan-07 3:59

4-Jan-07 3:59

Do you have any similar samples because I am not familiar with parameterized queries?

Re: format date in MySql

jds12074-Jan-07 5:27

Re: Performance with Editor

4-Jan-07 5:27

Here is some code that I tried but the dates are still being displayed as 0000-00-00 in db:

Dim myConnString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=192.168.1.27;" & _
"DATABASE=testship;" & _
"USER=;" & _
"PASSWORD=;" & _
"OPTION=3;"
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
MyConnection.Open()
Dim MyCommand As New Odbc.OdbcCommand
MyCommand.Connection = MyConnection

MyCommand.CommandText = "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 ('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "'" & ")"
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderDate", OrderDate))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderCustomer", OrderCustomer))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderAcctNum", OrderAcctNum))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderType", OrderType))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderSoNum", OrderSoNum))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemPartNum", OrderItemPartNum))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemPartDesc", OrderItemPartDesc))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemQty", OrderItemQty))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemQtyRec", OrderItemQtyRec))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemQtyDtRec", OrderItemQtyDtRec))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemBO", OrderItemBO))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemBODt", OrderItemBODt))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemCanceled", OrderItemCanceled))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemCanceledDt", OrderItemCanceledDt))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemNotes", OrderItemNotes))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemPONum", OrderItemPONum))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemRetailPrice", OrderItemRetailPrice))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("OrderItemSalePrice", OrderItemSalePrice))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("Status", Status))
MyCommand.Parameters.Add(New Odbc.OdbcParameter("InsertDate", InsertDate))

MyCommand.ExecuteNonQuery()
MyConnection.Close()

Performance with Editor

Cecil Cheah3-Jan-07 10:00

Cecil Cheah

3-Jan-07 10:00

Hi there

I wonder if anyone can help me with my problem.

I am building an Programming Editor for my Healthcare Software at work. The editor that comes with it is useless.

One formular i am using notepad to programme has over 20,000 lines of codes. I have started making an editor and the problem is it takes ages to read all the keywords and find the line and add this to a Treeview control. I use Regular Expression to reduce the time to find the keywords. However, i have to loop over each item in the Regular Expression Match Collections in order to find the line where each match occurs. This will enable me to add each keywords and the line number to the tree view control. This loop with my 20,000 lines of codes seems to take ages. Can someone tell me how can i increase the performance? Shall i use Multithreading to do all the looping in the background? However, this will only hide the problems. When the Form appears and the user clicks the treeview structure, the background tasks may not have been completed yet.

Can anyone tells me how to solve this performance problem?

Enriad4-Jan-07 2:47

Enriad

4-Jan-07 2:47

One way would be to use delegate's to perform the looping. Set the enabled value of the treeview to false and once the looping has finished, set it to true.

This will stop people from clicking on it before you want them to. There are some good tutorials on MSDN for delegating, though not many for placing arguments/paramters in the functions (if someone could let me know one, that would be great, I lost the bookmark and my code when my old HDD died Frown | :(

)

As for the faster matching, there isn't an overly fast way to loop over 20,000 lines of an array, you might have to deal with the regex loop searching. I'd have to take a look at the search part of the code to see, though you could try a google hit for 'vb.net text search optimizing'

Daniel

VB6 to VB.NET printing using Printer Language

oogyboogyme3-Jan-07 8:18

oogyboogyme

3-Jan-07 8:18

Hullo,

I'm trying to convert some code from vb6 to .NET. Basically I don't know how to convert this first line so that I can send a string to my printer. I want to use the label printer called "ZebraLP2" (its shared network name). Any help on how to send it this info using VB.NET would be awesome.

Open "\\127.0.0.1" & "\ZebraLP2" For Output As #1

'''Start of EPL2 code
strTemp = "N" & vbCrLf ''' Clear buffer
strTemp = strTemp & "q811" & vbCrLf ''' Label Width in dots (2 inches)
strTemp = strTemp & "Q1217,1" & vbCrLf ''' Label height and gap in dots (1 inch)
strTemp = strTemp & "S" & Trim(Me.strSpeed) & vbCrLf
strTemp = strTemp & "D" & Trim(Me.strDensity) & vbCrLf
strTemp = strTemp & "ZT" & vbCrLf ''' Direction
strTemp = strTemp & "OD" & vbCrLf ''' Direct Thermal mode
strTemp = strTemp & "A25,12,0,4,1,1,N,""" & sFirstLine & """" & vbCrLf
strTemp = strTemp & "A25,45,0,4,1,1,N,""" & sSecondLine & """" & vbCrLf
strTemp = strTemp & "A25,78,0,4,1,1,N,""" & sThirthLine & """" & vbCrLf
strTemp = strTemp & "P" & Trim(intQty)
''' End of the EPL2 code

Print #1, strTemp

Close

-Daniel

MatrixCoder3-Jan-07 8:40

MatrixCoder

3-Jan-07 8:40

Hmm. I'm not too good with VB6, but this article might help you: Basic Text and Image Printing

Trinity: Neo... nobody has ever done this before.
Neo: That's why it's going to work.

oogyboogyme3-Jan-07 8:51

oogyboogyme

3-Jan-07 8:51

hmm no that doesn't help me, thats the generic stuff I keep finding when I search for printing help with VB.NET.

The printer is a barcode/label printer and the formatting is very specific so i'm using the EPL2 printing language. Inside that code i have listed it actually controls and handles the print feature. So no print previews, no documents i just need to send a string to this shared printer and it should take care of the rest!

So how do i send a shared printer this string?

Tim Carmichael3-Jan-07 10:17

Tim Carmichael

3-Jan-07 10:17

Ok... we do something similar here using VB 6.0 and I have just checked that the following will work with .NET...

1. Write the desired data to a text file.
2. Create a second text file as a .BAT file with the following command: copy \b <your text="" file=""> \\127.0.0.1\ZebraLP2
3. Shell the .BAT file: Shell(<your .bat="" file="">, AppWinStyle.Hide)

Slimy... yes, but, it works.

Tim

oogyboogyme4-Jan-07 13:00

oogyboogyme

4-Jan-07 13:00

i ran a pretty simple test, and my Zebra Label printer didn't respond at all to running the .BAT file.

Put the first file in folder EPLprint.txt and then ran my .BAT file with the following line:

copy /b EPLprint.txt \\127.0.0.1\ZebraLP2844

...my Zebra Label Printer didn't react. (Yes its on and status is ready) I dunno what I'm doing wrong.

Engineering problem-please advise

Tim Carmichael5-Jan-07 2:41

Tim Carmichael

5-Jan-07 2:41

The "copy /b" just tells the computer to copy a file as binary to the specified network location or device.

Create a text file and send it to a different printer, or, if you can set the printer to be the default, again, from the command prompt, use:
print <file name="">

Tim

neobavesten3-Jan-07 6:10

neobavesten

3-Jan-07 6:10

Hi,

I find this site very useful. It is real comprehensive resource for all of us who would like to improve coding skills. Although I searched site in many details I still have some questions with the problem I am trying to solve.
I have developed algorithm for one special task that deals with power flows in power electricity networks. Program works well and I have tested it numerous times but now I would like to build more advanced GUI.
I would like to create some workspace where user could place necessary elements( for example place generators, transformers and connect them with power lines...when they are satisfied with topology they would start the simulation). That is nothing new and looks like workspace in Simulink or any other engineering program.

Since I am not a programmer could you please tell me weather that can be done in VB and how? Also what is the easiest way to complete this? Are there any available solutions?

I am ready to learn but please tell me what I should look for in books and reference manuals.

I hope you understood what I was trying to explain.

Thank you in advance.

Please help!

Kind regards,
Velimir Lackovic

Re: Engineering problem-please advise

nagendra rao s.v.3-Jan-07 20:07

nagendra rao s.v.

3-Jan-07 20:07

Hi,
I am also an Electrical Engineer Working as a Programmer.
The task what you said is very easy for telling but it is very difficult for Implimentation. Because as you told it looks like simulation software we need to define each and every block with some sort of Default Data and we need to define some properties to that so that it will act as a block for us for simulations.
i am very happy to see a guy like me and i am also trying to some sort of simulations but i dont have a clear idea of what to do. As you have that idea i think i can help you if you need any help.
And as per my Knowledge this needs Extensive Research and Highly Responsive Algorithm.
Feel free to ask me any thing in this context.

MySql connection

jds12073-Jan-07 5:59

3-Jan-07 5:59

I don't know why it is so difficult for me to connect to the MySQL database. But I have made a little progress. Data is being inserted to the db but the column names are being inserted instead of the values. Also after the program run for a while I will get the following error:
Can't connect to MySql Server(10048).

Here is some code to go by:
Dim myConnString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=192.168.1.27;" & _
"DATABASE=;" & _
"USER=;" & _
"PASSWORD=;" & _
"OPTION=3;"
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
MyConnection.Open()
Dim MyCommand As New Odbc.OdbcCommand
MyCommand.Connection = MyConnection

MyCommand.CommandText = "INSERT INTO ls_orderitems (OrderItemID, OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('OrderItemID', 'OrderDate', 'OrderCustomer', 'OrderAcctNum', 'OrderType', 'OrderSoNum', 'OrderItemPartNum', 'OrderItemPartDesc', 'OrderItemQty', 'OrderItemQtyRec', 'OrderItemQtyDtRec', 'OrderItemBO', 'OrderItemBODt','OrderItemCanceled', 'OrderItemCanceledDt', 'OrderItemNotes', 'OrderItemPONum', 'OrderItemRetailPrice', 'OrderItemSalePrice', 'Status', 'InsertDate')"

MyCommand.ExecuteNonQuery()
MyConnection.Close()

jds1207

jds1207

Dave Kreskowiak3-Jan-07 6:34

3-Jan-07 6:34

jds1207 wrote:
MyCommand.CommandText = "INSERT INTO ls_orderitems (OrderItemID, OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('OrderItemID', 'OrderDate', 'OrderCustomer', 'OrderAcctNum', 'OrderType', 'OrderSoNum', 'OrderItemPartNum', 'OrderItemPartDesc', 'OrderItemQty', 'OrderItemQtyRec', 'OrderItemQtyDtRec', 'OrderItemBO', 'OrderItemBODt','OrderItemCanceled', 'OrderItemCanceledDt', 'OrderItemNotes', 'OrderItemPONum', 'OrderItemRetailPrice', 'OrderItemSalePrice', 'Status', 'InsertDate')"

jds1207 wrote:
Data is being inserted to the db but the column names are being inserted instead of the values.

Did you happen to look at what's in the VALUES clause?? It's inserting the Column names because that exactly what your telling it to do! VALUES ('OrderItemID', 'OrderDate', ... You've told it to insert these strings into the database. You have to build the SQL statement to insert the data you have stored in variables somewhere, preferrably using a parameterized query.

Dave Kreskowiak
Microsoft MVP - Visual Basic

jds12073-Jan-07 7:17

3-Jan-07 7:17

What about the error message? Do you have any ideas?

Dave Kreskowiak3-Jan-07 7:22

3-Jan-07 7:22

You didn't supply a database name in your connection string. You left the DATABASE option blank. You have supply the name of the database you want the connection to work with.

Dim myConnString As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _
   "SERVER=192.168.1.27;" & _
   "DATABASE=????????????;" & _
   "USER=;" & _
   "PASSWORD=;" & _
   "OPTION=3;"

Dave Kreskowiak
Microsoft MVP - Visual Basic

jds12073-Jan-07 7:29

3-Jan-07 7:29

The database name is in the original code. I just did not post the names.

Dave Kreskowiak3-Jan-07 11:37

VB 6.0 IDE Project Explorer form icon

3-Jan-07 11:37

It could the IP is wrong, the MySQL server isn't running, the user ID and/or password is wrong, ...

Dave Kreskowiak
Microsoft MVP - Visual Basic

A long way from home3-Jan-07 5:16

A long way from home

3-Jan-07 5:16

In the Project Explorer window in VB 6.0, some of the icons for my forms are partially greyed out. The blank page part and the read-only lock part are okay, but the window with the aqua strip is greyed out. Does anyone know what this means? Thanks.

Confused | :confused:

A long way from home

Problem

amaneet3-Jan-07 1:25

amaneet

3-Jan-07 1:25

Sir/madam,
I am facing problem in the following line which is bold.I want to know the meaning of this line.It is associated with the following function.

<browsable(true), defaultvalue(false)=""> Public Property [ReadOnly]() As Boolean
Get
Return _ReadOnly
End Get
Set(ByVal Value As Boolean)
_ReadOnly = Value
End Set
end sub

Thanks and Regards
Pankaj

Re: Problem

Dave Kreskowiak3-Jan-07 2:16