Click here to Skip to main content
14,428,056 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 9-Jun-14 2:31am
v4
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!:)
Rate this:
Please Sign up or sign in to vote.

Solution 1

The problem is this line of code:
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

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.
   
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.
Rate this:
Please Sign up or sign in to vote.

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:
            SqlCommand cmd = new SqlCommand("select * FROM UsersTbl WHERE UserName = @UserName", connToDB);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@UserName";
            param.Value = userName;
// ...
   
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!:)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,178
Richard MacCutchan 1,899
phil.o 1,270
Patrice T 1,247
MadMyche 1,185



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100