Using VB.net IF statement with SQL value

Question

I am trying to use an IF statement to do a certain action if my SQL value is greater than 1.

However when I run the below code, it returns an error.

Operator '>' is not defined for types 'System.Data.SqlClient.SqlCommand' and 'Integer'.

Here is the code:

Using cmdb = New SqlCommand("INSERT INTO AdditionalDaisyCodes (AddCode) VALUES (@AddCode)", con)
  con.Open()
  cmdb.ExecuteNonQuery()
  con.Close()

  If cmdb > 1 Then

    MessageBox.Show("Duplicate Codes Exist!", "Indigo Billing", _
    MessageBoxButtons.OK, MessageBoxIcon.Information)

  Else
  End If
End Using

Can anybody help with the correct syntax?

ExecuteNonQuery() is never going to be greater than 1 - all you're doing is inserting a single row, and you're not even defining the value to be passed in via the parameter you use in the query string. — Tim
– Tim, Commented May 12, 2014 at 8:21

user3217843 · Accepted Answer · 2014-05-12 07:41:03Z

4

Use this

   int result=cmdb.ExecuteNonQuery();
if result > 1 then
//code here
else
end if

answered May 12, 2014 at 7:41

user3217843

4241 gold badge3 silver badges17 bronze badges

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

1 Comment

Lali Over a year ago

@user3580480! actually result will be returned by ExecuteNonQuery() instead of SQLCommand object.

Tim · Accepted Answer · 2014-05-12 08:39:55Z

You have a bigger problem than just a syntax error. Based on your posted code, it looks like you want to inform the user that duplicate codes exist. Since you have an Else, I'm going to make a guess and say that if duplicate codes aren't found, you want the insertion made.

Currently the number of rows affected by ExecuteNonQuery() will never be greater than 1. In addition, in your posted code you never assign a value to the @AddCode parameter - if the AddCode column cannot be null, that could cause an error.

What you probably want to do is to check to see if the code exists, and if not then add it. Something like this would do the trick:

Using cmdb = New SqlCommand("SELECT * FROM AdditionalDaisyCodes WHERE AddCode = @AddCode", con)
    con.Open()

    cmdb.Parameters.AddWithValue("@AddCode", txtAddCode.Text)
    Dim reader As SqlDataReader = cmdb.ExecuteReader()

    If reader.HasRows Then
        MessageBox.Show("Duplicate Codes Exist!", "Indigo Billing", _
MessageBoxButtons.OK, MessageBoxIcon.Information)

    Else
        cmdb.CommandText = "INSERT INTO AdditionalDaisyCodes(AddCode) Values(@AddCode)"
        cmdb.Parameters.AddWithValue("@AddCode", txtAddCode.Text)
        cmdb.ExecuteNonQuery()

    End If

    con.Close()
End Using

txtAddCode.Text in the above example is assuming the code is coming from a TextBox named txtAddCode. First it checks to see if the code is already in the table by running a SELECT query restrained by a WHERE clause. If the returned SqlDataReader has rows, then that code is already in the table and the message box is displayed.

If the SqlDataReader does not have rows, then the code is not in the table and cmdb is reused to do the insert.

Also note that you can put the SqlConnection in a Using block as well, eliminating the need to call Close() on the connection explicitly.

Collectives™ on Stack Overflow

Using VB.net IF statement with SQL value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related