INSERT MULTIPLE ROWS? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT MULTIPLE ROWS?

Author

Topic

losstww
Starting Member

13 Posts

Posted - 2008-08-01 : 23:01:01

I have a web page where the user can select "Add additional table Row" and a row is added via javscript. The user then types data into the row. Different users may add data into multiple rows or a varying number of rows.

The SQL statement I'm using is simple:

IF NOT EXISTS(SELECT 1 FROM AssetTracking WHERE CaseID=@CaseID AND AssetType=@AssetType AND Description=@Description AND Qty=@Qty AND SKU=@SKU AND Saleable=@Saleable AND Recovered=@Recovered AND Retail=@Retail)

BEGIN

INSERT AssetTracking(CaseID, AssetType, Description, Qty, SKU, Saleable, Recovered, Retail)
VALUES(@CaseID, @AssetType, @Description, @Qty, @SKU, @Saleable, @Recovered, @Retail)
END

How do I create a SQL statement that will allow the insertion of multiple rows or a varying number of rows?

Any help would be appreciated!

losstww

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-01 : 23:05:38

[code]
INSERT AssetTracking(CaseID, AssetType, Description, Qty, SKU, Saleable, Recovered, Retail)
select @CaseID, @AssetType, @Description, @Qty, @SKU, @Saleable, @Recovered, @Retail
from F_TABLE_NUMBER_RANGE (1, @Number_of_Rows)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

losstww
Starting Member

13 Posts

Posted - 2008-08-02 : 14:31:33

I tried the F_TABLE_NUMBER_RANGE (1, @Number_of_Rows) function and I still can not get it to work correctly. When I click the add table row button it copies the above row and adds another row below. When I enter the data, and click insert, the rows combine into the original row and the sql database table shows only one row.

What do I need to do to fix the problem?

My stored procedure:
ALTER PROCEDURE dbo.CaseDataInsert

@ReportType varchar(50), @CreatedBy varchar(50), @OpenDate smalldatetime, @Territory varchar(10),
@Region varchar(10), @StoreNumber varchar(10), @StoreAddress varchar(200), @TiplineID varchar(50),
@Status varchar(50), @CaseType varchar(200), @Offense varchar(200), @CaseID int, @Subject varchar(50),
@LastName varchar(50), @FirstName varchar(50), @MiddleInitial varchar(10), @IDType varchar(50),
@IDNumber varchar(50), @HireDate smallDateTime, @TermDate smallDateTime, @LastDayWorked smallDateTime,
@Phone varchar(50), @Cell varchar(50), @Email varchar(50), @Email2 varchar(50), @JobTitle varchar(50),
@Address varchar(200), @City varchar(50), @State varchar(10), @Zip varchar(10), @Sex varchar(10),
@BirthDate smallDateTime, @Age varchar(10), @Height varchar(10), @Weight varchar(10), @Eyes varchar(50),
@HairColor varchar(50), @Ethnicity varchar(50), @AssetType varchar(50), @Description varchar(200), @Qty varchar(50),
@SKU varchar(50), @Saleable varchar(10), @Recovered varchar(10), @Retail smallmoney, @Number_of_Rows int

AS

IF NOT EXISTS (SELECT 1 FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense)

BEGIN

INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,
Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense)
VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,
@StoreNumber,@StoreAddress,@TiplineID,@Status,@CaseType,@Offense)
SET @CaseID=SCOPE_IDENTITY()
End

ELSE

BEGIN
SELECT @CaseID=CaseId FROM CaseData
WHERE ReportType=@ReportType AND CreatedBy=@CreatedBy AND OpenDate=@OpenDate AND Territory=@Territory AND Region=@Region AND StoreNumber=@StoreNumber AND StoreAddress=@StoreAddress AND TiplineID=@TiplineID AND Status=@Status AND CaseType=@CaseType AND Offense=@Offense
END

IF NOT EXISTS (SELECT 1 FROM Subject WHERE CaseID=@CaseID AND Subject=@Subject AND LastName=@LastName AND FirstName=@FirstName
AND MiddleInitial=@MiddleInitial AND IDType=@IDType AND IDNumber=@IDNumber AND HireDate=@HireDate AND
TermDate=@TermDate AND LastDayWorked=@LastDayWorked AND Phone=@Phone AND Cell=@Cell AND Email=@Email AND Email2=@Email2
AND JobTitle=@JobTitle AND Address=@Address AND City=@City AND State=@State AND Zip=@Zip AND Sex=@Sex
AND BirthDate=@BirthDate AND Age=@Age AND Height=@Height AND Weight=@Weight AND Eyes=@Eyes AND
HairColor=@HairColor AND Ethnicity=@Ethnicity)

BEGIN

INSERT Subject(CaseID, Subject, LastName, FirstName, MiddleInitial, IDTYpe, IDNumber, HireDate, TermDate, LastDayWorked, Phone,
Cell, Email, Email2, JobTitle, Address, City, State, Zip, Sex, BirthDate, Age, Height, Weight, Eyes, HairColor,
Ethnicity)
VALUES (@CaseID, @Subject, @LastName, @FirstName, @MiddleInitial, @IDType, @IDNumber, @HireDate, @TermDate,
@LastDayWorked, @Phone, @Cell, @Email, @Email2, @JobTitle, @Address, @City, @State, @Zip, @Sex, @BirthDate,
@Age, @Height, @Weight, @Eyes, @HairColor, @Ethnicity)

End

IF NOT EXISTS(SELECT 1 FROM AssetTracking WHERE CaseID=@CaseID AND AssetType=@AssetType AND Description=@Description
AND Qty=@Qty AND SKU=@SKU AND Saleable=@Saleable AND Recovered=@Recovered AND Retail=@Retail)

BEGIN

INSERT AssetTracking(CaseID, AssetType, Description, Qty, SKU, Saleable, Recovered, Retail)
SELECT @CaseID, @AssetType, @Description, @Qty, @SKU, @Saleable, @Recovered, @Retail
FROM F_TABLE_NUMBER_RANGE (1, @Number_of_Rows)

END

OnClick VB Code:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Message.Text = ""

Dim cs As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Database2.mdf;Integrated Security=True;User Instance=True"
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "CaseDataInsert"
cmd.Parameters.AddWithValue("@ReportType", DropDownList1.SelectedValue)
cmd.Parameters.AddWithValue("@CreatedBy", DropDownList2.SelectedValue)
cmd.Parameters.AddWithValue("@OpenDate", TextBox1.Text)
cmd.Parameters.AddWithValue("@Territory", TextBox2.Text)
cmd.Parameters.AddWithValue("@Region", DropDownList3.SelectedValue)
cmd.Parameters.AddWithValue("@StoreNumber", TextBox3.Text)
cmd.Parameters.AddWithValue("@StoreAddress", TextBox4.Text)
cmd.Parameters.AddWithValue("@TiplineID", TextBox5.Text)
cmd.Parameters.AddWithValue("@Status", DropDownList4.SelectedValue)
cmd.Parameters.AddWithValue("@CaseType", DropDownList5.SelectedValue)
cmd.Parameters.AddWithValue("@Offense", DropDownList6.SelectedValue)
cmd.Parameters.AddWithValue("@CaseID", TextBox6.Text)
cmd.Parameters.AddWithValue("@Subject", tbFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", tbLastName.Text)
cmd.Parameters.AddWithValue("@FirstName", tbFirstName.Text)
cmd.Parameters.AddWithValue("@MiddleInitial", tbInitial.Text)
cmd.Parameters.AddWithValue("@IDType", ddlIDType.SelectedValue)
cmd.Parameters.AddWithValue("@IDNumber", tbIDNumber.Text)
cmd.Parameters.AddWithValue("@HireDate", tbHireDate.Text)
cmd.Parameters.AddWithValue("@TermDate", tbTermDate.Text)
cmd.Parameters.AddWithValue("@LastDayWorked", tbLDW.Text)
cmd.Parameters.AddWithValue("@Phone", tbPhone.Text)
cmd.Parameters.AddWithValue("@Cell", tbCell.Text)
cmd.Parameters.AddWithValue("@Email", tbEmail.Text)
cmd.Parameters.AddWithValue("@Email2", tbEmail2.Text)
cmd.Parameters.AddWithValue("@JobTitle", ddlJobTitle.SelectedValue)
cmd.Parameters.AddWithValue("@Address", tbAddress.Text)
cmd.Parameters.AddWithValue("@City", tbCity.Text)
cmd.Parameters.AddWithValue("@State", tbState.Text)
cmd.Parameters.AddWithValue("@Zip", tbZip.Text)
cmd.Parameters.AddWithValue("@Sex", ddlSex.SelectedValue)
cmd.Parameters.AddWithValue("@BirthDate", tbBirthDate.Text)
cmd.Parameters.AddWithValue("@Age", tbAge.Text)
cmd.Parameters.AddWithValue("@Height", tbHeight.Text)
cmd.Parameters.AddWithValue("@Weight", tbWeight.Text)
cmd.Parameters.AddWithValue("@Eyes", ddlEyes.SelectedValue)
cmd.Parameters.AddWithValue("@HairColor", ddlHairColor.SelectedValue)
cmd.Parameters.AddWithValue("@Ethnicity", ddlEthnicity.SelectedValue)
cmd.Parameters.AddWithValue("@AssetType", ddlAssetType.SelectedValue)
cmd.Parameters.AddWithValue("@Description", txtDescription.Text)
cmd.Parameters.AddWithValue("@Qty", txtQty.Text)
cmd.Parameters.AddWithValue("@SKU", txtSKU.Text)
cmd.Parameters.AddWithValue("@Saleable", ddlSaleable.SelectedValue)
cmd.Parameters.AddWithValue("@Recovered", ddlRecovered.SelectedValue)
cmd.Parameters.AddWithValue("@Retail", txtRetail.Text)
cmd.Parameters.AddWithValue("@nMax", TextBox7.Text)

cmd.ExecuteNonQuery()
con.Close()
cmd.Dispose()

Message.Text = "Record has been inserted into Database."

End Using
End If

End Sub

For the click event on the asp page I used javascript:

<script type="text/javascript">

function cloneR(){
var root = document.getElementById('tab').getElementsByTagName('tr')[2].parentNode;
var obj = root.getElementsByTagName('tr')[2].cloneNode(true);
root.appendChild(obj);
}
</script>

<a href="javascript:cloneR('tab')">Add row</a>

losstww

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-03 : 09:35:37

Your problem appears to be that you have no idea what the difference between a web page and a database is.

losstww
Starting Member

13 Posts

Posted - 2008-08-03 : 10:29:23

quote:
“The maxim "Nothing but perfection" may be spelled "Paralysis”, Winston Churchill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources