Adapter problem

F

fkheng

Well-known member
Joined
May 8, 2003
Messages
155
Location
Malaysia
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 wats 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...
 
Code: 
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 dont 
see the value in reassigning this value.
            adapter.UpdateCommand() = New OleDbCommand(updateSQL, conn)
            adapter.UpdateCommand.Parameters.Add("Username", OleDbType.VarWChar) you dont actually assign 
this parameter a value that I can see...
 and I dont 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...
Click to expand...

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

Jon
 
Last edited by a moderator:
ok, er.......ill post the whole body of code as below :

Code: 
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...thats all

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

i dont 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 dont
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 thats in the code)?
 
This works, Ive commented the code to explain what is going on and what you do and dont need in your original code and why.

Code: 
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
 
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, wouldnt 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? ive 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?
 
1. Sure you can delete it if you dont 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...its 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.
 
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?
 
then wat is the point of using an oledbcommand object if the data adapter already uses the sql statement?
 
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 its 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
 
Back
Top