I want to retrieve multiple data from my data base. Im using C# and sql server

Question

This is my code and i cannot retrieve multiple data if i give only one column it works but when i give multiple columns it dosent work i work with c# and sql server.

 SqlConnection con3 = new SqlConnection(conString);
 con3.Open();
 if (con3.State == System.Data.ConnectionState.Open)
 {
     string s = "SELECT ([reg_grade] ,[t_name],[start_time], [end_time]) from addteacher where class_id='" + comboBox2.SelectedItem.ToString() + "' ";
     SqlCommand cm = new SqlCommand(s, con3);
     SqlDataReader dr1 = cm.ExecuteReader();
     while (dr1.Read())
     {
         grade_att.Text = (dr1["reg_grade"]).ToString();
         tname_att.Text = (dr1["t_name"]).ToString();
         stime.Text = (dr1["start_time"]).ToString();
         etime.Text = (dr1["end_time"]).ToString();
     }
 }
 con3.Close();

Doesn't work please clarify. See Sql injection, no need to check if the connection is open you open it on previous line. Use using or try/catch/finally for your SqlConnectioin. Better SqlDataAdapter wrapped in using block again and fill the data in dataset. — mybirthname, Dec 13 '16 at 14:09
What do you mean by it doesn't work? Do you get an error message? — Dark Hippo, Dec 13 '16 at 14:10
it probably "doesnt work" because he's setting the results to a Text Field, so it's only showing the last line of the query... — Stormhashe, Dec 13 '16 at 14:11
You need to be more specific than "doesn't work". What happens if you use `SELECT * FROM ...` instead of specifying a bunch of columns? — sab669, Dec 13 '16 at 14:12

K. Berger · Accepted Answer · 2016-12-13T14:16:09.507

5

Your SQL-Statement is invalid due to the brackets around your fields' names.

Change this:

string s = "SELECT ([reg_grade] ,[t_name],[start_time], [end_time])..."

to look like this:

string s = "SELECT [reg_grade] ,[t_name],[start_time], [end_time]..."

Hope that helps.

You should try to send complete error-/exception-descriptions with your post. Not only will that let you identify the problem yourself, but it will also help to answer your questions here.

edited Dec 13 '16 at 14:16

answered Dec 13 '16 at 14:14

K. Berger

361
1
9

Your first example omits the opening bracket – stuartd Dec 13 '16 at 14:15
This. And also, if you want to save all the data you get from the query, you need to add it to a List. Right now, you are setting the results of the query on every run of the While, so when it finishes, it will only show you the last line of the query – Stormhashe Dec 13 '16 at 14:15

score 3 · Answer 2 · answered Dec 13 '16 at 14:13

3

I do not know if that's the problem, but i never saw column names wrapped in parentheses. try

"SELECT [reg_grade], [t_name], [start_time], [end_time] from addteacher where class_id='" + ...

answered Dec 13 '16 at 14:13

Jacopo

525
3
15

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Incorrect syntax near ','. – Malinda Peiris Dec 13 '16 at 14:19
string s = "SELECT (reg_grade ,t_name,start_time,end_time) from addteacher where class_id='" + gg + "' "; SqlCommand cm = new SqlCommand(s, con3); SqlDataReader dr1 = cm.ExecuteReader(); – Malinda Peiris Dec 13 '16 at 14:20
Your code is still invalid. See my reply above. It's the (....) which breaks things. Remove the parantheses. When putting them there, you cannot query multiple fields. – K. Berger Dec 13 '16 at 14:23

score 0 · Answer 3 · edited May 23 '17 at 11:45

@mybirthname is right on a number of points.

Using a SQL Wrapper

I'm fond of using Dapper Dot Net made by StackExchange. Very good and easy to use when you are writing your own sql to pull data from the database. It's worth utilizing parameterized queries; this should help (using loosely) to avoid writing insecure sql statements which are vulnerable to sql injection attacks. Bear in mind you SQL has to be written in such a way to avoid sql injection Owasp SQL Injection Information.

It also helps to deal with a bunch of issues as explained here in another question

Back to the problem..

I would suggest opening up SQL Management Studio (or even LinqPad) and try running the query on it's own. I'm sure the problem is based on the select:

SELECT ([reg_grade] ,[t_name],[start_time], [end_time]) from addteacher

This should be

SELECT [reg_grade] ,[t_name],[start_time], [end_time] from addteacher

If you test your queries in SQL first, you can easily identify errors in the query.

@Jacopo & @K.Berger is spot on about the parenthesis around the parameters, I'm too slow :) however that doesnt address the security risk of SQL injection.

I want to retrieve multiple data from my data base. Im using C# and sql server

3 Answers3