Syntax error near '=' -- delete statement

Question

I'm trying to add something so that things can be deleted from a table, though it says there is a syntax error near '=' and I can't seem to spot it. I know this isn't the most ideal way to be doing this, but I've been told to do it this way.

Here's what I've put:

Con.Open();
                    string query = "DELETE FROM tablepassengers WHERE passportno.=" + tbpassno.Text + ';';
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("deleted");
                    Con.Close();
                    populate();

You have a `.` after `passportno`, is that meant to be there? I would say this is where the error is. — JayV, May 08 '22 at 20:34
@JayV its meant to be there since thats the name of the row in the table, but i can try change it and see if anything happens — yana, May 08 '22 at 20:38
If the column is called `passportno.`, then it needs to be wrapped in square brackets abd be. `[passportno.]` — JayV, May 08 '22 at 20:39
While *"quoting"* it (with the square brackets) will fix the problem, you really should choose a better column name. Don't use anything in the column name you can't use as a variable name in C#. You'll still run into unexpected keywords, so look out for those; you'll still need to quote them. — madreflection, May 08 '22 at 20:40
@JayV ohhh okay i see, ill try that, i didnt know this + never got told this by any teachers, thank you!! — yana, May 08 '22 at 20:40
@madreflection i changed it anyway, but now the error changed and says error converting datatype varchar to numeric, though i dont understand since i declared when creating the table that its varchar — yana, May 08 '22 at 20:44
That's a whole different issue. Frankly, based on the 4 questions you've asked so far today, whatever you're using to learn is garbage. It's just telling you "do this" or "do that", not *teaching* you anything. You need to find something else. — madreflection, May 08 '22 at 20:45
@madreflection but then how come its working completely fine for them? sorry for bothering but i really dont get how it when everything is done in the same method as they have, it wont work on my end — yana, May 08 '22 at 20:47
As Caius pointed out on your other question, you're being shown ***bad practices***. — madreflection, May 08 '22 at 20:50
@madreflection okay i understand that mindset, but genuinely the reasoning behind how something would run for them but not me? — yana, May 08 '22 at 20:50
For one thing, you're not being taught the fundamentals of syntax, so you're unable to see when you make a simple omission. You're not doing the same thing, but you're not being taught enough to be able to recognize it. — madreflection, May 08 '22 at 20:52
And it gets worse from there, because [SQL Injection](https://bobby-tables.com/) is a major security flaw, as Caius pointed out. You should never write code that way. This tutorial is *causing you harm from the very start*. It "works" until someone enters something you never imagined could be a problem, and then you're compromised. — madreflection, May 08 '22 at 20:55
I concur 100%, you are better off *not* learning SQL at all than learning via this, because then at least you'll have a chance in the future. You are actively learning extremely bad programming habits. I wish I could say the most egregious one is SQL injection (!) but it's not. It's the fact it is teaching "cargo-cult programming": doing things because "Youtube video said so" or "random dude on reddit/StackOverflow/etc said so" rather than understanding how the language works, and why certain practices are better or worse. — Charlieface, May 08 '22 at 23:30

score 2 · Accepted Answer · edited May 08 '22 at 23:33

2

As you said the . is meant to be there and that the column name is passportno., this is where your problem is. It's not something that is expected, or recommended, but it is something that can be handled.

When using Sql you really should be using Parameters when constructing Sql statements in code. It is strongly suggested, not only is it good practice it will protect your applications from targetted attacks, to use Parameters -- Please read Why do we always prefer using parameters in SQL statements?

Change your code to look like this:

string query = "DELETE FROM tablepassengers WHERE [passportno.]=@passportNo;";
using (SqlCommand cmd = new SqlCommand(query, Con))
{
    cmd.Parameters.Add(new SqlParameter("passportNo", SqlDbType.VarChar, 100).Value = tbpassno.Text;
    cmd.ExecuteNonQuery();
}
MessageBox.Show("deleted");
Con.Close();

edited May 08 '22 at 23:33

Charlieface

52,284
6
19
43

answered May 08 '22 at 20:51

JayV

3,238
2
9
14

,, i changed it but theres an error saying argument3: cannot convert from string to int ,, i dont see where in the code there was even attempt for it to be converted – yana May 08 '22 at 21:00
@yana When you created the table, what type did you assign to the column `passportno.` ? Was it a VarChar or Int ? – JayV May 08 '22 at 21:01
i assigned it to varchar, im not working with any int related data on this – yana May 08 '22 at 21:03
@yana I made an update to the Parameter line, please give that a go – JayV May 08 '22 at 21:06
ohh ok that worked thank you, could i ask how changing it to `{ Value = tbpassno.Text });` fixed it? – yana May 08 '22 at 21:11
Previous was just wrong, it didn't assign the `tbpassno.Text` to the parameter, but passed the text value to the `Size` parameter of the constructor. As this answer works for you, please [Accept the answer](https://stackoverflow.com/help/someone-answers) – JayV May 08 '22 at 21:14

score -1 · Answer 2 · answered May 08 '22 at 21:03

-1

        try
        {
            string query = "DELETE FROM tablepassengers WHERE passportno=" + tbpassno.Text;
            SqlCommand cmd = new SqlCommand(query, Con);
            Con.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("deleted");

        }
        catch (SqlException ex)
        {
            MessageBox.Show("Error\n" + ex.Message);
        }
        finally
        {
            Con.Close();
        }

answered May 08 '22 at 21:03

Peter Reda

19
4

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 09 '22 at 13:29

Syntax error near '=' -- delete statement

2 Answers2