I have a project, which divided into three categories.
i) Presentation layer
ii) business layer, and
iii) Data Execution Layer.
Business layer communicate between 1st and 3rd layer. here my problem is I want update some tables through transaction batch. Here I am facing logical concept who to update transaction batch in business layer or data execution layer.
I am trying to use a transaction for the duration that the dialog is open and then commit the changes once the dialog has been ok�d or rollback'd if canceled by the user.My problem is when I use the transaction it is locking the whole of the tables that I�m using to update and insert new records too.I update the record in the first (current) table and insert a new record into the second (history) table. Obviously this is causing me problems as I only want the transaction to lock the specific records i'm updating and not allow a new insert record with my PK associated to my that I�m updating, not lock the whole of the 2 tables.This is my code[code]....
View 3 RepliesI have an application which upon a save button needs to save data to 8 different tables. 7 of those tables will have multiple new records added to them, while 1 will have just one new record. I want to encapsulate this giant Insert operation in a Transaction so that it will not complete unless I can save successfully to all tables.I have considered using Insert commands to a command statement, but adding what amounts to like 50 parameters, and the fact that using a parameterized query means it becomes very difficult to troubleshoot problems such as invalid values in the SQL statement (since I cannot figure out what the statement looks like with values in it
View 3 RepliesI am trying to update a link table with values from multiple datagrids, the field types in the link table are numbers and the value selected from the datagrids are also numbers.
I am getting the following error:
The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.
In line : cmdCompany.Parameters.Add("@COMPANY_ID")
[Code].....
I'm trying to verify if data exists in two different tables in a single transaction. The reason for the single transaction is the database gets hit about 1-3 million times a day so adding anymore than 1 extra transaction would increase that number up to 9 million, and my poor little server needs a break :)
So I need to check if an ID exists in table X and table Y and return the results to my VB.net script so I can handle the outcome Ideally something like this would work
if exists (select id from X where id = @id)
print 'True,' else print 'False,'
if exists (select id from Y where id = @id)
print 'True' else print 'False'
Which gives me "True, True" if exists in both or "True, False" etc etc... But that only displays in SQL print and not actually returning it as an object/string or array values that I can use.
I'm open to any sort of solution of this nature that can give me two results from a single transaction and how to handle that response in vb.
I want to know that do you still need to rollback your transaction if you haven't commit the transaction that was created earlier.
[Code]...
I have taken over support of a VB .Net application that makes use of an Access database. Now I discover that the developer who wrote this application did not create any unique indexes on the primary key columns for the tables in the database. So none of the primary key fields are actually flagged as being primary keys in access and so no duplicate checking is being performed by Access. Furthermore, the primary key columns are not being auto-incremented with an AutoNumber type in Access, rather the developer has used queries like this to insert rows into the tables:
INSERT INTO Users (pkid, FirstName, Surname, City)SELECT iif(isnull(max(pkid)),0,max(pkid))+1, [@strFirstName], [@strSurname], [@strCity], FROM Users;This application is generally run as a networked application with up to 40 people using the application simultaneously, and combined with design issues already mentioned, this has resulted in serious concurrency issues with several users being assigned the same primary key value. This == BIGNow I need to sort this out (guess what I will be doing this weekend!! Great.). I have thought about maybe using a mutex around the code that calls the Access insert queries; not the neatest of solutions, but it would be the quickest. Ultimately though, I would like to have the database corrected and have proper primary key values in place. But the application is currently running at about 15 different sites, so I need to do some sort of batch update of the database.
I get this error at the Commit of a transaction in a desktop application:
This OleDbTransaction has completed; it is no longer usable.
Other posts I have seen with similar error suggests this can occur if it takes a long time, or contains large amounts of data. This is not the case here.Logging tells me it takes 140 ms from Begin to Commit and about 10 commands executed inside the transaction.It is using an Oracle database.This class is a simplified version of my database class:
Class MyDatabase
Private mConnection AS OleDbConnection
Private mTransaction AS OleDbTransaction
[code]....
Is it not allowed to run a select inside a transaction like this? Or can it be done by running the transaction in a specific isolation level (I see that the BeginTransaction method has an optional parameter for doing this) ? ..Or some other sollution..? In my case, it was not a problem to move the select to run before the transaction started, but what if you need to run selects that must run inside the transaction?
I've seen plenty of examples of transactions in the actual store procedure's SQL. But I'm wanting to do the transaction and keep track of it in code. I tried this but it says that my insertcommand's transaction object is null. How can I fix this?
vb Dim conn As SqlConnection = DBConnections.SQLServerConnection
conn.Open()
Dim params(1) As SqlParameter
[CODE]....
I have a web application and on page is an update page to update some profile information. Below is the code I am using to update the table. But I think it is wrong. Does anything stick out? The connection string works cause it is used to read the database to get the profile information, I just removed it due to it containing password/login info for the db.player is the class of properties that contains player information and ds is the dataset, but I would like to update the database itself online.
[Code]...
What is this kind of exception?
Error : Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The transaction property of the command has not been initialized..
This one causes the error: Dim oOperation As COperationOutputTypeBS
oOperation = COperationOutputTypeBS.GetOperationOutputType(txtOperationCode.Text)
I am creating a form in VB to enter customer sales invoices and need to update two tables i.e. InvoiceHeader and InoviceDetails. The Invoice details can have more than one line for one Invoice header. I was wondering if there is any thing like Access subforms or how can I update both tables through an SQL statement.
View 1 RepliesI have a Database with the following tables, PolicyHolder, Beneficiary, FinacialDetails, Gender, Nationality, Relationship, Income. They are all linked by ClientID primary key of the PolicyHolder table.
When i'm adding my data in the Beneficiary table using a DataGridView i get an error when im adding the second entry saying - FK ClientID already has a value and this value cannot be duplicated. I want the FK ClientID value to be saved on every record i enter under Beneficiary.
I'm Using vb 2008 and an access DB.I have 2 tables in the DB which is transaction and products.When a Customer Buy product the transaction will record and the stock of that product will deduct accdg to the quantity bought by the customer. Myproblem is products table didn't update. Stock of that product still didn't change.
View 5 RepliesI have one DataSet and two forms, Customers and Efficiency. When I make changes to the Customers table (Delete a Customer for instance) It does not reflect the changes on the Efficiency table. The default form is Efficiency and I'm calling the Customers form from within the Efficiency form. I make the change in the Customer form, I then close the Customer form and call the Efficiency form back. The data is not updated on the Efficiency side until I close the application and re-open it. what code I need and where to inject this code?
View 14 RepliesI have three tables Tasks, Notes & CallTypes Each item in Tasks and Notes has a relationship to CallTypes and my form edits work fine. However: Notes only belong to tasks, and when i update the note record we will need to change the CallType for the Task as well as for the Note and they could both be different.
I have the Notes working fine, but i cannot update the tasks. I have created a databindng on to a form from the datasources window. All the notes fields are ok and are working I then add the calltype field from the tasks, draged from the data sources window (i dont want all of the tasks fields, just the calltype).
The code does not through up errors, but when i save the form it only updates the notes and not the tasks.
I've been searching all day about this and can't find a solution..I'm fairly new to VB and SQL Queries, so I apologize if I sound a little daft.Basically I'm working on a project in VB 2010 where a user can add new teams, players, also create matches between teams and record the score. I have done nearly all forms, but now I need to show data from 2 tables on one form and the method I used for 1 table doesn't work.I have 4 tables:team, player, match & matchdata and they are linked like this:
[Code]...
Using; - vb2008 Express, data on ACCESS 2003, objective; - to update a separate table not on the original query.From what i have gathered i need to add a second table adapter to make the changes to a related field in another table. Since i already have the 1st table adapter the second does not appear as easy as the second (dragn the data set over).
So i suppose i need to create the second table adapters programmatically? [URL]..I see that because they are not nested they need to each be declared.. right? If so that is where my issue lies. I'm having a problem with how to declare them both now that there must be two.
i have 3 tables t1,t2,t3..if i update t1,t2 and t3 must also be updated.i have just been tried
UPDATE t1,t2,t3 SET t1.f1='sample',t2.f1='sample',t3.f1='sample' WHERE t1.f1='test' and t2.f1='test' and t1.f1='test'
using this,update fails if t2 and t3 could not find 'test' in their fields,if i use OR all records will be..i have also tried JOINS
Update tbl_maincat AS m LEFT JOIN tbl_maincat AS m2 ON m.cTitle = m2.cTitle,
tbl_main AS c LEFT JOIN tbl_main AS c2 ON c.MainCat = c2.Maincat,
tbl_subcat AS s LEFT JOIN tbl_subcat AS s2 ON s.csCat = s2.csCat
[code]....
I am getting a System.Data.InvalidConstraintException" ForeignKeyConstraint CompanyPasswords requires the child key values(-1) to exist in the parent Table. When saving updates to the parent and child tables.My parent table consists of 3 columnsCompanyID (auto increment, pk)Company NameURLMy Child table consists of 4 columns PasswordID (auto increment, pk)CompanyID (fk)User NamePasswordHere is all the code on the form
[Code].....
I have made a form that creates a table in a database based off of the text a user inputs into a text box.I would like to then display that table in a DataGridView on the form so that the user can update the data in that table.Is there any way that i can add those tables to the dataset and get them to display in the DataGridView after the user clicks the create table button?
View 7 RepliesIn my Project I have a Dialog Box (MonkeyList), a Database(MonkeyList.sdf), and DataGridView1. DataGridView1 is on the MonkeyList DialogBox. I want to save changes to the table when OK_Button is clicked.
View 11 RepliesAfter making the changes or adding a record I use invoke childmethod from my toolbar to call these subs. The first table updates and adds, but the second does not. I have set all of my textboxes databindings and double checked them. I get no error, but it does not update the second table. What am I doing wrong?
Public Sub AddRecord()
'Clear out the input data fields on a new Add.
ClearInputFields()
[code].....
How can I do this, this what I got so far.
Dim ClassID As Integer = 1
Dim da As New SqlDataAdapter
Dim con As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=D:Database
[code].....
I would like to know the best, or standard, way to accomplish real-time client updates in a small multi-user application using an access data table as a data source.Specifically, this application will be used by 3-4 people. It uses a datatable which is filled on form load and bound to a datagrid at design time. The datatable is stored on a shared drive. The data table on the shared drive is updated in two ways:
1) Users can update the datagrid which then updates the datatable on the shared drive via the data adapter's Update method.
2) A server application does some work on some text files, periodically updating the data table.
I need the clients to reflect the changes to the data table on the shared drive as closely to real-time as possible. I know that the data table stored in memory on the client is disconnected from the data table on the shared drive, so I must query again to get the updates.
1) Is there an alternvative model I can use which is connected, and which will automatically reflect the updates in the data table on the shared drive?
2) If not, what is the best way to check for changes in the data table (so I can know when to call the data adapter fill method)? I am planning to poll the drive for a change to the .mdb file, raise an event and then fill the data adapter when the event is triggered. I am using this method successfully now to check for changes to a text file in another application, and I know how to implement it?
I would like to know the best, or standard, way to accomplish real-time client updates in a small multi-user application using an access data table as a data source.
Specifically, this application will be used by 3-4 people. It uses a datatable which is filled on form load and bound to a datagrid at design time. The datatable is stored on a shared drive. The data table on the shared drive is updated in two ways:
1) Users can update the datagrid which then updates the datatable on the shared drive via the data adapter's Update method.
2) A server application does some work on some text files, periodically updating the data table.
I need the clients to reflect the changes to the data table on the shared drive as closely to real-time as possible. I know that the data table stored in memory on the client is disconnected from the data table on the shared drive, so I must query again to get the updates.
I have two questions:
1) Is there an alternvative model I can use which is connected, and which will automatically reflect the updates in the data table on the shared drive?
2) If not, what is the best way to check for changes in the data table (so I can know when to call the data adapter fill method)? I am planning to poll the drive for a change to the .mdb file, raise an event and then fill the data adapter when the event is triggered. I am using this method successfully now to check for changes to a text file in another application, and I know how to implement it
I am using VS2008 and SQLite. I have created a database and several tables and am attempting to populate the tables with data from arrays.
The following code (example) works fine but stops after it inserts 30 to 50 records. I am running the VB code in debug mode and when it stops, I press pause to see where it is. It stops on "SQLcommand.ExecuteNonQuery()" and when I press continue (F5) it will insert another 30 to 50 records.
I do not get an error message. Is this a timeout issue? How do I keep the loop running to the end of the array?
Following is example code.
Dim
SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
CODE:.....................
have a datagridview containing 2 tables left joined, so that:table1 LEFT JOIN table 2 ON table1.id=table2.idI get an error whenever I try to edit my datagridview."invalidOperationException was unhandled by the user codeDynamic SQL generation is not supported against multiple base tables."The error points to this line:
da.FillSchema(dt, SchemaType.Mapped)
da.Update(dt) << This line
'da = dataadapter
[code].....
In my datagridview I was fill in using join tables (table1 = pr_employees, table2 = pr_employee_deduction). My problem now is during save button I want to update records from table2 only.[code]
View 3 RepliesWe are presently developing an application, let's call it APP1, which uses a SQL Database which have about 800 stored procedures, 600 tables, etc. APP1 was originally created in order to replace another application, APP0, from which we do not have source code but only SQL tables, Stored Procedures, views, etc. Previous programers of APP1 used some DB objects from this same database and added some other objects specific to APP1 because it becomes bigger than APP0. And we do not need APP0 anymore as APP1 does all what we want, and more.
So, now, we are thinking about a way to find out which objects are used by APP1 in order to remove objects which are ONLY used by APP0.What is the best approach to discover all objects used by APP1 without having to open every single class and form?
Once we will have a complete list of these objects, it will be easy to use a program we bought which detects all dependencies for all SQL Objects specified directly from SQL and remove objects which do not return from any dependencies. Any ideas of how I could get this list without having to go through all our program that have many, many, many classes and forms?
Note : I know, in a perfect world, all calls to PSs and tables should be in a DAL but in the case of the application we're presently working on ... this is not our case! Yippy! (sarcastic yippy) ;)
Note 2 : This application is not using any ORM. So all queries are directly using SqlCommand. So any call to any DB objects are in string format.