Solved

Executing commands in Data Environment

Posted on 2002-05-28
14
305 Views
Last Modified: 2008-03-03
I am using Data environment in my project. I added Insert command with parameters. "INSERT INTO EMPLOYEE VALUES( ?, ?, ?, ? )"
To execute this command in my code, I tried the following:
MyDE.InsertEmp "12345", "aaa", "bbb", "ccc"
but it did not work. it always generates an error.
What is the proper way of executing Insert or Delete commands in Data environment?
Thanks!

by the way, i have another question, which is better to use for accessing data in a database, using Data Environment or using ADO connections, and recordset in code?
0
Comment
Question by:yhel7
[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
  • 5
  • 3
  • 2
  • +4
14 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7038712
Absolutely the latter. Use ADO connection and code rather than the dataenvironment.
0
 
LVL 1

Expert Comment

by:hiranmaya
ID: 7038747
If you are using DataEnvironment: Then use as follows


Dim de1 As DataEnvironment1
Dim cmd1 As Command
Set de1 = New DataEnvironment1
Set cmd1 = de1.Commands(1)
cmd1.CommandText = "Insert into TABLE1 (ABC) VALUES('CDE')"
cmd1.Execute
Set cmd1 = Nothing

You can use this for delete command too.

Have a happy programming!
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7038784
Hi.,

Before u insert values u should add new row to recordset.It is like this
MyDE.rsInsertEmp.AddNew
then use the update

MyDE.rsInsertEmp.update '12345', 'aaa', 'bbb', 'ccc'
And check the Advance properties in ur command object to ensure the cursor type and lock type.Cursor type should be keyset or Dynamic to insert records and put lock type as Optimistic to improve the performance.

But I am not recommending this because this consume lot of resources.And this is very inefficent in multiuser enviorenment.In multiuser enviorenment always use ADO codes.That is very efficent and very helpfull to improve performance.

Lalitha.
 

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:yhel7
ID: 7038840
to hiranmaya,
The insert statement was already set at design time in Data Environment. I want to use that statement. Is there any way to call the data enviroment command without defining another command object in the code?
0
 

Author Comment

by:yhel7
ID: 7038844
to lalithaw:

thanks for the answer, but does it work on data environment recordset? I tried it but it did'nt work.
0
 
LVL 1

Expert Comment

by:lalithaw
ID: 7038869

Hi .,

r u really want to insert those values without user interaction.If not u can drag and drop ur command object to a form and just write and execute

MyDE.rsInsertEmp.addnew in command button

and after that

execute

MyDE.rsInsertEmp.update in another command button after inserting values in form.


If u want to do that without user interaction just write this code in ur command button.

MyDE.rsInsertEmp.update 12345, aaa, bbb, ccc

and did u check ur command advance properties that cursor type and lock type?

Lalitha.

0
 
LVL 1

Expert Comment

by:K7
ID: 7042000
Hi
i think what u might be trying to do is execute a command with parameters.  To do that u have to create and append a parameter object for each parameter in ur command.
Look up the specs for the CreateParameter and Append methods as in ur case the parameters arn't named and it might be the order in which the ojects are added to the colection that determands which value goes where but i'm not 100% on that.

any ways the code would look something like this for each parameter:

'create parameter object
Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

'append the prameter object to the colection
command.Parameters.Append parameter

i hope this helps
Cat
0
 

Author Comment

by:yhel7
ID: 7043548
Thanks for taking time for answering my question.
yup k7, i'm trying to execute a command with parameters. But this command is a DATA ENVIRONMENT command. And in data environment designer, you can set the parameters in command's properties, so i don't think i need to a parameter object in my command. I saw in msdn that you can call a DATA ENVIRONMENT command with parameters through the following code:

MyDE.Insert_cmd "123456", "aaa", "bbb", "ccc"

where
  MyDE = a data environment instance
  Insert_Cmd = a data environment command which is      declared like the following:
 (INSERT INTO EMP VALUES(?,?,?,?)
0
 

Author Comment

by:yhel7
ID: 7043551
Thanks for taking time for answering my question.
yup k7, i'm trying to execute a command with parameters. But this command is a DATA ENVIRONMENT command. And in data environment designer, you can set the parameters in command's properties, so i don't think i need to a parameter object in my command. I saw in msdn that you can call a DATA ENVIRONMENT command with parameters through the following code:

MyDE.Insert_cmd "123456", "aaa", "bbb", "ccc"

where
  MyDE = a data environment instance
  Insert_Cmd = a data environment command which is      declared like the following:
 (INSERT INTO EMP VALUES(?,?,?,?)

But the statement above didn't work. What are the correct way in executing a data environment command with parameters?
0
 
LVL 1

Expert Comment

by:K7
ID: 7046407
Hi
i'm sorry u were abosolutely correct that u don't need to create parameter objects if u r using the DatEnv. the bad news is that i tried ur code exactly as u did just using numbers instead of ur strings and it wourked fine.  so if u could tell us what the error mesage is we might be able to be of more assistance.

also i noticed that ur first value was a number and that u were passing it as a string so i thought that this might be causing a problem but when i tried to send a string in vb to a numeric field in the db it also worked.

Cat
0
 

Author Comment

by:yhel7
ID: 7046499
it's alright Cat. I tried using numbers too instead of strings, and it also worked fine. And i already figured out what's causing the error "3704: Appliccation defined of object defined error". In the command properties window, parameters tab, i did not set the size property, it's default is zero, and my data tyope is advarchar. since the size is set as zero, it did not accept my string parameter.

Thank you so much for the help! more power.

(should i give you the points?)
0
 
LVL 1

Expert Comment

by:K7
ID: 7046965
HI
i'm glad that the problem resolved itselve

i think that you should simply delete the question, as you yourselfe provided the solution.

Cat
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7851180
yhel7, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 7912777
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

752 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