ExecuteNonQuery

[irque]

I have to create a SQL server DB that has a table named people with, people_id int PK IDENTITY, firstName varChar(30), lastName varChar(40).
(completed)

create a windows program that lists the firstName and lastName combined with a space detween them in a listBox
(completed)

provide functionality in code using command objects/parameters (no wizards) to add and delete from database and reflect the changes in the listBox
(i'm using two textBoxes)
add (completed)

I am having trouble with the delete. Can someone maybe make a suggestion..

thanks

this code populates the listBox on page load...it works.

Public Class Form1
    Private Sub loadListBox()
        ListBox1.Items.Clear()
        Dim connStr As String = My.Settings.dbPeopleConnectionString
        Dim sql As String
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        sql = "SELECT people_id, firstName, lastName FROM people"
        conn = New SqlConnection(connStr)
        conn.Open()
        cmd = New SqlCommand(sql, conn)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        Dim moreresults As Boolean = True
        Do While moreresults
            While reader.Read
                ListBox1.Items.Add(reader("firstName") & " " & (reader("lastName")))
            End While
            moreresults = reader.NextResult()
        Loop
        conn.Close()

    End Sub

here is my ADD code...it works.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim connStr As String = My.Settings.dbPeopleConnectionString
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        conn = New SqlConnection(connStr)
        conn.Open()
        cmd = New SqlCommand()
        cmd.Connection = conn

        ' create parameters
        Dim Pfname As New SqlParameter("@firstName", SqlDbType.VarChar)
        Pfname.Direction = ParameterDirection.Input
        Dim Plname As New SqlParameter("@lastName", SqlDbType.VarChar)
        Plname.Direction = ParameterDirection.Input
        ' end parameters

        Pfname.Value = txtFirst.Text
        Plname.Value = txtLast.Text
        cmd.Parameters.Add(Pfname)
        cmd.Parameters.Add(Plname)
        cmd.CommandText = "INSERT INTO people(firstName, lastName) VALUES (@firstName, @lastName)"
        Dim result As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        If result > 0 Then
            MessageBox.Show("Success")
        Else
            MessageBox.Show("Failure")
        End If
        loadListBox()
        txtFirst.Text = ""
        txtLast.Text = ""
        txtFirst.Focus()

    End Sub

here is my DELETE code, it is the same as the ADD, just changed the SQL statement...it crashes at ExecuteNonQuery()

incorrect syntax near '(',

 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim connStr As String = My.Settings.dbPeopleConnectionString
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        conn = New SqlConnection(connStr)
        conn.Open()
        cmd = New SqlCommand()
        cmd.Connection = conn

        ' create parameters
        Dim Pfname As New SqlParameter("@firstName", SqlDbType.VarChar)
        Pfname.Direction = ParameterDirection.Input
        Dim Plname As New SqlParameter("@lastName", SqlDbType.VarChar)
        Plname.Direction = ParameterDirection.Input
        ' end parameters

        ' textBoxes
        Pfname.Value = txtFirst.Text
        Plname.Value = txtLast.Text
        cmd.Parameters.Add(Pfname)
        cmd.Parameters.Add(Plname)
        cmd.CommandText = "DELETE FROM people(firstName, lastName) VALUES (@firstName, @lastName)"
        Dim result As Integer = cmd.ExecuteNonQuery()   <<--incorrect syntax
        conn.Close()
        If result > 0 Then
            MessageBox.Show("Success")
        Else
            MessageBox.Show("Failure")
        End If
        loadListBox()

    End Sub
End Class

[si_the_geek]

Welcome to VBForums

The syntax of a Delete is not the same as an Insert, so you need to change more than just the first two words of the statement.

You can see the syntax needed in lots of SQL tutorials, including the one at the start of the "SQL" section of our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[irque]

thanks....yes I saw that my syntax was wrong and fixed it after posting...


here is corrected line:

cmd.CommandText = "DELETE FROM people WHERE firstName='@firstName' AND lastName='@lastName'"
        Dim result As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        If result > 0 Then
            MessageBox.Show("Success")
        Else
            MessageBox.Show("Failure")
        End If
        loadListBox()

but messageBox posts "Failure"

I also used a TRY to catch exception but none showed..

Try
     cmd.ExecuteNonQuery()
Catch ex As Exception
     MessageBox.Show(ex.Message)
End Try

[Shaggy Hiker]

Ok, I would guess then, that nothing was deleted, but the SQL looks reasonably good. Are you sure that the parameters are correct? Do they have the right values?

Also, you don't need those single quotes when using parameters. After thinking about it, those should be causing you trouble, as they would probably meant that you are looking for a string surrounded by single quotes once the parameters are put in place.

I was also curious about this line from your first post:

moreresults = reader.NextResult()

You don't HAVE more results in that query, there is only the one result set which you are iterating through with the inner loop. Where did you get that outer loop?

[irque]

Ok, I would guess then, that nothing was deleted, but the SQL looks reasonably good. Are you sure that the parameters are correct? Do they have the right values?

Also, you don't need those single quotes when using parameters. After thinking about it, those should be causing you trouble, as they would probably meant that you are looking for a string surrounded by single quotes once the parameters are put in place.

I was also curious about this line from your first post:

moreresults = reader.NextResult()

You don't HAVE more results in that query, there is only the one result set which you are iterating through with the inner loop. Where did you get that outer loop?

Thanks....it was the single quotes, I removed them and success.....it is odd as they are required in SQL server.

cmd.CommandText = "DELETE FROM people WHERE firstName= @firstName AND lastName= @lastName"

thanks again

[jmcilhinney]

it is odd as they are required in SQL server.

It's not because they're not required. In T-SQL, single quotes denote a text literal. A parameter is not a text literal, so it doesn't require single quotes. It's exactly the same as using String literals or variables in VB. If you use a String literal in code you wrap it in double quotes. If you use a String variable though, you just use the variable name. You don't wrap the variable name in double quotes.