SQL Data into Textbox or Listbox

[Ramon78]

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.

[Ramy Mahrous]

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..

[Ramon78]

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.

[hollystyles]

SELECT Name + '\n' + Street + '\n' + ZipCode +'\n' + City
FROM Customers
WHERE (CustomerID = @1)

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.

[Ramon78]

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

[hollystyles]

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.

[hollystyles]

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.

[Ramon78]

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

[hollystyles]

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)

[JerryShaw]

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:

private void toolStripButton1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection("Data Source=SHAWHP;Initial Catalog=SalonWiz;Integrated Security=True");
            conn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(
                          "SELECT top 10 * from Employee",conn);
            adapter.Fill(ds);
            foreach(DataRow row in ds.Tables[0].Rows)
            {
                for(int i=0;i< ds.Tables[0].Columns.Count;i++)
                    textBox1.Text += row[i].ToString()+Environment.NewLine;

                textBox1.Text += Environment.NewLine;
            }
        }

// Jerry

[Ramon78]

JerryShaw,

I understand the part of filling up the textbox in your code.

But I cannot follow the code below; I do not see its relationship with my database.

“SqlConnection conn = new SqlConnection("Data Source=SHAWHP;Initial Catalog=SalonWiz;Integrated Security=True");”

Ramon

[Ramon78]

Oke I got the connection string, but now I am faced a new problem, running the app gives me this message when executing the command conn.Open();

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Ofcause I did some google-ing and I enabled remote connections in the SQL Server Surface Area Configuration. But on google I saw that many people have this error with all differnt solutions.... tried several of them, but without success.

[JerryShaw]

That SQL message can occur for a number of reasons. It is a generic message. You have to look at the innermessage and sometimes the innermessage.innermessage to see the real reason.

The connection string I sent uses windows authentication, and is pointing to my server. Make sure you are passing the name of your server, and catalog (aka database) in your own connection string.

If the message takes 30 seconds to appear, it is because you connection string is pointing to the wrong server/database. If it comes back immediately, it means that it sees the server, but either your database name is wrong, the authentication type is wrong, or the credentials are wrong.

Tell me the name of your server, database name, authentication mode, and credentials, and I will send you the correct connection string.

// Jerry

[Ramon78]

Oke it takes more than 30 sec before I get the error message. So it looks like it is searching for the wrong server.

Here is the stuff I am using; everything runs local on my Vista machine:

MS Visual C# 2008 Express Edition
MS SQL Server 2005 (It came with Visual C# 2008, so it must be also Express Edition)
Workstation name= vista-sp1
Database name=Dorpshuis.mdf
Authentication= Windows authentication
Username=gebruiker
Password=paswoord

If you need more, please let me know.

Thanks,
Ramon

[JerryShaw]

I have adjusted the connection string to your criteria (below).
Make sure that your Sql server is alive. You should (in task manager process) have the following processes running: (as a minium)
sqlserver.exe
sqlwriter.exe
sqlbrowser.exe
If not, then go to windows services, and set the SqlServer.exe to run, it should cause the others to fire up.
You should also have the Sql Server Management program installed. This will allow you to confirm the server is runing, create tables, etc. If it can connect to the server, then the connection string is the issue.

SqlConnection conn = new SqlConnection("Data Source=vista-sp1;Initial Catalog=Dorpshuis;Integrated Security=SSPI);

[Ramon78]

Hi JerryShaw,

I got the services running, could connect to the database with the SQL Server Management Studio, but it gave me still the same problem....

Tried several things, now I reinstalled SQL 2005 Express but without success. Now I can not even start SQL Server Management Studio anymore, it give me this message

Cannot connect to VISTA-SP1\SQLEXPRESS.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Ofcause I did enabled the remote connections..... Pfff I need to fix this first I am affraid..

At least thanks for your help so far!

Ramon

[Jens]

If your database is running locally, try

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Dorpshuis;Integrated Security=SSPI);

For the adding of your password or username, you can add ;Uid = id;pwd = password.
If this connection still gives errors, please just tell so. You are not out of options yet :p.

Alternatively, I had a neat trick at school to generate connection strings. I mailed my teacher about it so it should arrive soon.
For now, maybe this link can help you:

http://www.connectionstrings.com/

[Ramon78]

Thanks,

By now I figured out what was wrong with the connection string, I have the database running under a named instance. By changing the connection string in to this I got one step further.

SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Dorpshuis;Integrated Security=True;User Instance=True");

Now it stops by

conn.Open();

with the error: Cannot open database "Dorpshuis" requested by the login. The login failed.
Login failed for user 'DOMAIN\username'.

I want to use windows authentication and not hardcode any username or password.

Ramon

[Jens]

If that is the case your windows verification should be the same as the verification for your database (ie: when installing sql server you should have opted for the windows verification).

The options that I know are to either hardcode it (and at least check if that works ;) ) untill you find a better solution or prompt for username and password.

While I haven't used prompt myself, I found something handy in connectionstrings.com:

oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;

Hope this helps,

Jens

[JerryShaw]

Do you have access to that database through the SQL management studio ?

[Ramon78]

Yes I have.

[JerryShaw]

Ramon,

Tell ya what I will offer. Send me a backup of your database (zip'ed) along with your project source code, and I will test it here. Either I will be successful, meaning that your Server is at fault, or I will discover the problem in your source, and tell you what needs to be fixed.
If you don't want to post your database here, then you can send it to [e-mail] or post it somewhere so I can download it.

Regards,
Jerry

[Ramon78]

Oke, just sent you an email.

Ramon

[JerryShaw]

Okay, I just took care of your problem. Sent you an email with the details.
I believe you can mark this as solved.

Jerry

[[e-mail]]

Hi Jerry... ur code is very useful 2 my project. Thank u