473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB.NET Execute Multiple SQL Statements using OleDb command

I would like to execute multiple SQL Statement using OleDB command but its coming up with error " Characters found after end of statement"

Below is my pieces of code.

Expand|Select|Wrap|Line Numbers
  1. Public Class DalOleDb
  2. Public Class DalOleDb
  3. Private _dss As DataSet
  4.     Private _daa As OleDbDataAdapter
  5.     Private _cnn As OleDbConnection
  6.     Private _cmd As OleDbCommand
  7.     Private _drr As OleDbDataReader
  8.     Private _cnnStr As String
  9.  
  10.     Public Sub New()
  11.         Try
  12.             cnn = New OleDbConnection(cnnString)
  13.             daa = New OleDbDataAdapter()
  14.             dss = New DataSet()
  15.             cmd = New OleDbCommand()
  16.         Catch ex As Exception
  17.             Throw New Exception(ex.Message)
  18.         End Try
  19.     End Sub
  20.  
  21. Sub DeleteBatch()
  22. Try
  23.  
  24. 'Delete batches related to this company
  25.             sb = sb.Append("DELETE FROM BATCH_HEADERS WHERE BH_CUSTNUMB=" & "'" & CompanyCode & "'")
  26.             sb = sb.Append(";")
  27.             sb = sb.Append("DELETE FROM BATCH_DETAILS WHERE BD_CUSTNUMB=" & "'" & CompanyCode & "'")
  28.             sb = sb.Append(";")
  29.             sb = sb.Append("DELETE FROM BATCH_HEADERS_BACKUP WHERE BHB_CUSTNUMB=" & "'" & CompanyCode & "'")
  30.             sb = sb.AppendLine
  31.             sb = sb.Append("DELETE FROM BATCH_DETAILS_BACKUP WHERE BDB_CUSTNUMB=" & "'" & CompanyCode & "'")
  32.             sb = sb.Append(";")
  33. ExecuteSQL(sb.ToString())
  34.  
  35.   Catch ex As Exception
  36.             Throw New Exception(ex.Message)
  37.         Finally
  38.         End Try
  39.   End Sub
  40.  
  41.  
  42. Sub ExecuteSQL(ByRef SQLStatement As String)
  43.         Try
  44.             cmd.CommandText = SQLStatement
  45.             cmd.Connection = cnn
  46.             cnn.Open()
  47.             cmd.ExecuteNonQuery()
  48.         Catch ex As Exception
  49.             Throw New Exception(ex.Message)
  50.         Finally
  51.             cnn.Close()
  52.         End Try
  53.     End Sub
  54.  
End Class
Sep 4 '09 #1
2 12815
kunal pawar
297 100+
look every thing right

plz change code

sb = sb & "DELETE FROM BATCH_HEADERS WHERE BH_CUSTNUMB=" & "'" & CompanyCode & "';"

sb = sb & "DELETE FROM BATCH_DETAILS WHERE BD_CUSTNUMB=" & "'" & CompanyCode & "';"
sb = sb & "DELETE FROM BATCH_HEADERS_BACKUP WHERE BHB_CUSTNUMB=" & "'" & CompanyCode & "';"
sb = sb & "DELETE FROM BATCH_DETAILS_BACKUP WHERE BDB_CUSTNUMB=" & "'" & CompanyCode & "';"
Sep 4 '09 #2
Frinavale
9,735 Expert Mod 8TB
I recommend using Parameters instead of dynamically building your query string.
See http://bytes.com/topic/net/insights/...e-your-program for an example.
Sep 4 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from...
8
by: DB2 Novice | last post by:
I am trying to use DB2 Control Centre (version 8.2) to load one flat file into multiple tables. However, I don't see the options in Control Centre that allows that. Anyone knows how to do this?...
2
by: Nabil | last post by:
I am new to DB2 and here is my situation. I have 2 temp tables where I am trying to insert data (from 2 select statements) DECLARE v_t1 VARCHAR(50); DECLARE v_t1 VARCHAR(50); DECLARE stmt1...
4
by: NS | last post by:
Hi, I am trying to execute a prepare statement using oledb provider for DB2. The command.Prepare() statement is giving me an exception " No error information available:...
1
by: svijay | last post by:
hi I have got a strange problem. May I know any solution for this. Here is the detailed description about the problem We have got a mainframe system and also production and development...
1
by: TheSteph | last post by:
Hi, I need some Advice.. What is the best practice to execute multiple "nonQuery" SQL operations in a transaction ? 1) Execute all in one "sqlCommand.ExecuteNonQuery();" : ...
1
by: arthy | last post by:
Hi, Is it possible to execute multiple statements on to the database using a single dbconnection object.what is the drawback in using .If not possible ,then how can the execution of multiple...
1
by: Muhamamd Khayyam | last post by:
I am passing a query to Oracle 9i using Oledb.command object. Code is Dim c As OleDb.OleDbCommand Dim sOURCE As BindingSource Dim DR As OleDb.OleDbDataReader ...
0
by: teckguan | last post by:
Hi everyone, I would like to ask how am I going to execute multiple scripts using UNIX command? I have found a command to execute. However, I cannot execute it. The command is db2 -txf <<EOF...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.