update problem

[anandsk99]

i want to update database through command object by using data adapter,dataset and not command builder.
from anand

[jfackler]

Here's a block of code, commented, to get you started.

Dim conStr, sqlStr As String
Dim cmSQL As OleDbCommand
Dim conn As OleDbConnection
Dim adapter As OleDbDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim numrows As Integer = 0
Dim i As Integer
Dim dr As DataRow
conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\MainDB.mdb"
conn = New OleDbConnection(conStr)
sqlStr = "SELECT * FROM LoginTable"
cmSQL = New OleDbCommand(sqlStr, conn)
'cmSQL.CommandTimeout = 30 Not necessary (unless the 'puter is intensely slow)
adapter = New OleDbDataAdapter(sqlStr, conn) 'the sqlStr is passed into the
'constructor as your selectcommand so the next line is unnecessary
'adapter.SelectCommand = cmSQL
ds = New DataSet()
adapter.Fill(ds, "LoginTable") 'The system.data.common.dbdataadapter class supports
'several versions of the fill method the tableName is the source table for table
'mapping in this version.

'If your goal is simply to change all the usernames to uppercase,
'since the datarows are a collection of the datatable, you can do the following:
For Each dr In ds.Tables(0).Rows
dr("UserName") = DirectCast(dr("UserName"), String).ToUpper
Next
'dt = New DataTable()
'dt = ds.Tables(0)
'numrows = dt.Rows.Count

'For i = 0 To numrows - 1
'Dim temp As String = DirectCast(dt.Rows(i).Item("Username"), String)
'dr = ds.Tables("LoginTable").Rows(i)
'dr("Username") = UCase(temp)
'adapter.Update(ds)
'Next

Dim updateSQL As String = "UPDATE LoginTable SET Username = ? WHERE Username = ?"
'cmSQL.CommandText = updateSQL The dataadapter class contains references to four
'Command objects each of which has a commandtext property that contains the actual
'SQL command to be executed. Your next line creates a new oledbcommand object (as opposed
'to reusing the select command object) passing the update string into the constructor and then assigns the update command
'that new command object.

adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
adapter.UpdateCommand.Parameters.Add("UserName", OleDbType.VarWChar, 50, "Username")
adapter.UpdateCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_UserName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UserName", System.Data.DataRowVersion.Original, Nothing))

'Dim col As String
'col = adapter.UpdateCommand.Parameters.Item("Username").SourceColumn()
'Debug.WriteLine(col)
'Dim workParm As OleDbParameter = adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)
'workParm.SourceColumn() = "Username"
'workParm.SourceVersion = DataRowVersion.Original
adapter.Update(ds, "LoginTable")


Jon

[jfackler]

Here's a block of code, commented, to get you started.

Dim conStr, sqlStr As String
Dim cmSQL As OleDbCommand
Dim conn As OleDbConnection
Dim adapter As OleDbDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim numrows As Integer = 0
Dim i As Integer
Dim dr As DataRow
conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\MainDB.mdb"
conn = New OleDbConnection(conStr)
sqlStr = "SELECT * FROM LoginTable"
cmSQL = New OleDbCommand(sqlStr, conn)
'cmSQL.CommandTimeout = 30 Not necessary (unless the 'puter is intensely slow)
adapter = New OleDbDataAdapter(sqlStr, conn) 'the sqlStr is passed into the
'constructor as your selectcommand so the next line is unnecessary
'adapter.SelectCommand = cmSQL
ds = New DataSet()
adapter.Fill(ds, "LoginTable") 'The system.data.common.dbdataadapter class supports
'several versions of the fill method the tableName is the source table for table
'mapping in this version.

'If your goal is simply to change all the usernames to uppercase,
'since the datarows are a collection of the datatable, you can do the following:
For Each dr In ds.Tables(0).Rows
dr("UserName") = DirectCast(dr("UserName"), String).ToUpper
Next
'dt = New DataTable()
'dt = ds.Tables(0)
'numrows = dt.Rows.Count

'For i = 0 To numrows - 1
'Dim temp As String = DirectCast(dt.Rows(i).Item("Username"), String)
'dr = ds.Tables("LoginTable").Rows(i)
'dr("Username") = UCase(temp)
'adapter.Update(ds)
'Next

Dim updateSQL As String = "UPDATE LoginTable SET Username = ? WHERE Username = ?"
'cmSQL.CommandText = updateSQL The dataadapter class contains references to four
'Command objects each of which has a commandtext property that contains the actual
'SQL command to be executed. Your next line creates a new oledbcommand object (as opposed
'to reusing the select command object) passing the update string into the constructor and then assigns the update command
'that new command object.

adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
adapter.UpdateCommand.Parameters.Add("UserName", OleDbType.VarWChar, 50, "Username")
adapter.UpdateCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_UserName", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "UserName", System.Data.DataRowVersion.Original, Nothing))

'Dim col As String
'col = adapter.UpdateCommand.Parameters.Item("Username").SourceColumn()
'Debug.WriteLine(col)
'Dim workParm As OleDbParameter = adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)
'workParm.SourceColumn() = "Username"
'workParm.SourceVersion = DataRowVersion.Original
adapter.Update(ds, "LoginTable")


Jon