Sqlclient exception error - sql server 2014

Question

0.00/5 (No votes)

See more:

, +

Hi,
It is my first time that I'm using sql server (I only have experiment with access databases).
I get all the time error excpetion when I'm trying to check if the user name is already exist in database (I have user ID for each user, but I search the username by the username I get from other function).
I realy don't know where to continue to solve this problem,.
This is the function that connect to the data base:

bool IWCFClientService.CheckIfUserNameAvailable(string userName)
        {
            string connectionString = @"Data Source=localhoast;Initial Catalog=MailChatApplicationDB;Integrated Security=True";
            SqlConnection connToDB = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName =" + userName, connToDB);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@UserName";
            param.Value = userName;
            cmd.Parameters.Add(param);
            SqlDataReader reader; 

            bool checkIFAvailable = false;
            try
            {
                connToDB.Open();
                reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    checkIFAvailable = true;
                }
              
            }
            catch (Exception ex)
            {
                string errorStr = ex.ToString();
                Console.WriteLine("Error messege: " + errorStr);
                DateTime errorDate = DateTime.Now;
                string filePath = @"c:\Errors Text Files\";
                string fileName = "Exception Error.txt";
                StreamWriter file = new System.IO.StreamWriter(filePath + fileName);
                file.WriteLine(errorStr);

                file.Close();
                  
                 
            }
            finally
            {
               
                connToDB.Close();
            }
            return checkIFAvailable;

            
        }

And this is the table of the users (UsersTbl):
http://s12.postimg.org/a8n2iu31p/Users_Tbl.jpg

Now about the excpetion error:
I get now two errors, dependents on the use name that the function get.
When the user name is w4e the exatation error is:

Quote:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'w4e'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ClientService.WCFClientService.ClientServiceContract.IWCFClientService.CheckIfUserNameAvailable(String userName) in c:\Users\MyName\Documents\Visual Studio 2013\Projects\CollegeFinalProject\ClientService\WCFClientService.cs:line 405
ClientConnectionId:...............>

The second error when the function get the user name blabla(sory about my stupid examples)

Quote:
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'blabla'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ClientService.WCFClientService.ClientServiceContract.IWCFClientService.CheckIfUserNameAvailable(String userName) in c:\Users\MyName\Documents\Visual Studio 2013\Projects\CollegeFinalProject\ClientService\WCFClientService.cs:line 405
ClientConnectionId:...............

Could it be also problem with the database propeties> Becouse if it does, I doesn't know what the correct propesties it shluld be.

Hope you can help me with this.

Posted 9-Jun-14 1:11am

ShiraFor

Updated 9-Jun-14 1:31am

v4

Add a Solution

Comments

[no name] 9-Jun-14 7:18am

The first thing is that you are using string concatenation to construct your query, don't.
2nd, you are using username as if it were an integer, it probably is not.
3rd you are constructing a parameter for your query but not including it in your query.

ShiraFor 9-Jun-14 7:33am

Sory but I didn't get the two first things you wrote here.
Can you please give me an example to explain me my mistake?

ShiraFor 9-Jun-14 7:44am

Ok, I think I get it, and it works now. Thank you!:)

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)

ZurdoDev · Accepted Answer · 2014-06-09T01:21:00

Solution 1

The problem is this line of code:

C#

SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName =" + userName, connToDB);

If you read the lines after it you add a parameter named @UserName but you never use it. Your code should be

C#

SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName = @UserName", connToDB);

Also the way you have it will allow you to be hacked by SQL injection. Always use parameterized queries.

Posted 9-Jun-14 1:21am

ZurdoDev

Comments

ShiraFor 9-Jun-14 7:43am

Thank you all!
I didn't saw this mistake before.
After fix it, I get a new error :<blockquote class="FQ"><div class="FQA">Quote:</div>The data types text and nvarchar are incompatible in the equal to operator.</blockquote>
I change the data type of the UserName value in the Users table to nvarchar , and now it finally works!:)

ZurdoDev 9-Jun-14 7:44am

Glad to hear it.

Richard MacCutchan · Accepted Answer · 2014-06-09T01:24:00

Solution 2

You have (correctly) used a parameterized query, but you added the value rather than the parameter name to your select statement. It should be:

C#

            SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName = @UserName", connToDB);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@UserName";
            param.Value = userName;
// ...

Posted 9-Jun-14 1:24am

Richard MacCutchan

Comments

ShiraFor 9-Jun-14 7:43am

Thank you all!
I didn't saw this mistake before.
After fix it, I get a new error :<blockquote class="FQ"><div class="FQA">Quote:</div>The data types text and nvarchar are incompatible in the equal to operator.</blockquote>
I change the data type of the UserName value in the Users table to nvarchar , and now it finally works!:)