Geek asks and answers

System.Data.SqlClient.SqlException: Incorrect syntax next to the 'file' keyword

I am new to AsP.net when I try to use my regular code to connect to the database, there the exception is shown and does not know that the exception is wrong:

" System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'file'."

and this code:

string ID = Request.QueryString["id"];

SqlCommand cmd = new SqlCommand("select title,file path,Upload Date from [Media] where ID=@id", conn);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr=null;

try
{
    conn.Open();
    rdr = cmd.ExecuteReader();
    try
    {
        conn.Open();
        rdr = cmd.ExecuteReader();

        // print the CustomerID of each record
        while (rdr.Read())
        {
            pathTextBox.Text = rdr["file Path"].ToString();
            DateTextBox.Text = rdr["Upload Date"].ToString();
            titleTextBox.Text = rdr["title"].ToString();
        }

        Image1.ImageUrl = pathTextBox.Text;
    }
+3
source share
2 answers

If column names contain spaces (which are not recommended), you should use square brackets such as []. For instance,[Upload Date]

Column names must follow the rules for identifiers.

From Database Identifiers

SELECT *
FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10   --Identifier is a reserved keyword.

This is why you should use them like:

select title,[file path],[Upload Date]

Also, you do not add a parameter value anywhere in your code.

SqlCommand cmd = new SqlCommand("select title,[file path],[Upload Date] from Media where ID=@id", conn);
cmd.Parameters.AddWithValue("@id", YourIDValue);

Also use usingstatement to place your SqlConnectionand SqlCommand.

using (SqlConnection conn = new SqlConnection(YourConnectionString))
{
   using (SqlCommand cmd = new SqlCommand())
   {
      //Your code..
   }
}
+1

, ,

select title,[file path],[Upload Date] from [Media] where ID=@id

using (var conn = new SqlConnection(SomeConnectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "select title,[file path],[Upload Date] from [Media] where ID=@id";
    cmd.Parameters.AddWithValue("@id", idval); // set the id parameter
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read()) // you don't need while loop
        {
            pathTextBox.Text = reader.GetString(reader.GetOrdinal("[file path]"))
        }
    }
}
+1

More articles:


All Articles