Solved

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

Posted on 2006-06-22
2
598 Views
Last Modified: 2008-01-09
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
Comment
Question by:tkendall57
2 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 150 total points
ID: 16962977
Hi tkendall57,

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


Cheers!
0
 
LVL 12

Accepted Solution

by:
topdog770 earned 350 total points
ID: 16963379

// 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question