Add, Put and Delete

[o-mheien@hdsoftware.no]

Hi folks
I'm kind of new to VB.NET and the usage of SQL. In My past I have been working with a DEV tool called Clarion. Whats nice with Clarion is the Database access engine, wich I like wery much. Reason to move on to VB.net is that Clarion dont have .NET support and probably wil not have it in a long time. Som here is what I nead to know how to do

I nead to know how to access data files and how to update records etc... I'l give a couple of examples here and if some of you nice guys wil give the VB.NET version of the exactly same code, I would really appreciate it.

Ok, here goes:
1. Access a record, do some change and then update it:

CustomerFile.CustomerID = 1234
Get(CustomerFile, CustomerFile.CustomerID)
if errorcode()
  Message('Record 1234 doesnt exist')
Else
  CustomerFile.LastVisited = Today()
  Put(CustomerFile)
End!if

2. Update some records based on a filter and allso delete some

Clear(CustomerFile)
CustomerFile.LastVisitedDate = SomeDate
Set(CustomerFile.VisitDate)
Loop until EOF(CustomerFile)
  Next(CustomerFile)
  if CustomerFile.LastVisitedDate > SomeOtherDate then break.
  if CustomerFile.OrderTotal < 100000 then cycle.
  if CustomerFile.IsToBeDeleted = True
    Delete(CustomerFile)
    Cycle
  End!if
  CustomerFile.Discount = 20
  Put(CustomerFile)
End!Loop

 

[o-mheien@hdsoftware.no]

And of course, to add a new record

CustomerFile.CustomerID = 1234
CustomerFile.Name = "Testing Testingsson"
CustomerFile.Address = "Bradstreet 1"
CustomerFile.Enything = Something
Add(CustomerFile)

 

[cjard]

Ok, here goes:
1. Access a record, do some change and then update it:

CustomerFile.CustomerID = 1234
Get(CustomerFile, CustomerFile.CustomerID)
if errorcode()
  Message('Record 1234 doesnt exist')
Else
  CustomerFile.LastVisited = Today()
  Put(CustomerFile)
End!if

Dim x As New CustomerFileDataTable
customerFileTableAdapter.FillByID(x, "1234")
if(x.Rows.Count = 0)
  MessageBox.Show("No exist")
else
  x(0).LastVisited = DateTime.Now
  customerFileTableAdapter.Update(x)
endif

2. Update some records based on a filter and allso delete some

Clear(CustomerFile)
CustomerFile.LastVisitedDate = SomeDate
Set(CustomerFile.VisitDate)
Loop until EOF(CustomerFile)
  Next(CustomerFile)
  if CustomerFile.LastVisitedDate > SomeOtherDate then break.
  if CustomerFile.OrderTotal < 100000 then cycle.
  if CustomerFile.IsToBeDeleted = True
    Delete(CustomerFile)
    Cycle
  End!if
  CustomerFile.Discount = 20
  Put(CustomerFile)
End!Loop

x.Clear()
foreach(cfdr As CustomerFileDataRow in x.Select("LastVisitDate = #01-jan-1901#"))
  if cfdr.orderTotal < 10000 then continue for
  if cfdr.IsToBeDeleted then cfdr.Delete
  cfdr.Discount = 20
next cfdr
customerFileTableAdapter.Update(x)

I dont really read clarion code.. I'm doing a lot of guessing here.

Also if you clear a recordset, there is nothing in it to filter. Why would you do this? One thing you need to apprecaite with .NET is that it uses disconencted data. We laod data down from the client, mess with it a lot,thne in one sweep, put it back. We dont Update() on every iteration of the loop. Think of it like jumping around an excel sheet then saving at the end, rather than scrolling up and down an access table, and every row we change is persisted as we go..

 

[cjard]

And of course, to add a new record

CustomerFile.CustomerID = 1234
CustomerFile.Name = "Testing Testingsson"
CustomerFile.Address = "Bradstreet 1"
CustomerFile.Enything = Something
Add(CustomerFile)

x.AddCustomerFileROw(1234, "Test Testingson", "Bradstreet", something)
customerFIleTableAdapter.Update(x)

 

[o-mheien@hdsoftware.no]

AHH!! Excellent. Thanx

You are right on the spot :-D

The CLEAR I do in Clarion code is to make sure the record buffer is emptyed before I set key values. Not to worry. You read Clarion code wery well, only the clear part did confuse you a bit.

Thanx alot

Ole

 

[o-mheien@hdsoftware.no]

Uhm, one more thing

You say you read out a dataset, do the changes and then update. What wil happend if you are in a multiuser environment? Like two users opens the same workbook and make changes.
In Clarion I would do it like this:

Hold(DocumentFile,1)  ' Trying to hold the next fileaccess for 1 second
DocumentFile.DocumentID = 10
Get(DocumentFile, DucumentFile.K_DocumentID)
if errorcode() = ERR:RecordAllreadyHeld
  Message('Document opened by another user')
else
  EditTheDocument(DocumentFile.DocumentPath)
  Release()  ' Releasing the holded document
End!if

Ole

 

[cjard]

Workbook? What is that?

In .NET we use a disconnected architecture. If youre not sure of the implications of this, look into how ADO.NET works.. The wizards have the ability to create optimistic-lock compatible SQLs, because they know the original and changed values. FOr example:

UPDATE table SET field1 = @field1_newvalue WHERE pimarykey = @primarykey and (field1 = @field1_oldvalue or field1 is null)

the DataSet knows what value it downloaded. It knows what you edited it to. If this update statement updates 0 records, it is probably because someone else changed it while you were editing it. The architecture can detect this, and your app can be written to know what to do..

this article talks a bit about it:
http://www.15seconds.com/issue/030604.htm


If youre happier with the clarion way, then implement a flag system whereby you flag a record as being checked out to you, and adjust your DML statements to operate on only rows you have checked out (i.e. you do the locking)

 

[o-mheien@hdsoftware.no]

Workbook: Wel, you brought up the Excel Sheet ;-)
Never mind. Your explanation of the situation and how to solve it is directly on the spot here. Thanx alot.

Ole