How to resync/reload a single row from database to datatable

[Yorkshireman2]

In VB6 I used this simple line:
recordset.Resync adAffectCurrent

to quickly reload a single row (the current record) from the current database values into my client recordset so the user can start with latest data, in case another user changed something after this client loaded the recordset and disconnected.

How do I do this in vb.net?
I have so far used a dataAdapter to fill a dataTable; set a Binding Source to this dataTable, set my textboxes and dataGridView source to the Binding Source, then closed the connection, and disposed of the dataAdapter.

I simply want to refresh the current row from the latest database values so the user doesn't waste time editing an out of date value.

 

[WomanPro]

Dim SqlCmd As SqlCommand
SqlCmd="your sql update query"
SqlCmd = New SqlCommand(SqlCmd , MyConn)
SqlCmd.ExecuteNonQuery()

You can use one value of the following, it's up to you.
SqlCmd.UpdatedRowSource = UpdateRowSource.Both
SqlCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
SqlCmd.UpdatedRowSource = UpdateRowSource.None
SqlCmd.UpdatedRowSource = UpdateRowSource.OutputParameters

Does this make sence?

 

[WomanPro]

Another example about to use datasets and dataview is the following
SqlQuery = "Select * from Exercises where HelpExID=1 and ChapterID='1GRMDR'" & _
" order by HelpExID"

SqlQuery = New SqlDataAdapter(SqlQuery , MyConn)
Ds = New DataSet
SqlQuery.Fill(Ds , "FirstGRChapterAndExercise")
MyDataView = New DataView(Ds.Tables("YourTable"))
YourTable = CType(Me.BindingContext(MyDataView), CurrencyManager)

However I believe that you have to bind both of your controls to the dataset
YourControl.DataBindings.Clear()
YourControl.DataBindings.Add("PropertyAsString", MyDataView, "DataMemberAsString")

I hope it helps.

 

[jebenson]

Check out this link for a pretty good article on database locking.

http://www.codeproject.com/Articles/114262/ways-of-doing-locking-in-NET-Pessimistic-and-opt

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Yorkshireman2]

I assume the UpdatedRowSource must be set before the SqlCmd.ExecuteNonQuery() is done, yes?
However I don't understand this UpdatedRowSource property. I tried reading MS help but it does not really explain anything - it seems to be for people who already know.

Also I don't understand what your second parameter is, in: sqlQuery.Fill(Ds , "FirstGRChapterAndExercise")
So far I have learned the .fill method with only one parameter- the datatable(or dataset).
MS help indicates the two parameter form "Adds or refreshes rows in the DataTable to match those in the data source using the DataTable name and the specified IDataReader. So presumably "FirstGRChapterAndExercise" is a datareader. I don't understand this; I have so far only used a dataAdapter to retrieve rows from my stored procedure into a datatable.

However, between your suggestions and MS help it sounds like I could simply use the dataAdapter.fill method on my existing datatable in memory but set the datadapter sql expression to simply select one row instead of "filling" the datatable, yes?
So:

dataAdapter = New OleDb.OleDbDataAdapter("SELECT MyColumnName FROM MyTable WHERE MyPrimaryKey=" & gbindSourceMyTable.Current("PrimaryKey"), gconDBConnection)
dataAdapter.fill(gdtMyTable)
// (where gbindSourceMyTable.datasource is gdtMyTable)

I don't know if this will refresh the one row as I wish but I tried it anyway; unfortunately I get "No value given for one or more required parameters." at the .fill statement.
Yet I am not using parameters; my string appends the bindingsource current-row's primary key value directly to my select statement string.

jebenson, thank you, I will get to your recommended article after this.
Once I can read in this single row, to get the latest version of the column I am editing, my next task is to save the new edit to database and I will have to check at update time if this value has changed again since I just retrieved it. So your article will be useful.
After that I must look at immediate saving of changes (to other controls and a datagridview column)back to database as well.
Anyway, first thing's first.

 

[Yorkshireman2]

AH- now I tried adding a comma after my datatable in the .fill statement where the error popped up...
gdatAdaptFields.Fill(gdtFields, )

..and the intellisense pops up and says it expects another parameter after the datatable! Now I'm completely puzzled.
First, the examples I found on forums for the vb.net equivalent of loading a vb6 recordset from database said you just use dataAdapter.fill(dataTable), so that's what I did and it works without any errors to get all my data loaded into my program.
Now for this piece of code that refreshes a row when a sub-form opens, the intellisense shows a second parameter in .fill() and that second parameter is...... an ADODB recordset! Huh? Everyone is telling me ADODB recordsets no longer exist in vb.net. That's why I am learning to use dataAdapters etc.
So why doe sit suddenly tell me that .Fill() needs a second parameter and why ADODB??

 

[WomanPro]

SqlQuery is a string, that you put your sql statement in it.
Dim MySqlQuery as string

MySqlQuery = "UPDATE table_name _
SET column1 = value1, column2 = value2...., columnN = valueN _
WHERE [condition]"

In a button name Update or Save whatever you want, you will put your code.

YourAdapter = New SqlDataAdapter(CommandText As String, ConnectionString As String)
Ds= New DataSet
YourAdapter.Fill(Ds, TableName as String)
Dv = New DataView(Ds.Tables("TableName"))

Another example of an update sql query could look like :
Public UName as String
Public TempUsername As String
Public RdioName As String

MySqlQuery = "Update UserSettingsBookmarks set UserID='" & TempUsername & "',SettingsID=(" & _
"select SettingsID from Settings where AccuracyLvlName='" & RdioName & "'), SelectionValue='" & True & "'," & _
"SpeedValue=" & SettingsForm.TextBox1.Text & " where UserID='" & UName & "'"

In that query you can Update the field UserID from a value of the variable TempUserName that you specify in your code for example, the SettingsID from an internal query that selects that field of Settings table where the AccuracyLvlNme is equal to RdioName - variable that you declare in your code and you specify the value inside your code, the field SelectionValue is setting to true Value and finally the field SpeedValue is getting a value from a textbox for example and the finally where is used to the UserSettingBookmarks table about the row you want to update where UserId is equal to UName.

NewUserSaveSettingsCmd = New SqlCommand(MySqlQuery , MyConn)
NewUserSaveSettingsCmd.ExecuteNonQuery()

I hope it helps

 

[Yorkshireman2]

Thank you WomanPro, The sql makes good sense to me - I took to SQL well when I was first taught it.
The problem for me is mainly the totally different methods/properties used in VB.Net and Microsoft's poor help.
At one time their F1 help would show a full statement/function call with all available parameters and describe each parameter with examples. In most cases now I find F1 help simply gives a one line simple description of the statement/function and I could already guess that much. They give no real help- it seems you have to search through hundreds of pages(and know what to look for) to go through all possible parameters etc.

SO... do you think my assumption is true that using datadapter.fill on my existing datatable will refresh just a single row or even a single column of my existing datatable if I simply provide the appropriate SQL query?

If this is sound then I wonder why I get the error "No value given for one or more required parameters." at the .fill statement. I noticed in your examples that you surround numeric values with single quotes but not whe nsupplying a control's valu ein text form. That sounds right.
In fact I tried both
"SELECT MyColumnName FROM MyTable WHERE MyPrimaryKey=" & gbindSourceMyTable.Current("PrimaryKey")
and then
"SELECT MyColumnName FROM MyTable WHERE MyPrimaryKey='" & gbindSourceMyTable.Current("PrimaryKey") & "'"
where I surround the value from the bindsource by single quotes, but both give the error.
My SQL does not use any parameters so I don't undrstand the error.

I'm stumped.

 

[Yorkshireman2]

p.s. I should correct my previous post by saying that SOME of the web online help from MSDN does seem to include descriptions of parameters etc. although good examples are rare.
I have now found some descriptions of dataAdapter.fill() when different versions are used (overloaded). Mind you, to find these I had to search specifically for the different forms of the statement. So again, it is not as easy to use as the older F1 help on disc (that was stored locally and was faster to bring up), and still not as good, I think.

I understand now that the ADODB recordset parameter is only for one-time loading of an old style recordset into a datatable. There is no mention on those MSDN pages that the other parameters are optional yet they must be because my .fill statement works with only a datatable suppplied, when I load the datatable upon form load.
datadapter.fill(gdtFields)

So if that parameter is not the reason for the error the nit must be disliking my SQL expression, yet I see nothing wrong with it.
Still stumped

 

[Yorkshireman2]

WELL... I don't know why this worked but I put my sql statement into a string variable and created the dataadapter by passing the string variable instead of putting the sql string directly in the parentheses...and.. it stopped the error. I managed to get the form to load like this BUT...
Uh Oh! Now a duplicate row appears in the dataview grid, with the same primary key.
In fact- each time I close this new form and open it again another duplicate row appears in the grid.
(This single row refresh I want is done in form load of a small form that allows editing of this particular field)

So although MSDN help says the .fill method "Adds or refreshes rows in the DataTable to match those in the data source..." there is clearly something they are not explaining because this is adding a duplicate row instead of refreshing it. Strange because I am not even selecting a whole row; I only select one field from the database table.

Does anyone know what I am doing wrong? Is it something to do with the rest of the MSDN description "Adds or refreshes rows in the DataTable to match those in the data source using the DataTable name and the specified IDataReader.
I don't understand; IDataReader - yet another object! Things were much simpler in VB6.
Am I supposed to create a datareader instead of a dataadapter to refresh one field or row??
Yet the MSDN description of datadapter.fill says it "Adds or refreshes..." so that suggests to me that it should be able to refresh a row (not add a duplicate row).

Please, does someone have the missing puzzle piece- Have you already solved this task??

 

[WomanPro]

My dear Yorkshireman2 I think you need 2 books, one for sql and one for vb.net 2005

Take a look here, to see about fill method

http://net-informations.com/vbprj/dataadapter/vb-dataAdapter-fill.htm

Excuse me for the fill method, when you use select sql statement you will use the fill method too because you only retrieve data. When you insert, update, or delete rows you will use executeNonQuery method.
Your primary key must be unique, you shouldn't have dublicated rows with same primary key. Please check your data table field types for that.
You can search at google for any method you need you can write for example executeNonQuery method vb.net 2005 and you will get several links of help about.
When you write coce, your sql query should be always a string, the ' ' I think you use it always in your creteria, I don't remember if it's depending of data type of your field. Take a look too here

https://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx

 

[Yorkshireman2]

Hi WomanPro, Thank you very much for your help.
I shall certainly look up these things and I appreciate your recommendations. I note that I must use the executeNonQuery method to update etc. thanks.

I feel you may misunderstand my background though. I certainly do not ask questions here because I am lazy- I hope you don't think that.
I decided to change career back in 1998 and went to college part time while still working full time and paying my mortgage. It took until 2002 to reach the cooperative program and 2 years to complete my cooperative project working all hours except during the day (analysis and design of complete database and software system for a local area government department- they have been using the system for some years now). By then the colleges were allowed to offer diploma programmes so I upgraded and went full time for another year to get my diploma, as I was by then unemployed.
My SQL class was taught by an experienced database administrator working for the government and I did very well at it. My VB6 system, SQL queries & stored procedures included, has worked well all this time.
I now have become unemployed and need to learn the new versions of languages because all job posts want .Net.

The main problem is learning VB.net, which is very different and MS has changed everything from the way VB6 worked.
I chose a smaller application that I wrote a few years ago as an exercise to learn VB.Net. I tried the conversion wizard but that produced so many unresolved errors that I gave up after some time and began writing the application from scratch in VB.net, learning as I go.

Just about everything that works in the VB6 version does not work in VB.Net (I'm using VS2008). The sql expressions still work fine in the VB6 program but the VB.Net program is still not there yet- it has been throwing up errors in the IDE during runtime. At least I have the main form of the program running and loading all the data so far.

Just so you know, I have actually spent a LOT of time searching on Google to solve these problems before resorting to Tek Tips. I spend all day and all week working on this, aiming to understand it as much as I do VB6 and C++ so I can apply for a job. I am exhausted at the end of each day.
Unfortunately Internet searches can be hit or miss (no pun intended) and unless you use exactly the right search words you can miss by a mile. I have also found search results on MSDN help less helpful than the help provided on forums.

Anyway, once again, I really appreciate all help offered here and I will definitely follow up on your recommendations and keep trying until I get this to work and get another job.

Thank you.

Regarding the primary key. Yes, that's a very basic requirement of database design that I learned years ago.
My database for this application is a small Access database, to keep the program portable; I could set the client to connect to a shared database on the company network or to a local copy when I travelled away from the office and country.

The table in question does have a primary key and I use that in the sql statement to refresh the current row or field I am working on. However, whereas in Vb6 the recordset.Resync adAffectCurrent method correctly refreshes the recordset and grid, in VB.Net a new (duplicate) row appears in the grid when I do the .fill with a sql string that specifies the primary key of this record:
"SELECT * FROM Fields WHERE FieldID=" & gbindSourceFields.Current("FieldID").ToString

I tried with and without the .ToString and both ways produce the same string variable, so when I hover over this string variable in my debugging, it displays "SELECT * FROM Fields WHERE FieldID=2"
This same type of expression works for me in stored procedures and although the primary key above is produced from accessing a bindsource column value, the string variable still shows it looks correct.

I had also tried with the extra single quotes around the appended primary key as you saw in my earlier post, just in case the bind-source value was not being supplied in the way I expected, but naturally that raised an error.

The whole set of lines from this section is:

Dim pstrSQL As String = "SELECT * FROM Fields WHERE FieldID=" & gbindSourceFields.Current("FieldID")
gdatAdaptFields = New OleDb.OleDbDataAdapter(pstrSQL, gconDBConnection)

'// resync current record with database value in case it changed after I first loaded the client
gdatAdaptFields.Fill(gdtFields)

AHA.. This very minute, while writing this and testing, I think I have it working but I don't know why my statement above causes that problem.
I just tried selecting the single column that I want to refresh:

"SELECT NoteField FROM Fields WHERE FieldID=" & gbindSourceFields.Current("FieldID")

... instead of all fields (SELECT *) and the form opened with NO extra duplicate row!
That will work for me so I will press on and try to get changes updated to the database. Phew.

I wonder why SELECT * produces a duplicate row, even though I am using a WHERE clause with the primary key??
I don't like loose ends so it would be nice to resolve the reason for this.

I hope others may benefit from this finding.

Many thanks again for all help offered.

 

[Yorkshireman2]

DRAT I don't believe it. The form opened without problem and I thought it was working but I just made a test by starting the program and examining the contents of the column in a text box, then I closed the edit form and manually edited that database column while the program was still running (using MS Access and closing it afterwards) so the column had a sentence in it.
When I opened the edit form again (and the refresh code ran) the contents of the text box had not changed; I checked the bindsource column value and it is still null.
I put a watch on the datatable too and it also still shows DBNull so the table never refreshed that column value.
I still don't know what I am missing. MSDN says .fill "Adds or refreshes rows..

 

[WomanPro]

There is no reason to apologize Yorkshireman2, you just have to learn, that's why I suggested you to find a book because vb.net is based on different technologies than vb6, but is more efficient and more eays because it provides more dynamic ways to do whatever you want with less code than vb6.
You may have to write you query like that
"select field1, field2, field4 from table where field3=" value
because with select statement you can select each field you want and not dublicated columns.
About dublicated rows, have you ever take a look on your database table, if you have 2 rows with that field value?
For example select * from customers where age = 23 this query could result more than one rows because many customers could be at 23th years old. You have to check all these really carefully.

Second problem: Could you post your code of your form please to help you better?

 

[Yorkshireman2]

Very kind words, thank you. Sorry if I got my back up a bit- that's just the frustration coming out.
Yes I think you're right about the book(s). Everything may seem to be on-line these days but there's also a lot of misleading information out there and a lack of detail in certain areas, so I'll peruse a few books to see if they have the sort of information/examples that will help real situations.

Duplicate rows- yes, curious. I have the database primary key field set for 'no duplicates' so it should prevent any duplicate keys. However I have searched this database again just in case I have corrupted this copy during my trials (rule#1 of databases is always test your SQL or code on a copy of data while developing). Definitely no duplicate primary keys and my query's WHERE clause is specifying a primary key.

The duplicate row only appended to the datatable of my program (and of course to the bound 'binding source' and 'datagridview') and is not in the database. Each time I ran the program in the IDE and opened this form that queries to refresh the row the duplicate row appeared in the grid; then when I closed and started it again the grid was back to normal.
I just made some tests on this.
Remember now that I am using a new, different datatable this datatable fills with only 1 row on this query- good (except for the other problem of actually reading the new value).

When I go back to filling the existing datatable with SELECT *, a duplicate row is appended to the datatable from this action and it appears in the datagridview too. Another row is added to the datatable and grid everytime the code runs. Restart the program and it's back to no duplicates.
I'm checking in a watch on datatable.rows.count)

However, if I fill the existing datatable but with SELECT NoteField ,the datatable still appends duplicate rows but the grid does NOT.
Interesting.

Now with the SELECT * conditions and with the datatable row count at 4 extra rows, the grid shows the primary keys (the first of two columns) of the first four rows as the same (2, 2, 2, 2)but if I examine the primary key of these in the datatable I see the values are actually 2, 3, 4, 5 as they should be!

So: extra rows in datatable, yet correct primary keys, and extra rows in grid but same primary key as the first one displayed.
Wait... I just checked a watch on the binding source current("FieldID") and EVERY row I select on the grid shows the same FieldID (2) in the binding source.
I checked the datagridview column properties and they seem correct. I manually set the first column like this: dgv1.Columns(0).DataPropertyName = "FieldID" and FieldID is the correct name of the database primary key column.

Odd that the datatable, binding source and grid can all have some difference.

I'll post the code of the form load in the next post to keep it separated a bit.

 

[Yorkshireman2]

Well... I was going to post my code for the form load but when I tested it tonight it works now!
I tried starting the program and opening the Notes edit form to see th evalue. Then I opened Access and manually changed the notes and saved. Then I opened my Notes Edit form again- presto it had the new value.

Oh well ,thoroughly embarrassed- it just wouldn't pull in the new value yesterday.
The only thing I have changed is while testing for the last post I kept changing things so I queried SELECT * then SELECT NoteField and I changed my temporary datatable pdtField back into gdtFields (My global datatable that exists for the life of the program) and back again.
Clearly the fact it works now means there must have been a mistake somewhere and I accidentally fixed it when I changed it back after that post. Never mind- happy is a good word.
Here it is anyway:

Private Sub frmNotes_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'create db connection again(I closed it all after loading)
        gconDBConnection = GetConnection()  ' returns connection object
        Dim pdtField As New DataTable
        Try
            'recreate Fields dataadapter and set to the connection
            Dim pstrSQL As String = "SELECT NoteField FROM Fields WHERE FieldID=" & gbindSourceFields.Current("FieldID")
            
            gdatAdaptFields = New OleDb.OleDbDataAdapter(pstrSQL, gconDBConnection)

            ' refresh NoteField of current record with database value (now into a new datatable)
            ' (in case it changed after we loaded the program)
            gdatAdaptFields.Fill(pdtField)
            '  now using a temporary datatable to avoid duplicate rows being created

        Catch ex As Exception
            MsgBox("Error querying DB.  Err# " & Err.Number & "   " & ex.Message)

        Finally
            'close db connection and destroy
            gconDBConnection.Close()
            gconDBConnection.Dispose()
            ' dispose of dataadapter
            gdatAdaptFields.Dispose()
            pdtField.Dispose()
        End Try

        ' load current field Edit notes from the temporary pdtField table
        '  (as long as the field is not empty or null)
        If IsDBNull(pdtField.Rows(0).Item("NoteField")) Then
            txtNotes.Text = ""
        Else
            If pdtField.Rows(0).Item("NoteField") <> "" Then txtNotes.Text = pdtField.Rows(0).Item("NoteField")
            mstrNotesAsFormLoads = txtNotes.Text ' store the loaded value
        End If
    End Sub

 

[WomanPro]

It doesn't look to have an error!!!! So does it work now? It would be better to run it many times to be absolutely sure. Because if that problem insists you will face it again very soon

 

[Yorkshireman2]

Yes, it seems to be working now and I will be testing this often as I move on to saving changes. Thanks v. much.

 

[Yorkshireman2]

Almost a month and all still works well- The rest of the save procedures and error trapping are all done and I only have to solve an issue with a called-process output-redirection not updating a textbox live.
Thanks again for all the help.
I would class this one resolved- How do I do this?