Solved

Please help to find the error in the code

Posted on 2008-06-18
31
242 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 92

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 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 200 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 150 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 79
SQL Recursion 6 33
SQL Query Syntax Assistance 2 34
Sql case statement to calculate totals 5 33
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

763 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