Another DataGrid issue - Updating a row

[bikercaz]

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

[elite]

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

[bikercaz]

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

[bikercaz]

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