Record locking ADO -net - Database Discussion Boards

Create one DataView from Data Source or Manually replace those table name with your logic...

I have created a fake table, in real situation , query from database

/// Create DataSource :: select tablename,tablealias from all_tables

/*Code::*/

ICollection CreateDataSource() {
DataTable dt = new DataTable();
DataRow dr;

dt.Columns.Add(new DataColumn("TableName", typeof(string)));
dt.Columns.Add(new DataColumn("TableAlias", typeof(string)));

for (int i = 0; i < 9; i++) {
dr = dt.NewRow();

dr[0] = i.ToString() + " No Table";
dr[1] = i.ToString() + " No Alias";

dt.Rows.Add(dr);
}

DataView dv = new DataView(dt);
return dv;
}
// Fill the list with above mentioned datasource
private void FillList()
{
ListBox1.DataSource = CreateDataSource();
ListBox1..DataTextField="TableName";
ListBox1.DataValueField="TableAlias";

}

happy programming..

Pijush Biswas, Jaisalmer
softestpk@yahoo.com

love2code

hi

Im trying to use reporting services in sql 2005.

ı want to ask that can ı change my datasource in my report project dynamically? ı have another .net 2005 project and ı have a datagrid here. ım using this report for printing this datagrid. can ı pass this datagrid(or its datasource) to my report?

thanx...

Ali Sapancı
alisapanci@gmail.com

are there anyone whom thinking about something about that matter? or, can't ı explain my problem to you? Smile | :)

ı think ı must explain this again;

ı have a report project in a server and ı have a datagrid in my other project. ı want to report this grid with using this report project. when ım doing this, ı want to use this datagrid's datasource in my report project. can ı pass this datasource in my report project dynamically?

thanks for your help,

Ali

I posted this question before bit I think I may have asked it wrong. I want to set up a program to query 1 field in the DB by pressing a button and then displaying the results in a listbox. At which point I can click in the listbox and display the record in the textboxes.

BINARY

if you are showing an ID field in your list you can use the id ( whenever is selected by the user) and select the record and then show its field in your text boxes.
for example:
using System.Data;
using System.Data.OleDb;

public void listbox_click(object sender,EventArgs e)
{
string selecet_string="SELECT * FROM MyTable WHERE id="+listbox.selectedItem.ToString();
DataSet ds=new DataSet();
OleDbDataAdapter adapter=new OleDbDataAdapter(select_string,cnn) //you should pass your own connection object.
adapter.Fill(ds);
//Then you can fill the text boxes
textBox1.Text=ds.Tables[0].Rows[0][0].ToString();
//etc.
}

I am useing an OleDbDataAdapter with an OleDbConnection. This is connecting directly to a Microsoft Access database (.mdb) with Jet4 inside of a web form ASP.Net page useing Visual Basic.

I have a datagrid object on the page which is databound to the dataset useing the following code.

OleDbDataAdapter1.Fill(DataSet41)
DataGrid1.DataBind()

The code is placed in the page's load event and the .mdb database file as full user permision on the ASP.NET account.

Here is the interesting part that I have discovered. We all know that when you open a database that it will create a .ldb file for locking. When I run the web form it will generate this error.

Could not lock file.
Exception Details: System.Data.OleDb.OleDbException: Could not lock file.
Source Error:
Line 101: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 102: 'Put user code to initialize the page here
Line 103: OleDbDataAdapter1.Fill(DataSet41)
Line 104: DataGrid1.DataBind()
Line 105: End Sub

After playing with this for a while what I did was set the .ldb files ASP.NET permissions to also have full user control for read and write. When I run the page with this .ldb files permissions set in this manner the datagrid will populate the information from the dataset with no errors.

Here is my problem... when you close the webform naturally the .ldb file is deleted automatically. When I run the webform again it will generate another .ldb file but the permissions are back in its orrigional form and I receive the same error "Could Not Lock File" as above.

How do I solve this problem? It appears to me the issue is the .ldb files permissions must be set to accept the ASP.NET read/write permissions but this is a temperary file. Any suggestions as to how I can fix this issue I would really appreciate the help!

Javolin
VB.Net, ASP.Net

I just install the Mdac Sdk 2.8 (I got Windows XP sp1, VC 6 sp5 with Platform Sdk of February 2003 and Msxml Sdk 4.0).
When I compile PRIVLIB.LIB, I got error as "fatal error C1083: Cannot open include file: 'msxmloledb.h': No such file or directory".

I search msxmloledb.h anywhere but can't get it !!
No result in Msdn or google and so on.

Anyone could tell me where is the "msxmloledb.h" file ? where can I get it ?

Thks by advance

Not a developper, just learn programming since 1 month

Hello,
I have windows form application that is updating an sql2k db. Because we want serial keys we have decide to not use SQL2k to generate the keys. To facilitate this we have a table that holds the last key (lastkey) for each table. Right now for each insert into a table this key table must be hit and updated. So in code we have a

Select lastkey form tbKey into an ADO recordset
Update tbKey set lastkey = rs(0)+1

Now that has worked for 3 years. Yesterday it broke. Two applications selected lastkey at the same time and thus inserting the same PK into a table. That sucked what sucked more is that it was a published database so it took out all of the subscribers.

I’ve come up with this bit of sql to update and get the the key at the same time, within one record lock.
declare @Last_System_id int
update tbkey
set tbkey.lastkey =(lastkey+1), @Last_System_id = lastkey+1
where tbname='sometablename'
select @Last_System_id as myKey

I can’t figure out how to execute it with ADO and VB6. Actually it executes just fine. I can’t figure out how to get that select to return into a recordset.
I’ve tried this.

Dim sUpdate as string<br />
Dim NewKey as int <br />
sUpdate = "declare @Last_System_id int update tbkey set tbkey.lastkey =(lastkey+1), Last_System_id = lastkey+1 	where tbname='sometablename' select @Last_System_id as myKey"

<br />
Connection.open<br />
Set my_Recordset = Connection.Execute(sUpdate)<br />
NewKey = my_Recordset(0)

My_recordset is empty I also tried:

<br />
My_Recordset(1)<br />
My_Recordset("myKey")

All give me this error “Run-time error ‘3265’ Item cannot be found in the collection corresponding to the requested name or ordinal.”

Any insight will be much appreciated.
Thanks Ron

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net

I have done something similar. My solution is to generate the Primary Key using a SP.

CREATE TABLE dbo.Sequence
(
TableName varchar(30) NOT NULL,
NextVal int NOT NULL
)
LOCK DATAROWS
go
IF OBJECT_ID('dbo.Sequence') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.Sequence >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.Sequence >>>'
go

CREATE PROCEDURE GetSequence (
@tbl varchar(30))
AS
DECLARE @nextval int
BEGIN
BEGIN TRAN
UPDATE Sequence
SET NextVal = NextVal + 1
WHERE TableName = @tbl

IF @@rowcount = 1
SELECT @nextval = NextVal
FROM Sequence
WHERE TableName = @tbl
COMMIT TRAN
SELECT @nextval
END

Hope this helps.

Thank you for the reply.
At this shop we don’t like using SP’s. The best reason I can come up with is that the boss simply doesn’t trust MS enough to let SQL2k handle such things. But that is a cool bit of code thanks.

We have come up with this solution on the software side. We will no longer care if there is a multiple or Phantom read from the key table, the function will take what ever key it gets; however, on the insert, if a phantom read has occurred, we will catch the Duplicate PK error generated by sql and go back and grab another new Key. I know that I specified that we needed a serial key but this solution will be nearly 100% serial and for the times that the sequins is broken there will only be, statistically, one or two missed. And that won’t hurt us. The reason we need serial is to make DB maintenance easer.
Simply put one error try again.

Ronald Hahn, CNT - Computer Engineering Technologist

New Technologies Analyst

HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net

Sounds like you also don't trust SQL2k to generate the PK as an identity either, if you did the simple solution would be to make the PK field an Integer identity type with an increment of 1...eliminating the need for the PK table and the round trip to get the pk.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Agreed. I dont trust SQL2k to generate the PK as an identity. I have seen in some situations when the Server crashes enormous identity gaps are created. The only solution in this case is to BCP out the data, drop and recreate the table abd then BCP in. It has happened twice with me. So I dont use identity columns anymore.

Live Life King Size
Alomgir Miah

Any recommendations on Data Modeling software that is open source/free?

<signature>
It's good to be alive,
Josef Wainz
Programmer Analyst

Can anyone tell me how to insert NULL value to visual foxpro table with
DataSet? Have posted this question for a long time without an answer. Cry | :((

Urgent..

Mei.

Are you saying that DBNull.Value does not work?

Yes, I tried DBNull.Value, SqlDateTime.Null,"",NULL," "....

All of them failed.

Are you sure the field is nullable?

I think the field is nullable
as I can leave it intact (blank) without problems while
inserting values to other fields in the same row
when opening it with Access .

It seems you got loads of experience.
Is that possible to talk to you of
this problem on phone?

Mei.

0044 792 531 8977

I am trying to bulk insert data from a text file to a temp table. The bulk insert is unable to recognise the linefedd character and tries to insert complete data in one column.

Can anyone help me solve this problem.

Try and alter the line break from {CR}{LF} to {CR} or {LR}. Some Unix files can have strange formating for new carriage returns.

Failing that if you can use anything as row delimiter such as |||@@@|||, as long as you can alter the orginal text file.

Thanks for ths reply, but none of them worked. It works when downloaded through command prompt ftp command, but not when downloaded using our ftp code written in VB.net.

Hi,

I wonder which is better using direct sql statments directly in the code or writing stored procedures inside the SQL Server database? What are the advantages and disadvantages of each?

Thank you

stored procedures are compiled and operated much faster than free line code.
Store procedures are also more secure
Store procedure also let you modify the back end business logic without effecting your web or client applicated.
Store procedures can be unit tested as a seperate business logic.
Store procedure can return scaler values, tables , or action as action code.
Store procedures can have error trapping and rollbacks if multiple tables need to be updates.

Don't use free line SQL unless you can avoid it.

Stored Procedures are better in my opinion.

Here are a couple of reasons:
SPs mean that you can revoke the permissions on direct table access pushing everything through SPs. This means the only actions that an application can perform are only the actions the SPs permit.

SPs can do additional checking of the data. This allows the database to weed out potentially dangerous data as part of a layered security approach. e.g. The application weeds out potentially harmful data first, but if that security layer should be compromised somehow the database itself has a layers of security to prevent dangerous actions - this includes logic within the SPs

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Show him your article colin. Smile | :)

<italic>Work hard, Work effectively.