SQL Server parameters C#

Question

Ok, this should add a new hashtag to the databse if it doesn't already exist, else it should increment the counter.

However, all it does so far is adds new ones, even if they're the same. So I have lots of identical hashtags, all with 1. Any suggestions?

HashTagReader r = new HashTagReader();

int i;
i=1;

if (r.HashTagSearch(s))
    MessageBox.Show("I Found it!");

else
{
    SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Table1 (HashTag, Counter) Values (@HashTag,@Counter)", connection);

    myCommand.Parameters.Add("@HashTag", SqlDbType.VarChar, 50).Value = s;  //Your hashTagvalue
    myCommand.Parameters.Add("@Counter", SqlDbType.VarChar, 50).Value = i++; //Your Counter Value
    myCommand.ExecuteNonQuery();
}

connection.Close();

The HashTag Search is implemented as such

public bool HashTagSearch(string hashtagstring)
{
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jordan Moffat\Desktop\coursework\WindowsFormsApplication1\WindowsFormsApplication1\HashTags.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        //  SqlConnection connection = new SqlConnection();
        //  connection.ConnectionString = "C:/Users/Jordan Moffat/Desktop/coursework/WindowsFormsApplication1/WindowsFormsApplication1/HashTags.mdf"; //Your connection string
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "FindString";
        command.Parameters.AddWithValue("@MyString", hashtagstring);
        try
        {
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                return true;
            }
        }
            catch (Exception)
    {
        // MessageBox.Show("heel");
    }
    finally
    {
        if (connection.State == ConnectionState.Open)
            connection.Close();
    }
    return false;
}
    }

Has variable 'i' modified elsewhere? In the sample being provided, it will always have the same value. Besides, auto-increment implementation at the client side it's a very, very bad practice. — Dennis
– Dennis, Commented May 2, 2012 at 5:27
It is not very clear from your question whether you would like to update the existing database record with existing hashtag or create a new database record with the same hashtag but a diffenter counter. Please clarify this. — user1234883
– user1234883, Commented May 2, 2012 at 5:28

Eren Ersönmez · Accepted Answer · 2012-05-02 05:42:40Z

2

It's hard to tell from the code provided, but looks like this code will always either find the hashtag or add a new row with counter=1. If I understand what you're trying to do correctly, you want to find the row for the hashtag, and then update its "counter" value. If not found, insert a new row with counter=1.

I would recommend writing a stored procedure that performs the update/insert wrapped in a transaction.

CREATE PROC InsertOrUpdateHashTag
(
    @hashtag nvarchar(100)
)
AS
BEGIN TRAN   
    UPDATE Table1 SET Counter+=1 WHERE Hashtag = @hashtag   
    IF @@ROWCOUNT = 0   
    BEGIN
        INSERT Table1 (Hashtag, Counter) VALUES (@hashtag,1)
    END
COMMIT TRAN

edited May 2, 2012 at 5:42

answered May 2, 2012 at 5:30

Eren Ersönmez

39.2k8 gold badges76 silver badges94 bronze badges

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

Comments

Sasha · Accepted Answer · 2012-05-02 05:22:03Z

0

Try using prefix increment, not postfix. Like this:

myCommand.Parameters.Add("@Counter", SqlDbType.VarChar, 50).Value = ++i;

answered May 2, 2012 at 5:22

Sasha

1,8161 gold badge17 silver badges18 bronze badges

Comments

bitoshi.n · Accepted Answer · 2012-05-02 05:33:30Z

0

I don't get the counter that what you want. counter will always give you value 1, because you set it

int i;

i=1

even if you use i++.

If you mean that counter is hashtag number, you can declare in the database that column "counter" is autoincremented index.

edited May 2, 2012 at 5:33

answered May 2, 2012 at 5:28

bitoshi.n

2,3381 gold badge16 silver badges16 bronze badges

Comments

Damith · Accepted Answer · 2012-05-02 05:39:33Z

0

You can try with query like below with Stored procedure.

IF EXISTS (SELECT * FROM dbo.Table1 WHERE HashTag = @HashTag)
UPDATE dbo.Table1 SET Counter = @Counter+1
ELSE
INSERT INTO dbo.Table1 (HashTag, Counter) Values (@HashTag,@Counter)

edited May 2, 2012 at 5:39

answered May 2, 2012 at 5:30

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Collectives™ on Stack Overflow

SQL Server parameters C#

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related