Solved

Executing commands in Data Environment

Posted on 2002-05-28
14
326 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
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: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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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 Month5 days, 16 hours left to enroll

627 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