Hi Everyone,
Im using ASP.NET and SQL Server. I have Insert , delete data working well. but if i update a particular record it just all the records are updated with the same value.

for example if i have 3 records with 3 different values, if i display the second record, and edit it, and update it. then all the 3 values are updated with the same value what i have entered. i donno what is the error in my code. can anyone enlighten me?

here is the code,

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Partial Class Default7
    Inherits System.Web.UI.Page
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim cmb As SqlCommandBuilder
    Dim adp As SqlDataAdapter
    Dim data As DataSet3
    Dim dr As DataRow
    Dim selct, uptext As String


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        con = New SqlConnection("Data Source=ACER;Initial Catalog=preethi;Integrated Security=True")
        con.Open()
    End Sub

    Private Sub fillcontrols()
         TextBox1.Text = data.Tables(0).Rows(0).Item(0)
        TextBox2.Text = data.Tables(0).Rows(0).Item(1)
        TextBox3.Text = data.Tables(0).Rows(0).Item(2)
    End Sub

    Protected Sub Update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
             uptext = "update cooldrinks set drinkname = @drinkname ,color = @color, price = @price"
             cmd = New SqlCommand(uptext, con)
             cmd.Parameters.AddWithValue("@drinkname", TextBox1.Text)
            cmd.Parameters.AddWithValue("@color", TextBox2.Text)
            cmd.Parameters.AddWithValue("@price", TextBox3.Text)
            cmd.ExecuteNonQuery()
            Response.Write("Data Updated Successfully")

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try


    End Sub

    Protected Sub AddNew_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        clearcontrols()
    End Sub
    Private Sub clearcontrols()
        TextBox1.Text = " "
        TextBox2.Text = " "
        TextBox3.Text = " "
        TextBox1.Focus()

    End Sub

    Protected Sub Insert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim Ssql As String
        Try
            Ssql = "insert into cooldrinks(drinkname,color,price)values(@drinkname,@color,@price)"
            cmd = New SqlCommand(Ssql, con)
            cmd.Parameters.AddWithValue("@drinkname", TextBox1.Text)
            cmd.Parameters.AddWithValue("@color", TextBox2.Text)
            cmd.Parameters.AddWithValue("@price", TextBox3.Text)
            cmd.ExecuteNonQuery()
            clearcontrols()

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
        Response.Write("One Record inserted Successfully")
    End Sub

    Protected Sub Delete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim Ssql As String
        Ssql = "delete from cooldrinks where price = @price "
        cmd = New SqlCommand(Ssql, con)
        cmd.Parameters.AddWithValue("@price", TextBox3.Text)
        cmd.ExecuteNonQuery()
        Response.Write("Deleted")
    End Sub

    Protected Sub LoadTable_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
        Try
            
            selct = "select * from cooldrinks"
            adp = New SqlDataAdapter(selct, con)
            data = New DataSet3()
            adp.Fill(data, "cooldrinks")
            fillcontrols()

        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
    End Sub
End Class

It is so obvious that you dont have any where condition in the update statement.

uptext = "update cooldrinks set drinkname = @drinkname ,color = @color, price = @price" --> You need to add the where clause here.

hi,
i gave Update Sql Command, but its not working.

this is the code i have given.....

uptext = "update cooldrinks set drinkname = @drinkname ,color = @color, price = @price where price = " & TextBox3.Text & " "

Wrap you code between

tags to keep the formatting. Post you existing query again.

hi,
i gave Update Sql Command, but its not working.

this is the code i have given.....

uptext = "update cooldrinks set drinkname = @drinkname ,color = @color, price = @price where price = "  &  TextBox3.Text &  "  "

hi,
i gave Update Sql Command, but its not working.

this is the code i have given.....

uptext = "update cooldrinks set drinkname = @drinkname ,color = @color, price = @price where price = "  &  TextBox3.Text &  "  "

You need to check your records to see the prices are same for all records.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.