Go Premium for a chance to win a PS4. Enter to Win

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

Executing commands in Data Environment

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
yhel7
Asked:
yhel7
  • 5
  • 3
  • 2
  • +4
1 Solution
 
TimCotteeCommented:
Absolutely the latter. Use ADO connection and code rather than the dataenvironment.
0
 
hiranmayaCommented:
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
 
lalithawCommented:
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.

 
yhel7Author Commented:
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
 
yhel7Author Commented:
to lalithaw:

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

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
 
K7Commented:
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
 
yhel7Author Commented:
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
 
yhel7Author Commented:
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
 
K7Commented:
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
 
yhel7Author Commented:
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
 
K7Commented:
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
 
DanRollinsCommented:
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
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now