Not updating to the db.

[tiffany]

Hi, I had a problem on updating from textboxes to db. From the beginning iof the system, it will retreive data from datbase and display in the datagrid. Once the update push button is click, it will display in a form where form contain textboxes.

Textboxes are displaying the data once the oush button is clicked. When i click "save changes" button in the form, it will auto update to the db. But it can't work. Here are the codes:


Dim cnn AsNew OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\Inetpub\wwwroot\button\movie.mdb")

Dim strSQL AsString = "Update movieDet SET Title = @Title, price = '" + Price.Text + "' WHERE Title = @Title "

Dim cmd2 AsNew OleDbCommand(strSQL, cnn)

With cmd2.Parameters

.Add(New OleDbParameter("@MovieID", _

OleDbType.Integer)).Value = _

CInt(update.DataKeys(update.SelectedIndex).ToString)

.Add(New OleDbParameter("@Title", _

OleDbType.VarChar, 40)).Value = txtTitle.Text

.Add(New OleDbParameter("@price", _

OleDbType.Currency)).Value = CDbl(Price.Text)

EndWith

Try

cnn.Open()

cmd2.ExecuteNonQuery()

Response.Write("Product successfully saved to the database.")

pnlForm.Visible = False

Catch exp As Exception

Response.Write("Error: " & exp.Message)

Finally

cnn.Close()

EndTry

The only prob is it can't update the existing data in the db. Help pls!

I had another qns. If i had a checkboxlist in a webform and i had three items in the checkboxlist. Namely:"abc", "def", "ghi". If two among the three had been checkedit saved in the db like this:
row1: abc
row2:ghi
Here are the codes:
Dim i As Integer = tb_num.Text

Dim a As Integer

For a = 1 To i

Dim cmd As New OleDbCommand("INSERT INTO roomDet ([branch]) VALUES('" & CheckBoxList1.SelectedValue & "')", New OleDbConnection(strConn))

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

Next

How to imrove this codes to the requirement i want? thnkx

Rdgs,
tiffany

 

[Blokz]

1) What do you mean "it can't update the existing data in the db"? Does it throw some kind of exception?

2) You need to loop through the items in the CheckBoxList and see if they are selected. If so, insert them.

Example:

Dim conn as new [size=2]OleDbConnection(strConn)
conn.open
Dim i as integer
For i = 0 to CheckBoxList1.Items.Count - 1
	 If CheckBoxList1.Items(i).Selected Then
		 [/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff] New[/color][/size][size=2] OleDbCommand("INSERT INTO roomDet ([branch])	VALUES('" & CheckBoxList1.Items(i).Text & "')", [/size][size=2] conn)
		 cmd.ExecuteNonQuery
	 End If
Next
[/size]

HTH
Blokz

 

[tiffany]

Hi,

1) What do you mean "it can't update the existing data in the db"? Does it throw some kind of exception?

It means that i click on the update push button in datagrid. It will retrieve datas from the row which had been selected abd display in the textboxes in a panel. After the changes had been make in textboxes, click save changes. And it will be saved in the datagrid as well as in the db.

2) I set the ID to duplicate and to text data type. i key in 3 in the textbox and it create 3 rows in db. i include a counter in ID, every insertion to the db the ID will increment 1. I don't wish to use auto number beacuse i had three sets of same ID num.

here are the codes.

Dim z As Integer = 0

Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("movie.mdb"))

If DropDownList1.SelectedIndex = 1 Then

z += 1

ID = z

For a = 1 To i

Dim strSQL As String = "INSERT INTO roomDet ([roomID],[branch]) VALUES('" & ID & "','" & DropDownList1.SelectedValue & "')"

Dim strSQL2 As String = "UPDATE roomDet SET status = 'available' WHERE branch = '" + DropDownList1.SelectedValue + " ' "

Dim cmd As New OleDbCommand(strSQL, cnn)

Dim cmd2 As New OleDbCommand(strSQL2, cnn)

cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

cmd2.Connection.Open()

cmd2.ExecuteNonQuery()

cmd2.Connection.Close()

Next

z += 1

End If

How can use the counter to incremnet the ID num?


Thnk
Rdgs,
tiffany

 

[Blokz]

1) So it seems as if you are not using the standard "Edit,Update,Cancel" button set for editing a datagrid row. Instead of editing the textboxes used by default in the datagrid, you want a panel of textboxes filled with the data to be edited, then you have a "save changes" button (update) on this panel? If so, I would use a regular "button" column to start the editing process. Once clicked, it transfers the row selected rows data to the panel textboxes. Your save button on this panel then updates the database. Rebind the datagrid after this update and you have the changes reflected in the datagrid.

2) If you are using "z" as your counter and you want it to keep the previous value it had, you cant keep setting it to 0 to start. Declare it as STATIC.

Static z as integer

This way it will retain its previous value from the last time it was incremented.

Blokz