Visual Basic

13-Sep-07 17:16

hello,

I am trying to add a new row to a dataset. I then write this changes to a xml file for local storage. However, I have noticed that when I read the xml file back into a dataset and display in the datagrid, the incidentID (PK) is NULL. The incidentID is auto incremented in the sql server database.

Am I doing something wrong?

<br />
Dim dr As DataRow<br />
dr = dsIncidents.Tables(0).NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dr("Priority") = Me.cboPrority.Text<br />
dr("Subject") = Me.txtSubject.Text<br />
dr("AssignedTo") = Me.cboUsers.Text<br />
dsIncidents.Tables(0).Rows.Add(dr)<br />
<br />
dsIncidents.AcceptChanges()<br />
<br />
dsIncidents.WriteXmlSchema(incidentXSD)<br />
dsIncidents.WriteXml(incidentXML, XmlWriteMode.DiffGram)<br />
<br />
Me.grdIncidents.Datasource = dsIncidents.Tables(0).DefaultView<br />

-- modified at 23:22 Thursday 13th September, 2007

Dave Kreskowiak13-Sep-07 17:27

13-Sep-07 17:27

steve_rm wrote:
The incidentID is auto incremented in the sql server database

Yeah, but your code never updates the database and the SQL Server will never update your XML file on it own. XML files also do not have an "auto incrementing" field. An XML file is most definately NOT a database and will disappoint anyone who expects it to act like one.

Sooo....what how does the XML file relate to the SQL Server database? How does your code interact with both of these.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

steve_rm13-Sep-07 19:36

13-Sep-07 19:36

Hello Dave,

Thanks for the reply.

The user would like to use the client offline. So the client will download from the web serivce a table. This table will be saved to an xml file using the diffGram write mode.

The user will read in this xml file everytime they want to add, edit, and delete a row. These will be saved to the xml file. So at the end of the day the user will go online and synchronise the data with the central database.

However, when I do the add row, it displays the IncidentID (PK) as NULL. Which is not good as I need the incidentID when I want to edit that row.

Any suggestions would be most helpfull,

Thanks,

Steve

Tom Deketelaere13-Sep-07 21:05

13-Sep-07 21:05

why do you use an xml file for the offline part. why not just use an acces mdb that way you'll have an actual database in wich you can use sql statements. It will also improve performence.

Also when doing something like this you have to keep in mind that while you're client is working offline other users might add rows too so you can't just syncronise with the id that is stored in the offline db/xml file (unless you're client is the only user of the programme)

in answer to a question or remark that probably will follow: you can use an access db without having access installed so for you're client this will not mean that he has to purchase it.

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistaks.

steve_rm13-Sep-07 22:59

13-Sep-07 22:59

Hello TD,

Thanks for the reply.

I am interested in the MS Access. But how can I used Access when it is not installed?

I have already started the project this way by using the xml. And now I am interested to finish it as it is a learning experience for me. If I stop now, then this is nothing to learn from not finishing.

However, this is what I am having problem with.

I wondering what is the best method to synchronise data on a local client in xml form to a central database.

The dataset will be filled by the web service returning a dataset from the central database.

<br />
ds = ws.GetIncidents()<br />

I only have one table to update and the client has to be used offline.
Once the dataset has this data, I write the schema and xml. Now the client can use this xml to update, add, and delete incidents.

<br />
ds.writeXMLSchema(path)<br />
ds.writeXML(path, XmlWriteMode.DiffGram) 'DiffGram used for tracking updates<br />

So I am make all my updates and write those changes to the xml file.
example.

<br />
Dim dr As DataRow<br />
dr = ds.Tables(0).NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text          <br />
ds.Tables(0).Rows.Add(dr)<br />
<br />
ds.AcceptChanges() 'Question - is acceptChanges needed before the new row is written to the xml file.<br />
<br />
ds.WriteXmlSchema(incidentXSD)<br />
ds.WriteXml(incidentXML, XmlWriteMode.DiffGram)<br />

'For getting the changes - read in the xml file

<br />
ds.ReadXML(path, XmlReadMode.DiffGram)<br />
IF(ds.HasChanges)THEN<br />
    dim dsChanges as new Dataset()<br />
    dsChanges = ds.GetChanges()<br />
End IF<br />

So dsChanges now has new changes.
This is the problem I am having problem with, as the central dataset could have deleted a row. And my client might have a modified
that row. How would I handle that sort of conflict, as the row would not exist on the central database?

Another problem I would send my dsChanges to the central database using the web service. But how would I write the code to update
the central database with my changes?

'My code for the web service to update the central database and synchronise the data

<br />
'Update the central database and return the updated dataset<br />
    <WebMethod()> _<br />
    Public Function SynchroniseChanges(ByVal dsChanges As DataSet) As DataSet<br />
        Dim cmd As New SqlCommand()<br />
        Dim da As New SqlDataAdapter()<br />
        Dim ds As New DataSet<br />
<br />
        Try<br />
            Me.openConnection()<br />
            cmd.Connection = cnn<br />
            cmd.CommandType = CommandType.Text<br />
            cmd.CommandText = "SELECT * FROM Incident"<br />
<br />
            da.SelectCommand = cmd<br />
            da.Fill(ds, "Incident")<br />
<br />
            dsChanges.Merge(ds, True, MissingSchemaAction.Add)<br />
<br />
            'How would I get ds to update the central database<br />
<br />
            'Once all changes are made to the database return the updated dataset<br />
            Return ds<br />
        Catch ex As Exception<br />
            Console.WriteLine(ex.Message)<br />
        Finally<br />
            cnn.Close()<br />
        End Try<br />
        Return Nothing<br />
    End Function<br />

Any suggestions to the last to points would be most helpfull.

Many thanks,

Steve

Tom Deketelaere13-Sep-07 23:27

13-Sep-07 23:27

for devolepment you would need access installed but once the access database is created you can use it without office access.

for the syncronisation part you will need to set up a few rules.

in you're example (delete row on central db and edited offline)

if you're rule is that the central db has priority then discard the offline changes and move on
if you're rule is that the offline db has priority then insert the row in central db

for updating the central db:
use primary keys that can't be changed then when you update search on these key's if the key doesn't exists in the central db but does exist in the offline db then insert it in central db
if row has changed that means the row existed in central db when last syncronised so you can just update the central db
if the row has been deleted in offline db I would suggest you somehow keep track of that (boolean in offline db that's set to true or something) so that you know wich rows to delete in central db

but this all comes down to making rules and sticking to them so that's the first thing you'll need to do

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistaks.

Dave Kreskowiak14-Sep-07 5:29

14-Sep-07 5:29

TDDragon wrote:
for devolepment you would need access installed

Says who?? No, you don't need it installed at all.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Tom Deketelaere14-Sep-07 5:43

14-Sep-07 5:43

perhaps I shoud have said: for making the access db you need access installed (or at least as far as I know) after that you indeed don't need it anymore

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistaks.

Dave Kreskowiak14-Sep-07 8:00

14-Sep-07 8:00

TDDragon wrote:
for making the access db you need access installed

No you don't. You can create it from the Data Sources (ODBC) Administrative Tool. It cannot be done from inside Visual Studio.

Under Win2000, open Control Panels, Administrative Tools, Data Sources (ODBC). Under the User DSN's tab, User Data Sources, double click the MS Access Database line. In the dialog that opens, click the Create button, then give it the fully qualified path for the file you want to create, then click OK. You can also use the options on the right side of this dialog to set the Jet version to use and wether to create a System Database and Ecrypt it. Then just Cancel out of everything. Walla! Done. All you need to do then is add a Data Source to your Visual Studio project to point at the new file. You can then add tables, stored proc's (yeah, right!), views and functions using the Server Explorer.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Tom Deketelaere16-Sep-07 3:01

16-Sep-07 3:01

didn't know that thanks for the info

you see we learn something new every day Poke tongue | ;-P

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistaks.

Dave Kreskowiak16-Sep-07 4:39

16-Sep-07 4:39

It's one of those things that isn't very widely known and it's documentation is pretty obscure.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Dave Kreskowiak14-Sep-07 5:28

addhandlers and addressofs

14-Sep-07 5:28

steve_rm wrote:
However, when I do the add row, it displays the IncidentID (PK) as NULL.

You have a HUGE problem, since you're code MUST tolerate this situation. XML is NOT a database and will not fill in the PK for you automatically. This PK can NOT be assigned until the record is written back to the real database. Since you can have multiple clients using their own copy of the disconnected data, you cannot have clients assigning permanent PK's on their own. Two clients will get the opportunity to assign the SAME PK to their own, different, records!! Yikes!

You'll have to come up with a scheme to generate a TEMPORARY key that you can use to connect to other records in the database, but you'll have to replace this temp key when the data is synced up to the real database.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Cedrickdeorange13-Sep-07 14:25

Cedrickdeorange

13-Sep-07 14:25

I'm having a problem with the Addressof part. Do i have to do a sub procedure for the click events, if so how? Do I use the performclick method?

I have added the part of code i'm using.

Any help or advice will be welcomed.

Dim mnuFileRecent1 As New MenuItem
Dim mnuFileRecent2 As New MenuItem
Dim mnuFileRecent3 As New MenuItem
Dim mnuFileRecent4 As New MenuItem
Dim RecentFiles(3) As String
Dim ThePath As String
Dim I As Integer

Private Function AppPath() As String
Dim A As String
'Executing file location and name
A = Reflection.Assembly.GetExecutingAssembly.Location
'Strip off file name
A = Mid(A, 1, InStrRev(A, "\"))
Return (A)
End Function

Private Sub btnExit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()

End Sub

Private Sub mnuFileOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuFileOprn.Click
If dlgOpen.ShowDialog() = DialogResult.OK Then
FileOpen(1, dlgOpen.FileName, OpenMode.Input)
txtOutput.Text = InputString(1, CInt(LOF(1)))
txtOutput.SelectionLength = 0
FileClose(1)
ThePath = dlgOpen.FileName
RearrangeFiles(ThePath)
End If

End Sub

Private Sub RearrangeFiles(ByVal F As String)

'Reorder the four most recent files located in the menu structure
Dim TempArray(4) As String
Dim TempArrayCounter As Integer = 1
Dim i As Integer
'The first item is the name of the file just opened.
TempArray(0) = F
'Loop the the origonal array, if the item is not the
'same as the new file opened add it to the temp array.
For i = 0 To 3
If RecentFiles(i) <> F Then
TempArray(TempArrayCounter) = RecentFiles(i)
TempArrayCounter += 1
End If
Next i
'Reassign the array
RecentFiles = TempArray
'Re-populate the menu.
mnuFileRecent1.Text = "1 " + RecentFiles(0)
mnuFileRecent2.Text = "2 " + RecentFiles(1)
mnuFileRecent3.Text = "3 " + RecentFiles(2)
mnuFileRecent4.Text = "4 " + RecentFiles(3)

End Sub

Private Sub frmRecent_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try

FileOpen(1, AppPath() + "Recent.dat", OpenMode.Input)
mnuFileRecent.MenuItems.Add(mnuFileRecent1)
mnuFileRecent.MenuItems.Add(mnuFileRecent2)
mnuFileRecent.MenuItems.Add(mnuFileRecent3)
mnuFileRecent.MenuItems.Add(mnuFileRecent4)

'AddHandler mnuFileRecent1.Click, AddressOf
Input(1, RecentFiles(0))
Input(1, RecentFiles(1))
Input(1, RecentFiles(2))
Input(1, RecentFiles(3))
FileClose(1)

Catch ex As Exception

End Try

Try

For I = 0 To 3
Select Case I
Case 0
mnuFileRecent1.Text = "1 " + RecentFiles(I)
Case 1
mnuFileRecent2.Text = "2 " + RecentFiles(I)
Case 2
mnuFileRecent3.Text = "3 " + RecentFiles(I)
Case 3
mnuFileRecent4.Text = "4 " + RecentFiles(I)
FileClose(1)
End Select
Next
Catch ex As Exception
End Try
FileClose(1)

End Sub

Private Sub frmRecent_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
'Write the contents of RecentFiles() to recent.dat
FileOpen(1, AppPath() + "Recent.dat", OpenMode.Output)
PrintLine(1, RecentFiles(0))
PrintLine(1, RecentFiles(1))
PrintLine(1, RecentFiles(2))
PrintLine(1, RecentFiles(3))
FileClose(1)

End Sub

Private Sub Recents_click()
mnuFileRecent1.PerformClick()
mnuFileRecent2.PerformClick()
mnuFileRecent3.PerformClick()
mnuFileRecent4.PerformClick()
End Sub
End Class

Regards and Thanks
Zeldacat

Re: addhandlers and addressofs

Luc Pattyn13-Sep-07 15:05

Luc Pattyn

13-Sep-07 15:05

Hi,

some comments:

1.
you can add a single MenuItem Click handler for all four mnuFileRecent MenuItems.
you can find its MenuItem from its sender argument, and the filename from its text.
I see no need for Addressof.

2.
you don't need PerformClick, it simulates clicking something. What I am missing is the
code that sets the texts for mnuFileRecent1-4

3.
You don't need tempArray:
first check whether the new file is already in RecentFiles;
if not, shift them down (from hi to lo index) before you add F at 0.
if it is there, shift them down starting with overwriting the old F, upwards, then put F at 0.

4.
You may want to improve the handling when fewer than 4 recent files are known (initially
or after clearing the list, if that is offered).
suggestion: set up a convention to store/load the situation to/from file; disable menu
items that are not functional yet.

5.
You might consider using Application.UserAppDataPath rather than your AppPath(), since
the latter may be read-only, and the former automatically exists per user.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

this weeks tips:
- make Visual display line numbers: Tools/Options/TextEditor/...
- show exceptions with ToString() to see all information
- before you ask a question here, search CodeProject, then Google

Help me please ,assembly trust and \or security problem

CodeMaster_0513-Sep-07 10:43

CodeMaster_05

13-Sep-07 10:43

I am working on a database application that uses MS SQL server 2000 and vb.net ide,the application works fine locally ,but when launch the application from another workstation(over network),I encounter a security error saying that my sql client request could not go through cause of a security problem.I had initially used the administrative tool"the .net configuration tool 1.1" to increase the assembly trust to the highest,the application launched but still some forms and look up procedures raise that security exception,the error says that I need to configure the .net security using this tool,but i already have and there is no more thing to do,please help if u can..
Thanks in advance

Re: Help me please ,assembly trust and \or security problem

Tom Deketelaere13-Sep-07 21:40

Re: Help me please ,assembly trust and \or security problem

13-Sep-07 21:40

don't know for shure but I think you'll have to set some properties on the sql server in order for it to be accesable over network (or was it internet like I said don't know for shure)

also adjusting you're connectionstring might be a solution

check www.connectionstrings.com for all connectionstrings

If my help was helpfull let me know, if not let me know why.

The only way we learn is by making mistaks.

CodeMaster_0516-Sep-07 3:50

CodeMaster_05

16-Sep-07 3:50

well it turns out that the problem was very easy to solve,in my haste i wrote to youfore trying much.,well anyway, I was using a third party control,an infragestics button control,this has a dll that the exe uses "obviously" so i only had to add it to the assembly cache,or raise the assembly trust on the contorl dlls and it worked perfectly.this button was sending commnads to the sql server so the error that was rasied said sql security problem,in reality i had to rasie the trust on that dll Smile | :)

Thank you for your time

DIRECTX audiovideoplayback

plural13-Sep-07 9:54

plural

13-Sep-07 9:54

I give the owner of a video object

vdio=new video.fromFile("location")
vdio.owner=sampicturebox

but the video doesnt play but without an owner, it opens in the default windows which
is a new default window for the directx video??
Any help???
thanks in advance

phatkin

Question on VBScript

T4AMD13-Sep-07 8:49

T4AMD

13-Sep-07 8:49

Can you please show me what is the correct code for that??

Sub Logic()
Const a = 2
Const b = 3
Const c = 5
If a + b = c Then
WScript.Echo (c) //It gives me an error saying Object required
End If
End Sub

Re: Question on VBScript

Dave Kreskowiak13-Sep-07 9:24

13-Sep-07 9:24

Get rid of the Const declarations.
Just do this:

Sub Logic()
    a=2
    b=3
    c=5    
    If a + b = c Then
        WScript.Echo(c)
    End If
End Sub

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Question on VBScript

T4AMD13-Sep-07 10:28

T4AMD

13-Sep-07 10:28

But I am still getting the same error

Re: Question on VBScript

Dave Kreskowiak13-Sep-07 10:42

Synchronising data from a client to a sql server database

13-Sep-07 10:42

OK. This is EXACTLY the code I put into a .vbs file and it works. I think you're missing a couple of characters in your version, possibly because you think they're insignificant, but they're not. Any comments should be denoted by an ' character and not \\ or //

Logic
 
Sub Logic()
    a=2
    b=3
    c=5    
    If a + b = c Then
        WScript.Echo(c)
    End If
End Sub

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

steve_rm13-Sep-07 8:46