'Syntax error in from clause.' VB.NET - access

Question

1.00/5 (1 vote)

See more:

At first, when it showed this error
i did put [*FROM] before the From statement
e.g: ["Delete student_id *FROM student]

Still it doesn't work

Can someone pls help me on this

What I have tried:

VB

Private Sub btndelete_Click_1(sender As Object, e As EventArgs) Handles btndelete.Click
        cmdDelete.CommandText = "Delete [student_id] FROM student as S1 join payment as S2 on S1.student_id=S2.student_id where S1.student_id=(" + txtsid.Text + ");"
        cmdDelete.CommandType = CommandType.Text
        cmdDelete.Connection = cnnOLEDB
        cmdDelete.ExecuteNonQuery()
        MessageBox.Show("Are You Sure You Want To Delete?", "Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If DialogResult.Yes Then
            UserHomepage.Show()
            Me.Hide()
        ElseIf DialogResult.No Then
            Me.Show()
            UserHomepage.Hide()
            lblname.Hide()
            txtsid.ResetText()
        End If
    End Sub

Posted 8-Jun-18 3:50am

Member 13864666

Updated 8-Jun-18 10:23am

Maciej Los

v2

Add a Solution

Comments

MadMyche 8-Jun-18 17:26pm

Please explain why the Payment table (S2) is being referenced; will help fix your coding issues

MC LENGDING 27 22-Sep-23 3:27am

AN ERROR MESSAGE "SYNTAX ERROR FROM CLAUSE WHEN I WANT IT TO DISPLAY DATAFIEDS IN VB"HOW DO I GO ABOUT SOMEONE HELP

3 solutions

Solution 1

As the error says, your DELETE syntax is incorrect.

Your code is also vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

And you probably want to ask the user whether or not they want to delete the record BEFORE you actually delete the record! :)

VB.NET

cmdDelete.CommandText = "Delete student.* FROM student as S1 join payment as S2 on S1.student_id=S2.student_id where S1.student_id = ?"
cmdDelete.Parameters.AddWithValue("sid", txtsid.Text)

EDIT: As pointed out in solution 3, Access doesn't support joins in the DELETE statement:
DELETE Statement (Microsoft Access SQL)[^]

VB.NET

cmdDelete.CommandText = "DELETE * FROM student WHERE Exists(SELECT 1 FROM payment WHERE payment.student_id = student.student_id) And student_id = ?"
cmdDelete.Parameters.AddWithValue("sid", txtsid.Text)

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Posted 8-Jun-18 3:59am

Richard Deeming

Updated 9-Jun-18 4:10am

v3

Comments

Dave Kreskowiak 8-Jun-18 10:46am

Umm... "DELETE student.* FROM ..."??

I don't do that much SQL, but shouldn't that be "DELETE FROM student ..."?

I don't even think that join would need to be there either.

Richard Deeming 8-Jun-18 10:47am

In SQL it would be, but the OP was asking about MS Access, which is a strange beast. :)

MadMyche 8-Jun-18 11:42am

Perhaps he only wants to delete the student if said student has paid....

Member 13864666 8-Jun-18 14:19pm

In the 5th line, last word which is "sid"
Is it the column name in my database?
If its not may i know what is it?
And why is tht for?
Thanks

Richard Deeming 8-Jun-18 14:37pm

"sid" is the parameter name.

The OLE DB commands don't support named parameters, so you just need to give it a unique name within the command, and make sure you add the parameters in the same sequence as the placeholder characters - ? - in the command text.

Maciej Los 8-Jun-18 16:12pm

Supports, supports...
The best way to find out is to check it out.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2018-06-08T04:08:00

Never, ever, construct an SQL query like you do by concatenating string obtained from user inputs. This leaves your code wide open to SQL injection attacks. Better use parameterized queries instead. This subject is discussed daily here on CP so you will not have a hard time finding some more informations about it (search sql injection in qa, for example).

Moreover, about your SQL query, to delete a row in a table you do not need to specify a column name, a DELETE statement is not a SELECT statement.
Plus, to delete a row in table S1, you do not need any join to S2 either.
So, all this would resort to a block of code which would look like:

cmdDelete.CommandText = "Delete FROM student where student_id=@id;"
cmdDelete.Paramaters.AddWithValue("@id", txtsid.Text)
...

If student_id column is of integer type, you may have to write:

VB

cmdDelete.CommandText = "Delete FROM student where student_id=@id;"
Dim id as Integer
If (integer.TryParse(txtsid.Text, out id) Then
   cmdDelete.Paramaters.AddWithValue("@id", id)
Else
   '' Error: provided id was not a valid integer representation
End If

instead.

Maciej Los · Accepted Answer · 2018-06-08T10:25:00

Solution 3

As far as i know, MS Access does not support DELETE + JOIN. You have to change your sql command to below form:

SQL

DELETE Table1.*
FROM Table1
WHERE EXISTS( Select 1 From Table2 Where Table2.Name = Table1.Name )

But, when i did look on you query again, i think you need only:

SQL

DELETE
FROM student
WHERE student_id=?;

Posted 8-Jun-18 10:25am

Maciej Los

Comments

MadMyche 8-Jun-18 17:30pm

Why aren't you using named parameters?

'Syntax error in from clause.' VB.NET - access

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us