sainavya1215
asked on
Urgent TransactionStored Proc
I have 4 textbox groups (ie 8 textboxes) on windows form where user enters the lanuage English and french text
For each lineEntered a record is entered in database.
Database Table:
Tbl_Languages :
Fields
Language_ID auto
Line_Number Integer
Englishtext varchar(40)
FrenchText varchar(40)
========================== ========== ========== ========== ========
TxtBoxEnglish1 Indicates line 1 eng entered FIRST RECORD
TxtBoxFrench1 indicates line1 french entered
TxtboxEnglish2 2nd line entered SECOND RECORD
TxtboxFrench2 2nd line entered
TxtboxEnglish3 3rd line entered THIRD RECORD
TxtboxFrench3 3rd line entered
TxtboxEnglish4 4th line entered FOURTH RECORD
TxtboxFrench4
The Issue is I have is a Transactional Stored procedure which inserts Lets sAy values in TableA first and then enters records in Table Tbl_languages
AS its transactional stored proc We wanna do it in one shot.
We cant call insert Statement or my DLL object to invoke the stored proc number of times the lines are inserted instead
the stored proc starts once and ends .How can this be done ... IF 4 lines are filled the stored proc is called once and 4 lines are inserted ...
if 3 then 3 records 2lines 2 records likewise..........All in one shot Only
Can a small example be shown
For each lineEntered a record is entered in database.
Database Table:
Tbl_Languages :
Fields
Language_ID auto
Line_Number Integer
Englishtext varchar(40)
FrenchText varchar(40)
==========================
TxtBoxEnglish1 Indicates line 1 eng entered FIRST RECORD
TxtBoxFrench1 indicates line1 french entered
TxtboxEnglish2 2nd line entered SECOND RECORD
TxtboxFrench2 2nd line entered
TxtboxEnglish3 3rd line entered THIRD RECORD
TxtboxFrench3 3rd line entered
TxtboxEnglish4 4th line entered FOURTH RECORD
TxtboxFrench4
The Issue is I have is a Transactional Stored procedure which inserts Lets sAy values in TableA first and then enters records in Table Tbl_languages
AS its transactional stored proc We wanna do it in one shot.
We cant call insert Statement or my DLL object to invoke the stored proc number of times the lines are inserted instead
the stored proc starts once and ends .How can this be done ... IF 4 lines are filled the stored proc is called once and 4 lines are inserted ...
if 3 then 3 records 2lines 2 records likewise..........All in one shot Only
Can a small example be shown
ASKER
Tbl_Languages :
Fields
Language_ID auto
Line_Number Integer (NOTNULL)
Englishtext varchar(40) (NOTNULL)
FrenchText varchar(40) (NOTNULL)
Fields are NOT NULL
lets say 2 lines are inserted or 3 or 4 or None are entered How can we control this in stored proc
Fields
Language_ID auto
Line_Number Integer (NOTNULL)
Englishtext varchar(40) (NOTNULL)
FrenchText varchar(40) (NOTNULL)
Fields are NOT NULL
lets say 2 lines are inserted or 3 or 4 or None are entered How can we control this in stored proc
well you check if there is anything enetered if yes then you insert
Now concerning the Nulls
you use this function
Public Function NullToString(Var As Variant) As String
If IsNull(Var) Then
NullToString = ""
Else
NullToString = Trim(CStr(Var))
End If
End Function
and it will insert Empty strings instead of nulls
Now concerning the Nulls
you use this function
Public Function NullToString(Var As Variant) As String
If IsNull(Var) Then
NullToString = ""
Else
NullToString = Trim(CStr(Var))
End If
End Function
and it will insert Empty strings instead of nulls
ASKER
ok let me put it this way
lets saythe stored proc looks like
Create procedure sp_trans_Proc
@name varchar(50) Table A value
@city varchar(30) Table A value
====================
@EngText1 varchar(30)
@FrenText1 varchar(30)
@EngText2 varchar(30)
@frenchtext2 varchar(30)
@EngText3 varchar(30)
@frenText3 varchar(30)
@Engtext4 varchar(30)
@FrenText4 varchar(30)
@lineNumber1 int,
@linenumber2 int
@lineNumber3 int
@linenumber4
Begin Tran
Insert into TableA(name,city) values (@name,@city)
if @@error!=0
begin
Rollback Tran
return -100
end
else
insert into Tbl_languages
(
EnglishText,
FrenchText,
LineNumber
)
values
(
@EngText
@frenchText
@LineNumber
)
if @@error!=0
begin
Rollback Tran
return -101
end
Commit Tran
Return 0
I dont understand how 1,2,3,4 or none can be inserted in this stored proc
lets saythe stored proc looks like
Create procedure sp_trans_Proc
@name varchar(50) Table A value
@city varchar(30) Table A value
====================
@EngText1 varchar(30)
@FrenText1 varchar(30)
@EngText2 varchar(30)
@frenchtext2 varchar(30)
@EngText3 varchar(30)
@frenText3 varchar(30)
@Engtext4 varchar(30)
@FrenText4 varchar(30)
@lineNumber1 int,
@linenumber2 int
@lineNumber3 int
@linenumber4
Begin Tran
Insert into TableA(name,city) values (@name,@city)
if @@error!=0
begin
Rollback Tran
return -100
end
else
insert into Tbl_languages
(
EnglishText,
FrenchText,
LineNumber
)
values
(
@EngText
@frenchText
@LineNumber
)
if @@error!=0
begin
Rollback Tran
return -101
end
Commit Tran
Return 0
I dont understand how 1,2,3,4 or none can be inserted in this stored proc
ASKER
Yes MIka I have seen that posting.Hope u had checked out my stored proc......I am well aware that multiple tables can be put in
But in our case we have Inserts into a single table 'n' number of times as per the LINES being passed from client. My question would be how can we insert 2 or 3 or 4 or none records in stored proc given above........How would be control that in stored proc is my question.
If u have the answer pls give a solution using the above stored proc given above. that would be great. It woul dbe easier for me to understand
Hope u got me..Thx in advance
But in our case we have Inserts into a single table 'n' number of times as per the LINES being passed from client. My question would be how can we insert 2 or 3 or 4 or none records in stored proc given above........How would be control that in stored proc is my question.
If u have the answer pls give a solution using the above stored proc given above. that would be great. It woul dbe easier for me to understand
Hope u got me..Thx in advance
ASKER
What is that change required to do in the stored proc and the logic to insert according to lines being passed 1 or 2 or 3 or 4 or none
null parameters
http://www.winnetmag.com/SQLServer/Article/ArticleID/23032/23032.html
OR
Optional Parameters
http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx
http://www.winnetmag.com/SQLServer/Article/ArticleID/23032/23032.html
OR
Optional Parameters
http://weblogs.asp.net/rmclaws/archive/2004/02/18/75381.aspx
ASKER
Can u pls use the above stored proc and show an example how it can be done ...........
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Which of this syntax is correct
if @EngText1 <> " Or if @EngText1 <> ""
if @EngText1 <> " Or if @EngText1 <> ""
@EngText1 <> "
Two Single coates is correct in SQl Store proc when you are checking for blank.
Two Single coates is correct in SQl Store proc when you are checking for blank.
ASKER
ok Sai......Thanks.......
INSERT INTO Tbl_Languages(SectionName,
INSERT INTO Tbl_Languages(SectionName,
INSERT INTO Tbl_Languages(SectionName,