Help required for implementing the rowlevel locking trough ADO Connection Object - Database Discussion Boards

Hi All,

I have .net webservice [with sybase backend], which Adds/Updates/Deletes the customer details. Considering the services is deployed on server where database also resides, if more than two clients requests[a multi-user environment] the same Update service, problems regarding the database locking arises.

I think record-level locking would be the better solution for handling datase locking issues related to Updates. for Inserts and Deletes actions using page loaking would be better.

Transaction obejct in .net have Isolation enums which can simulate these , odes anyone incrporated these.

Can Somebody help me out to solve these situation or any related links is appreciated.

Regards,
Vinay

In what way is this the last record in the table? It has to be last based upon some criteria (lname perhaps?). You can't guarantee that the last entry put into the database will be stored at the end of the table which is why you have to do a search on some criteria.

What database are you using?

store your internet favourites online - www.my-faves.co.uk

It is very difficult to search for the last record without any notification for each specific record. In my opinion, you should create one more fied that store a uniqe ID of the record. The structure as follow.

ID    fname    lname    age
1     abc1     bbb      22
2     xyz2     bbb      32
3     klm      css      23

So everytime when you insert new record it will generate the new unique ID. So when you want to call the last record use the following method (suppose that you use Ms access database and DAO.

dim dbs as DAO.Database
dim rst as DAO.Recordset

' Use this code in any procedure where you want to
' call the database.

set dbs = CurrentDb

' Suppose that tblData is the table that store your data.

set rst = dbs.OpenRecordset("SELECT * FROM tblData ORDER BY tblData.ID")

with rst
    if .Recordcount > 0 then
        .MoveFirst
        .MoveLast       ' Populate the recordset.
    end if
    ' Post the record on the form.
    txtId.value = .Field(0).value
    txtfname.value = .Field(1).value
    txtlname.value = .Field(2).value
    txtage.value = .Field(3).value
end with

Try to do it and hope it is working Laugh | :laugh:

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

How to get two or more params in the Extend Procedure DLL ?

In DLL the method is :

SRVRETCODE func( SRV_PROC* pSrvProc )
{
...
}

the pSrvProc might transmit two or more params. But i dont know How to get
their values?

for example, i SQL procedure, i will use this code:
Exec func @param1,@param2,@param3

How to get the three params' value in the DLL ?

Does this help at all?

http://www.codeproject.com/database/sqlunicode.asp

store your internet favourites online - www.my-faves.co.uk

I try to find the auto-increament for the Number - field, but can't.

In Ms SQL database, it supports this value.

Can anyone please advice me?

Thank you very much.

Yes access does support this. Its called an AutoNumber or Counter.

store your internet favourites online - www.my-faves.co.uk

Dear All,

I need the help for implementing the row level locking in my transaction-based form.

Following is the details of the working logic and the problem faced by me : -

Front End: MS Visual Basic 6.0
Back End: MS SQL Server 2000.

In one of my form first the user makes the document and saves it. While saving the document numbers is generated and then the details are saved in the detail table and one entry will be passed in the header table. After which the user needs to do the authorization of the same.

In the authorization form when the user selects the particular document for authorization then in the lost focus of the document number text box I want to lock the record for that document in the header table. The table contains precisely one record for each document.

I first BEGIN the transaction for the connection object I’m using and then I set the LOCK_TIMEOUT property to 0, by executing the statement 'SET LOCK_TIMEOUT 0' through the connection object. Then I execute the following statement: -

SELECT * from HEADERTBL WITH (UPDLOCK) where GRNNO='001'

But when I go back to SQL Server and execute the SP_LOCK procedure that gives me the status of locks hold by SQL Server. It shows that the record that I want is locked by Update lock but the other records are also locked by SHARED Lock, which I don’t want. After going through the number of records locks it was found that it locks the whole page by default with SHARED Lock and the record matching the where condition with the lock specified.

The problem is that if some other user when tries to lock some other document for UPDATE Lock then SQL Server refuses that until the rows locked previously are released.

Is anybody can help to set some property through ADO or directly so the records matching the where condition are only locked and other users are also allowed to lock other records then the one already locked. Also the locked records need to be allowed to read by all the users as they take the help for document number and due to which I was unable to use EXCLUSIVE Locks.

Revert back in case of any further details are required.

Chintan

You are attempting to use database level concurrency for an application concurrency problem, and it is a poor fit.

Rather create your own "locking" table, and use that to mark documents as locked.

my blog

hi chintan,

I a'm facing the similar proble, can u share how do u sort out the probelm

Dear All,

I am going write a small application which will use an ADO connection to an Access 2002 or MSSQL database.

I have traditionally used VC++ for nearly every project but I am hearing that this platform might not be the most suitable for applications requiring an ADO connection.

Is is better to create this project using VB or C#, or just stick with VC++?

Any help greatly appreicated.

Thanks

I have not used VC++ so I cannot say whether it is any good for ADO. I imagine it works just fine though.

VB6's prime advantage is the ease of GUI development, and interaction with COM. C# or VB.NET have a really good, strong API, and good support for all styles of applications, including ADO.NET (different from ADO).

my blog

Thanks for your reply. I'll give C# a look. If it's as intuative as it's supposed to be, then there should be no real trouble.

thanks

Greg

Have used ADO from CPP before, and TBH, pretty much as you'd expect - more hassle to set it up, more hassle to use it, and the results are the same - since ADO itself is written in CPP, there's no performance enhancement for the ADO calls.

If you do go down the CPP route, would suggest that you #import the dll, and get the TLI / TLH generated - simplifies things! Having said that, if using CPP, why not just use OLEDB?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

I think I understand. It's just, (and I've been a commerical programmer for some time) I don't want to learn yet another language. I've been resisting C# for some time.
I guess you just have to keep going.

Thanks

Greg

Hi all,
I tried to connect to an MSSQL 2k server using a secured (SSL) connection by enabling SSL support on either server side (installed CA and enabled the secured option) or client side (enable "Use data encryption" in ADO connection string). If I use the option on server side, it works ok. But I want to use client-side enabled secured channel, not server-side. When I tried to connect, the ADO connection reported error like: "SSL Security error.... SECDoClientHandShake()". I tried to import the CA to trusted root on client computer but still not work !?.
Currently I just want to make sure just specific clients (allowed) can connect to my SQL server using secured channel (by validate CA or something) but I dont know how.
Anyone can help?
Thanks very much.

Zro

And another problem: If the client connects to a secured enabled SQLServer, how can that client know the connection is secured or not? is there any properties/methods of ADO ConnectionPtr to detect that?

I dont have SQL server, just an XML file on my hard drive.
How do i use Server Explorer to build aconnection to that XML file?

I can, so far, load the file into a dataset using:

DataSet MDS = new DataSet();
MDS.ReadXml("C:\\Students.xml");

But this doesnt allow for manipulaiton e.g: SELECT, DELETE, INSERT INTO, etc....
Does it??

I've had a bash conecting to an Access database and mangaed to manipulate it and do every thing i need to really.... But i need to be able to do this with an XML file and the informaiton in : Developing Windows Based Applictions seems a bit thin with regards connecting to and altering XML files.

Thanks

It's not a server, why would server explorer connect to it ? Why do you need to put it into a dataset, instead of an XmlDocument ? I have no idea how far the Xml support goes in a DataSet, but I know you can perform selects on a datatable, at least.

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Can you give me an example of how i would perform a select on a DataSet please?
The only methods i have read in the book involve using the SqlConnection

i.e

string MyCom ="SELECT * WHERE id = 001"
SqlCommand MySqlCom = SQLCONNECTION.CreateCommand();
MySqlCom.CommanText = MyCom;
etc...

As you can see doing that requires you to refference the SqlConnection. So having no connection means i couldnt select in this way (and its the only way i have used up to now).

Also the Access files i have been using are not servers either but you can ad them through server explorer ??

JDUK wrote:
Also the Access files i have been using are not servers either but you can ad them through server explorer ??

Yes, because they are in fact a database.

The DataView has a RowFilter which basically operates like a select. I thought there was another object which held a table and took a select, but I could be thinking of that one.

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Hi
I'm creating insert command object with command builder and then adding "; SELECT SCOPE_IDENTITY() as primary_key_field" formula to CommandText property of this object. I'm also setting its UpdateRowSource property to FirstReturnedRecord.

After calling update method of data adapter I do not get new
primary key value.

I did the same without using command builder (I've created insert command object in code "manually" and it's identical to the one which is created by the command builder) and everything seems to work fine. Does someone have any idea why retrieving autoincrement value doesn't work when I use CommandBuilder?

Thanks for any clues

Maciek

ps. If you need to see a code example, just let me know.

I have the myfile.mdb in sub-folder database, sometime I want to delete it but after I do the following (correct location), it is still there:

<br />
   set fs=Server.CreateObject("Scripting.FileSystemObject")	  	<br />
   <br />
   set f = fs.GetFile("\database\myfile.mdb")<br />
   f.Delete[True]<br />
   <br />
   set f=nothing<br />
   set fs=nothing<br />

Does anyone know how to do it differently?
Thanks

Are there any open connections to the database (does the .ldb file exist)?

If the database is in use, you won;t be able to delete it.

What does Delete return?

store your internet favourites online - www.my-faves.co.uk

Can anyone please help me on the error handling?

This code below is belongs to SQL.

I'm doing in Access.

Catch ex As Exception

if ex.Number = 2627 <---- ex.Number is not support in system.data.OleDb.

Any solution?

Please help.

Try
myCommand.ExecuteNonQuery()
Message.Style("color") = "DarkBlue"
Message.InnerHtml = "Record is created successfully."
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try