none
syntax error near '1'. RRS feed

  • Question

  •  whats wrong with this i cant fine error near 1   only 1 is in datetimepicker hlp plz

    SqlCommand cmdd = new SqlCommand("INSERT INTO investor_detail (investor_id,investor_cnic,investor_amount,date,pay_amount,remaining_amount ) VALUES ('" + a + "','" + b + "','" + investoramount.Text + "','" + dateTimePicker1.Value + "','" + payamount.Text + "','" + remainnigamount.Text + "'", my);
                            cmdd.ExecuteNonQuery();

    Sunday, December 15, 2013 7:29 AM

Answers

  • Hi,

    a SQL Server DateTime literals must follow rules, also numeric values - as there are some "amount", columns - see Constants. First use parameters:

    SqlCommand cmdd = new SqlCommand(
    	"INSERT INTO investor_detail (investor_id,investor_cnic,investor_amount,[date],pay_amount,remaining_amount) " 
    	+ " VALUES (@investor_id, @investor_cnic, @investor_amount, @date, @pay_amount, @remaining_amount);",
    	my);
    	cmdd.Parameters.AddWithValue("@investor_id", a);
    	cmdd.Parameters.AddWithValue("@investor_cnic", b);
    	cmdd.Parameters.AddWithValue("@investor_amount",  investoramount.Text);
    	cmdd.Parameters.AddWithValue("@date",  dateTimePicker1.Value);
    	cmdd.Parameters.AddWithValue("@pay_amount", payamount.Text);
    	cmdd.Parameters.AddWithValue("@remaining_amount", remaining_amount.Text);
    	cmdd.ExecuteNonQuery();

    If the amount columns are numeric data types, for example decimal, use Decimal.Parse or an equivalent method, to convert them, as numeric value.

    Regards, Elmar

    • Marked as answer by Eason_H Monday, December 23, 2013 1:51 AM
    Sunday, December 15, 2013 5:39 PM
  • The code below is much easier to debug.

    string columns = "investor_id,investor_cnic,investor_amount,date,pay_amount,remaining_amount";
    string values = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}'",a, b, investoramount.Text, dateTimePicker1.Value, payamount.Text, remainnigamount.Text);
    string SQL = string.Format("INSERT INTO investor_detail ({0}) VALUES ({1})", columns, values);
    SqlCommand cmdd = new SqlCommand(SQL, my);

    • Edited by Joel Engineer Sunday, December 15, 2013 9:31 AM
    • Marked as answer by Eason_H Monday, December 23, 2013 1:51 AM
    Sunday, December 15, 2013 9:20 AM

All replies

  • The code below is much easier to debug.

    string columns = "investor_id,investor_cnic,investor_amount,date,pay_amount,remaining_amount";
    string values = string.Format("'{0}','{1}','{2}','{3}','{4}','{5}'",a, b, investoramount.Text, dateTimePicker1.Value, payamount.Text, remainnigamount.Text);
    string SQL = string.Format("INSERT INTO investor_detail ({0}) VALUES ({1})", columns, values);
    SqlCommand cmdd = new SqlCommand(SQL, my);

    • Edited by Joel Engineer Sunday, December 15, 2013 9:31 AM
    • Marked as answer by Eason_H Monday, December 23, 2013 1:51 AM
    Sunday, December 15, 2013 9:20 AM
  • Hover your mouth over the cmdd variable when the program halts on the error.

    You should see the text that the code created to use in the SQL.You should look for "1" and see what's the problem.

    If you can't find the problem. paste the TEXT here.

     

    Noam B.



    Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...

    • Proposed as answer by Noam B Sunday, December 15, 2013 3:29 PM
    Sunday, December 15, 2013 3:29 PM
  • Hi,

    a SQL Server DateTime literals must follow rules, also numeric values - as there are some "amount", columns - see Constants. First use parameters:

    SqlCommand cmdd = new SqlCommand(
    	"INSERT INTO investor_detail (investor_id,investor_cnic,investor_amount,[date],pay_amount,remaining_amount) " 
    	+ " VALUES (@investor_id, @investor_cnic, @investor_amount, @date, @pay_amount, @remaining_amount);",
    	my);
    	cmdd.Parameters.AddWithValue("@investor_id", a);
    	cmdd.Parameters.AddWithValue("@investor_cnic", b);
    	cmdd.Parameters.AddWithValue("@investor_amount",  investoramount.Text);
    	cmdd.Parameters.AddWithValue("@date",  dateTimePicker1.Value);
    	cmdd.Parameters.AddWithValue("@pay_amount", payamount.Text);
    	cmdd.Parameters.AddWithValue("@remaining_amount", remaining_amount.Text);
    	cmdd.ExecuteNonQuery();

    If the amount columns are numeric data types, for example decimal, use Decimal.Parse or an equivalent method, to convert them, as numeric value.

    Regards, Elmar

    • Marked as answer by Eason_H Monday, December 23, 2013 1:51 AM
    Sunday, December 15, 2013 5:39 PM
  • In my opinion you try to pass string value to column that take int or decimal values.

    Paste here table structure and all will be clear :)

    Sunday, December 15, 2013 5:46 PM
  • try this Code

    SqlCommand cmdd = new SqlCommand("INSERT INTO investor_detail (investor_id,investor_cnic,investor_amount,date,pay_amount,remaining_amount ) VALUES ('" + a + "','" + b + "','" + investoramount.Text + "','" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "','" + payamount.Text + "','" + remainnigamount.Text + "'", my);

    change the code dateTimePicker1.Value  to dateTimePicker1.Value.ToString("yyyy-MM-dd") as Sql Server Date Format

    Happy Coding, RDRaja

    Monday, December 16, 2013 5:59 AM
  • It is certainly easier to read, but it is still open to Sql Injection attacks.  Elmar's approach with parameters is better.
    Monday, December 16, 2013 10:19 PM
  • This code is also open to Sql injection attacks.  
    Monday, December 16, 2013 10:20 PM