[RESOLVED] How do you check if record is available in the database

[korae]

I have this code that Deletes a record by its IP address inputted by the user. How do you check if that record is available? how do evaluate first that the IP address inputted is not in the database before deleting anything? in vb 6.0 I used to do this line:

If rs.EOF And rs.BOF Then

but it doesn't work anymore in vb 2008

If TextBox1.Text = "" Then
                    MsgBox("Input IP Address", MsgBoxStyle.OkOnly, Title:="")
                Else
                    Dim myConnection As OleDbConnection = New OleDbConnection
                    Dim myCommand As OleDbCommand = New OleDbCommand
                    myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Network_Info.mdb"
 
                    myConnection.Open()
 
                    myCommand.Connection = myConnection
                    myCommand.CommandText = "DELETE * from Computer_Connection_Info WHERE IP_Address ='" & TextBox1.Text & "'"
 
                    myCommand.ExecuteNonQuery()
End If

[danasegarane]

Check for the existance of the IP Address and delete the entry

myCommand.CommandText = "If Exists(Select 1 From Computer_Connection_Info Where P_Address ='" & TextBox1.Text & "'"  )DELETE * from Computer_Connection_Info WHERE IP_Address ='" & TextBox1.Text & "'"

I would suggest you to go for the OLEDB parameter rather than the String Concatenation.

If any Record is Deleted the

myCommand.ExecuteNonQuery()

will return the number of rows affected

[korae]

How do you apply OLEDB parameters?

[kareninstructor]

How do you apply OLEDB parameters?

The example below is simply a template which is doing a insert, adjust this to do a delete instead.

AddWithValue
Param 1 is the param name
Param 2 is the value to place into it.

Create a command object

Dim objCommand As OleDbCommand = New OleDbCommand()

Fill in the command, the value section here will be populated next.

      objCommand.CommandText = _
         "INSERT INTO Customers " & "(FirstName, LastName,Address) " & "VALUES(@firstname,@lastname,@address);"

Do the values

      objCommand.Parameters.AddWithValue("@firstname", p.FirstName)
      objCommand.Parameters.AddWithValue("@lastname", p.LastName)
      objCommand.Parameters.AddWithValue("@address", p.Address)

[kareninstructor]

How do you apply OLEDB parameters?

Also if using VS2008 or higher you can write the entire statement in an XElement and convert to a string as you are assigning the statement to the command commandtext as follows.

        Dim myCommand As OleDbCommand = New OleDbCommand()
        myCommand.CommandText = _
        <SQL>
            DELETE 
                FROM Computer_Connection_Info 
            WHERE IP_Address ='<%= TextBox1.Text %>'
        </SQL>.Value

[korae]

wait guys. My delete code above works fine. What I want to do is add some code after my else that will check first if the inputted IP address is available so that I can add a msgbox that that IP address does not exist if its not available. I'm really not familiar with vb so I'm having a hard time understanding about what you are referring to. Just a simple checking method will do.

[kareninstructor]

wait guys. My delete code above works fine. What I want to do is add some code after my else that will check first if the inputted IP address is available so that I can add a msgbox that that IP address does not exist if its not available. I'm really not familiar with vb so I'm having a hard time understanding about what you are referring to. Just a simple checking method will do.

Dim myCommand As OleDbCommand = New OleDbCommand()
        myCommand.CommandText = _
        <SQL>
            SELECT COUNT(*)  
                FROM Computer_Connection_Info 
            WHERE IP_Address ='<%= TextBox1.Text %>'
        </SQL>.Value

Dim Count as Integer = myCommand.ExecuteScalar()