locked
Insert data into Multiple tables (I have problem in c#) RRS feed

  • Question

  • User-1831219222 posted

    I have more than a table. First, I inserted data into first table and read the auto generated ID from that table and insert to another table.

     

    I have a problem to do this, it tells me "Must declare the scalar variable "@url" , any help is appreciated. Thanks

     

    The Dynamic sql query is :

    string sql = " DECLARE @ID int;DECLARE @ID1 int;DECLARE @ID3 int;DECLARE @ID4 int;DECLARE @ID5 int;DECLARE @ID6 int;";

    sql = sql + "Insert into dbo.Records(Url,SourceTitleBar,SourcePageHead,RelatedRecords,Addi_Keywords,Description,IPHI_Title_Bar,IPHI_Page_Head) values ";

    sql = sql + "(@url,@sourceTitle,@sourcePage,@related,@additional,@description,@titleBar,@pageHead);";

    sql = sql + " SET @ID = SCOPE_IDENTITY();";

    sql = sql + " Insert into dbo.Categories(Cate_Name) values(@categoryName);";

    sql = sql + " SET @ID1 = SCOPE_IDENTITY();";

    sql = sql + " Insert into dbo.Subcategories(Sub_Cate_Name) values(@SubcategoryName);";

    sql = sql + " SET @ID3 = SCOPE_IDENTITY();";

    sql = sql + " Insert into dbo.Scope(Scope_Name) values(@scope);";

    sql = sql + " SET @ID4 = SCOPE_IDENTITY();";

    sql = sql + " Insert into dbo.Lead_Health_Indicator(Health_Indicator_Name) values(@indicator);";

    sql = sql + " SET @ID5 = SCOPE_IDENTITY();";

    sql = sql + " Insert into dbo.Category_Subcategory(Cate_ID,Sub_Cate_ID) values (@ID1,@ID3);";

    sql = sql + " SET @ID6 = SCOPE_IDENTITY();";

    sql = sql + " INSERT INTO dbo.Record_Category(RecordID,Cate_Subcate_ID) VALUES (@ID,@ID6);";

    sql = sql + " Insert into dbo.Record_Scope(RecordID,Scope_ID) values (@ID,@ID4);";

    sql = sql + " Insert into dbo.Record_HealthIndicator(RecordID,Health_Indicator_ID) values(@ID,@ID5);";

    SqlConnection conn = new SqlConnection(GetConnectionString());

    conn.Open();

    IDbCommand cmd = conn.CreateCommand();

    cmd.CommandText = sql;

     

     

    IDbDataParameter userParam12 = cmd.CreateParameter();userParam12.ParameterName = "@url";

    userParam12.Value = iphi.URL;

    userParam12.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam12);

    IDbDataParameter userParam2 = cmd.CreateParameter();

    userParam2.ParameterName = "@sourceTitle";

    userParam2.Value = iphi.SourceTitleBar;

    userParam2.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam2);

    IDbDataParameter userParam20 = cmd.CreateParameter();userParam20.ParameterName = "@sourcePage";

    userParam20.Value = iphi.SourcePageHead;

    userParam20.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam20);

    IDbDataParameter userParam3 = cmd.CreateParameter();

    userParam3.ParameterName = "@related";

    userParam3.Value = iphi.RelatedRecords;

    userParam3.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam3);

    IDbDataParameter userParam4 = cmd.CreateParameter();userParam4.ParameterName = "@additional";

    userParam4.Value = iphi.AdditionalKeywords;

    userParam4.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam4);

    IDbDataParameter userParam5 = cmd.CreateParameter();

    userParam5.ParameterName = "@description";

    userParam5.Value = iphi.Description;

    userParam5.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam5);

    IDbDataParameter userParam6 = cmd.CreateParameter();userParam6.ParameterName = "@titleBar";

    userParam6.Value = iphi.IPHITitleBar;

    userParam6.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam6);

    IDbDataParameter userParam7 = cmd.CreateParameter();

    userParam7.ParameterName = "@pageHead";

    userParam7.Value = iphi.IPHIPageHead;

    userParam7.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam7);

     

    IDbDataParameter userParam121 = cmd.CreateParameter();userParam121.ParameterName = "@categoryName";

    userParam121.Value = iphi.Categories;

    userParam121.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam121);

    IDbDataParameter userParam122 = cmd.CreateParameter();

    userParam12.ParameterName = "@SubcategoryName";

    userParam122.Value = iphi.SubCategories;

    userParam122.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam122);

    IDbDataParameter userParam123 = cmd.CreateParameter();userParam123.ParameterName = "@scope";

    userParam123.Value = iphi.Scope;

    userParam123.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam123);

     

    IDbDataParameter userParam124 = cmd.CreateParameter();userParam124.ParameterName = "@indicator";

    userParam124.Value = iphi.Indicator;

    userParam124.DbType = System.Data.
    DbType.String;

    cmd.Parameters.Add(userParam124);

     

     

     

    cmd.ExecuteNonQuery();

     

    }

     

    Thanks

     

    Wednesday, December 17, 2008 11:45 AM

Answers

  • User660823006 posted

    The Dynamic Data forum is for questions related to the new Dynamic Data feature that was added in .NET 3.5 SP1. It looks like your question is about writing dynamic SQL statements. You would be much more likely to get responses if you posted this in the "Data Access and ObjectDataSource Control" forum which is for general ADO.NET based data questions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 17, 2008 1:25 PM

All replies

  • User660823006 posted

    The Dynamic Data forum is for questions related to the new Dynamic Data feature that was added in .NET 3.5 SP1. It looks like your question is about writing dynamic SQL statements. You would be much more likely to get responses if you posted this in the "Data Access and ObjectDataSource Control" forum which is for general ADO.NET based data questions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 17, 2008 1:25 PM
  • User-1831219222 posted

    Thanks, I'll do that.

    Wednesday, December 17, 2008 1:34 PM