Also, INSERT ALL requires a SELECT Statement, but since you are inserting into one table SELECT * FROM DUAL should work.
Imports System.Xml.Linq.XElement
Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String)
Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
Try
Dim SQL =
<SQL>
INSERT ALL
INTO {YourSchemaName}.TEST
VALUES ('ID','Person','Location')
INTO {YourSchemaName}.TEST
VALUES (:strID, :strPerson, :strLocation)
SELECT * FROM DUAL
</SQL>
Using conn As New OracleConnection(OraConnStr)
Using cmd As New OracleCommand(SQL.Value, conn)
cmd.Parameters.Clear()
cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input)
cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input)
cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Catch ex As Exception
End Try
End Sub
Lannie
Contributor
3724 Points
726 Posts
Re: Insert multiple rows into Oracle from .Net
Jan 06, 2012 10:12 PM|LINK
Imports System.Xml.Linq.XElement Public Shared Sub InsertIntoTest(ByVal strID As String, ByVal strPerson As String, ByVal strLocation As String) Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString Try Dim SQL = <SQL> INSERT ALL INTO {YourSchemaName}.TEST VALUES ('ID','Person','Location') INTO {YourSchemaName}.TEST VALUES (:strID, :strPerson, :strLocation) SELECT * FROM DUAL </SQL> Using conn As New OracleConnection(OraConnStr) Using cmd As New OracleCommand(SQL.Value, conn) cmd.Parameters.Clear() cmd.Parameters.Add("strID", OracleDbType.Varchar2, strID, ParameterDirection.Input) cmd.Parameters.Add("strPerson", OracleDbType.Varchar2, strPerson, ParameterDirection.Input) cmd.Parameters.Add("strLocation", OracleDbType.Varchar2, strLocation, ParameterDirection.Input) conn.Open() cmd.ExecuteNonQuery() End Using End Using Catch ex As Exception End Try End Sub