C# SQL Query and apostrophe's

Question

Possible Duplicate:
SqlDataReader parameter not working

I am trying to get a pc name from a textbox to a sql parameter in a windows form application. It works fine when I input the parameter like the following.

                string query = @"select stationipaddress from station where stationname = @Name";
                using (SqlCommand cmd = new SqlCommand(query, cs))
            {


               SqlParameter param = new SqlParameter();
                param.ParameterName = "@Name";
                param.Value = "demo";
                cmd.Parameters.Add(param);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {

                    while (dr.Read())
                    {
                        label3.Text = dr.GetSqlValue(0).ToString();
                        results = dr.GetValue(0).ToString();
                        MessageBox.Show(dr.GetValue(0).ToString());
                        MessageBox.Show(results);
                    }

But Then it does nothing when I try and get the data from the textbox.

 string query = @"select stationipaddress from station where stationname = @Name";
            using (SqlCommand cmd = new SqlCommand(query, cs))
            {


                  SqlParameter param = new SqlParameter();
                param.ParameterName = "@Name";
                param.Value = textBox1.Text;
                cmd.Parameters.Add(param);

                using (SqlDataReader dr = cmd.ExecuteReader())
                {

                    while (dr.Read())
                    {
                        label3.Text = dr.GetSqlValue(0).ToString();
                        results = dr.GetValue(0).ToString();
                        MessageBox.Show(dr.GetValue(0).ToString());
                        MessageBox.Show(results);
                    }

Please don't re-post the same question: http://stackoverflow.com/questions/13647964/sqldatareader-parameter-not-working — Richard Deeming, Nov 30 '12 at 17:42

score 1 · Answer 1 · answered Nov 30 '12 at 17:42

1

I would suggest moving out all of your MessageBox Operations until after you've retrieved your results from the database. Add your results to a Strongly typed list and then display the message accordingly.

answered Nov 30 '12 at 17:42

bluetoft

5,373
2
23
26

score 0 · Answer 2 · answered Nov 30 '12 at 17:44

0

Try double apostrophes like '' -> "John's" --> "John''s"

answered Nov 30 '12 at 17:44

iefpw

6,816
15
55
79

I did this: ''@StationName'' and it said incorrect syntax near '@StationName' – user1836162 Nov 30 '12 at 17:51
add the entire exception in your question. – iefpw Nov 30 '12 at 17:51

Dave Zych · Answer 3 · 2012-11-30T18:04:17.773

0

After you call AddWithValue set the DbType of your parameter. That should quote your string properly.

cmd.Parameters["@StationName"].SqlDbType = SqlDbType.NVarChar;

EDIT: Sorry, I meant to specify SqlDbType

edited Nov 30 '12 at 18:04

answered Nov 30 '12 at 17:49

Dave Zych

21,581
7
51
66

When I do that I get the following error: Only assignment, call, increment, decrement, and new object expressions can be used as a statement – user1836162 Nov 30 '12 at 17:57
I have updated my code above – user1836162 Nov 30 '12 at 19:30

score 0 · Answer 4 · edited Nov 30 '12 at 18:13

0

Why don't you strip out the apostrophies i.e.

cmd.Parameters.AddWithValue("@StationName", textBox1.Text.Replace("'","")

If the database field values in StationName contain apostrophies then you will have to change your SQL statement to this:

select stationipaddress from station where replace(stationname,CHAR(39),'') = @StationName

edited Nov 30 '12 at 18:13

marc_s

732,580
175
1,330
1,459

answered Nov 30 '12 at 17:55

w0051977

15,099
32
152
329

what should the query say just @StationName or '@StationName'? – user1836162 Nov 30 '12 at 18:19
I don't understand the original question. Are the apostrophes is the database field (Station.StationName) or are they in the textbox (textbox1.text) or are they in both? – w0051977 Nov 30 '12 at 18:29
In order to get results back from the db it has to be like: where stationname = 'stationname', thats why I can't get any results back – user1836162 Nov 30 '12 at 19:10
In that case change your SQL statement to: select stationipaddress from station where StationName = char(39) + @StationName + char(39) – w0051977 Nov 30 '12 at 22:27

C# SQL Query and apostrophe's

4 Answers4