Deleting SQL Record

[Raidensdad]

Ok here is my situation, my finall project is due next week and Ive managed to figure out everything on my own so far except how to delete a specific records from my data grid in visual basic. Im using SQL as the backend. Here is the code

Public Class Movieform
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents btnExit As System.Windows.Forms.Button
    Friend WithEvents btnAdd As System.Windows.Forms.Button
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents dsMovies1 As finalproject2.DataSet1
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.dsMovies1 = New finalproject2.DataSet1
        Me.btnExit = New System.Windows.Forms.Button
        Me.btnAdd = New System.Windows.Forms.Button
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.dsMovies1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.DataSource = Me.dsMovies1.tblMovies
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(0, 0)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.ReadOnly = True
        Me.DataGrid1.Size = New System.Drawing.Size(552, 288)
        Me.DataGrid1.TabIndex = 0
        '
        'dsMovies1
        '
        Me.dsMovies1.DataSetName = "dsMovies1"
        Me.dsMovies1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'btnExit
        '
        Me.btnExit.Location = New System.Drawing.Point(440, 304)
        Me.btnExit.Name = "btnExit"
        Me.btnExit.TabIndex = 2
        Me.btnExit.Text = "E&xit"
        '
        'btnAdd
        '
        Me.btnAdd.Location = New System.Drawing.Point(16, 304)
        Me.btnAdd.Name = "btnAdd"
        Me.btnAdd.TabIndex = 3
        Me.btnAdd.Text = "&Add"
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=""DIRT-TOP"";packet size=4096;integrated security=SSPI;initial catal" & _
        "og=dsMovies1;persist security info=False"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblMovies", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("VideoID", "VideoID"), New System.Data.Common.DataColumnMapping("Title", "Title"), New System.Data.Common.DataColumnMapping("Genre", "Genre"), New System.Data.Common.DataColumnMapping("SubGenre", "SubGenre"), New System.Data.Common.DataColumnMapping("IMDB", "IMDB"), New System.Data.Common.DataColumnMapping("Summary", "Summary")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM tblMovies WHERE (Title = @Original_Title) AND (Genre = @Original_Genr" & _
        "e) AND (IMDB = @Original_IMDB OR @Original_IMDB IS NULL AND IMDB IS NULL) AND (S" & _
        "ubGenre = @Original_SubGenre) AND (Summary = @Original_Summary OR @Original_Summ" & _
        "ary IS NULL AND Summary IS NULL) AND (VideoID = @Original_VideoID)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Title", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Genre", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Genre", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_IMDB", System.Data.SqlDbType.VarChar, 35, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "IMDB", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_SubGenre", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "SubGenre", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Summary", System.Data.SqlDbType.VarChar, 500, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Summary", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_VideoID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "VideoID", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO tblMovies(Title, Genre, SubGenre, IMDB, Summary) VALUES (@Title, @Gen" & _
        "re, @SubGenre, @IMDB, @Summary); SELECT VideoID, Title, Genre, SubGenre, IMDB, S" & _
        "ummary FROM tblMovies WHERE (Title = @Title)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Title", System.Data.SqlDbType.VarChar, 25, "Title"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Genre", System.Data.SqlDbType.VarChar, 12, "Genre"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SubGenre", System.Data.SqlDbType.VarChar, 12, "SubGenre"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IMDB", System.Data.SqlDbType.VarChar, 35, "IMDB"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Summary", System.Data.SqlDbType.VarChar, 500, "Summary"))
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT VideoID, Title, Genre, SubGenre, IMDB, Summary FROM tblMovies"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE tblMovies SET Title = @Title, Genre = @Genre, SubGenre = @SubGenre, IMDB =" & _
        " @IMDB, Summary = @Summary WHERE (Title = @Original_Title) AND (Genre = @Origina" & _
        "l_Genre) AND (IMDB = @Original_IMDB OR @Original_IMDB IS NULL AND IMDB IS NULL) " & _
        "AND (SubGenre = @Original_SubGenre) AND (Summary = @Original_Summary OR @Origina" & _
        "l_Summary IS NULL AND Summary IS NULL); SELECT VideoID, Title, Genre, SubGenre, " & _
        "IMDB, Summary FROM tblMovies WHERE (Title = @Title)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Title", System.Data.SqlDbType.VarChar, 25, "Title"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Genre", System.Data.SqlDbType.VarChar, 12, "Genre"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SubGenre", System.Data.SqlDbType.VarChar, 12, "SubGenre"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@IMDB", System.Data.SqlDbType.VarChar, 35, "IMDB"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Summary", System.Data.SqlDbType.VarChar, 500, "Summary"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Title", System.Data.SqlDbType.VarChar, 25, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Title", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Genre", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Genre", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_IMDB", System.Data.SqlDbType.VarChar, 35, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "IMDB", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_SubGenre", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "SubGenre", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_Summary", System.Data.SqlDbType.VarChar, 500, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Summary", System.Data.DataRowVersion.Original, Nothing))
        '
        'Movieform
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(552, 350)
        Me.Controls.Add(Me.btnAdd)
        Me.Controls.Add(Me.btnExit)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Movieform"
        Me.Text = "Movie Library"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.dsMovies1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate
  

    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(dsMovies1)
        DataGrid1.Refresh()

    End Sub

    Private Sub SqlConnection2_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)

    End Sub

    Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        'Save and Exit
        SqlDataAdapter1.Update(dsMovies1)
        End
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim frmEditform As New frmEdit
        frmEditform.Show()


        Me.Hide()


    End Sub

    Private Sub SqlDataAdapter1_RowUpdated_1(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs) Handles SqlDataAdapter1.RowUpdated

    End Sub

   
End Class

and here is what it looks likehttp://i200.photobucket.com/albums/aa301/raidensdad/movie_library.jpg

I'm willing to add a new delete button or add a button to the actual data grid.
Either way could you be really specific when explaining this? thank you for helping me

 

[Raidensdad]

also if it isnt too much trouble how can i make the imdb column a hyperlink??

 

[jmcilhinney]

You don't have to do anything. The user selects the appropriate row and presses the Delete key. That deletes the row from the DataTable. When you call Update on the DataAdapter all changes, including deletions, are saved to the database. You must have created the appropriate DeleteCommand in your DataAdapter, of course.

 

[Raidensdad]

You don't have to do anything. The user selects the appropriate row and presses the Delete key. That deletes the row from the DataTable. When you call Update on the DataAdapter all changes, including deletions, are saved to the database. You must have created the appropriate DeleteCommand in your DataAdapter, of course.

Im sorry to sound dumb but i dont really get how that is done yet we barely touched on it in class and i havnt got the money for a decent sql book yet

ps. I have disabled the editing from the main page you can only add through clicking the add button which opens a new window I dont want any one to accidentally edit the data

 

[jmcilhinney]

You're already doing it. Your designer code shows that you've already created the DeleteCommand and in your own code you're calling Update on the DataAdapter. That's all you need. What's the problem?

Also, do not EVER call 'End'. Change that to Me.Close.

 

[Raidensdad]

i cant edit the table from that screen, i set it so i have to click add to put in new info because i dont want any one to mess up the data

 

[jmcilhinney]

I see. You've actually set the ReadOnly property of your DataGrid to True, thus selecting a row and pressing the Delete key not an option. You really should provide a proper explanation of your situation rather than relying on people readong all your code because chances are they won't.

In that case you can add a Delete button and then delete the current bound row. I haven't used the DataGrid much myself so I've never had to do this but I believe that would look like this:

Dim cm As CurrencyManager = DirectCast(Me.BindingContext(Me.DataGrid1.DataSource, _
                                                         Me.DataGrid1.DataMember), _
                                       CurrencyManager)
Dim drv As DataRowView = DirectCast(cm.Current, DataRowView)

drv.Row.Delete()

 

[Raidensdad]

YES you are awesome that worked.
Sorry if my information wasnt correct Im really new to this and our teacher doesnt teach that well and like i stated im poor and cant afford a book yet
any clue on the hyperlink statement?

 

[jmcilhinney]

any clue on the hyperlink statement?

I'm not sure what that means.

 

[cjard]

i cant edit the table from that screen, i set it so i have to click add to put in new info because i dont want any one to mess up the data

"I want to be able to delete a row, but I also dont want people to mess up the data so i made it read only and therefore turned off the ability to delete rows"

So do you want to delete rows, or not?

 

[cjard]

I'm not sure what that means.

He wants to make the grid column with the URLs in so as the URLs are clickable.. Probably involves implementing a custom DataGridColumnStyle or whatever it is, so that the rendering component is a linklabel rather than a textbox.. I dont recall how to do that, because it's Dinosaur.NET ;')

 

[cjard]

cant afford a book yet

The info you need is free online, read teh DW1 link in my signature. Dont complain to us that your teacher is a bad one, or use it as an excuse for not doing your work; go and see someone senior in your school or university, take extra lessons, talk to your teacher, your personal tutor and your peers. This is your education and your future, quit moaning and get on with making it!