sql problem

bapu2889

hello all i am working on one of my assignment about sql win app. with stored procedure.and there is two different tables with ralation to PlotID so it's working fine. now only problem is this application must have functionality to add multiple customer names with related land plot. At the moment i can add one name and one land plot i mean if i add some details about land plot and name of customer then it works fine but now i need to create functionality to add multiple customer name with same land plot bueause one land plot may have multiple owners. this is my insert sp so one of my friend said you can use multiple parameters to add multiple names but what if i need to add 3 or 4 names so this is what i have done according to my friend and this is also working fine.

ALTER PROCEDURE dbo.InsertNew
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@FirstName2 nvarchar(25),
@LastName2 nvarchar(25),
@Village nvarchar(25),
@District nvarchar(25)
)
AS
SET NOCOUNT ON;
DECLARE @PlotID int

INSERT INTO PlotDetails (Village,District)

VALUES (@Village, @District)

SELECT @PlotID=SCOPE\_IDENTITY()

INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])

(SELECT @FirstName, @LastName, @PlotID)
UNION ALL
(SELECT @FirstName2, @LastName2, @PlotID)

RETURN

and this is vb for insert record

Private Sub InsertNew()
Try
Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.CommandText = "InsertNew"

        Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
        Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString

        Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = TextBox1.Text
        Comm.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = TextBox2.Text


        Comm.Parameters.Add("@FirstName2", SqlDbType.NVarChar).Value = FirstName2.Text
        Comm.Parameters.Add("@LastName2", SqlDbType.NVarChar).Value = LastName2.Text

        Comm.ExecuteNonQuery()
        Conn.Close()
        MsgBox("Item Saved")

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try


End Sub

so is there any other way to do this i mean loop in sql? waiting for your kind help thanks

Goutam Patra

Try this Break your InsertNew into two SPs. One for PlotDetails with @Village and @District parameter and save into PlotDetails table. Create a second SP to save CustomerDetails as

ALTER PROCEDURE dbo.InsertNewCustomerDetails
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@PlotID int
)
AS
SET NOCOUNT ON;
IF @PlotID IS NULL BEGIN
SELECT @PlotID=SCOPE_IDENTITY()
END

INSERT INTO CustomerDetails ([FirstName], [LastName],[PlotID])

(SELECT @FirstName, @LastName, @PlotID)

SELECT @PlotID AS PLOTID

Now in your vb code send Null as plotid for the first time and use the same plotid generated in first time next.

bapu2889

hello thanks for your rep. ok but is this going to loop in sql quary? for adding multiple names this is what i have done

Private Sub GetNewName()
    AddName.Add(TextBox1.Text)
    AddName.ToArray()
End Sub

so with this i can add one name or 100 names and i have debug this code and it's looks fine but only thing is how can i insert all this name from array to database so in that case i have to loop while i am inserting so this is i have done for insert

  Comm = New SqlClient.SqlCommand("InsertNew", LetsGo.AndGetConnection)
        Comm.CommandType = CommandType.StoredProcedure
        Comm.CommandText = "InsertNew"

        Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
        Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString


        For I As Integer = 0 To AddName.Count
            Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
        Next



        Comm.ExecuteNonQuery()
        Conn.Close()
        MsgBox("Item Saved")

and it looping through all the name i have entered but at last it shows error error "Index was out of range. must be non-negative and less then the size of the collection parameter name index" waiting for your kind help

Goutam Patra

Not this way. First Execute

Comm = New SqlClient.SqlCommand("InsertNewPlot", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure

Comm.Parameters.Add("@Village", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
Comm.Parameters.Add("@District", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString

to Insert Plot where InsertNewPlot is the first SP to insert plot then something like this

Dim iPlotID as integer
For I As Integer = 0 To AddName.Count
Comm = New SqlClient.SqlCommand("InsertNewCustomerDetails", LetsGo.AndGetConnection)
Comm.CommandType = CommandType.StoredProcedure
Comm.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = AddName(I)
Comm.Parameters.Add("@PlotId", SqlDbType.NVarChar).Value = iPlotID
Dim da As SqlDataAdapter = New SqlDataAdapter(Comm)
da.Fill(ds)
iPlotID = ds.Tables(0).Rows(0)("PLOTID")
Next