Ivan Rubinson Ivan Rubinson - 4 months ago 18
SQL Question

Incorrect syntax near '(' and near '='

What I'm trying to do is to update an entry, and if it doesn't exist - create a new one.

This is what I'm trying to follow.

I'm getting a syntax error exception, and I have no idea what's wrong.




That's how I make my table:

CREATE TABLE [dbo].[Rides] (
[phone] VARCHAR (32) NOT NULL,
[destination] VARCHAR (50) NOT NULL,
[departure] VARCHAR (50) NOT NULL,
[time] DATETIME NOT NULL,
[comment] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([phone] ASC)
);


That's my query:

SqlCommand command = connection.CreateCommand();
command.CommandText =
@"UPDATE Rides SET (destination=@Dest, departure=@Depart, time=@Time, comment=@Comment) WHERE phone=@UName
IF (@@ROWCOUNT=0)
INSERT INTO Rides VALUES (destination=@Dest, departure=@Depart, time=@Time, comment=@Comment)";

command.Parameters.AddWithValue("@UName", entry.phone);
command.Parameters.AddWithValue("@Dest", entry.destinationID);
command.Parameters.AddWithValue("@Depart", entry.departureID);
command.Parameters.AddWithValue("@Time", entry.time.ToString("yyyy-MM-dd HH:mm:ss:fff"));
command.Parameters.AddWithValue("@Comment", entry.comment);
command.ExecuteNonQuery();


That's
entry
:

public struct Entry
{
public string phone;
public string destinationID;
public string departureID;
public DateTime time;
public string comment;
}


That's my error:


An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect syntax near '('.

Incorrect syntax near '='.


Stack trace:

[SqlException (0x80131904): Incorrect syntax near '('.
Incorrect syntax near '='.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442126
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5736904
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +628
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +225
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +337
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +280
RidesDatabaseAccessor.updateEntry(Entry entry) in c:\Users\Climax708\Documents\Programming\TrempLehayal\App_Code\RidesDatabaseAccessor.cs:145
newride.Page_Load(Object sender, EventArgs e) in c:\Users\Climax708\Documents\Programming\TrempLehayal\newride.aspx.cs:75
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51
System.Web.UI.Control.OnLoad(EventArgs e) +95
System.Web.UI.Control.LoadRecursive() +59
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2952

Answer

I made the following changes:

  1. I added a semicolon between your two statements (the UPDATE and the INSERT).
  2. I removed the parentheses from your UPDATE statement (they aren't needed)
  3. I corrected the syntax of your INSERT statement. The column names have to be given separately in their own parenthetical statement before VALUES.

    command.CommandText = @"UPDATE Rides 
         SET destination=@Dest, departure=@Depart, time=@Time, comment=@Comment 
         WHERE phone=@UName; 
         IF (@@ROWCOUNT=0)
             INSERT INTO Rides (destination, departure, time, comment) 
             VALUES (@Dest, @Depart, @Time, @Comment)";