Click here to Skip to main content
15,884,353 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
Imports System.Data.SqlClient

Public Class view1
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Button1.DataBinding
        If Not IsPostBack Then
            BindGridView()
        End If
    End Sub

    Private Function GetConnectionString() As String
        Return System.Configuration.ConfigurationManager.ConnectionStrings("nitink").ConnectionString
    End Function

    Private Sub BindGridView()
        Dim Customer As New DataTable()
        Dim connection As New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Try
            connection.Open()
            Dim sqlStatement As String = "SELECT Top(10)* FROM Customer"
            Dim cmd As New SqlCommand(sqlStatement, connection)
            Dim sqlDa As New SqlDataAdapter(cmd)

            sqlDa.Fill(Customer)
            If Customer.Rows.Count > 0 Then
                GridView1.DataSource = Customer
                GridView1.DataBind()
            End If
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Fetch Error:"
            msg += ex.Message
            Throw New Exception(msg)
        Finally
            connection.Close()
        End Try
    End Sub

    Private Sub UpdateOrAddNewRecord(ByVal ID As String, ByVal Company As String, ByVal Name As String, ByVal Title As String, ByVal Address As String, ByVal CounTry As String, ByVal isUpdate As Boolean)
        Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Dim sqlStatement As String = String.Empty

        If Not isUpdate Then
            sqlStatement = "INSERT INTO Customer" + "(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +
"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)"
        Else
            sqlStatement = "UPDATE Customer" + "SET CompanyName = @CompanyName,ContactName = @ContactName," + "ContactTitle = @ContactTitle,Address = @Address,Country = @Country" + "WHERE CustomerID = @CustomerID,"
        End If
        Try
            connection.Open()
            Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
            cmd.Parameters.AddWithValue("@CustomerID", ID)
            cmd.Parameters.AddWithValue("@CompanyName", Company)
            cmd.Parameters.AddWithValue("@ContactName", Name)
            cmd.Parameters.AddWithValue("@ContactTitle", Title)
            cmd.Parameters.AddWithValue("@Address", Address)
            cmd.Parameters.AddWithValue("@Country", CounTry)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Insert/Update Error:"
            msg += ex.Message
            Throw New Exception(msg)

        Finally
            connection.Close()
        End Try
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, False)

        BindGridView()

    End Sub

    Protected Sub GridView1_RowEditing(sender As Object, e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
        GridView1.EditIndex = e.NewEditIndex ' turn to edit mode
        BindGridView()

    End Sub

    Protected Sub GridView1_RowCancelingEdit(sender As Object, e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
        GridView1.EditIndex = -1 'swicth back to default mode
        BindGridView()

    End Sub

    Protected Sub GridView1_RowUpdating(sender As Object, e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text  'ID
        Dim company As String = (CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox)).Text  'Company
        Dim name As String = (CType(GridView1.Rows(e.RowIndex).Cells(2).Controls(0), TextBox)).Text  'Name
        Dim title As String = (CType(GridView1.Rows(e.RowIndex).Cells(3).Controls(0), TextBox)).Text  'Title
        Dim address As String = (CType(GridView1.Rows(e.RowIndex).Cells(4).Controls(0), TextBox)).Text  'Address
        Dim counTry As String = (CType(GridView1.Rows(e.RowIndex).Cells(5).Controls(0), TextBox)).Text  'Country

        Dim UpdateOrAddNewRecord(id, company, name, title, address, counTry, True)
        GridView1.EditIndex = -1
        BindGridView()
    End Sub



    Private Sub DeleteRecord(ByVal ID As String)
        Dim connection As SqlConnection = New SqlConnection("Data Source=KARANDE\sqlexpress;Initial Catalog=nitink;Integrated Security=True")
        Dim sqlStatement As String = "DELETE FROM Customer WHERE CustomerID = @CustomerID"
        Try
            connection.Open()
            Dim cmd As SqlCommand = New SqlCommand(sqlStatement, connection)
            cmd.Parameters.AddWithValue("@ID", ID)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String = "Deletion Error:"
            msg += ex.Message
            Throw New Exception(msg)

        Finally 'this is the error spot 
            connection.Close()
        End Try
    End Sub




    Protected Sub GridView1_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        Dim id As String = GridView1.Rows(e.RowIndex).Cells(0).Text
        DeleteRecord(id) 'call delete method
        BindGridView() 'rebind grid to reflect changes made

    End Sub
End Class
Posted
Updated 6-Jun-12 3:17am
v2
Comments
Tim Corey 6-Jun-12 9:18am    
What is the error you are getting?
nicky_008 6-Jun-12 9:46am    
GridView1_RowUpdating...>>>coversion from string to integer not valid.....
and
delete record...>>>>>Deletion Error:Must declare the scalar variable "@CustomerID"

1 solution

Based upon your comments, I have a solution for part of your problem and a way to identify the issue for the other part of your problem. First, the easy part.

In the DeleteRecord Sub, you are adding a parameter for the ID like so:
VB
cmd.Parameters.AddWithValue("@ID", ID)

However, the SQL statement is looking for @CustomerID, not @ID. The updated line would be like this:
VB
cmd.Parameters.AddWithValue("@CustomerID", ID)

The second part of this is a bit harder. It looks like your code is all correct. However, it might be that a runtime value is tripping you up. My guess would be that one of the fields (probably the ID field) is blank and it shouldn't be. Put a breakpoint on the start of the GridView1_RowUpdating Sub and walk through what it does. I'm betting you will see the problem right away. Step through each line and then see which line it fails on. Look at all the values that are coming into that line or are being used. I'm betting one of them is a null or another unexpected value.
 
Share this answer
 
Comments
nicky_008 6-Jun-12 10:23am    
after execute delete button....following message comes....
Deleting is not supported by data source 'SqlDataSource1' unless DeleteCommand is specified...
but row was deleted
Tim Corey 6-Jun-12 10:31am    
I don't see a reference to SqlDataSource1 in your code. It looks like you might be mixing front-end functionality with back-end and they are conflicting. Are you firing a delete command on the grid and then also running the DeleteRecord Sub on the back end?
taha bahraminezhad Jooneghani 6-Jun-12 10:36am    
very nice!
5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900