Solved

Please help to find the error in the code

Posted on 2008-06-18
31
234 Views
Last Modified: 2013-12-25
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?
0
Comment
Question by:rfedorov
  • 16
  • 11
  • 2
  • +1
31 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
ssql = "INSERT INTO Table1 ([Name], [Number], [Date]) " & _
    "VALUES ('" & name & "', " & intNumber & ", #" & DateR & "#)"
0
 

Author Comment

by:rfedorov
Comment Utility
thank you, but the same error
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
can you dump the value contained in your ssql variable here?

is your date in yyyy/mm/dd format?
0
 

Author Comment

by:rfedorov
Comment Utility
Ok:
 john
10/17/2008
6
my date in short date format mm/dd/yyy
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
?ssql
INSERT INTO Table1 ([Name], [intNumber], [DateR]) VALUES ('John', 25, #10/17/1962#)


please see attachment
error.doc
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
i do not have any constraints for the date

in access the query is working fine
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
can you try to format your date as yyyy/mm/dd?
0
 

Author Comment

by:rfedorov
Comment Utility
to format where?
in the program or in ms axxess?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
DateR = Format(CDate(txtDate.Text), "yyyy/mm/dd")
0
 

Author Comment

by:rfedorov
Comment Utility
same error, may be to put date in quots?
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
text
number:integer small number >100
date:short date
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
what is your ">100" ?

I just saw something else, change
Dim DateR As Date

for
Dim DateR As String
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rfedorov
Comment Utility
">100"  means it is an integer, all values less than 100
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
ok I change the data type to String as you said, but no luck-same mistake
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
can you post your new code?

are you sure that the same query (same values) are working directly in Access?
0
 

Author Comment

by:rfedorov
Comment Utility
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
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
have you tried replacing the # with '
0
 

Author Comment

by:rfedorov
Comment Utility
please see the attachment
code.doc
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 200 total points
Comment Utility
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
 

Author Comment

by:rfedorov
Comment Utility
still same error and i removed constraint
0
 
LVL 48

Expert Comment

by:jpaulino
Comment Utility
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
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 150 total points
Comment Utility
:) is just [DateR]

wrong copy/paste
0
 

Author Comment

by:rfedorov
Comment Utility
Database: DB
Table: Table1
Fields:
name, text
intNumber, number
DateR, text
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
A little explanation why you ask for closure would be great! Have you found something?
0
 

Author Closing Comment

by:rfedorov
Comment Utility
Thank you everybody, i remake whole program
0
 

Author Comment

by:rfedorov
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now