select query for selecting single value from table showing null value.

Question

0.00/5 (No votes)

See more:

C#

mycnOptionPanel.Open();
  mycmdOptionPanel = mycnOptionPanel.CreateCommand();             

  mycmdOptionPanel.CommandText = "select Device_ID from device_info where      Device_name=@Device_name";
                    mycmdOptionPanel.Parameters.Add("@Device_name",LabelDeviceName.Text);
                  
 string strDeviceID = mycmdOptionPanel.ExecuteScalar().ToString();

I want to select single value from table in database of mysql into strDeviceID variable, on last line strDeviceID variable contain null,why this is happend? is my code is wrong?
plz help.

Posted 15-Aug-15 22:42pm

ven.in

Updated 15-Aug-15 23:13pm

Maciej Los

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 16-Aug-15 5:14am

You may have no such device with @Device_name
Check directly from SQL Studio...

ven.in 16-Aug-15 5:21am

i have.

Kornfeld Eliyahu Peter 16-Aug-15 5:22am

As Device_name probably a string value you should enclose it in quotes - it may create a syntax problem otherwise and return the wrong result...
"select Device_ID from device_info where Device_name = '@Device_name'"

ven.in 16-Aug-15 5:35am

ya i did but not working..plz see this exception
Object reference not set to an instance of an object.

ven.in 16-Aug-15 5:26am

Object reference not set to an instance of an object. this exception is fired.

Kornfeld Eliyahu Peter 16-Aug-15 5:33am

Where?

ven.in 16-Aug-15 5:36am

In catch block,after executing last line it will directly jump to catch block. and my database is mysql.

Kornfeld Eliyahu Peter 16-Aug-15 5:38am

That because ExecuteScalar() is NULL...
No news there...
Can you run an SQL profiler to see what the exact SQL query executed?

ven.in 16-Aug-15 5:42am

it gives error there also. if we specifies name of field in column then it will error that no such column is found or exist.

Kornfeld Eliyahu Peter 16-Aug-15 5:45am

And? If SQL sais that there is no such (which?) column, than there is no...
Check for spelling (and for case too?)...

ven.in 16-Aug-15 5:49am

i want to compare with the particular field in the column,if i write directly that field then it will fire error bcoz it expecting column name there. so what is solution.

Kornfeld Eliyahu Peter 16-Aug-15 5:55am

I do not understand you comment - it is probably the language barrier...So it would be better, you post the code you have problem with it (not that in the question, but the actual, real-time SQL)...

Kornfeld Eliyahu Peter 16-Aug-15 5:46am

You may post a simple SQL query as it catched by the profiler...

ven.in 16-Aug-15 6:03am

in simple language, i want to select id in one variable from database where i know the name. so i mention name in where clause and want to retrieve id relative to that name. plz give query to execute this task in asp.net c#

Kornfeld Eliyahu Peter 16-Aug-15 6:05am

Your code looks all right for that (except the quotes around the string value), but you commented earlier, that you have a 'Invalid column name' error in SQL...You should resolve that...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-08-15T23:40:00

Solution 1

One problem is that the SqlParameterCollection.Add Method (String, Object)[^] is obsolete. Because of this, use AddWithValue[^] instead. In other words

C#

...
mycmdOptionPanel.Parameters.AddWithValue("@Device_name",LabelDeviceName.Text);
...

Also note that the parameter value must be an exact match with the value in the database. If you have case-sensitive database then even small and capital letters affect the result. If the ExecuteScalar returns no rows, the return value is null thus resulting into an exception.

Posted 15-Aug-15 23:40pm

Wendelius

Updated 15-Aug-15 23:44pm

v2

Comments

ven.in 16-Aug-15 5:54am

i did same.but not working. thanx for reply

Wendelius 16-Aug-15 5:59am

Try removing the WHERE condition. That should return you one row from the database. The row is most likely not the on you want but that would tell if the statement works or not.

I another words, use:

mycmdOptionPanel.CommandText = "select Device_ID from device_info";
string strDeviceID = mycmdOptionPanel.ExecuteScalar().ToString();

ven.in 16-Aug-15 7:35am

yes tried this one it return 1st field in device_id column. hence query is executing properly but with where clause it is not working properly. may u help plz

Wendelius 16-Aug-15 7:47am

If that is the case, then the problem seems to be with the condition.

Using debugger, check what is the value of "LabelDeviceName.Text". Copy this exact same value to the SQL Editor and execute the statement with any parameters. Do you get a row?

For example, if the value for LabelDeviceName.Text is XYZ, execute a statement like

select Device_ID from device_info where Device_name='XYZ'

ven.in 16-Aug-15 8:22am

yes i got the row by select Device_ID from device_info where Device_name='XYZ'

why it is not working in the code. it is working in sql editor

is this case that @Device_name should same as name of in table i tried that also but no result.

mycmdOptionPanel.Parameters.Add("@Device_name",LabelDeviceName.Text);

Wendelius 16-Aug-15 9:59am

The variable name can be whatever you like so it doesn't affect.

I really don't suggest leaving the code as you now have that you concatenate the values directly to the SQL statement. This is extremely dangerous since it leaves you open to SQL injections.

I noticed in your comments that this is My SQL. So try the following

mycmdOptionPanel.CommandText = "select Device_ID from device_info where Device_name = :Device_name";
mycmdOptionPanel.Parameters.Add("Device_name", MySqlType.VarChar, 100).Value =LabelDeviceName.Text;

ven.in 16-Aug-15 8:41am

thanks to both of u...

Maciej Los 17-Aug-15 1:56am

5ed!

Wendelius 20-Aug-15 14:15pm

Thank you Maciej.

ven.in · Answer 2 · 2015-08-16T02:40:00

Solution 2

mycmdOptionPanel.CommandText = "select Device_ID from device_info where device_info.Device_name='"+ LabelDeviceName.Text + "'";
// mycmdOptionPanel.Parameters.AddWithValue("@device",LabelDeviceName.Text);

sting strDeviceID=mycmdOptionPanel.ExecuteScalar().ToString() ;

i just commented bold lines and specifies value directly witout addwithparameter and its work.

Posted 16-Aug-15 2:40am

ven.in

Comments

Maciej Los 16-Aug-15 17:19pm

Your code is Sql Injection vulnerable.

ven.in 17-Aug-15 0:38am

yes i know but what else solution for this. thanx for reply

Maciej Los 17-Aug-15 1:58am

First of all, you have to debug your program to find out where the problem is. Second of all, you did not provide any information abot mycmdOptionPanel! The code provided by you did not contain initialization for this component (whatever it is).

ven.in 17-Aug-15 2:54am

void Load_Options_Panel()
{
MySqlConnection mycnOptionPanel = new MySqlConnection("Server=....");
MySqlCommand mycmdOptionPanel;

-
-
-
}

here i declared the mycnOptionPanel and mycmdOptionPane in function Load_Options_Panel()

i debug it but not found any problem rather it is not taking the value by using addwithparametervalue.