Re: format date - Visual Basic Discussion Boards

The problem is that he needs to return it as a Date datatype but wants it to be in that "yyyy-MM-dd" format which is a string format according to VB.NET.

Cleako

As far as I know Date is a sting just different format. I know that any string that represents date will go into the database with out any problem.

Ohhh, for some reason I was thinking XML. D'Oh! | :doh:

Cleako

Is this code correct because I keep gettting the error:

Incorrect date value: '02/07/2006' for column 'OrderDate' at row 1

Here is code:

Dim dtTable As New DataTable
Dim row As DataRow
Dim formatdate As New DateTime

For Each row In dtTable.Rows
If row("datereceived").ToString() = formatdate.ToString("MM/DD/YYYY") Then
formatdate.ToString("YYYY-MM-DD")
ElseIf row("backorderdate").ToString() = formatdate.ToString("MM/DD/YYYY") Then
formatdate.ToString("YYYY-MM-DD")
ElseIf row("datecanceled").ToString() = formatdate.ToString("MM/DD/YYYY") Then
formatdate.ToString("YYYY-MM-DD")
ElseIf row("orderdate").ToString() = formatdate.ToString("MM/DD/YYYY") Then
formatdate.ToString("YYYY-MM-DD")
End If
Next row

Try this.

Dim dtTable As New DataTable<br />
Dim row As DataRow<br />
Dim formatdate As New DateTime<br />
<br />
<br />
For Each row In dtTable.Rows<br />
If row("datereceived").ToString() = formatdate.ToString("MM/DD/YYYY") Then<br />
formatdate.ToString("yyyy-MM-dd")<br />
ElseIf row("backorderdate").ToString() = formatdate.ToString("MM/DD/YYYY") Then<br />
formatdate.ToString("yyyy-MM-dd")<br />
ElseIf row("datecanceled").ToString() = formatdate.ToString("MM/DD/YYYY") Then<br />
formatdate.ToString("yyyy-MM-dd")<br />
ElseIf row("orderdate").ToString() = formatdate.ToString("MM/DD/YYYY") Then<br />
formatdate.ToString("yyyy-MM-dd")<br />
End If<br />
Next row

First, the Date/Time in any SQL database doesn't have a format. The format only applies when the data is displayed, not when it's stored.

If you database and code is setup properly, you don't have to worry about the format at all. Using a parameterized query, the parameter objects worry about the conversion for you. All you have to do is pass in a DateTime object into the parameters Value property.

You can read up on parameterized queries here[^].

Dave Kreskowiak
Microsoft MVP - Visual Basic

Dave is right. Like I said had to do similar thing before and didn't have to convert anything.

Thanks,

I have tried using parameterized queries and when I run the program I get the error "Failed to convert parameter value from a String to a DateTime."

Code:

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", OdbcType.Date)).Value = OrderDate

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderCustomer", OdbcType.VarChar)).Value = OrderCustomer

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderAcctNum", OdbcType.VarChar)).Value = OrderAcctNum

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderType", OdbcType.VarChar)).Value = OrderType

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderSoNum", OdbcType.Int)).Value = OrderSoNum

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemPartNum", OdbcType.VarChar)).Value = OrderItemPartNum

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemPartDesc", OdbcType.VarChar)).Value = OrderItemPartDesc

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemQty", OdbcType.Int)).Value = OrderItemQty

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemQtyRec", OdbcType.Int)).Value = OrderItemQtyRec

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemQtyDtRec", OdbcType.Date)).Value = OrderItemQtyDtRec

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemBO", OdbcType.Int)).Value = OrderItemBO

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemBODt", OdbcType.Date)).Value = OrderItemBODt

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemCanceled", OdbcType.Int)).Value = OrderItemCanceled

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemCanceledDt", OdbcType.Date)).Value = OrderItemCanceledDt

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemNotes", OdbcType.VarChar)).Value = OrderItemNotes

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemPONum", OdbcType.VarChar)).Value = OrderItemPONum

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemRetailPrice", OdbcType.VarChar)).Value = OrderItemRetailPrice

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@OrderItemSalePrice", OdbcType.VarChar)).Value = OrderItemSalePrice

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@Status", OdbcType.Int)).Value = Status

MyCommand.Parameters.Add(New Odbc.OdbcParameter("@InsertDate", OdbcType.VarChar)).Value = InsertDate

MyCommand.Connection = MyConnection
MyConnection.Open()
MyCommand.ExecuteNonQuery()
MyConnection.Close()

Am I doing something wrong?

jds1207 wrote:
Am I doing something wrong?

You missed the part where I said you had to provide a DateTime object to the Value property of the Parameter object. You have to convert that String to a DataTime object first. Try the DateTime.Parse()[^] method to get a DateTime object from a String. Don't forget to valid your data before you pass it to the database. You don't want dates from the early 5th century getting in there now do you?

Dave Kreskowiak
Microsoft MVP - Visual Basic

Dave Kreskowiak wrote:
If you database and code is setup properly, you don't have to worry about the format at all.

Are you saying that, he had to format the string representing Date/Time since his database and code is not setup properly? Normally inserting a string into a Date/Time field in any format will work. What are the possibilities that result in a need for additional formatting work? Where has jds gone wrong here?

tonymathewt wrote:
Are you saying that, he had to format the string representing Date/Time

No. He doesn't have to use a string at all. Plus, concatenating this stuff together introduces large security holes into his code.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Hello everyone.

My company is switching from Lotus Notes to Exchange. Our Notes client have functionality, which allows user to select emails (as many as they want) and place them in a different custom built folders for legal purposes. We would like to duplicate this functionality in Exchange. Since this is a new product I have no idea if it's capble of doing so. I though someone might have some experince with this. Here's what I'm looking for.

1. User selects one or more e-mails.
2. User presses button to bring up a pop up list of xfolders (I guess this is have to be built in VB.NET if possible) that they have access to (preferably based on group membership)
3. User selects which folders they want the mails to be copied to (these will be public folders)
4. A copy of the mail is put in each folder.

Any ideas (links to articles, code, etc.) would be greatly appreciated. Thank you,

Alex.

You can already do this in Exchange/Outlook and do it without using any code. Each user would have two email accounts open, their own and the account that is public and has all the folders in it. Then they just drag and drop the messages they want to the appropriate folders.

Get into the Exchange Administrators Guide for more information.

Dave Kreskowiak
Microsoft MVP - Visual Basic

We would like to automate this somehow by providing user with list of folders to copy to at once rather than copy to one folder than copy the same email to another folder (you see what I mean).

Some sort of pop up form with list of folders comes to my mind (if possible).
Where user can first select all email he/she would like to copy then select all folders these email have to go to. Any thoughts?

Thanks Dave.

NOw that you explain it that way, you would need code to do that. It's entirely possible to do, but you'd have to write an Office Add-In project to do it. I haven't done any Office work like this so this is about as far as I can go.

But, I'd start with hasing out the interface. From what you've described, the user selects a bunch of documents and does what with them?

The possibilities are for your code to retrieve a list of folders based on userID and add them to the folder list on the left so the messages can be dragged and dropped on them.

Another possibility is to have the user select of bunch of messages, then right-click one of them to bring up a context menu with your folder options on it.

Or the user would select the messages and click on a button in the Toolbar to get a list of available folders to copy the messages to.

Or any combination of the above...

Dave Kreskowiak
Microsoft MVP - Visual Basic

Sorry Dave. I didn't explain it in more detail from the beginnind. You know how it is with writing. To me made sense. Smile | :)

. Yes. I think last option you've mentioned is the one we want to go with.
(Or the user would select the messages and click on a button in the Toolbar to get a list of available folders to copy the messages to).

We only need to copy (not move) these messages into selected folders (selected by user).

You've mentioned that it would be Office Add-In. Is it possible to write something like this in VB.NET using VS. Currently we have VS v. 2003? And if it is, how do I integrate it into Exchange?

Any reference will do as well.

Thank you, Alex.

alexfromto wrote:
You've mentioned that it would be Office Add-In. Is it possible to write something like this in VB.NET using VS. Currently we have VS v. 2003? And if it is, how do I integrate it into Exchange?

This would be a client-side Outlook Add-In, not an Exchange app. Start here[^].

Dave Kreskowiak
Microsoft MVP - Visual Basic

Thanks Dave. Client-side Add-In. Does this mean I have to install my add-in on each machine?
Or is it possible somehow avoid that? In Lotus Notes all users have their own database but the design template is one for everybody. So when we created this little application we only had to update main database to inlcude button (which brings folders list).

Thanks,

alexfromto wrote:
Does this mean I have to install my add-in on each machine?

Yep.

alexfromto wrote:
Or is it possible somehow avoid that?

Nope.

alexfromto wrote:
In Lotus Notes all users have their own database but the design template is one for everybody. So when we created this little application we only had to update main database to inlcude button (which brings folders list).

As you've probably already noticed, you're not asking about Notes...

Dave Kreskowiak
Microsoft MVP - Visual Basic

Hi All,

I have an unusal problem with my forms.

I press a button2 to display a form2 (which displays as topmost)
Then ill press button3 to close form2 and display form3 (form3 also display as top most) and this works fine.

However...if say i have form2 open and i press button4 to display form4, then close form4, and then press button3 (which closes form2 and siplays form3) what happens here instead is that form3 displays BUT form2 does not close.

this is the sort of code im using

f.Close()
f = New Form2
f.Show()

Was hoping somebody maybe able to help me with this problem.

Thanks in advance.

Jaidev

Without seeing the code for all the button click event handlers, it's impossible to tell you what's wrong from the little snippet you've provided.

Dave Kreskowiak
Microsoft MVP - Visual Basic

My guess is that based on the little code you posted you are using 'f' to represent the open form. So you press button2 and 'f' gets set to form2 which is then shown. When you press button3, you call f.close which closes form2 then you set 'f' to equal form3. However, the problem is that when you open form4 which you probably set 'f' equal to, you don't close form2. Now 'f' references form4 not form2. So when you click button3 and call f.close it try's to close form4 which is actually already closed.

Hello,

I am getting following error when hit the update button.

No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

Any idea Why? thanks.

here is my code:

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
Dim data As New DataSet
Dim dbConn As OleDb.OleDbConnection
Dim dataAdapter As OleDb.OleDbDataAdapter
Dim connectionString As String = "Provider=sqloledb;Data Source=A45292\SQLEXPRESS;Initial Catalog=FSS;Integrated Security=True"

Dim sqlString As String = "SELECT * FROM [Caller Records] Where RecordNo =" & TextBox2.Text

dbConn = New OleDb.OleDbConnection(connectionString)
dataAdapter= New OleDb.OleDbDataAdapter(sqlString, dbConn)
dataAdapter.Fill(data, "Caller Records")

Dim tbl As DataTable
tbl = data.Tables("Caller Records")

Dim selectedRows() As DataRow
selectedRows = tbl.Select("RecordNo = " & TextBox2.Text)

If selectedRows.Length > 0 Then
selectedRows(0).Item("FirstName") = FirstNameTextBox.Text
selectedRows(0).Item("LastName") = LastNameTextBox.Text
End If
dataAdapter.Update(data, "Caller Records")
dbConn.Close()

programmer

First, why are you using the Ole data providers when your using a MS SQL Server? Use the Sql Server providers (SqlConnection, SqlCommand, Sql...) for better performance.

Next, you're retrieving all this data just to find a record and write it back when you don't have to. The DataSet object that you filled with your DataAdapter already has this data in it. If the textbox's are bound to the DataSet, the updates have already been made. Just call the Update method on your DataAdapter and it'll write the changed records back to the database. Just about all of this code is unnecessary.

Dave Kreskowiak
Microsoft MVP - Visual Basic

because textboxes are not bound.

programmer