Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Oracle VB.NET
I am working on a vb application that runs sql scripts/commands/queries that are input by the user. It works fine for all single commands, but does not work if the user enters multiple commands at once, which is highly likely to happen with the use of the application. Fro example the text could be:
   INSERT INTO someTable(col1, col2, col3) VALUES (v1, v2, v3);
   INSERT INTO somaTable(col1, col2, col3) VALUES (v4, v5, v6);
and when the OracleCommand.ExecuteNonQuery is run it gets an error of invalid character (the ";"). Or if the user tries to create multiple procedures like:
  create or replace
  PROCEDURE owner.someprocedure
  ()
  Is
  BEGIN
  ...
  END;
 
  /
  
  create or replace 
  PROCEDURE owner.someprocedure2
  ()
  IS
  BEGIN
  ...
  END;
It will create the first procedure as:
create or replace
  PROCEDURE someprocedure
  ()
  IS
  BEGIN
  ...
  END;
 
  /
 
  create or replace 
  PROCEDURE owner.someprocedure2
  ()
  IS
  BEGIN
  ...
  END;
instead of two individual procedures. So how do I let the OracleCommand know that there are multiple commands/queries/scripts to be run?

Thank you in advance.
Posted 25-Sep-12 9:33am
ddrake2262
Edited 25-Sep-12 9:38am
v2
Comments
Maciej Los at 25-Sep-12 15:14pm
   
Probably, you need to loop through the commands.
digimanus at 25-Sep-12 17:30pm
   
which driver do you use?
ddrake2 at 25-Sep-12 18:24pm
   
I'm new to this, you will have to be a little more specific with the question.
digimanus at 26-Sep-12 6:57am
   
in your connectionstring a provider can be entered. Which one do you use?
digimanus at 27-Sep-12 9:24am
   
also: read about ADO and MARS (Multiple Active Result Sets)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

you where close in your search. Look here[^].
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

1. u should use loop inside stored procedure
2.firstly send multiple rows in single string and split string in procedure
or u can use table type variable
u should try like this

CREATE PROCEDURE [dbo].[spHRSaveSkillsDtls]
/**************************************************************************************************************



EXEC spHRSaveSkillsDtls @CandidateID=1,@SkillsDtls='0±1±P±1±1±$123±10±S±1±$124±12±P±1$'
***********************************************************************/
@CandidateID VARCHAR(20),
@SkillsDtls VARCHAR(5000)

AS
BEGIN
DECLARE
@len INT,@pos1 INT,@pos2 INT,@pos3 INT,@pos4 INT,@pos5 INT,@i INT,@row VARCHAR(50),@rowpos INT,@temp INT,
@SkillsDtlsID INT,@SkillsID SMALLINT,@SkillsType VARCHAR(10),@Active TINYINT,@DisOrder TINYINT
SET @len=LEN(@SkillsDtls)
SET @i=1
SET @temp=0

WHILE(@i<@len)
BEGIN
SET @rowpos=CHARINDEX('$',@SkillsDtls,@i)
SET @row=SUBSTRING(@SkillsDtls,@i,@rowpos-@temp-1)
SET @Pos1=CHARINDEX('±',@row,1)
SET @SkillsDtlsID= SUBSTRING(@row,1,@pos1-1)
SET @pos2=CHARINDEX('±',@row,@pos1+1)
SET @SkillsID =SUBSTRING(@row,@pos1+1,@pos2-@pos1-1)
SET @pos3=CHARINDEX('±',@row,@pos2+1)
SET @SkillsType=SUBSTRING(@row,@pos2+1,@pos3-@pos2-1)
SET @pos4=CHARINDEX('±',@row,@pos3+1)
SET @Active=SUBSTRING(@row,@pos3+1,@pos4-@pos3-1)
SET @pos5=CHARINDEX('±',@row,@pos4+1)
SET @DisOrder=SUBSTRING(@row,@pos4+1,@pos5-@pos4-1)

SELECT @pos1=0,@pos2=0,@pos3=0,@pos4=0,@pos5=0
SET @i=@rowpos+1
SET @temp=@rowpos
SET @row=null
IF(@SkillsDtlsID=0)
BEGIN
--SET @DisOrder=(SELECT MAX(@DisOrder )FROM tbHRCandidateSkillsDtls)
--SET @DisOrder=@DisOrder+1
INSERT INTO tbHRCandidateSkillsDtls(CandidateID,SkillsID,SkillsType,Active,DisOrder)
VALUES(@CandidateID,@SkillsID,@SkillsType,@Active,@DisOrder)
END
ELSE
BEGIN
UPDATE tbHRCandidateSkillsDtls SET CandidateID=@CandidateID,SkillsID=@SkillsID,SkillsType=@SkillsType,Active=@Active
WHERE SkillsDtlsID=@SkillsDtlsID
END
END
END
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sascha Lefévre 485
1 Sergey Alexandrovich Kryukov 325
2 Maciej Los 240
3 Abhinav S 174
4 Peter Leow 120
0 Sergey Alexandrovich Kryukov 6,773
1 OriginalGriff 6,311
2 Maciej Los 2,692
3 Peter Leow 2,654
4 Abhinav S 2,562


Advertise | Privacy | Mobile
Web04 | 2.8.150414.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100