|
You say "link in Access" which suggests you are not using Access. You don't say what you are using so I'll assume SQL Server 2000 (which is the most common database in use on this forum)
You change the SqlCommand.CommandText on the first INSERT to something like this:
INSERT table1 (person_name) VALUES(@person_name); SELECT @@IDENTITY; And instead of using ExecuteNonQuery() use this:
int personID = cmd.ExecuteScalar() (You can obviously delete the line that adds the parameter for the person_id.
In the second command, you use the personID you got from the first query for the @person_id parameter.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi, my question is in regards to record locking and updating datasets in a multiuser environment. We have a database that is used to track items in inventory. This windows application is multiuser and accesses a Microsoft access database. We do not want to use databinding for future maintenance and have been using datasets to populate our user interface. The problem we have noticed is that when two users open the same customer at the same time, one user can delete or modify the data and the other user will not be able to see the updated changes on their application. Is there any way to solve this issue? Thank you
|
|
|
|
|
I had a similar issue in an asp.net page. Two users would load the same dataset and whoever saved last would update the database. If the data is not connected there is nothing you can do about it. What I did was implement a save feature that required the user to save the data every two minute. This minimized the problem.
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
ToddHileHoffer wrote:
What I did was implement a save feature that required the user to save the data every two minute. This minimized the problem.
The solution is that when the user saves the information you check what the user was originally presented with with the current contents of the database, if there is no change then the save operation completes. If there is a change between what is currently in the database with what was in the database then the user is informed of the differences and is offered choices to overwrite, merge or cancel their save operation.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello!
First I have to say that I am a beginner - until some weeks I only had basic SQL knowledge.
Now I am working with the SQL server and I learned a lot about ADO.NET.
My question now is:
How can I hide the data from the customer? 
The problem is that we are developing an application and it may be that that the customer would try to manipulate the data if he has the tools to do that.
But we want him only to use our application to work with the data!
So do I have to use the user administration or is there a better strategy how to solve this problem?
Thanks for any help
|
|
|
|
|
Simple: Don't grant them the rights to manipulate the data. Ensure they log on using a restricted account that only allows them access to the functionality that they need. Your application should use an specific account that grants the application only access to the parts of the database it needs.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
Well, maybe 'hiding' is not the right word. Of course it's the customers data but I want him to use our application to work on it.
If he knows only a little about databases or doesn't understand the concept of the DB he could add redundant records or corrupt the integrity. So I want to be sure that he doesn't come up with the idea "Let's try some UPDATE... or DELETE... statements here."
Otherwise we could get some support calls because of errors in the integrity although it was the customers fault.
|
|
|
|
|
|
D2Necro,
As discussed in other threads, you may not consider hiding data from Customer. After all, it is his data. May be for auditing or other purposes, he may need to have his admin team to review it.
However you may like to protect the data from being inadvertantly modified in a format which may be rendered uncomprehendable for the client or may make the data unusable.
For these reasons, you may encrypt the data using standard keys and use standard stored procedures to access and manipulate the same.
Did this attend to your query?
Vasudevan Deepak Kumar
Personal Web: http://www.lavanyadeepak.tk/
I Blog At: http://deepak.blogdrive.com/
|
|
|
|
|
hi my dear,
I tried tried to restore the data base from enteprize manager but the permission error apeared.the manager of the host said me you have permission but yuo should restore tnhe database by using restore command from sql query analyser ?is him advice is true?if yes how can i do this,what command should i use?in the below x is the databse that i want to restore and a is the new name ?in query analyser what database must i select in change database?
restore database a from f:\x.mdb
|
|
|
|
|
You can't restore an mdb file. Use the help index in query analyzer to find the sytax for sp_attach_single_file_db
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
|
I am layman in terms of db programming...so far I have only used MFC ODBC classes. therefore my question may be a little daft.
Can anyone enlighten me what is the difference/advantage of using ADO? Why people are using ADO so much but not ODBC?
|
|
|
|
|
|
hi my dear,
I tried tried to restore the data base from enteprize manager but the permission error apeared.the manager of the host said me you have permission but yuo should restore tnhe database by using restore command from sql query analyser ?is him advice is true?if yes how can i do this,what command should i use?in the below x is the databse that i want to restore and a is the new name ?in query analyser what database must i select in change database?
restore database a from f:\x.mdb
|
|
|
|
|
I'm connecting to an SQL Server database, using BeginTransaction, and I've discovered that Rollback isn't working! The transactions are not rolled back.
Reading through MSDN, I found this:
To ensure that the .NET Framework Data Provider for SQL Server transaction management model performs correctly, avoid using other transaction management models, such as the one provided by SQL Server.
Now, what does that mean? How do I disable SQL Server's transaction management model? Or is this not the problem.
Thanks!
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
The MSDN text means "dont send BEGIN TRAN commands to the server yourself.".
Have you assigned the transaction back to the command, before using it?
There's a sample on Rollback.
|
|
|
|
|
Morten Rasmussen wrote:
The MSDN text means "dont send BEGIN TRAN commands to the server yourself.".
OK. Not doing that.
Morten Rasmussen wrote:
Have you assigned the transaction back to the command, before using it?
Yes. I'm looking at the Rollback example in MSDN, and I'm not doing things different (so says me now, but I'm sure there's something goofy going on).
I did notice, to my horror, that I was using the same SqlConnection over and over. Gads, and this is for a server application!
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
1. issue "set implicit_transactions on" at any point in your code (sql statements) as this turns on autocommit and defeats the possibility of rollback
2. Use transactions inside stored procedures or SQL statements, since commits inside the sproc/SQL will break rollback of the containing (external .NET) transactions.
Basically you have three choices:
1. do all transactions with the API.
2. Use autocommit and forget transactions (bad idea most of the time).
3. do transactions in stored procedures/SQL and not the API.
any mixing of these will be broken in strange ways.
this quote from books online is also pertinent:
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
[Edit] modified to clarify that Transactions inside SQL statements should not be mixed either
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
|
|
|
|
|
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error.
Hmmm. Even if a BeginTransaction (via ADO.NET) was called before the batch commands were issued?
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
Yes.
but, if you issue a "SET XACT_ABORT ON" the behavior is achanged sot the entire transaction is rolled back (by the rollback statement).
See SET XACT_ABORT in the BOL for an example
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
|
|
|
|
|
BTW, that is EXACTLY the problem! Except its not a constraint violation, but a "Cannot insert the value NULL into column..." error.
[edit]erm, ok, that is a constraint violation. What am I thinking![/edit]
Thank you very much!
Now, erm...how does one set this programatically in ADO.NET?
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
sqlCommand.ExecuteNonQuery() whith "SET XACT_ABORT ON" as the SQL text should work. Not sure if the setting persists beyond the life of the connection, but I wouldn't plan on it.
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
|
|
|
|
|
Rob Graham wrote:
sqlCommand.ExecuteNonQuery() whith "SET XACT_ABORT ON" as the SQL text should work.
Tried that. It doesn't seem to. Works fine in Query Analyzer, just not running it from code. Hmmm...
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
