Web - Update Dataset

[mus3na 5]

Assalammualaikum dan salam sejahtera. Aku nak mintak tlg otai2 tengokkan code dibawah dimana silapnya. aku buat web (ASPX) yg link ke database. takat ni aku lepas nak link and read database dan manipulate data, tetapi aku kantoi nak write balik data ke database. letih dah aku usha google tapi tak jadi gak.

just mintak korang perbetulkan bahagian write data tu bila aku click button1 kat web. aku nak masukkan data dalam textbox1 kedalam database Row(0), Column(0). database yg aku load ni just utk main2 je.

Imports System.Data
Imports System.Data.OleDb

Partial Class test
    Inherits System.Web.UI.Page

    Dim DB As OleDb.OleDbConnection
    Dim Dc As New OleDb.OleDbCommand()
    Dim Da As New OleDb.OleDbDataAdapter()
    Dim Ds As New DataSet()
    Dim DBHitCount As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Connect()
    End Sub

    Private Sub Connect()
        Dim STrPath = "C:\Users\xxxxx\Documents\Visual Studio 2005\WebSites\wwwroot\database1.mdb"

        DB = New OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source='" & STrPath & "';") 'jet oledb:database password=password")
        'Dc = New OleDbCommand("Select * from Table1") ' where code<>null order by name asc")

        DB.Open()
        'Dc.Connection = DB

        Da = New OleDb.OleDbDataAdapter("Select * from Table1", DB) ' where code<>null order by name asc", DB)
        Ds = New DataSet("MS")
        Da.Fill(Ds, "MS")

        Me.GridView1.DataSource = Ds
        Me.GridView1.DataBind()  'GridView1.DataMember = "MS"

        FILL(0) 'fill with row number in ()
        TextBox1.Text = DBHitCount

        DB.Close()
    End Sub

    Private Sub FILL(ByVal X As Long)
        If Ds Is Nothing Then Return

        With Ds.Tables("MS").Rows(X)
            DBHitCount = IIf(IsDBNull(.Item("UserID")), "NULL", .Item("UserID"))
        End With
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        'aku perlukan code dalam block ni supaya bila aku click button1 system akan amik
        'apa2 value dalam textbox1 dan gunakan value ni untuk overwrite data dalam 
        'database. aku nak overwrite Table1 > UserID > Row 0
        'Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

        Dim STrPath = "C:\Users\xxxxx\Documents\Visual Studio 2005\WebSites\wwwroot\database1.mdb"

        DB = New OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source='" & STrPath & "';") 'jet oledb:database password=password")

        DB.Open()
        Da = New OleDb.OleDbDataAdapter("Select * from Table1", DB) ' where code<>null order by name asc", DB)
        Ds = New DataSet("MS")
        Da.Fill(Ds, "MS")

        Ds.Tables("MS").Rows(0).Item("UserID") = TextBox1.Text
        Da.Update(Ds, "MS")

        DB.Close()
    End Sub
End Class

[mus3na 5]

wei takde org leh tolong ke, dah 2 hari aku menunggu sambil mencari ni. naga tolg la aku oi.... tak poloi la cam ni.....

[mus3na 5]

puas aku godek2 google, jumpa la code yg nampak leh pakai. so aku pun test la kat web server aku. ni code dia;

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        'aku perlukan code dalam block ni supaya bila aku click button1 system akan amik
        'apa2 value dalam textbox1 dan gunakan value ni untuk overwrite data dalam 
        'database. aku nak overwrite Table1 > UserID > Row 0
        'Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

        Dim STrPath = "C:\xxxxxxxxxxxxxxxx\Visual Studio 2005\WebSites\wwwroot\database1.mdb"

        DB = New OleDbConnection("Provider=microsoft.jet.oledb.4.0; data source='" & STrPath & "';") 'jet oledb:database password=password")

        DB.Open()
        Da = New OleDb.OleDbDataAdapter("Select * from Table1", DB) ' where code<>null order by name asc", DB)
        Ds = New DataSet("MS")
        Da.Fill(Ds, "MS")

        Ds.Tables("MS").Rows(0)("UserID") = TextBox1.Text
        Da.Update(Ds, "MS")
        Console.WriteLine("Sent Update to database.")


        DB.Close()
    End Sub

bila run web server bagi error ni;

Server Error in '/wwwroot' Application.

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

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Source Error:

Line 62:

Line 63: Ds.Tables("MS").Rows(0)("UserID") = TextBox1.Text

Line 64: Da.Update(Ds, "MS")

Line 65: Console.WriteLine("Sent Update to database.")

Line 66:

Source File: C:\xxxxxxxxxxxxxxxxxxxxxx\Visual Studio 2005\WebSites\wwwroot\test.aspx.vb Line: 64

Stack Trace:

[invalidOperationException: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.]

System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +481

System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +41

System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +2029

System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) +41

System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) +181

test.Button1_Click(Object sender, EventArgs e) in C:\xxxxxxxxxxxxxxxxxxxxxxxxxx\Visual Studio 2005\WebSites\wwwroot\test.aspx.vb:64

System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75

System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4921

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

Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

Edited January 10, 2007 by mus3na

[mus3na 5]

takde ke org yg leh tlg aku?

[1kHz 0]

Da.UpdateCommand tak ada, sebab ko tak specify command dia lagi. Da.Update() tak boleh berfungsi kalau Da.UpdateCommand tak ada.

Cara nak buat, try refer sini: http://msdn2.microsoft.com/en-us/library/s...atecommand.aspx

Tapi link ni utk SqlDataAdapter, utk OleDbDataAdapter lebih kurang je.

Nak senang, gunakan cara drag-drop dari toolbar. Dia akan generate select/update/delete semua. Kalau tak kena taip sendiri la.

Utk web, aku tak biasa buat update mcm tu. Sebab dia kena get semua table dulu guna XxxDataAdapter.Fill(), barulah boleh XxxDataAdapter.Update(). Tak efisyen. Kalau standalone app mungkin ok guna cara ni.

Cara simple, selalunya aku create XxxCommand, bagi update sql dia, dan parameters yg sepatutnya, set value utk parameters tu, dan panggil XxxCommand.ExecuteNonQuery. Selalunya aku akan specify primary key utk bagitau row mana yg nak update.

[mus3na 5]

oh, cam tu ek! aku ada try sample executenonquery tu, tapi utk masukkan data baru lepas la. nak update data masih terkial2. nanti aku usha link tu. kalau kau ada sample leh share. aku nak wat benda simple je, PAGE COUNT. cuma aku nak dia count ke database, sebab aku nak keep track setiap page, so lepastu aku nak wat satu page utk monitor activity server aku.

[mus3na 5]

1khz aku dah try read and modify code dari page yg kau kasi tu, tapi tak berjaya-jaya gak wat apa yg aku nak. maybe aku ni bengap sikit kot, hokhokhohkohk.....

sementara aku mencuba ni, kalau kau ada masa lapang leh tak tlg tuliskan aku sample code.

aku hust nak code tu copy contect database, kemudian dia tambah numbor dalam database tu ngan no 1 and write balik ke database. (utk function page count), so setiap kali aku refresh webpage tu nilai dalam database akan naik 1.

database structure simple je utk study. ada 1 table and dalam tu ada 1 column (nama letakla PageCount). jenis dia integer.

so dalam ni la letak numbor. aku taknak wat banyak2 column, just 1 column tu je, nilai data dia je ubah2.

kalau kau taknak tlg takpa la, tapi tak best la kita sama2 member, tlg la akuuuuUUUUUuu...........

[mus3na 5]

aku dah jumpa dah caranya, nanti aku post lepas aku try keberkesanannya.