Re: Validate date from VB to insert into Oracle table

#1

At the risk of asking an obvious question have you tried format() ?

I have been doing a bit of VB<>mySQL and its mySQL dates are non VB
friendly also.

Whenever I need to store a date value in mySQL i use something like:
' Set up the SQL update.
strSQL="UPDATE the_table SET this_date ='" &
format(strThisDate,"YYYY-MM-DD") & "' WHERE index = '" &
intIndexPointer &"'"
' Now send it to the database
dbConn.execute strSQL

Originally posted by Me

Quote:

I have a problem in that the date format of VB and that of oracle 8i
don't match

For example:
Private Sub Command1_Click()
' Check user input
If Not IsDate(Text1) Then
MsgBox "Sorry! That is not a valid date or Date/Time.", vbCritical
& vbOK, "Error"
Exit Sub
End If

Validates a date for VB but the same date format is not compatible
with Oracle and I get an error when I try to insert values.
How can this be solved?

Next, when ever I try to insert wrong values into tables from vb
Eg a null value into an attribute that cannot be null, I get an error
and the VB project is ended. How can I prevent this?
i.e.
Is there a way to get the error message (from oracle) show it to the
user and then continue running the project?

This question is probably for a VB group, but you know, I kind of feel
at home here; after being flamed many times

Thanks.

--
Posted via http://dbforums.com

#2

I didn't read the entire post and replies, but saw that you were having
troubles getting a Date in VB format into oracle. Is this true?

If so, try this

You could instead of using a Text Box, use a Masked Edit Control and
specify ##/##/#### as the mask. That will only enable them to enter
numerics. and the formatting will be already there in VB Date format.

Then do the below to convert it to Oracle Date format

Dim strDate as string

'********************* COMMENTS **************************'
'format the date into the oracle format dd-mmm-yyyy (unless it is set
differently on your system.
'********************* COMMENTS **************************'

strDate = Format(Date, "dd-mmm-yyyy")

'********************* COMMENTS **************************'
'then perform your insert string something like
'********************* COMMENTS **************************'

"INSERT INTO table1 (fieldintablefordate) VALUES ('" & strDate & "')"

'********************* COMMENTS **************************'
after VALUES ... that is a "single quote" and then a "double quote" to
end the string then "&" to concatenate the string with the value in
strDate then "&", open double quote, single quote close parenthesis, and
close the string w/ another dbl quote.
'********************* COMMENTS **************************'

--
Posted via http://dbforums.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Validate date from VB to insert into Oracle table

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Validate date from VB to insert into Oracle table - 07-04-2003 , 10:22 AM

Re: Validate date from VB to insert into Oracle table - 07-15-2003 , 09:33 AM