Better code for this?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

hi this is my code..but am getting error..can any one tel me what s the error in my code..thanks

   private User GetUserInfo(string userId)
    {
        string sqlConnection = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
        SqlConnection conn;
        SqlCommand cmd;
        User lst = new User();

        try
        {
            using (conn = new SqlConnection(sqlConnection))
            {
                conn.Open();

                StringBuilder sbQry = new StringBuilder();
//begin..in this line am getting error
                sbQry.Append("select * FROM User where UserId = " + userId);
//end
                using (cmd = new SqlCommand(sbQry.ToString(), conn))
                {
                    cmd.CommandType = CommandType.Text;

                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            User temp = new User();
                            temp.UserID = Tools.Tools.IifInt(dr["UserID"]);
                            temp.Username = Tools.Tools.IifStr(dr["Username"]);
                            temp.Password = Tools.Tools.IifStr(dr["Password"]);
                            temp.LastName = Tools.Tools.IifStr(dr["LastName"]);
                            temp.FirstName = Tools.Tools.IifStr(dr["FirstName"]);
                            temp.MiddleName = Tools.Tools.IifStr(dr["MiddleName"]);
                            temp.WorksiteCode = Tools.Tools.IifStr(dr["WorksiteCode"]);
                            temp.AccessLevel = Tools.Tools.IifInt(dr["AccessLevel"]);
                            temp.Active = Tools.Tools.IifStr(dr["Active"]);
                            temp.DateCreated = Tools.Tools.IifDT(dr["DateCreated"]);
                            //temp.DateUpdated = Tools.Tools.IifDate(dr["DateUpdated"]);
                            temp.WorksiteCode = Tools.Tools.IifStr(dr["Worksitedesc"]);
                            lst = temp;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            lst.Username = "Error in retrieving record";
        }
        return lst;
    }

error is invalid syntax near User...
can any one suggest me?

Posted 9-May-12 0:28am

ythisbug

Add a Solution

5 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)

DominicZA · Answer 1 · 2012-05-09T00:37:00

This is vulnerable to SQL injection. Read here http://www.unixwiz.net/techtips/sql-injection.html[^]

Rather use parameters:

C#

SqlCommand sql = new SqlCommand("SELECT * FROM Users WHERE UserID = @userID", sqlConnection);

sql.Parameters.Add(new SqlParameter("userID", userId);

sql.ExecuteReader();

That should work and will be exponentially more secure ;)

-Dom

OriginalGriff · Answer 2 · 2012-05-09T00:38:00

Solution 2

The code you have shown does not give the error you describe.

If I copy it into one of my files, I get the kind of errors I would expect ("User" class doesn't exist, User.UserId doesn't exist, that sort of thing)

But the line you are reporting as having the error doesn't report any error. Go back to your compiler, do a clean re-build, then double click on the error message to take you to the line. I don't think it is where you think it is.

Posted 9-May-12 0:38am

OriginalGriff

Comments

BalaThakur 9-May-12 7:04am

You may have used another table name than "User". Thats y...

OriginalGriff 9-May-12 7:11am

No, he is reporting the error as:
"error is invalid syntax near User..."
on the line:
//begin..in this line am getting error
sbQry.Append("select * FROM User where UserId = " + userId);
//end

There are quite a few reasons why that will cause SQL exceptions, but they will occur further on in the code.

ythisbug 9-May-12 7:22am

exactly but i changed to tblUser. so now working thanks

Shahin Khorshidnia 9-May-12 13:58pm

+5

cyclopic · Answer 3 · 2012-05-09T00:49:00

Solution 3

Hello...

1 ) User is a reserved word in SQL server .. you can rename the table or write [User]
2 ) You should use stored proc

bye

Posted 9-May-12 0:49am

cyclopic

Mits Machhi · Answer 4 · 2012-05-09T02:23:00

Solution 6

C#

sqldataadapter ad=new sqldataadapter("string s=select * from table where userid='"+txtuname.text+"'",connection object);
dataset ds=new dataset();
ad.fill(Ds);

if(Ds.table[0].rows.count!=0)
{
          means record found
}
else
{
        means record not found
}

Posted 9-May-12 2:23am

Mits Machhi

Updated 9-May-12 7:49am

v4

Chamila Nishantha · Answer 5 · 2012-05-09T00:52:00

Solution 4

Replace with following,

sbQry.Append("select * FROM User where UserId = '" + userId+"'");

Posted 9-May-12 0:52am

Chamila Nishantha