locked
how to take a value from excel and import to table in sql server its corresponding id RRS feed

  • Question

  • User821258875 posted

    hi
    I'm doing import excel to SQL server using epplus .i imported excel data to the targeted table Finding_Details but I want to do some manipulation in data. I have two tables in the database Finding_Details (user_id,  name, location, job title) and Location(location, id). I want to take the location imported from the excel and look its ID from Location table and saves that ID in the Finding_Details table.

    what I thought of is
    //string variable that specifies which rows of a specified column in excel i'm working on.
    //looping over each row for that column.
    //used SQLcommand type storedprocedure.

    THIS IS WHAT I REACHED IN STORED PROCEDURE:

    GO
    ALTER PROCEDURE [dbo].[prcGetLocation_PerFinding]
    @Location_Area nvarchar(150),
    @New_Location_Area_ID nvarchar(50) OUTPUT
    AS

    Begin

    select @New_Location_Area_ID = e.Location_Area_ID from Location_Area e
    WHERE Location_Area=@Location_Area

    END

    for(int i=0;i<rows.count;i++)
    {
    string locationRow = dt.row[i].itemArray[9];
    using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("ProcedureName", conn) {
    CommandType = CommandType.StoredProcedure }) {
       conn.Open();
    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);
       command.ExecuteNonQuery();
    }
    }

    but i get an error in line :

    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);

    that stored procedure awaits for a value for @Location_Area

    Tuesday, November 19, 2019 6:57 AM

Answers

  • User821258875 posted

    hi
    I'm doing import excel to SQL server using epplus .i imported excel data to the targeted table Finding_Details but I want to do some manipulation in data. I have two tables in the database Finding_Details (user_id,  name, location, job title) and Location(location, id). I want to take the location imported from the excel and look its ID from Location table and saves that ID in the Finding_Details table.

    what I thought of is
    //string variable that specifies which rows of a specified column in excel i'm working on.
    //looping over each row for that column.
    //used SQLcommand type storedprocedure.

    THIS IS WHAT I REACHED IN STORED PROCEDURE:

    GO
    ALTER PROCEDURE [dbo].[prcGetLocation_PerFinding]
    @Location_Area nvarchar(150),
    @New_Location_Area_ID nvarchar(50) OUTPUT
    AS

    Begin

    select @New_Location_Area_ID = e.Location_Area_ID from Location_Area e
    WHERE Location_Area=@Location_Area

    END

    for(int i=0;i<rows.count;i++)
    {
    string locationRow = dt.row[i].itemArray[9];
    using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("ProcedureName", conn) {
    CommandType = CommandType.StoredProcedure }) {
       conn.Open();
    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);
       command.ExecuteNonQuery();
    }
    }

    but i get an error in line :

    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);

    that stored procedure awaits for a value for @Location_Area

    I solved it 

    so here is the code i used :

    //TO GET THE AUDIT TYPE ACCORDING TO ITS ID
    string con_string = ConfigurationManager.ConnectionStrings["AuditConnectionString"].ConnectionString;
    //TO GET THE Audited_Area ACCORDING TO ITS ID
    string Audited_Area_IDRow = dt.Rows[i].ItemArray[5].ToString();
    using (var conn = new SqlConnection(con_string))
    using (var command = new SqlCommand("prcGetAudited_Area_PerFinding", conn))
    {
    command.CommandType = CommandType.StoredProcedure;
    {
    //retrieves parameter information from storedprocedure
    command.Parameters.AddWithValue("@Audited_Area", Audited_Area_IDRow);
    command.Parameters.Add("@New_Audited_Area_ID", SqlDbType.NVarChar, 50);
    command.Parameters["@New_Audited_Area_ID"].Direction = ParameterDirection.Output;
    conn.Open();
    command.ExecuteNonQuery();
    conn.Close();
    dt.Rows[i]["Audited_Area_ID"] = command.Parameters["@New_Audited_Area_ID"].Value.ToString();

    }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 20, 2019 11:22 AM

All replies

  • User-719153870 posted

    Hi khloud_ali39,

    but i get an error in line :

    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);

    that stored procedure awaits for a value for @Location_Area

    string locationRow = dt.row[i].itemArray[9];

    As we can see from your code, you have set the value for this parameter locationRow. Maybe you should bebug the code to see if the locationRow can get the value from dt.row[i].itemArray[9].

    Also, it should be command.Parameters.AddWithValue("@New_Location_Area_ID", locationRow); instead of addedwithvalue.

    Best Regard,

    Yang Shen

    Wednesday, November 20, 2019 6:25 AM
  • User821258875 posted

    Yang Shen

    Hi khloud_ali39,

    khloud_ali39

    but i get an error in line :

    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);

    that stored procedure awaits for a value for @Location_Area

    khloud_ali39

    string locationRow = dt.row[i].itemArray[9];

    As we can see from your code, you have set the value for this parameter locationRow. Maybe you should bebug the code to see if the locationRow can get the value from dt.row[i].itemArray[9].

    Also, it should be command.Parameters.AddWithValue("@New_Location_Area_ID", locationRow); instead of addedwithvalue.

    Best Regard,

    Yang Shen

    HERE is the code i wrote :

    string Audited_Area_IDRow = dt.Rows[i].ItemArray[5].ToString();
    using (var conn = new SqlConnection(con_string))
    using (var command = new SqlCommand("prcGetAudited_Area_PerFinding", conn))
    {
    command.CommandType = CommandType.StoredProcedure;
    {

    conn.Open();
    //retrieves parameter information from storedprocedure
    SqlCommandBuilder.DeriveParameters(command);
    command.Parameters["@Audited_Area"].Value = Audited_Area_IDRow;
    SqlParameter sqlParameter = command.Parameters.Add("@New_Audited_Area_ID", SqlDbType.VarChar, 50);
    sqlParameter.Direction = ParameterDirection.Output;
    command.Parameters["@New_Audited_Area_ID"].Value = sqlParameter.ToString();
    dt.Rows[i]["Audited_Area_ID"] = sqlParameter;
    command.ExecuteNonQuery();

    }
    }

    and the error that pops up at the exceuteNonQuery():

    the stored procedure has too many arguments

    Here is my stored procedure:

    USE [Audit]
    GO
    /****** Object: StoredProcedure [dbo].[prcGetAudited_Area_PerFinding] Script Date: 11/20/2019 8:51:29 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[prcGetAudited_Area_PerFinding]
    @Audited_Area nvarchar(150),
    @New_Audited_Area_ID nvarchar(50) OUTPUT
    AS

    Begin

    SELECT @New_Audited_Area_ID= F.Audited_Area_ID FROM LKP_Audited_Area F where Audited_Area = @Audited_Area

    END

    and the ID is the one I'm trying to put and replace the audit area from targeted table that matches the audit area from the original table

    by the way :

    audit_areaRow gets the rows I wanted from the excel.

    best regards,

    kholoud.

    Wednesday, November 20, 2019 8:41 AM
  • User821258875 posted

    hi
    I'm doing import excel to SQL server using epplus .i imported excel data to the targeted table Finding_Details but I want to do some manipulation in data. I have two tables in the database Finding_Details (user_id,  name, location, job title) and Location(location, id). I want to take the location imported from the excel and look its ID from Location table and saves that ID in the Finding_Details table.

    what I thought of is
    //string variable that specifies which rows of a specified column in excel i'm working on.
    //looping over each row for that column.
    //used SQLcommand type storedprocedure.

    THIS IS WHAT I REACHED IN STORED PROCEDURE:

    GO
    ALTER PROCEDURE [dbo].[prcGetLocation_PerFinding]
    @Location_Area nvarchar(150),
    @New_Location_Area_ID nvarchar(50) OUTPUT
    AS

    Begin

    select @New_Location_Area_ID = e.Location_Area_ID from Location_Area e
    WHERE Location_Area=@Location_Area

    END

    for(int i=0;i<rows.count;i++)
    {
    string locationRow = dt.row[i].itemArray[9];
    using (var conn = new SqlConnection(connectionString))
    using (var command = new SqlCommand("ProcedureName", conn) {
    CommandType = CommandType.StoredProcedure }) {
       conn.Open();
    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);
       command.ExecuteNonQuery();
    }
    }

    but i get an error in line :

    command.parameter.addedwithvalue("@New_Location_Area_ID",locationRow);

    that stored procedure awaits for a value for @Location_Area

    I solved it 

    so here is the code i used :

    //TO GET THE AUDIT TYPE ACCORDING TO ITS ID
    string con_string = ConfigurationManager.ConnectionStrings["AuditConnectionString"].ConnectionString;
    //TO GET THE Audited_Area ACCORDING TO ITS ID
    string Audited_Area_IDRow = dt.Rows[i].ItemArray[5].ToString();
    using (var conn = new SqlConnection(con_string))
    using (var command = new SqlCommand("prcGetAudited_Area_PerFinding", conn))
    {
    command.CommandType = CommandType.StoredProcedure;
    {
    //retrieves parameter information from storedprocedure
    command.Parameters.AddWithValue("@Audited_Area", Audited_Area_IDRow);
    command.Parameters.Add("@New_Audited_Area_ID", SqlDbType.NVarChar, 50);
    command.Parameters["@New_Audited_Area_ID"].Direction = ParameterDirection.Output;
    conn.Open();
    command.ExecuteNonQuery();
    conn.Close();
    dt.Rows[i]["Audited_Area_ID"] = command.Parameters["@New_Audited_Area_ID"].Value.ToString();

    }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 20, 2019 11:22 AM
  • User-719153870 posted

    Hi khloud_ali39,

    Glad you solved it, you can mark yourself's post as the answer and this will help other people who faces the same issue.

    Best Regard,

    Yang Shen

    Thursday, November 21, 2019 1:09 AM