How do I add new record of date

Question

0.00/5 (No votes)

See more:

Private Sub AddRecord_Click()
RSSQL = "SELECT * FROM D"
ASSIGNRS.Open RSSQL, DB, adOpenDynamic, adLockOptimistic
ASSIGNRS.AddNew "DOB", "'" & Text1.Text & "'"
ASSIGNRS.Close
End Sub

This is the simple code for adding date fields into table D having only column DOB.
In table D,SQL accepts date as '01-JAN-99' format.So I set data_format property of combo1 as date:dd-MMM-yy
But still While running this code error occurred- Multiple-step operation generated error, check each status value.
Though both sided date formats are same then why it is not accepting addnew record?
Please help

Posted 28-Dec-12 23:40pm

surkhi

Updated 28-Dec-12 23:51pm

Abhishek Pant

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-12-28T23:53:00

Solution 1

Try using TO_DATE function. So something like

ASSIGNRS.AddNew "DOB", "TO_DATE('" & Text1.Text & "', 'DDMMYYYY')"

In that example the text should be formatted like

26032011

Posted 28-Dec-12 23:53pm

Wendelius

Comments

surkhi 29-Dec-12 6:39am

This code is also not working,same error occurred.
SQL not taking any date field input,rather in SQL window records get added by writing query
Insert into D values('12-dec-2012');
But it is not added through vb code.
What should I do?

Wendelius 29-Dec-12 7:22am

What interface are you using ADO with VB6? if that's true I think both field list and the values should be arrays. Have a look at http://msdn.microsoft.com/en-us/library/windows/desktop/ms677536(v=vs.85).aspx[^]

You could also try a different, ADO syntax for the date: #01/01/2011#

surkhi 29-Dec-12 7:26am

I am using microsoft activex data objects2.7 library from referenses.

Wendelius 29-Dec-12 7:30am

Have a try with the ADO date syntax if that helps...

surkhi 29-Dec-12 7:30am

Dim DB As New ADODB.Connection
Dim ASSIGNRS As New ADODB.Recordset

Private Sub OPENDB()
DB.CursorLocation = adUseClient
DB.Mode = adModeReadWrite
If ASSIGNRS.State = 1 Then ASSIGNRS.Close
DB.Open "Provider=OraOLEDB.Oracle.1;Password=aus123 ;Persist Security Info=True;User ID=aus ;Data Source=soft"
End Sub

Private Sub Combo1_Click()
Dim ASSIGNRS As New ADODB.Recordset
Dim RSSQL As String
RSSQL = "SELECT *FROM D WHERE DOB='" & Combo1.Text & "'"
Set ASSIGNRS = DB.Execute(RSSQL)
End Sub

Private Sub Command1_Click()

RSSQL = "SELECT * FROM D where DOB='" & Combo1.Text & "'"
ASSIGNRS.Open RSSQL, DB, adOpenDynamic, adLockOptimistic

If Combo1.Text = Empty Then

ASSIGNRS.AddNew "DOB", "TO_DATE('" & Text1.Text & "', 'DDMMMYYYY')"

MsgBox ("RECORD ADDED")
Else
If Combo1.Text <> Empty Then

ASSIGNRS.Update "DOB", "'" & Combo1.Text & "'"

MsgBox ("RECORD INSERTED")

End If
End If
ASSIGNRS.Close
End Sub

Private Sub Form_Load()
OPENDB
Dim ASSIGNRS As New ADODB.Recordset
Dim RSSQL As String
RSSQL = "SELECT * FROM D"
ASSIGNRS.Open RSSQL, DB, adOpenDynamic, adLockOptimistic
Set ASSIGNRS = DB.Execute(RSSQL)
If ASSIGNRS.RecordCount > 0 Then
ASSIGNRS.MoveFirst
For I = 0 To ASSIGNRS.RecordCount - 1
Combo1.AddItem ASSIGNRS.Fields("DOB").Value
ASSIGNRS.MoveNext
Next
End If
End Sub

This is my full code

Espen Harlinn 29-Dec-12 7:38am

5'ed!

Wendelius 29-Dec-12 7:54am

Thank you :D

surkhi 31-Dec-12 6:35am

That is also not working......
What should I do?

Wendelius 6-Jan-13 11:13am

What is the error message you now get?