c# select query does not work with parameters

Question

I have encountered this situation whereby this SQL statement does not work..

command.Parameters.AddWithValue("@OA_Name", obj.GEToperatingauthority());
command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = [@OA_Name]";

but this SQL statement works when i changed the ['@OA_Name'] to simply a value found in the table.

command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = ['OA 101']";

I have tested the value i got from the obj.GetOperatingauthority() and it is exactly the same as the one in the database. I have used reader.hasrows to check whether there are any rows returned..

btw i am using c# and access 2010 database..

edit: i put in the bulk of the code below:

        using (var command = connection.CreateCommand())
        {
            connection.Open();
            command.CommandType = CommandType.Text;

            command.Parameters.AddWithValue("@NRIC", obj.GETnricfinnumber());
            command.Parameters.AddWithValue("@Participant_Name", obj.GETname());
            command.Parameters.AddWithValue("@Gender", obj.GETgender());
            command.Parameters.AddWithValue("@DOB", obj.GETdateofbirth());
            command.Parameters.AddWithValue("@Nationality", obj.GETnationality());
            command.Parameters.AddWithValue("@Race", obj.GETrace());
            command.Parameters.AddWithValue("@Residential_Address", obj.GETresidentialaddress());
            command.Parameters.AddWithValue("@Contact_Number", obj.GETcontactnumber());
            command.Parameters.AddWithValue("@Email_Address", obj.GETemailaddress());

            command.CommandText = "SELECT [NRIC] FROM [Participant_Table] WHERE [NRIC] = [@NRIC]";
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader.GetString(0).ToLower().Equals(obj.GETnricfinnumber().ToLower()))
                    {
                        message.show(1, "", "exists");
                        //return false;
                    }   
                }
            }

            command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = @OA_Name";
            command.Parameters.AddWithValue("@OA_Name", obj.GEToperatingauthority());

            using (var reader = command.ExecuteReader())
            {
                if (!reader.HasRows)
                {
                    message.show(1, "", "no rows!"); return false; //to test whether has rows or no rows
                }

edit 2: the getOperatingAuthority can't be wrong as it is basically a "closed-loop" system whereby OA_Name are used to populate a combobox, and values selected from this combo box are used to referred back to the table to get the ID.

is your @OA_Name parameter returning 'OA 101' or just OA 101? — Dylan Hayes
– Dylan Hayes, Commented Nov 5, 2011 at 7:12
'OA 101' is the exact name of the school and I am trying to return its id (PK). using this 'OA 101', reader.hasrows returned me true — BOOnZ
– BOOnZ, Commented Nov 5, 2011 at 7:14
for the record in the code above, i'd move "command.Parameters.AddWithValue("@OA_Name", obj.GEToperatingauthority());" back up to the top, and make sure you include the [ ] around your @OA_Name. I see no reason that shouldn't work if the others are working... — Dylan Hayes
– Dylan Hayes, Commented Nov 5, 2011 at 7:22
@edit2 - if you take that data and populate a list box with it, the single ' ' would not carry over, and then when you get the data from the list the query would not have the ' ' needed to compare the string data in a sql statement. — Dylan Hayes
– Dylan Hayes, Commented Nov 5, 2011 at 7:28

NaveenBhat · Accepted Answer · 2011-11-05 07:32:58Z

1

Try by removing the enclosing braces of @OA_Name.

command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = @OA_Name";
command.Parameters.AddWithValue("@OA_Name", obj.GEToperatingauthority());

answered Nov 5, 2011 at 7:32

NaveenBhat

3,3364 gold badges41 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dylan Hayes Over a year ago

He already had this in his original code, that's not the issue.

Dylan Hayes · Accepted Answer · 2011-11-05 07:31:22Z

0

Try testing:

command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = [" + obj.GEToperatingauthority() + "]";

Not as elegant, but it would work, if it doesnt, then it's definitely an issue with

command.Parameters.AddWithValue("@OA_Name", obj.GEToperatingauthority());

EDIT - OK in response to your comment: now try this:

command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = [" + "'" + obj.GEToperatingauthority() + "'" + "]";

if that works then its the lack of ' '

edited Nov 5, 2011 at 7:31

answered Nov 5, 2011 at 7:16

Dylan Hayes

2,3661 gold badge24 silver badges33 bronze badges

4 Comments

BOOnZ Over a year ago

it doesnt work too, but I have tested the value of obj.Getoperatingauthoity() with the one in the database and there are no differences, even the casing are all identical

Dylan Hayes Over a year ago

For me this keeps leading me back to thinking that it's just because of the difference between when you manually typed 'OA 101' and your parameter that I am guessing is just OA 101 not 'OA 101'

BOOnZ Over a year ago

No data exists for the row/column. : when i got the statement correct and basically created the function from scratch just to read this OA_ID

BOOnZ Over a year ago

also should i mention that the value i am returning is a long integer? would there be a differnce?

Crimsonland · Accepted Answer · 2011-11-05 07:54:52Z

0

Change it to this:

Try to test first if your obj.GEToperatingauthority() has a value.

 command.CommandText = "SELECT [OA_ID] FROM [Operating_Authority_Table] WHERE [OA_Name] = @OA_Name";

  var getoperation = Convert.ToString(obj.GEToperatingauthority());
     command.Parameters.AddWithValue("@OA_Name", getoperation );
 //use this for checking      

  var dt = new DataTable();

   var da = new SqlDataAdapter(command);

    connection.Open();

   da.Fill(dt);

   if(dt.Rows.Count > 0)
   {
     //records found
   }
   else
   {
      //No records Found
   }

Regards

edited Nov 5, 2011 at 7:54

answered Nov 5, 2011 at 7:10

Crimsonland

2,1943 gold badges25 silver badges43 bronze badges

3 Comments

Dylan Hayes Over a year ago

that shouldn't matter, you can add the parameters before or after.

Crimsonland Over a year ago

Yup @Dylan Hayes ur correct but It is much ok to read it if the parameters is below the command text..

BOOnZ Over a year ago

yes, the function returns a value which is identical to that of the one in the database

Collectives™ on Stack Overflow

c# select query does not work with parameters

3 Answers 3

1 Comment

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related