Please help to find the error in the code

good morning, ***!
need your help
i have a form with three text boxes: txtName,txtDate, txtnumber and button 'Add'
when you enter all values:  let say txtName=John, txtdate=21/2/2008 and txtnumnber=5
(forget about validation)
and click button everthign should be save in table1 in database DB
Below  the code for Form1

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset


Private Sub cmdAdd_Click()
Dim ssql As String
Dim name As String
Dim DateR As Date
Dim intNumber As Integer

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.Oledb.4.0; data source=" & App.Path & "\DB.mdb"

name = txtName.Text
DateR = txtDate.Text
intNumber = txtNumber.Text



ssql = "insert into Table1 ([Name],[Number],[Date])"
ssql = ssql & " values ('" & name & "','" & intNumber & "','#" & Date & "#'" & ")"
conn.ADOConnection.Execute ssql
End Sub

Private Sub Form_Load()
Path = App.Path
End Sub


i am getting the error on conn.ADOConnection.Execute ssql-------run-time error 3001
arguments are of the wrong type, are out of acceptancerange, or are in conflict with one another

what seems to be a problem?
rfedorovAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
if you tell me that this exact same query works in Access (exactly same values), then I don't know what to say!

If you still have the error "run-time error 3001: arguments are of the wrong type, are out of acceptancerange, or are in conflict with one another" then it has to do with your constraints.
0
 
Patrick MatthewsCommented:
ssql = "INSERT INTO Table1 ([Name], [Number], [Date]) " & _
    "VALUES ('" & name & "', " & intNumber & ", #" & DateR & "#)"
0
 
rfedorovAuthor Commented:
thank you, but the same error
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Éric MoreauSenior .Net ConsultantCommented:
can you dump the value contained in your ssql variable here?

is your date in yyyy/mm/dd format?
0
 
rfedorovAuthor Commented:
Ok:
 john
10/17/2008
6
my date in short date format mm/dd/yyy
0
 
Éric MoreauSenior .Net ConsultantCommented:
I want to see the real content of your variable.

stop your application on this line:
conn.ADOConnection.Execute ssql

and output the variable in your immediate window, copy and paste the value here.
0
 
rfedorovAuthor Commented:
?ssql
INSERT INTO Table1 ([Name], [intNumber], [DateR]) VALUES ('John', 25, #10/17/1962#)


please see attachment
error.doc
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you try to format your date as yyyy/mm/dd?

Do you have constraints on your fields?

can you try your query directly in Access (maybe we will get a more accurate error)?
0
 
rfedorovAuthor Commented:
i do not have any constraints for the date

in access the query is working fine
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you try to format your date as yyyy/mm/dd?
0
 
rfedorovAuthor Commented:
to format where?
in the program or in ms axxess?
0
 
Éric MoreauSenior .Net ConsultantCommented:
DateR = Format(CDate(txtDate.Text), "yyyy/mm/dd")
0
 
rfedorovAuthor Commented:
same error, may be to put date in quots?
0
 
Patrick MatthewsConnect With a Mentor Commented:
rfedorov,

Just for giggles, please list the data types for the three columns.  I've been assuming text, Long, and Date,
but what the heck...

Regards,

Patrick
0
 
rfedorovAuthor Commented:
text
number:integer small number >100
date:short date
0
 
Éric MoreauSenior .Net ConsultantCommented:
what is your ">100" ?

I just saw something else, change
Dim DateR As Date

for
Dim DateR As String
0
 
rfedorovAuthor Commented:
">100"  means it is an integer, all values less than 100
0
 
Éric MoreauSenior .Net ConsultantCommented:
shouldn't it be <100 ?

You told me that you had no constraints on the table. Here is one. Do you have others?

And what about "Dim DateR As String"?
0
 
rfedorovAuthor Commented:
this is not really a constraint, you will not be able to enter anything more than 100 in the filed
I can not check it right now, thank you for your support
i am going to do that the very fitst thing in the morning
0
 
rfedorovAuthor Commented:
ok I change the data type to String as you said, but no luck-same mistake
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you post your new code?

are you sure that the same query (same values) are working directly in Access?
0
 
rfedorovAuthor Commented:
my new code here, the query in Access is working


Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset

Private Sub Form_Load()
    Path = App.Path
End Sub
Private Sub cmdAdd_Click()
Dim ssql As String
Dim name As String
Dim DateR As String
Dim intNumber As Integer

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.Oledb.4.0; data source=" & App.Path & "\DB.mdb"

name = txtName.Text
intNumber = txtNumber.Text
DateR = Format(CDate(txtDate.Text), "yyyy/mm/dd")


ssql = "INSERT INTO Table1 ([Name], [intNumber], [DateR]) " & _
    "VALUES ('" & name & "', " & intNumber & ", #" & DateR & "#)"

     conn.adoConnection.Execute ssql
End Sub
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried replacing the # with '
0
 
rfedorovAuthor Commented:
please see the attachment
code.doc
0
 
rfedorovAuthor Commented:
still same error and i removed constraint
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Just one note: you started to post using [Date] and now in all your code you use ],[DateR]. What are the name of the field on the db ?
0
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
:) is just [DateR]

wrong copy/paste
0
 
rfedorovAuthor Commented:
Database: DB
Table: Table1
Fields:
name, text
intNumber, number
DateR, text
0
 
Éric MoreauSenior .Net ConsultantCommented:
A little explanation why you ask for closure would be great! Have you found something?
0
 
rfedorovAuthor Commented:
Thank you everybody, i remake whole program
0
 
rfedorovAuthor Commented:
Instead of "conn.ADOConnection.Execute ssql' i am using now "normal"  procedures like
rs.addnew and rs.update.
it is not what i was thinking to do, but it is working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.