Click here to Skip to main content
15,112,662 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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:
SQL
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:
SQL
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:
SQL
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
Updated 25-Sep-12 9:38am
v2
Comments
Maciej Los 25-Sep-12 15:14pm
   
Probably, you need to loop through the commands.
Herman<T>.Instance 25-Sep-12 17:30pm
   
which driver do you use?
ddrake2 25-Sep-12 18:24pm
   
I'm new to this, you will have to be a little more specific with the question.
Herman<T>.Instance 26-Sep-12 6:57am
   
in your connectionstring a provider can be entered. Which one do you use?
Herman<T>.Instance 27-Sep-12 9:24am
   
also: read about ADO and MARS (Multiple Active Result Sets)

you where close in your search. Look here[^].
   
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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900