Adapter problem

[fkheng]

I have used this code. I keep getting an error at the line where i invoke the data adapter fill method for the second time, they state that i have not specified a parameter. But i see no difference between the 1st and 2nd time i invoke the fill method. Can anybody help me see wat's wrong?

 

 

conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=MainDB.mdb"

conn = New OleDbConnection(conStr)

sqlStr = "SELECT * FROM LoginTable"

cmSQL = New OleDbCommand(sqlStr, conn)

cmSQL.CommandTimeout = 30

adapter = New OleDbDataAdapter(sqlStr, conn)

adapter.SelectCommand = cmSQL

ds = New DataSet()

adapter.Fill(ds, "LoginTable")

dt = New DataTable()

dt = ds.Tables(0)

numrows = dt.Rows.Count

Dim temp As String = dt.Rows(i).Item("Username")

Dim updateSQL As String = "UPDATE LoginTable SET Username = ? WHERE Username = ?"

cmSQL.CommandText = updateSQL

adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)

adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)

Dim workParm As OleDbParameter = adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)

workParm.SourceColumn() = "Username"

workParm.SourceVersion = DataRowVersion.Original

adapter.Fill(ds, "LoginTable")

 

oh yah, if i wanna post vb code in here, wat tags do i need to insert? so that nex time i can use it...

[jfackler]

conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=MainDB.mdb"
           conn = New OleDbConnection(conStr)
           sqlStr = "SELECT * FROM LoginTable"
           cmSQL = New OleDbCommand(sqlStr, conn)
           cmSQL.CommandTimeout = 30
           adapter = New OleDbDataAdapter(sqlStr, conn)
           adapter.SelectCommand = cmSQL
           ds = New DataSet()
           adapter.Fill(ds, "LoginTable")
           dt = New DataTable() 
           dt = ds.Tables(0)  'this is the zero index of the 
'datasets table collection and = "LoginTable" 
'as is defined in your fill command. 
           numrows = dt.Rows.Count  
           Dim temp As String = dt.Rows(i).Item("Username") 'is there a value here?  
'What is the value of i?
' Do you actually use the value of temp after you declare it and assign it here?
           Dim updateSQL As String = "UPDATE LoginTable SET Username = ? WHERE Username = ?"   
           cmSQL.CommandText = updateSQL  'cmSQL is your selection string above, right?  
'Since in the next line you declare your 
'update command string using the updateSQL string I don't 
'see the value in reassigning this value.
           adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
           adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar) 'you don't actually assign 
'this parameter a value that I can see...
' and I don't see an actual call to the update method to write your 
'values back to the source. (adapter.update(ds, LoginTable))
           Dim workParm As OleDbParameter = adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)
           workParm.SourceColumn() = "Username"
           workParm.SourceVersion = DataRowVersion.Original
       adapter.Fill(ds, "LoginTable")

 

oh yah, if i wanna post vb code in here, wat tags do i need to insert? so that nex time i can use it...

 

you can place your code in blocks using "[ vb ]" and "[ /vb ]" tags (remove the spaces) to

accomplish the pretty code windows.

 

Jon

Edited June 1, 2003 by jfackler

[fkheng]

ok, er.......i'll post the whole body of code as below :

 

conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=MainDB.mdb"
           conn = New OleDbConnection(conStr)
           sqlStr = "SELECT * FROM LoginTable"
           cmSQL = New OleDbCommand(sqlStr, conn)
           cmSQL.CommandTimeout = 30
           adapter = New OleDbDataAdapter(sqlStr, conn)
           adapter.SelectCommand = cmSQL
           ds = New DataSet()
           adapter.Fill(ds, "LoginTable")
           dt = New DataTable()
           dt = ds.Tables(0)
           numrows = dt.Rows.Count
           Dim updateSQL As String = "UPDATE LoginTable SET Username = ? WHERE Username = ?"
           cmSQL.CommandText = updateSQL
           adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
           adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)
           Dim workParm As OleDbParameter = adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar)
           workParm.SourceColumn() = "Username"
           workParm.SourceVersion = DataRowVersion.Original
For i = 0 To numrows - 1
           Dim temp As String = dt.Rows(i).Item("Username")
           dr = ds.Tables("LoginTable").Rows(i)
           dr("Username") = UCase(temp)
           adapter.Update(ds)
       Next

 

sorry that i misplaced some lines of code the last time, i modified a few lines...

 

ok, actually wat i want to do is to loop through records in a table, and change the username value to upper case, no matter wat, just to read in the value and to reassign the username field with an upper case version of the former...that's all

 

i'm actually confused about the purpose of parameters, i tried to follow the msdn article, that's y i added it there, but to be honest, i do not know why i need it...

 

i don't know why i need to put (?) placeholders in my updateSQL statement

 

cmSQL.CommandText = updateSQL 'cmSQL is your selection string above, right?

'Since in the next line you declare your

'update command string using the updateSQL string I don't

'see the value in reassigning this value.

adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)

 

so u mean i can remove the cmSQL.commadntext = updateSQL line?

wat should i do here?

 

overall, i get errors, even an error related to being una ble to find table mapping or datatable ("Table"), i dunno why this error is coming up,m maybe it is due to errors in earlier sections of my code here, wat do u think (this is all that's in the code)?

[jfackler]

This works, I've commented the code to explain what is going on and what you do and don't need in your original code and why.

 

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")

 

Give it a run. Post back here with success, failure, errors or questions.

 

Jon

[fkheng]

i see, thank you so much jfackler!

 

however, i realise that my oledbcommand variable 'cmSQL' is not used anywhere in this code, apart from initialising it...does it play any part here? if not i should delete it...

 

if i want to reflect the changes of the dataset back to the data source, wouldn't i have to modify my updateSQL statement to just UPDATE LoginTable ? unless i place this inside the for loop to update each time a row value is changed?

 

also, why is a question mark placeholder necessary in the statement? i've read the msdn for the .parameter stuff but am still blur...why do i still have to specify them if the data adapter reflected the exact actual database structure from MainDB.mdb into the dataset?

[jfackler]

1. Sure you can delete it if you don't use it anywhere else in your code.

2. No, the update statement is exactly as it should be to modify your source db. And no you should not put a call to update a data source in any for loop...it makes a call with each loop to the db. If fact, we removed the for loop. Making a call to the datasource with each for loop ties up the source and slows your app down unreasonably...it's the whole point of a disconnected architecture.

3. The question mark place holder is necessary for the oledbcommand parameter collection. The sqldbcommand object uses named parameters. Either way, parameter objects then have to be defined and added to the parameters collection to create a parameter object for each parameter in the query.... the difference is that the added parameters must be added in the appropriate order in the oledb environment. i.e. the first question mark in the query equals the first parameter added, the second question mark equals the second parameter added, etc. Why this is so is not clear... I assume it is so the oledbcommand object can be used accross a broader spectrum of database providers. Remember, the SQLDb objects are optimized for SQL Server.

[fkheng]

wow, the explanation about the parameters is quite confusing...in the first place, why would we have to redefine parameters and create objects for them if the data adapter has already mapped all the parameters from the data source to the data set?

[fkheng]

then wat is the point of using an oledbcommand object if the data adapter already uses the sql statement?

[jfackler]

The dataadapter references four different command objects to implement its functionality. Those four command objects have to be created, either by a commandbuilder or manually, or the dataadapter can not do it's job. The parameters are not mapped by the dataadapter they are actually a collection. Perhaps you mean the table mapping that is created when the dataadapter is instantiated and a select commandobject is defined with a command.text property equal to the select sql statement. All of these concepts are consistent with OOP, and although it may take some effort to explore fully, make perfect sense once you have wrapped your brain fully around them. Try a search for parameters in your VS. MS has multiple resources available there, although at first you may have to page back and forth from one reference article to the next to get the full picture, the full picture is there. Read up and if I can clarify anything for you let me know.

 

Jon