Must declare the scalar variable error

Question

1.00/5 (1 vote)

See more:

Hi,
I try to create a web site project, but i get "Must declare the scalar variable" error when i build the project. My code is:

C#

SqlConnection conn = new SqlConnection();
                    SqlDataReader dr , dr2;
                    DataTable dt = new DataTable();
                    DataTable dt2 = new DataTable();
                    conn.ConnectionString = "server= **; user = **; pwd=***;
database = ***";
                    conn.Open();
                    string TCNumber = Request.QueryString["TC"].ToString();
                    int intTNumber = Convert.ToInt32(TCNumber);
                    SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= @intTCNumber;", conn);
                    
                    comm.Connection=conn;
               
                    dr = comm.ExecuteReader();
                 
                    dt.Load(dr);
                    MeasureRecord.DataSource = dt;
                    MeasureRecord.DataBind();

                    SqlCommand comm2 = new SqlCommand("SELECT TC, Name, Surname, BirthDate, Phone from Pers_Info WHERE TC=@intTCNumber;", conn);
         
                    comm2.Connection = conn;
                    dr2 = comm2.ExecuteReader();
                
                
                    TCNo.Text = dr2[0].ToString();
                    Name.Text = dr2[1].ToString();
                    Surname.Text = dr2[2].ToString();
                    BirthDate.Text = dr2[3].ToString();
                    Phone_Number.Text = dr2[4].ToString();
                    
                    

                    dr.Close();
                    dr2.Close();
                    conn.Close();

Can you help me? Why this error appears?

Posted 31-Jul-12 1:26am

sesenyg

Updated 31-Jul-12 1:28am

Kenneth Haugland

v2

Add a Solution

Comments

[no name] 31-Jul-12 7:33am

Change to parameterized queries to help protect yourself from SQL injection attacks.

3 solutions

Solution 2

You have added @intTCNumber in query but it is not you have not declared it.
Add this before executereader.

SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= @TCNumber;", conn);
comm.Parameters.Add(new SqlParameter("TCNumber", Request.QueryString["TC"].ToString()));

Posted 31-Jul-12 1:42am

pradiprenushe

Updated 31-Jul-12 1:56am

v4

Comments

sesenyg 31-Jul-12 8:06am

Thank you so much dude:)

pradiprenushe 31-Jul-12 8:08am

Welcome.

Solution 3

SqlConnection conn = new SqlConnection();
                    SqlDataReader dr , dr2;
                    DataTable dt = new DataTable();
                    DataTable dt2 = new DataTable();
                    conn.ConnectionString = "server= **; user = **; pwd=***;
database = ***";
                    conn.Open();
                    string TCNumber = Request.QueryString["TC"].ToString();
                    //int intTNumber = Convert.ToInt32(TCNumber);
                   SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= '"+TCNumber+ "'", conn);
                    
                    comm.Connection=conn;
               
                    dr = comm.ExecuteReader();
                 
                    dt.Load(dr);
                    MeasureRecord.DataSource = dt;
                    MeasureRecord.DataBind();
 
                    SqlCommand comm2 = new SqlCommand("SELECT TC, Name, Surname, BirthDate, Phone from Pers_Info WHERE TC='"+TCNumber+"'", conn);
         
                    comm2.Connection = conn;
                    dr2 = comm2.ExecuteReader();
                
                
                    TCNo.Text = dr2[0].ToString();
                    Name.Text = dr2[1].ToString();
                    Surname.Text = dr2[2].ToString();
                    BirthDate.Text = dr2[3].ToString();
                    Phone_Number.Text = dr2[4].ToString();
                    
                    
 
                    dr.Close();
                    dr2.Close();
                    conn.Close();

Posted 31-Jul-12 2:05am

Santhosh Kumar Jayaraman

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)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-31T01:30:00

Solution 1

Your syntax is incorrect.Try this in command

C#

SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= "+intTCNumber, conn);

SqlCommand comm2 = new SqlCommand("SELECT TC, Name, Surname, BirthDate, Phone from Pers_Info WHERE TC= "+intTCNumber, conn);

Posted 31-Jul-12 1:30am

Santhosh Kumar Jayaraman

Updated 31-Jul-12 1:40am

v3

Comments

sesenyg 31-Jul-12 7:38am

Thank you for your solution it helped me but now it says "the conversion of the varchar value '4577765884' overflowed an int column." Do you know what is this?

Santhosh Kumar Jayaraman 31-Jul-12 7:39am

int intTNumber = Convert.ToInt32(TCNumber);

in this line instead of int, make it as int64
int64 intTNumber = Convert.ToInt64(TCNumber);

sesenyg 31-Jul-12 7:41am

No, it did not work. It said the same thing again:(

Santhosh Kumar Jayaraman 31-Jul-12 7:45am

int64 intTNumber = Convert.ToInt64(TCNumber);

Have you replaced it in both ends? and at both lines in your code? In which line you are getting error?

sesenyg 31-Jul-12 7:50am

Actually, my parameter TC is string which i used in sql command. Firstly, i try to do
string TCNumber = Request.QueryString["TC"].ToString();
SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= @TCNumber;", conn);

but it gave me errors, so i change the data type. I think the first problem is my data TC on database is a string but i try to match it with an int.

pradiprenushe 31-Jul-12 7:50am

Then you cant enter such big value in int column. If you have do it then change database table column datatype to numeric or decimal.

sesenyg 31-Jul-12 7:52am

No, it is impossible. I have to leave it string. Is there anyway to compare a string in select-where query?

pradiprenushe 31-Jul-12 7:54am

See my below answer. If database column is varchar then you dont need to convert it while comparing.

Santhosh Kumar Jayaraman 31-Jul-12 8:02am

Then try this.
SqlCommand comm = new SqlCommand("SELECT Sistolic,Diastolic,Pulse,Date from MeasureRecords WHERE TC= '"+TCNumber+ "'", conn);