how to fix SQL syntax error...upto...for the right syntax to use near '())' at line 1

Question

i am getting error in I am getting error in da.Fill(ds) . i want to check CNIC before saving the form . if it exists then form should be submitted otherwise it should say , CNIC is not valid.

public partial class ReportCrime : System.Web.UI.Page
       {
         MySqlConnection con = new MySqlConnection();
         MySqlCommand cmd = new MySqlCommand();
         DataSet ds = new DataSet();
     protected void Page_Load(object sender, EventArgs e)
        {
            con.ConnectionString = "Data Source=localhost;Initial Catalog=fir;Integrated Security=True;User Name=root;Password=;";
        con.Open();
        }
     protected void btnSubmit_Click(object sender, EventArgs e)
        {

        MySqlCommand cmd = new MySqlCommand("select count(CNIC) from reportcrime where CNIC=(txtCnicNo.Text.ToString())", con);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(ds);
        int i = ds.Tables[0].Rows.Count;
        if (i > 0)
        {
MySqlCommand acmd = new MySqlCommand("insert into reportcrime" + "(Name,CNIC,Phone1,Phone2,Email,Gender,City,Address,CrimeType,CrimeDetail)values(@Name,@CNICNO,@ContactNo.1,@ContactNo.2,@EmailAddress,@Gender,@CityName,@PostalAddress,@CrimeType,@CrimeDetail)", con);

                   acmd.Parameters.AddWithValue("@Name", txtName.Text);
                   acmd.Parameters.AddWithValue("@CNICNO", txtCnicNo.Text.ToString());
                   acmd.Parameters.AddWithValue("@ContactNo.1", txtcontactNo1.Text);
                   acmd.Parameters.AddWithValue("@ContactNo.2", txtcontactNo2.Text);
                   acmd.Parameters.AddWithValue("@EmailAddress", txtEmail.Text);
                   acmd.Parameters.AddWithValue("@Gender", ddlGender.SelectedItem.Value);
                   acmd.Parameters.AddWithValue("@CityName", txtCityName.Text);
                   acmd.Parameters.AddWithValue("@PostalAddress", txtaddress.Text);
                   acmd.Parameters.AddWithValue("@CrimeType", ddlCrimeType.SelectedItem.Value);
                   acmd.Parameters.AddWithValue("@CrimeDetail", txtCrimeDetails.Text);

                  acmd.ExecuteNonQuery();
         }

        else
        {
            MessageBox.Show("CNIC is not Valid");
        }

    }

 }

`txtCnicNo.Text.ToString()` doesn't look like any dialect of SQL. Perhaps you meant to pass the value as a parameter to the query. — HABO, May 04 '19 at 03:35
An example? You ought to be able to find several in `protected void btnSubmit_Click(object sender, EventArgs e)`. And using `.ToString()` on a `string` is a bit redundant. — HABO, May 04 '19 at 13:38

Atul Mathew · Accepted Answer · 2019-05-06T03:31:55.737

0

if the value passing from text box is just string then this should work


MySqlCommand cmd = new MySqlCommand("select count(CNIC) from reportcrime where CNIC='"+Convert.ToString(txtCnicNo.Text)+"'", con);

edited May 06 '19 at 03:31

answered May 04 '19 at 04:46

Atul Mathew

445
3
15

no its still not working .. CNIC data type in mysql is String-> varchar . and i have convert it into string. – Mishaal May 04 '19 at 05:18
thanksss.....now there is no error in Query. but i have stored CNIC already in same Table, and i want that the CNIC that the user is going to enter , if it exists in the same table then this form should be submitted. other wise it should not submitted.. now at the end of "if { } part " acmd.ExecuteNonQuery(); it is saying "duplicate PRIMARY key" if i remove the Primary key as CNIC column , then i won't be able to search data that i have to do in future work... – Mishaal May 04 '19 at 05:53
which column u have given as primarykey – Atul Mathew May 04 '19 at 06:05
i think u have to do a ```con.open()``` and ```con.close()```between ```cmd.executenonquery();``` – Atul Mathew May 04 '19 at 06:07
@CNICNO in database as CNIC i have given primary key – Mishaal May 04 '19 at 06:07
y dont u create another colmun as id and making it as primarykey and increase the value when inserting a new data would help – Atul Mathew May 04 '19 at 06:22
in CNIC column , i have storeed 13 digit cnic no.. basically i want to validate cnic by doing this method. – Mishaal May 04 '19 at 06:27
if u just want to validate CNIC i can provide a method for that?or if u want to do it in this method only it would be complicated!! and since my answer cleared your error please consider it accepting as answer by clicking the tick mark on left its a boost for every developer:) happy coding! never give up – Atul Mathew May 04 '19 at 07:31
yes please provide me another method to validate cnic.. i want to store cnic in another database or table , and compare it to my reportcrime table... i am using mysql – Mishaal May 04 '19 at 07:44
where i have to paste public bool checkCNIC function?? Please, i am new to c# , asp.net. – Mishaal May 04 '19 at 08:17
u just have to call checkCNIC() in your button click – Atul Mathew May 04 '19 at 08:19
i am getting some errors in this method.. can you please tell me can i compare a column in 2 different databases using Query or just in 2 tables in the same databases.. – Mishaal May 04 '19 at 08:50
u can compare two tables in different datatables as well – Atul Mathew May 04 '19 at 09:05
can you send me query please?? – Mishaal May 04 '19 at 09:17
refer this link:https://stackoverflow.com/questions/7606175/how-to-compare-data-between-two-table-in-different-databases-using-sql-server-20 – Atul Mathew May 04 '19 at 09:21
@marcelo Building a SQL statement by concatenating inputs rather than using parameters opens the way to [SQL Injection](http://bobby-tables.com/). – HABO May 04 '19 at 13:42

how to fix SQL syntax error...upto...for the right syntax to use near '())' at line 1

1 Answers1