Syntax Error Ocurring on the Data Reader

Question

try
{

    int i = 0;
    using (SqlConnection sqlCon = new SqlConnection(Form1.connectionString))
    {

        string commandString = "INSERT INTO Logindetail (Account,ID,Logint,Logoutt) values ('" + acc + "'," + textbxID.Text + "," + null + ", SYSDATETIME()" + ");";
            // MessageBox.Show(commandString);
            SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
            sqlCon.Open();
            SqlDataReader dr = sqlCmd.ExecuteReader();
            i = 1;
            if (i == 0)
            {
                MessageBox.Show("Error in Logging In!", "Error");
            }
            MessageBox.Show("Successfully Logged In");

    }

}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

I'm making a LoginForm for a Project.I have created a table which shows the LoginDetails(Account,ID,LoginTime,LogoutTime).But when I run the Program,it doesn't runs successfully.I face an error which is in Pic-2.When I remove sql 'data reader',the program runs without displaying the error.

Do not post pictures showing the errors and code but the error and code themselves. Also please do not use string concatenations... its SQL Injection susceptible - use parameterized queries - It'll also solve your current problem — Gilad Green, Aug 27 '17 at 07:13
I didn't get it..... Kindly,if u will send me the link,it would be easier for me... Thanks — Hafiz M Taha Waseem, Aug 27 '17 at 07:15
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) — marc_s, Aug 27 '17 at 07:16
see https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements — rene, Aug 27 '17 at 07:17
Sorry! Can you resolve directly my problem.... I didn't understand from the link. — Hafiz M Taha Waseem, Aug 27 '17 at 07:19
@HafizMTahaWaseem - Remove the semi-colon from `SYSDATETIME()" + ");"`. — Am_I_Helpful, Aug 27 '17 at 07:34
The reason is that you are inserting records in db so you need to use `ExecuteNotQuery()` which returns no of affected rows: `i = sqlCmd.ExecuteNonQuery();` . `ExecuteReader()` is used while selecting data from db. — mmushtaq, Aug 27 '17 at 07:34
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Aug 27 '17 at 08:32

Milana · Accepted Answer · 2017-08-27T08:23:40.057

2

When you concatenate a null it basically adds nothing to the string, so this code:

string commandString = "INSERT INTO Logindetail (Account,ID,Logint,Logoutt) values ('" + acc + "'," + textbxID.Text + "," + null + ", SYSDATETIME()" + ");";

results of this string, and as you can see it has an extra comma, that causes the exception:

"INSERT INTO Logindetail (Account,ID,Logint,Logoutt) values ('acc',textbxID,, SYSDATETIME());"

If you want to add NULL to the query it has to be a string, so do this instead:

string commandString = "INSERT INTO Logindetail (Account,ID,Logint,Logoutt) values ('" + acc + "'," + textbxID + ", NULL , SYSDATETIME()" + ");";

And you are using ExecuteReader instead of ExecuteNonQuery. You cannot use ExecuteReader for inserting rows to the DB.

Also, as someone mentioned in the other answer, you better do it with parametes to avoid SQL Injections.

edited Aug 27 '17 at 08:23

answered Aug 27 '17 at 07:42

Milana

557
9
20

It is running the application successfully,but it is not saving it in the database – Hafiz M Taha Waseem Aug 27 '17 at 07:55
Kindly,guide me for the further process – Hafiz M Taha Waseem Aug 27 '17 at 07:58
I showed you the error and how to fix it. Change the creation of the query (As shown in my answer) and try it. The query you created is not formatted correctly – Milana Aug 27 '17 at 08:01
I changed it according to your answer.... It is now running the program successfully without any error.But the only problem is that it is even not saving the data in database by using your answer too. – Hafiz M Taha Waseem Aug 27 '17 at 08:02
Debug the code to the point where you see the query that was built. Copy this query and try to run in the sql-server. If it doesn't work then see the error that it gives you. Perhaps you can't put nulls in that column or perhaps the other columns have different constraint. If you want further help show the definition of the Logindetails table. – Milana Aug 27 '17 at 08:07
The Query is running successfully in SQL SERVER 2012 – Hafiz M Taha Waseem Aug 27 '17 at 08:11
It also adds the row? Do you get any error now from the code after that you changed the query? – Milana Aug 27 '17 at 08:14
But it is not saving data in C# – Hafiz M Taha Waseem Aug 27 '17 at 08:14
It successfully adds the row in the table – Hafiz M Taha Waseem Aug 27 '17 at 08:14
But it is not adding row using C# – Hafiz M Taha Waseem Aug 27 '17 at 08:15
See my edit about the `ExecuteReader`, you should use `ExecuteNonQuery` for inserting rows – Milana Aug 27 '17 at 08:24
I did it.But there is ocurring an error: Cannot implicitly convert type 'int' to 'System.Data.SqlClient.SqlDataReader' – Hafiz M Taha Waseem Aug 27 '17 at 08:58

Syntax Error Ocurring on the Data Reader

1 Answers1