Data not getting inserted into SQL Server

Question

CreateConnectionString();
SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[MURL_InsertTokens]";

SqlParameter ParameterTokenID = new SqlParameter();
ParameterTokenID.ParameterName = "@TokenID";
ParameterTokenID.SqlDbType = System.Data.SqlDbType.Int;
ParameterTokenID.Direction = ParameterDirection.Input;
ParameterTokenID.Value = token;
cmd.Parameters.Add(ParameterTokenID);

SqlParameter ParameterToken = new SqlParameter();
ParameterToken.ParameterName = "@Token";
ParameterToken.SqlDbType = System.Data.SqlDbType.NVarChar;
ParameterToken.Direction = ParameterDirection.Input;
ParameterToken.Value = token;
cmd.Parameters.Add(ParameterToken);

return true;

Stored procedure:

ALTER PROCEDURE [dbo].[MURL_InsertTokens] 
   @TokenID int ,
   @Token nvarchar(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM Tokens)
        BEGIN
            UPDATE Tokens SET Tokens = @Token WHERE TokenID = @TokenID
        END
    ELSE
        BEGIN
            INSERT INTO Tokens(Tokens) VALUES (@Token)
        END
END

It's returning true but my data is not getting inserted and I don't know why.

Show the code that creates and opens the connection and assigns the connection to the command's Connection property. — Tim
– Tim, Commented May 31, 2013 at 17:21

SQLMenace · Accepted Answer · 2013-05-31 17:07:15Z

2

you forgot to check for the ID here

IF EXISTS (SELECT * FROM Tokens)

If there is at least one row in the table the insert part will never fire, just the update part

You probably want

IF EXISTS (SELECT * FROM Tokens WHERE TokenID = @TokenID)

answered May 31, 2013 at 17:07

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikhil Agrawal Over a year ago

Using * will bring all columns with all data. To satisfy IF Exists instead use IF EXISTS (SELECT 1 FROM Tokens WHERE TokenID = @TokenID) This way the data will be just a column with all values as 1. This would be lighter on SQLEngine.

marc_s Over a year ago

@NikhilAgrawal: this statement is plain wrong. SELECT * inside a IF EXISTS() will NOT retrieve all data. The IF EXISTS() just checks for existence of data - it doesn't retrieve anything. Using IF EXISTS (SELECT 1 ...) and IF EXISTS (SELECT * ...) are identical in performance and execution plan

LarsTech · Accepted Answer · 2013-05-31 17:09:26Z

1

Are you calling the procedure?

cmd.ExecuteNonQuery();

answered May 31, 2013 at 17:09

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

2 Comments

vini Over a year ago

still not happening don't know why

LarsTech Over a year ago

@vini ConnectionString looks like an issue. Is it attached to the SqlCommand object? Is it opened?

Matt Houser · Accepted Answer · 2013-05-31 17:43:45Z

0

Your snippet of code does not appear to actually perform the action that you are building. Add:

cmd.ExecuteNonQuery();

To actually perform the command.

edited May 31, 2013 at 17:43

answered May 31, 2013 at 17:10

Matt Houser

36.3k6 gold badges78 silver badges93 bronze badges

1 Comment

Matt Houser Over a year ago

I was not calling ExecuteDataReader. I had a typo in my code. I was calling ExecuteNonReader but was intending it to be ExecuteNonQuery.

Collectives™ on Stack Overflow

Data not getting inserted into SQL Server

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related