INSERT INTO - Missing semicolon (;) at end of SQL statement

Question

I am trying to insert the img source, which has just been upload, to the database with SQL INSERT INTO.

This is the line in which I get the:

"Missing semicolon (;) at end of SQL statement."

Error.

command.CommandText = "INSERT INTO users (pic) VALUES (Images/"+fileName+") WHERE id="+theId;

This is the whole .aspx.cs file:

using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Collections.Generic;  

public partial class CS : System.Web.UI.Page
{

  protected void Upload(object sender, EventArgs e)
  {

    if (FileUpload1.HasFile)
    {
        string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Images/") + fileName);

        string theId = Request.Cookies["Logged"].Value;
        System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
        connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Etay\Documents\Visual Studio 2012\WebSites\Josef\Shared\users.mdb";
        try
        {
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
            command.Connection = connection;
            connection.Open();
            command.CommandText = "INSERT INTO users (pic) VALUES (Images/"+fileName+") WHERE id="+theId;
            int rows = command.ExecuteNonQuery();
            Response.Redirect("~/signIn.cshtml");
        }
        finally
        {
            connection.Close();
        }
    }
  }
}

Please don't concatenate the filename and id into the SQL statement. Use parameters, or else you set yourself up for SQL injection. — BlakeH, Apr 21 '16 at 16:27
Remove sql-server tag, use MS Access. You are getting incorrect code answers for MS Access (OleDb) — Crowcoder, Apr 21 '16 at 16:36
The proper way to redirect is: `Response.Redirect(url, false); Context.ApplicationInstance.CompleteRequest();` — Crowcoder, Apr 21 '16 at 16:39

score 7 · Answer 1 · edited May 23 '17 at 12:23

Couple of things:

INSERT statement doesn't have WHERE clause, you probably need UPDATE statement if you are trying to modify existing record.
Use parameters. You are prone to SQL Injection

If you are going to INSERT new record then the statement should be like:

command.CommandText = "INSERT INTO users (pic) VALUES (@image)";
command.Parameters.AddWithValue("@image", "Images / " + fileName);

If you are going to update then use:

command.CommandText = "UPDATE users SET PIC = @images WHERE id=@id";
command.Parameters.AddWithValue("@image", "Images / " + fileName);
command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) {Value = theId});

Also consider enclosing Connection and Command object in using statement that will ensure proper disposal of resources.

Just noticed that you are actually targetting MS Access. The concept of using parameters should remain same as of SQL Server. See this for adding parameters with OleDbcommand: using parameters inserting data into access database

In MS Access, regardless of the identifiers you put in, the parameters will actually be passed in as positional. Something to be aware of. — willaien, Apr 21 '16 at 16:53

INSERT INTO - Missing semicolon (;) at end of SQL statement

1 Answers1