Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

Urgent 500 points! SqlException: Incorrect syntax near '?'

Does anyone see what's wrong with this code snippet that would cause an "SqlException: Incorrect syntax near '?'" when executing the DataAdapter.Update method?

    private Guid SetupProject(SqlConnection msSql, string projectName)
    {
        Guid guid;
        // See if the project exists
        SqlDataAdapter da = new SqlDataAdapter(
            "SELECT id, name FROM Project WHERE name = '" + projectName + "'",msSql);
        DataSet ds = new DataSet();
        da.Fill(ds, "Project");
        if (ds.Tables[0].Rows.Count == 0)
        {
            // Project does not exist, add it.
            da.InsertCommand = msSql.CreateCommand();
            da.InsertCommand.CommandText =
                "INSERT INTO Project (id, name) VALUES (?,?)";
            da.InsertCommand.Connection = msSql;
            da.InsertCommand.Parameters.Add(
                new System.Data.SqlClient.SqlParameter("id", SqlDbType.UniqueIdentifier, 0, "id"));
            da.InsertCommand.Parameters.Add(
                new System.Data.SqlClient.SqlParameter("name", SqlDbType.NVarChar, 0, "name"));

            DataRow newRow = ds.Tables[0].NewRow();
            guid = System.Guid.NewGuid();
            newRow["id"] = guid;
            newRow["name"] = projectName;
            ds.Tables[0].Rows.Add(newRow);
            da.Update(ds, "Project");
        }
        else
            guid = (Guid)ds.Tables[0].Rows[0]["id"];

        ds.Dispose();
        da.Dispose();

        return guid;
    }

0
tkendall57
Asked:
tkendall57
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
Hi tkendall57,

shouldn't it be
 da.InsertCommand.CommandText =
                 "INSERT INTO Project (id, name) VALUES (@id,@name)";


Cheers!
0
 
topdog770Commented:

// this line should be
new System.Data.SqlClient.SqlParameter("id", SqlDbType.UniqueIdentifier, 0, "id"));

// like this                                          // param name                                    // column name in db
new System.Data.SqlClient.SqlParameter("@id", SqlDbType.UniqueIdentifier, 0, "id"));
new System.Data.SqlClient.SqlParameter("@name", SqlDbType.NVarChar, 0, "name"));


// this should be...
  da.InsertCommand.CommandText =
                "INSERT INTO Project (id, name) VALUES (?,?)";

// like this..
  da.InsertCommand.CommandText =
                "INSERT INTO Project (id, name) VALUES (@id,@name)";

In my limited perspective( Oracle user), Oracle will allow the ? marks.. and actually doesn't work with the names and Sql works with names but not (?? I don't think supports the question mark approach )

Very simple and quick overview..
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now