Solved

Executing commands in Data Environment

Posted on 2002-05-28
14
294 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

828 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