Another DataGrid issue - Updating a row

Hi guys,

Can you help pls?

I have some code and I am having probs with it.

Trying to update a record in SQL Server 2k through the update command in a datagrid - with no success.

Here's my code. If anyone can help I will be really thankful.

-----------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim CurrentCategoryID As Long
Dim DBConn As SqlConnection
Dim DBCommand As SqlDataAdapter
Dim DSPageData As New DataSet()
DBConn = New SqlConnection("Server=localhost;" _
& "Initial Catalog=erhartdb;" _
& "Integrated Security=SSPI")
DBCommand = New SqlDataAdapter _
("Select RentID, Reference, Lease, Type, Location, " _
& "BriefDescription, NoBedrooms, Pool, Balcony, MonthlyRent " _
& "From RentEng Order By Reference", DBConn)
DBCommand.Fill(DSPageData, "Properties")
dgProducts.DataSource = _
DSPageData.Tables("Properties").DefaultView
dgProducts.DataKeyField = "Reference"
dgProducts.DataBind()

End Sub

Private Sub dgProducts_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgProducts.UpdateCommand
Dim DBConn As SqlConnection
Dim DBUpdate As New SqlCommand()
Dim DSPageData As New DataSet()
DBConn = New SqlConnection("Server=localhost;" _
& "Initial Catalog=erhartdb;" _
& "Integrated Security=SSPI")
Dim RefA, LeaseA, TypeA, LocationA As String
RefA = CType(e.Item.Cells(1).Controls(0), TextBox).Text
LeaseA = CType(e.Item.Cells(2).Controls(0), TextBox).Text
TypeA = CType(e.Item.Cells(3).Controls(0), TextBox).Text
LocationA = "Playa"
'LocationA = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Label1.Text = RefA
Label2.Text = LocationA

DBUpdate.CommandText = "Update RentEng SET Location = RefA"


DBUpdate.Connection = DBConn
DBUpdate.Connection.Open()
DBUpdate.ExecuteNonQuery()
DBUpdate.Connection.Close()


'Response.Redirect("./Amend.aspx")

End Sub

Private Sub dgProducts_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgProducts.CancelCommand
dgProducts.EditItemIndex = -1
DataBind()
End Sub

Private Sub dgProducts_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgProducts.EditCommand
dgProducts.EditItemIndex = e.Item.ItemIndex
DataBind()
End Sub


End Class


-----------------------------

Thanks if you can, and, well, thanks for trying if you can't.

Cheers,

BikerCaz

Didn't try executing the code, but noticed what might be a possible error
The variable Refa is put within quotes while getting assigned to the commandtext,hence the value in it will not get evaluated.
So try Using something like
Commandtext = "update table1 set field2='aaa' where field1=" + Var1

This is close to working, but I am getting a

String or binary data would be truncated. The statement has been terminated.

So this is a SQL issue. However, if I delete all the date and replace it with a single character I still get this.

Any ideas please?

Code
------------------------

Dim Ref1, Lease1, Type1, Location1, Brief1, Beds1, Balcony1, Pool1 As String

Ref1 = CType(e.Item.Cells(1).Controls(0), TextBox).Text
Lease1 = CType(e.Item.Cells(2).Controls(0), TextBox).Text
Type1 = CType(e.Item.Cells(3).Controls(0), TextBox).Text
Location1 = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Brief1 = CType(e.Item.Cells(5).Controls(0), TextBox).Text
Beds1 = CType(e.Item.Cells(6).Controls(0), TextBox).Text
Balcony1 = CType(e.Item.Cells(7).Controls(0), TextBox).Text
Pool1 = CType(e.Item.Cells(8).Controls(0), TextBox).Text

Dim DBUpdate As New SqlCommand()

DBUpdate.CommandText = "Update RentEng SET Type = '% " & Type1 & " %' WHERE Reference LIKE '%" & Ref1 & "%'"
Dim DBConn As SqlConnection
DBConn = New SqlConnection("Server=localhost;" _
& "Initial Catalog=erhartdb;" _
& "Integrated Security=SSPI")

DBUpdate.Connection = DBConn
DBUpdate.Connection.Open()
DBUpdate.ExecuteNonQuery()

------------------------------------------------

The Where variable works here, but can't get the update data variable to work.

Any thoughts?

Cheers guys.


Biker

The 96 coming out there in my last post is an oppostrophe (did I spell that correctly?) and a percentage sign.