?
Solved

Please help to find the error in the code

Posted on 2008-06-18
31
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 11
  • 2
  • +1
31 Comments
 
LVL 93

Expert Comment

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

Author Comment

by:rfedorov
ID: 21814567
thank you, but the same error
0
 
LVL 70

Expert Comment

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

is your date in yyyy/mm/dd format?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rfedorov
ID: 21815316
Ok:
 john
10/17/2008
6
my date in short date format mm/dd/yyy
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21815448
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
ID: 21815522
?ssql
INSERT INTO Table1 ([Name], [intNumber], [DateR]) VALUES ('John', 25, #10/17/1962#)


please see attachment
error.doc
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21815565
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
ID: 21815658
i do not have any constraints for the date

in access the query is working fine
0
 
LVL 70

Expert Comment

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

Author Comment

by:rfedorov
ID: 21816421
to format where?
in the program or in ms axxess?
0
 
LVL 70

Expert Comment

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

Author Comment

by:rfedorov
ID: 21816532
same error, may be to put date in quots?
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 450 total points
ID: 21816567
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
ID: 21816612
text
number:integer small number >100
date:short date
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21818093
what is your ">100" ?

I just saw something else, change
Dim DateR As Date

for
Dim DateR As String
0
 

Author Comment

by:rfedorov
ID: 21818217
">100"  means it is an integer, all values less than 100
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21818329
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
ID: 21818424
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
ID: 21822464
ok I change the data type to String as you said, but no luck-same mistake
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21822630
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
ID: 21822777
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 70

Expert Comment

by:Éric Moreau
ID: 21822806
have you tried replacing the # with '
0
 

Author Comment

by:rfedorov
ID: 21822890
please see the attachment
code.doc
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 600 total points
ID: 21823016
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
ID: 21823127
still same error and i removed constraint
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21823248
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 450 total points
ID: 21823258
:) is just [DateR]

wrong copy/paste
0
 

Author Comment

by:rfedorov
ID: 21823314
Database: DB
Table: Table1
Fields:
name, text
intNumber, number
DateR, text
0
 
LVL 70

Expert Comment

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

Author Closing Comment

by:rfedorov
ID: 31468427
Thank you everybody, i remake whole program
0
 

Author Comment

by:rfedorov
ID: 21825076
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

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