Hi all,

In my C# application I have a SQL database with a table called Customers. In this table I have several columns like Name, Street, ZipCode and City.

I already managed to get data in and out the database with the use of datasets, tableadaptors and bindingsources and get them into textboxes and listboxes. So far no problem.

But…. the thing I want is to run a SQL query like below and get all the results in a single textbox or listbox. The important things is, that every entry in the textbox or listbox should be on a new line. Multi line for example.

So the result in the textbox should look like:

Ramon
Streetname
NewYork
1111AA

This is the statement I want to run where @1 is a variable.

SELECT Name, Street, ZipCode, City
FROM Customers
WHERE (CustomerID = @1)


I hope someone can point me to the right direction.

Cheers,
Ramon.

In your dataset you can add some queries or paramatized queries and pass the value of its variable(s) at the runtime, on your DataTable right click then Add query and go through it....
Concerns Textbox you should do some process on the data return from database before showing them on the textbox, first to have multiple lines on textbox, set the from its properties or use RichTextBox..

Hi RamyMahrous,

Thanks for your fast reply!

I already found out how to add the queries and their variables to the DataSet and run them. When running the queries I need a textbox for each collumn to display it's corresponding value.

But I need it to display in a single box, this can also be a Richtextbox, but I have no idea how.

Cheers,
Ramon.

This assumes none of the columns allow NULLS. Otherwise you will need to use ISNULL(name, '') + '\n' + ... etc

Set the TextMode property of you textbox to Multiline (it will render to the browser as a textarea) set the rows property of the textbox to 4.

Hi hollystyles,

Thanks for your query help, I got the statement below working and it gives me the desired result in in querybuilder.

SELECT ISNULL(Naam, Naam) + '\n' + ISNULL(Aanhef, Aanhef) + '\n' + ISNULL(Straat, Straat) + '\n' + ISNULL(Postcode, Postcode) + '\n' + ISNULL(Plaats, Plaats) AS Expr1
FROM Klanten
WHERE (KlantID = @1)

I run it using:

this.klantenTableAdapter.FillByKlantAdres(this.dorpshuisDataSet.Klanten, KlantID);

But I have no clue on how to get the result in a textbox, listbox or label ect.... I can only bind the textboxes to a single collumn from the bindingsource.

Can someone help me to get the result of the query in a textbox?

Ramon

The query has the single column you want 'Expr1' bind the textbox to that.

I am not very knowledgeable with BindingSources I am afraid, I don't like them. I prefer to have simple business objects.

So I would not do the concatenation in the TSQL (it doesn't really belong there, it belongs in the presentation layer or as a business rule) So I would have an Address class and an AddressSource class, the AddressSource is in the DataLayer and uses ADO to connect to the DB and lift the data and returns an address object, then make the Address object provide the concatenated address with carriage returns.

I attach an example project I knocked up in response to this thread. There is a .sql script in the AppGloablResources folder that creates the table and populates a demo record.

Oops perhaps you are doinf a Forms project not a web project.

I have started a forms project and I can't get the binding source to use the new query either yet. I am too tired at the moment.

One solution could be a computed column in the sql table, but that smells a bit to me.

Yes indeed I am making a Form app.

At least you point me a little closer to the solution, the query result is indeed stored in the Expr1. So I created a collumn Expr1 in my klantenDataTable where the query is also stored.

When I do a preview data from there I can run the query and see it's results in the Expr1 collumn. Now I can also bind the textbox to this collumn trough the bindingsource, but when I run the program and then the text shows only a blank line.

This is my first real program I am creating so sorry for my ignorance. But I am having fun with it.

You said you don't like bindingsources but instead you use simple business objects. What does that mean?

Ramon

It's just a preference. I like things to be as loosley coupled as possible. I prefer to bind to collections of objects, rather than strongly typed datasets. Like most things it depends on what your requirements are. I wouldn't worry about it too much as this is your first real program.

I'm sure the presentation of the address can be done in a better way, using a bindingsource. When I have figured it out I'll post my solution here. (I do mostly web apps so I'm a bit rusty with forms where the platform is a little different)

Instead of mangling the SQL statement (embedding CRLF), just iterate through the rows and columns, and populate the text box.
Here is a simple example:



// Jerry