In a VB.NET, I code to exec a store procedre (sp_truncate) to truncate 10 tables and using sqlBulkCopy to download a huge data. Both them are working fine. Now I want to use transaction with vb.net (not in store procedure?) to rollback truncate tables if download is failed. Is it possible to use transaction to work with these two steps?
View 6 Repliesi get an "This SqlTransaction has completed; it is no longer usable." error everytime i run my code:
the main code
If DBOpen() = True Then
Try
'begin transaction
[Code].....
i specified the transaction of sqlcommand named cmd via the Public Sub BeginTranscation. but, is this correct?
also, the error points to the Public Sub RollbackTransaction if the insert to the stored procedure fails. : "This SqlTransaction has completed; it is no longer usable."
how can i perform multiple stored procedure calls within a single transaction correctly?
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]...
what is store procedure and why we use it?
View 5 RepliesI 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]....
Is it possible to code to copy store procedures from one SQL server to another SQL server in vb.net application without using SQL server replication?
View 3 RepliesI am extremly fresher in the above things. I want to create a store procedure in my programme and use it or call the store procedure. give me a standard sample code so that I can understandand.
View 4 RepliesI'm reading "Programming with Microsoft Visual Basic 2008". At the end of each chapter there is excersize that I don't have the answers sheet for. Anyway, the first one is:
A procedure needs to store an item's name and price (which may have decimal places). Write the appropriate Dim statements to declare the necessary procedure-level variables.
What I have so far is:
Dim "Candy" as String
I see this asked many times on hereI'm copying this question that I found on here.Is there any way to pass a null value (I mean a value that the sql will interpret as null) to a stored procedure when using:tableAdapter.Fill(dataSet.table_name, param1 (as string),param2 (as string) etc...Which is the most painless way to do this(assuming that the sp stays inside the database!!)
View 4 RepliesI need information about store procedure in SQL and how can i access textbox values into store procedure. and connection string for remote server (sql server 2005 installed on server)
View 1 RepliesWhat 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 have successfully create a user name = 'root' and host = '127 .0.0.1 ', use the mysql command (client mode) as follow: sql = String.Format("CREATE USER '{0}'@'{1}' IDENTIFIED BY '{2}'; GRANT ALL PRIVILEGES ON *.* TO '{0}'@'{1}' IDENTIFIED BY '{2}' WITH GRANT OPTION;", userName, serverHost, userPassword) When the script above I change it to store procedure (server mode) as below, I found the error as follows: (Error 1064 - You have an error in your SQL syntax; check the manual That corresponds to Yout MySQL server version for the right syntax to use near 'pass' at line 1)
[Code]....
i have problem with inserting data to database by calling store procedure from sqlserver2005. i try to insert, but i can insert some field i don't know this problem correct the code below the procedure is :
[Code]...
I am having some difficulty with an xPath expression. I am using an XML document to store queries / stored procedure names in an ASP application. I use a utility function to load the xmldocument (if it isn't previously loaded) and I am attempting to select the node set from the document that matches an Id element. [code] the node variable only returns the first element. I have verified the that the qry string that is used to SelectSingleNode IS the correct Id value (i.e. USER002) - however the node is getting loaded with the USER001 element. It is obviously the xPath expression that is messed up. What do I need to tweak on the xPath expression so that I can return the correct <Id> element and corresponding child <Sql> element.
View 2 RepliesMy question is How can i create insert,update,delete query by using class file and store procedure. This query is use for 3 tier.
View 2 RepliesI'm using SQL Server 2005, VB.NET 2005. I want to be able to import a very large excel file into a SQL table called "XYZ"
I've done this by doing the following:
1. Save the excel file as csv.(Using SaveAs XLCSV option)
2. Build a datatable "ABC" From CSV.(using ODBC Connection and Select * from '*'.csv command)
3. copy the datatable"ABC" into database table "xyz" (using sqlBulkCopy.WriteToServer())
It works fine without any error but when i checked my database i found that data type for some columns has been changed and hence it didn't copy some of the records.
i have a csv file that needs to be imported to sql server the file includes Unicode characters, so i saved it in notepad as Unicode now when i run this in sql it works excellent BULK INSERT personimps FROM 'C:MyImp.csv' WITH (FIELDTERMINATOR = ',',datafiletype='widechar')but when using the following in my code-behind in asp.net, i get gibberish where the Unicode chars should be. [code] it seems like the oledbconnection is ignoring the extended properties, or maybe i didn't set up the connection-string correctly? the ASCII chars come out OK, the Unicode fields are just gibberish.
View 1 RepliesWe are getting the following error (only on the staging server) when we use SqlBulkCopy. The stored procedure has execute permissions. And they are working properly in our test environment. So what is the setting to be corrected in stage environment to work it properly?
[Code]....
We have a legacy application that is dumping large volumes of data to tab delimited files.Each file contains a single record type and all fields are fixed length.These files can readily be imported into corresponding tables in our SQL server database using the BCP utility from the command line.We have a VB.Net program written in VS 2003 that imports these files using the SQLDMO.BulkCopy routine. We are updating the system to use VS 2010 with SQL Server 2008 and according to the Microsoft documentation SQLDMO is no longer available.I have searched on the internet and have rewritten the import routine to import the tab delimited files into a DataTable using the Microsoft.Jet.OLEDB.4.0 provider. The SqlClient.BulkCopy object is then used to import this DataTable. This issue I am having is that fields in the tab delimited file that are set to spaces are being treating as NULLs when imported into the DataTable. When the DataTable is processed by the SqlClient.BulkCopy the copy fails because the null values are rejected by the SQL table fields that are defined as NOT NULL.Code being tested is shown below
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim data As DataTable = RetrieveSourceData()
[code]....
I need to update a database table from a dataset and only have access to the database through an odbc connection. Is there anyway to make this work or do I need to go a different direction to do this ?
View 1 RepliesI have a .csv file that is selected using the OpenFileDialog that then populates a ataGridView. The .csv file is then visible for the user to check a number of things. This part works fine.What I then need is once it is checked the user then needs to click a button to import this into my SQL Server table (CSV_Import). I have looked at SQLBulkCopy but am failing to get the data imported.Here is some code I have picked up and I am guessing that it is the bc.WriteToServer(dt) needs correcting within the brackets but I am at a loss at this point.
bc.BatchSize = dt.Rows.Count
bc.DestinationTableName = "CSV_Import"
bc.WriteToServer(dt)
[code].....
I am coding a program that will allow the user to input 5 payrolls for Store 1, Store 2, and Store 3. The total of the 5 payrolls are then added together and shown in the respective Store's labels. A total label is also there to add up all the totals into one number. The numbers in the label must be in currency form. My problem is the numbers don't add correctly when I hit calculate. The first two numbers add together, but then after that the number just seems to subtract a random amount.
So far I have this --
Public Class Form1
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
[CODE[...
I am trying to sqlbulkcopy. I have a dataset defined (.xsd). It seems that I cannot use it with sqlbulkcopy. All the information I can find on sqlbulkcopy shows that it can only take a connection string or a variable that contains the connection string.
View 4 RepliesI used sqlbulkcopy to upload huge data from local to remote. It works great.The only problem is that there is a table tClaim in which there are 50 columns. One clumn "IsUpload" do not need to upload. How to upload all columns except for this column? If I use columnmappings, do I have to code add column one by one?
View 1 RepliesI was wondering if the following is possible. In a particular operation I want to create a dataset, bulk copy it to another SQL Server, then update a flag column in the copied rows.I'll have a system that will be regularly inserting new rows of data, then at regular intervals want to copy that data to another SQL Server. Once the rows have copied I need to change a flag column so that they won't get copied again.Whilst this is going on there is a strong possibility new rows of data will be being inserted into the source table. These new rows will be copied at another interval.
View 3 RepliesThe instructions are as follows:
1. Write a Visual Basic Console Application.Inside the main procedure call a function procedure to input and return a value for a double variable called x, the width of a right triangle.Inside the main procedure call the same function procedure a second time to get a value for a double variable called y, the height of a right triangle.
2. From the main procedure call a function procedure which calculates and returns a value for the hypotenuse equal to the square root of (x squared + y squared).You will have to pass the values of x and y to this function procedure.The procedure should calculate and return a double type value.The value that is returned by this function procedure should be equal to the square root of (x squared + y squared).
3. Also from the main procedure call a sub procedure to display the values of x, y, and the hypotenuse.
In a tic tac toe game I am making there are two blocks of code that are identical except that they access different arrays:[code]Arrays plyrTaken and plyrPairs are used in the first block and cmptrTaken and cmptrPairs are used in the second. Is it possible to wrap these blocks into one Sub Procedure passing the different arrays when the procedure is called?
View 5 RepliesWell i ran into some problems changing my getfwt to a independant sub procedure. This stuff is so confusing to me.[code]...
View 14 Repliesi able to get data then i click second time i got error Procedure or function "procedure name" display has too many arguments specified" why this error. [code]
View 2 Replies