Solved

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

Posted on 2006-06-22
2
590 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use of unassigned local variable 'id' 6 38
Reading the Web.Config using IIS 7.5? 4 35
SqlDataBase 7 46
Add different cell to otherwise similiar row 4 38
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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