[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

how can I actually commit changes to the SQL database programtically?

Posted on 2008-02-01
12
Medium Priority
?
884 Views
Last Modified: 2013-12-25
I have VB 2008 Express Edition with MS SQL Server 2005 Express Edition. I have used the built-in wizards in the VB environment to build a database, connect it to the program and insert, change and delete records. It even saved the records. However, after I modified the program a little (didn't do anything to automatically generated code that saves the records to the database), it quit saving the changes. It will save them as long as I don't quit the application, but once I commit the applciation the changes I made are no longer there. Any suggestions?
0
Comment
Question by:evault
  • 6
  • 5
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 20802957
You may have a transaction open and when you close out of the program the transaction rolls back.  Do you know what stored procedures your program is calling?  Is it using inline code in the program?
0
 
LVL 4

Expert Comment

by:jindalankush
ID: 20803407
i think in proram ur using begin tran,rollabck tran,commit tran,
ur commit tran code is not working and rollback tran is working that why u r not able to  see change data.
if you are not executeing rollback tran then ur server speed continues down.
u can see it by sp_lock
0
 
LVL 1

Author Comment

by:evault
ID: 20803450
I am testing the coe and everytime I make a change I click on the save button (code attached) to commit the changes, then either modify znother record or add one. I do not believe I have any open transactions, however I am new to SQL server so I am not going to discount that. How d I tell if I have any open transactions? By the way, I have only the one table in the database, so far.

    Private Sub Tbl_WtyBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Tbl_WtyBindingNavigatorSaveItem.Click
        ' save items???
        Me.Validate()
        Me.Tbl_WtyBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.WtyDBDataSet)
    End Sub

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21011003
In TSQL you can check the @@TRANCOUNT variable.  If it is > 0 then there are open transactions
0
 
LVL 1

Author Comment

by:evault
ID: 21014136
Thank you chapmandew, I will try thast this afternoon snd report back to you.
0
 
LVL 1

Author Comment

by:evault
ID: 21074358
chapmandew - How do I check that variable programmatically?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21074372
In the stored procedure (TSQL) you can do it like this


IF @@TRANCOUNT > 0
PRINT 'A transaction is still open'
ELSE
PRINT 'No transactions open'
0
 
LVL 1

Author Comment

by:evault
ID: 21074716
The language (VB Express 2008) Does not recognize the verb @@TRANCOUNT
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21074839
That is true...you have to call a stored procedure in vb (using ADO or ADO.net) to use the TSQL code....
0
 
LVL 1

Author Comment

by:evault
ID: 21076429
Actually I found out what is going on. Wehn I process the Excell spreadsheets, pulling information out of them, the transaction won't save. Neither will it save when I cut or copy and paste into any of the fields that I am using on the user form. However, if I manually enter a vlaue into any field, by typing, then the record will save. Any idea on that?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 21076935
It sounds like you are having an OLE problem.....post your code.
0
 
LVL 1

Author Comment

by:evault
ID: 21194201
Sorry about he delay, I'll try to be better. Which part of the code do you want?
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question