Solved

Executing commands in Data Environment

Posted on 2002-05-28
14
280 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

777 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