SQL INSERT ISSUE

bapu2889

HELLO ALL I AM WORKING ON ONE OF MY ASSIGNMENT WITH TWO TABLES AND I HAVE TO USE STORED PROCEDURES BOTH THE TABLES ARE RELATED. LAND PLOT MAY HAVE MORE THEN ONE AQUIRER SO THERE HAS TO BE A PROVISION TO ADD MORE THEN ONE NAMES FOR ONE LAND PLOT SO ONE TABLE IS FOR LAND DETAILS AND SECOND ONE IS FOR CUSTOMER DETAILS THIS IS MY SP

ALTER PROCEDURE dbo.NewInsertCommand
(
@City nvarchar(50),
@State nvarchar(50),
@FirstName nvarchar(25),
@LastName nvarchar(25),
@Count int
)
AS
SET NOCOUNT ON;
DECLARE @LocationID int;
DECLARE @CountB int;
INSERT INTO [Location] (City,State)
VALUES (@City, @State)

	SELECT @LocationID = SCOPE\_IDENTITY()

SET @CountB = 0
WHILE @CountB < @Count
BEGIN
SET @CountB = @CountB + 1

INSERT INTO [UserData] (LocationID, FirstName, LastName)
(SELECT @LocationID, @FirstName, @LastName)
IF @CountB = @Count
BREAK
END

AND THIS IS MY VB THIS IS FOR TO ADD MULTIPLE NAMES IF THERE IS ANY

Private Sub AddNewNames()
FirstNameArray.Add(FirstName.Text)
FirstNameArray.ToArray()
LastNameArray.Add(LastName.Text)
LastNameArray.ToArray()
End Sub

THIS IS FOR TO INSERT DATA

Private Sub InsertData()

    Try

        'GetDBConnection()
        Dim cmd As New SqlCommand("NewInsertCommand", LetsGo.GetDBConnection)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem
        cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem

        For I As Integer = 0 To CInt(FirstNameArray.Count) - 1
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstNameArray(I).ToString
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastNameArray(I).ToString
        Next

        cmd.Parameters.Add("@Count", SqlDbType.Int).Value = CInt(FirstNameArray.Count)

        cmd.ExecuteNonQuery()
        MsgBox("Data Saved Successfully !", MsgBoxStyle.Information)
    Catch ex As SqlException
        MessageBox.Show(ex.ToString)
    Finally
        FirstNameArray.Clear()
        LastNameArray.Clear()
    End

SO IF I ADD ONE NAME THEN IT WORKS JUST FINE BUT IF I ADD MORE THEN ONE THEN I AM GETTING THIS ERROR Argument 'Prompt' cannot be converted to type 'String'. ANY HELP FOR THIS PLEASE

Matt U

I'm sorry I have no answer to your question. However, I don't know that you will get one seeing as how you violated #8 in the "How to get an answer to your question" sticky thread. :-P 8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.

Stoffy1972

Hello bapu2889, i agree to Matt U. if you want to get fast and good answers, don't SHOUT. To your problem. You're calling a stored procedure which has 5 parameters (in). When you use more than one pair of names, your code creates a sqlcommand with 7 parameters, 9 parameters and so on. When i try your code, i am getting an sqlexception because there are too many parameters. The exception you are getting is an conversion error in the catch-clause. Sorry, but i can't find this out because i haven't installed vb.net. Try this: Insert the location data (change your sp, return the id of the new location as out parameter). Insert each of the userdata with the id of the new location (execute second sp in a loop, set parameters). hth stoffy

bapu2889

hello sir thanks for ur rep. and i dont know that i have shouted on any body this is what i have done so far but no luck. this is 2 different sp

ALTER PROCEDURE dbo.NewInsertCommand
(
@City nvarchar(50),
@State nvarchar(50)
)
AS
SET NOCOUNT ON;

DECLARE @LocationID int;

INSERT INTO [Location] (City,State)
VALUES (@City, @State)

SELECT @LocationID = SCOPE_IDENTITY()

this is the second one

ALTER PROCEDURE dbo.InsertUser

@FirstName varchar(25),
@LastName varchar(25),
@LocationID int

AS

INSERT INTO \[UserData\] (LocationID, FirstName, LastName)

(SELECT @LocationID, @FirstName, @LastName)

RETURN

and this is the vb

Private Sub AddNewNames()
FirstNameArray.Add(FirstName.Text)
FirstNameArray.ToArray()
LastNameArray.Add(LastName.Text)
LastNameArray.ToArray()
End Sub

Private Sub InsertData()

    Try
        Dim Flag As Boolean = False
        'GetDBConnection()
        Dim cmd As New SqlCommand("NewInsertCommand", LetsGo.GetDBConnection)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem
        cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem
        cmd.ExecuteNonQuery()

        cmd = New SqlCommand("InsertUser", LetsGo.GetDBConnection)
        cmd.CommandType = CommandType.StoredProcedure
        For I As Integer = 0 To FirstNameArray.Count - 1
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstNameArray(I).ToString
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastNameArray(I).ToString
            cmd.ExecuteNonQuery()
        Next

        MsgBox("Data Saved Successfully !", MsgBoxStyle.Information)

and one more thing i dont know how to get @LocationID from one sp to another sp as well so would you mind to guide me please waiting for your kind help thanks

PIEBALDconsult

Don't add the parameters in the for loop -- add them before it and then set them inside the loop.

Stoffy1972

The first stored procedure:

CREATE PROCEDURE [dbo].[InsertLocation]
(
@City nvarchar(50),
@State nvarchar(50),
@LocationID int out
)
AS
INSERT INTO [Location] (City,State)
VALUES (@City, @State)

SELECT @LocationID = SCOPE\_IDENTITY()

GO

and the second:

CREATE PROCEDURE [dbo].[InsertUserData]
(
@LocationID int,
@FirstName nvarchar(25),
@LastName nvarchar(25)
)
AS
SET NOCOUNT ON;

INSERT INTO \[UserData\] (LocationID, FirstName, LastName)
	(SELECT @LocationID, @FirstName, @LastName)

GO

My code, in c#: Assuming that the array of names is filled with items

    private static void insertData()
    {
        try
        {
            //Create connection string using SqlConnectionStringBuilder
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = ".\\\\SQLEXPRESS";
            sb.InitialCatalog = "Database";
            sb.IntegratedSecurity = true;

            // Create SqlConnection
            using (SqlConnection cn = new SqlConnection(sb.ToString()))
            {
                // Open the connection
                cn.Open();

                // Create command to insert location 
                SqlCommand insertLocationCommand = new SqlCommand("InsertLocation", cn);
                insertLocationCommand.CommandType = CommandType.StoredProcedure;
                // Add first parameter for City (nvarchar)
                insertLocationCommand.Parameters.Add("@City", SqlDbType.NVarChar).Value = "City1";
                // Add second parameter for State (nvarchar)
                insertLocationCommand.Parameters.Add("@State", SqlDbType.NVarChar).Value = "State1";
                // Add third parameter for LocationID (int)...
                insertLocationCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = 0;
                // ... and mark it as OUTPUT-Parameter
                insertLocationCommand.Parameters\["@LocationID"\].Direction = ParameterDirection.Output;
                // Execute command...
                insertLocationCommand.ExecuteNonQuery();
                // ... and get value of out parameter
                int locationID = (int)insertLocationCommand.Parameters\["@LocationID"\].Value;

                // Create command to insert user data
                SqlCommand insertUserDataCommand = new SqlCommand("Ins

bapu2889

HELLO SIR THANK YOU VERY MUCH IT'S WORKS NICE NOW I WAS LOOPING IN MY SP AND VB BOTH SO NOW I HAVE MUCH BETTER IDEA ABOUT THIS SO AND I HAVE CONVERTED TO VB. I CAN UNDERSTAND C# BUT I AM NOT VERY GOOD WITH IT BUT ANY WAY YOUR HELP AND UR SUPPORT WAS VERY GOOD THANKS AGAIN :)

Luc Pattyn

from the forum guidelines: 6. Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you. 8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job. :|

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.