sql statement if exists update else insert sql server

[zizo.ahmed.779]

hello everybody...nice to back again
i m working on project to calculate measures and numbers and insert data inside datagridview(dgv) from number.text and measure.text but if value of measure.text present in dgv son update number cell with new value + present value
problem is how to do so....
i tried alot of sql statements but i have errors

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        If Not sqlcon.State = ConnectionState.Open Then
            '  open connection
            sqlcon.Open()
        End If
                com2 = New SqlCommand("UPDATE frames SET [number] = [number] + '" & number.Text & "' where measure = '" & measure.Text & "'", sqlcon)
                com2.ExecuteNonQuery()

                com = New SqlCommand("insert into frames ([number],measure) values('" & number.Text & "','" & measure.Text & "'),('" & number1.Text & "','" & measure1.Text & "')", sqlcon)
                com.ExecuteNonQuery()

            End If
        Next


    End Sub

this is the code i m working on but i search for better way and find this ones

com2 = New SqlCommand("UPDATE frames SET [number] = [number] + '" & number.Text & "' where measure = '" & measure.Text & "' if @@rowcount=0 insert into frames ([number],measure) values('" & number.Text & "','" & measure.Text & "'),('" & number1.Text & "','" & measure1.Text & "'", sqlcon)
com2.ExecuteNonQuery()

but it give me error Incorrect syntax near '93.5' .........93.5 is value exists in measure.text but all sql statement ok except this error i dont know why
i have tried another way

com = New SqlCommand("IF NOT EXISTS (SELECT * FROM  frames) BEGIN insert into frames ([number],measure) values('" & number.Text & "','" & measure.Text & "'),('" & number1.Text & "','" & measure1.Text & "') END ELSE BEGIN UPDATE frames SET [number] = [number] + '" & number.Text & "' where measure = '" & measure.Text & "'", sqlcon)
com2 = New SqlCommand("IF EXISTS (SELECT * FROM frames) THEN BEGIN Update frames set [number] = [number] + '" & number.Text & "' where measure = '" & measure.Text & "' End ELSE bEGIN INSERT INTO frames ([number],measure) values('" & number.Text & "','" & measure.Text & "'),('" & number1.Text & "','" & measure1.Text & "' END", sqlcon)
com2.ExecuteNonQuery()

i got this error "****Incorrect syntax near the keyword 'THEN'.

Incorrect syntax near the keyword 'ELSE'.

Incorrect syntax near the keyword 'END'.****" or

i got this error "Incorrect syntax near '61.19'." .........61.19 is value present in measure.text

please help me find way to do that without errors or what wrong in these????

i heared about transaction but i searched this way and understand nothing .....

[djjeavons]

Hi

The following syntax should work:

IF EXISTS (your select statement)
    BEGIN
        'Your update statement
    END
ELSE
    'Your insert statement

Note in the above there is no BEGIN or END on the ELSE part of the statement.

Also, I am not sure if your number and measure are string values or numeric but you are treating them as if they are strings. Therefore, I would recommend that you use parameters to avoid having to worry about data types. The following is a code sample using the above:

Dim connectionString As String = "Your connection string"

Using connection As New SqlConnection(connectionString)

    connection.Open()

    Dim sqlStatement As String = "IF EXISTS (select measure FROM frames WHERE measure=@Measure) " _
                               & "BEGIN " _
                               & "UPDATE frames SET number=@Number WHERE measure=@Measure " _
                               & "END " _
                               & "ELSE " _
                               & "INSERT INTO frames (number, measure) VALUES (@Number, @Measure)"

    Using Command As New SqlCommand(sqlStatement, connection)

        Command.Parameters.AddWithValue("@Number", number.Text)
        Command.Parameters.AddWithValue("@Measure", measure.Text)
        Command.ExecuteNonQuery()

    End Using

End Using

HTH

[zizo.ahmed.779]

thank you so much djjeavons
it works perfect
thanks for help hope to help you any time