469,365 Members | 1,840 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,365 developers. It's quick & easy.

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 12381
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

Post your reply

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

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.