[RESOLVED] error when using DataEnvironment

Hi. I have the following code:
DataEnvironment1.Connection1 = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=agenda_templ"
DataEnvironment1.Commands.Item(1).CommandType = adCmdTable
DataEnvironment1.Commands.Item(1).CommandText = "select * from Agenda_angajati where cod_ang= '" & Text2.Text & "' "
DataEnvironment1.Command1

When the program tries to execute
DataEnvironment1.Command1
I get this error:
"Syntax error in FROM clause!"
But I do not understand what is wrong. Could someone help me please? Thank you!:confused:

Are you sure spelled everything correctly in your SELECT statement?

Did you do a Debug.Print DataEnvironment1.Commands.Item(1).CommandText just before you attempted to execute the SQL statement?

No I did not.

Try it and post what you get in the immediate window.

Hint: Put a breakpoint on the line where it attempts to execute the statement.

I have tried now and the error is
"Failed getting rowset from current data source. But the connection is ok.

That's not quite write. Post your code around that area.

the program can successfully pass
Debug.Print DataEnvironment1.Commands.Item(1).CommandText
The error appears when it tries to execute
DataReport1.show
But this is something else.
i will have a look into this again.

Sorry. This is the code once more.

Dim tabela As String
tabela = "Agenda_angajati"


' DataEnvironment1.Connection1.Close
DataEnvironment1.Connection1 = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=agenda_templ"
DataEnvironment1.Connection1.Open
DataEnvironment1.Commands.Item(1).CommandType = adCmdText
DataEnvironment1.Commands.Item(1).CommandText = "select cod_ang from " & tabela & " where cod_ang= '" & Text2.Text & "' "

Debug.Print DataEnvironment1.Commands.Item(1).CommandText

'DataEnvironment1.Connection1.Open
DataEnvironment1.Command1

DataReport1.Show
DataReport1.LeftMargin = 1200
DataReport1.TopMargin = 1000
DataReport1.BottomMargin = 1000

The question was not if it would pass but what the output was...

The error I get is the one I have described above.
""Failed getting rowset from current data source"
I have tried to define these parameters, but I still get the error.
Set DataReport1.DataSource = DataEnvironment1
DataReport1.DataMember = Command1

And yet you fail to do the simple things I asked...

The output of this line is supposed to be your SQL statement


Debug.Print DataEnvironment1.Commands.Item(1).CommandText

I did. I have placed a break point for
Debug.Print DataEnvironment1.Commands.Item(1).CommandText
Nothing happens. The program just goes on. I get the error and the report is blank. Sorry. Maybe I do not understand to well.

What does nothing happen mean? The IDE will stop on the breakpoint. BTW the breakpoint is supposed to be on the next line anyway...

Nothing means that the program will execute this line without showing any message. everyting goes well until it finds the line
DataReport1.DataMember = Command1
Please let me know if this explains it for you. Thanks!

No, You are running in the IDE. Debugging the program correct? And you say the program doesn't stop? If it doesn't stop then none of that code is getting executed.

If the code is not executed, how do I get that error? I have run the code using F8 key. The code is executed.

You may not be setting a breakpoint. If you are getting past that then all else is suspect. If you set a breakpoint and the code get's there the IDE will stop.

How are you attempting to debug this?

I have set a break. The program stops. This is not a problem. But I get no error message when the program executes this line.

Ok. Let me ask you something else. Is it possible to use this command in the CommandText properties of DataEnvironment?
select * from " & tabela & " where cod_ang= '" & Text2.Text & "'
Where "tabela" is the name of the table and text2.text=a value which I give to the program. If not, could I use at least:
select * from Agenda_angajati where cod_ang= '" & Text2.Text & "'
To use at least a variable?
I have tried but in the first case I get the error
"Failed getting rowset from current data source"
I the second case the result is blank as if it wouldn't find the variable text2.text.
Thank you!

Why is this so hard for you to do? After executing that line show the output of that statement from the immediate window....

My friend, please check the attached file. There is no message. It just runs. Thank you!:confused:

That's a video clip. What am I supposed to do with that? I would need to see the actual project.

You were interested in seeing the message after debug command. Just please answer me this: is it possible to use in a DataEnvironment, in the sql section a command like this: "select * from Agenda_angajati where cod_ang= '" & Text2.Text & "'" where text2.text is a variable. I have tried and it did not work. Am I doing something wrong or it just won't work?

Hi. Yes I am sure.

Quote:

---

Originally Posted by gavio

Are you sure spelled everything correctly in your SELECT statement?

---

Yes I am sure.

I don't know most people don't use the native bound data controls for this very reason. I need to see if you formulated the sql query correctly in the data environment to see if that may be the cause of your problem.. But this is very hard to do...

Then could you please tell me how could I create a report in VB without using DataEnvironment?

Crystal Reports... But you would need to purchase the package.

Why not just post the project an have someone look at it...

Without using aditional software. Only with VB? There is no other way? The project contains only simple operations, save, update. And I want to create a report using one or more table for one or more records. That is why I need to use a variable. If I use for example
" Select * from Agenda_Angajati " then I see the report with all records from that table. If I use
" Select * from Agenda_Angajati where cod_and='198'" then I see the report for one record only. But I need to allow to the users to choose if they want the report for one record or not. The report is empty when I use
" Select * from Agenda_Angajati where cod_and='" & text2.text & "'". As you have seen in the video clip the sql commands are ok. The variables are initiated. But it looks like DataReport is blind. If I use sql expression in the DataEnvironment , without writing the code by myself in vb, but using the properties window of DataEnvironment , then DataEnvironment cannot read the value from Text2.text which is placed on the form. So this project is not a big deal. My real question is why I can generate the report using DataEnvironments and constants and not with variables? How could I make DataEnvironment read a variable, Text2.text for example, from a form. Text2.text is used in case the user wants the report for only one record. Thank you!

You know this is like pulling teeth...

Why can't you just post the project?

Quote:

---

Originally Posted by randem

You know this is like pulling teeth...

Why can't you just post the project?

---

This is the code from my project which I use for generating the report. I will upload the table as well.

Quote:

---

Originally Posted by randem

You know this is like pulling teeth...

Why can't you just post the project?

---

Here it is. Thank you!

A few things:

Use Option Explicit at the top of all forms/modules/classes
You should get an error on this line

DataEnvironment1.Connection1.Close

for there is no connection open and you attempt to close one.

Your sql statement looks like:

select * from Agenda_angajati where cod_ang= '198'

Your database seems to be setup incorrectly. Why are your fields defined as text when they only hold numbers?

I can get it to stop in the debugger. Make sure you have your IDE to stop on unhandled errors.

If your gonna go through the trouble of manipulating the data environment at run tmie rather than leaving it as it is as determined at design time, then might as well skip it and manipulate the data report directly.

http://www.vbforums.com/showthread.p...ataenvironment

The misconception of most people is that they need the data environment to use the data report.

Right now I am using just string for testing purposes. Thanks for the tip wit Option Explicit. I do not see how could it be setup incorrectly. What exactly is wrong?

Well, leinad31's solution seems to work. At least after my first test. Thank you very much to all of you. I am a happy person. Thanks a lot. My best regards too all! :)

Look at that... Post your project and a few post later the issue is resolved. so this could have been done in 5 post instead of 38...