Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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?
0
rfedorov
Asked:
rfedorov
  • 16
  • 11
  • 2
  • +1
3 Solutions
 
Patrick MatthewsCommented:
ssql = "INSERT INTO Table1 ([Name], [Number], [Date]) " & _
    "VALUES ('" & name & "', " & intNumber & ", #" & DateR & "#)"
0
 
rfedorovAuthor Commented:
thank you, but the same error
0
 
Éric MoreauSenior .Net ConsultantCommented:
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.

 
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 MatthewsCommented:
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
 
Éric MoreauSenior .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
 
rfedorovAuthor Commented:
still same error and i removed constraint
0
 
jpaulinoCommented:
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
 
jpaulinoCommented:
:) 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 16
  • 11
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now