CODEWITHSUNDEEP
c#sql-server
Jordan Moffat
Jordan Moffat

Reputation: 337

SQL Server parameters C#

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;
}
    }

Upvotes: 0

Views: 921

Answers (4)

Eren Ersönmez
Eren Ersönmez

Reputation: 39085

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

Upvotes: 2

Damith
Damith

Reputation: 63065

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)

Upvotes: 0

bitoshi.n
bitoshi.n

Reputation: 2318

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.

Upvotes: 0

Sasha
Sasha

Reputation: 1736

Try using prefix increment, not postfix. Like this:

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

Upvotes: 0

Related Questions