Question 87642 in ASP.NET Question posted in
The official documentation can be found here.

Adding SqlCommand parameters in C# – Asp.net

Please help me to add paramaters in this queryToDB SqlCommand

this is the code

cons = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
cons.Open();

SqlCommand queryToDB = new SqlCommand("INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)", cons);

//param.ParameterName  "@Name";
//param.Value = prefix + extension;
//string queryToDB = "INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)";
connExcel.Open();
cmdExcel.CommandText =
    "SELECT NAMA , REPLACE(NOHP, '-', '' ) as NOHP,TANGGAL, NOMINAL  From [" + sheetName +
    "] WHERE NAMA IS NOT NULL OR NoHP IS NOT NULL OR Tanggal IS NOT NULL OR NOMINAL IS NOT NULL";
odaExcel.SelectCommand = cmdExcel;
//dapetin data dimasukin ke dtSheet
odaExcel.Fill(dtSheet);

connExcel.Close();

I want to add parameters :

@Name = prefix+extension
@Path = filepath+extension
@Blasted = 0
@CreatedBy = user
@CreatedDate = DateTime.Now()

I have no idea to use SqlCommand because it’s first time using this.
Thanks 🙂

Tags:

2

Answers


  1. 0

    There are different ways to add parameters to an SqlCommand, e.g:

    command.Parameters.Add("@ID", SqlDbType.Int).Value = customerID;

    or

    command.Parameters.AddWithValue("@demographics", demoXml);

    so for example, you could use this:

    queryToDB.Parameters.AddWithValue("@Name", prefix + extension);
    Login or Signup to reply.
  2. 0

    As others have commented my answer was more of a comment than an answer, I will expand. I was merely following SIMILAR to what M4N was offering.
    However, I have found, and other noted that although you do NOT NEED to explicitly list all the columns of the table you are inserting, it also implies you are inserting them in the order they are in the table structure. If your parameters were bogus simple like @var1, @var2, @var3, but represented column7, column2, column4 of the table, you are toast, especially if data types are different.

    So, the need of explicitly stating the columns prevents ambiguous results as indicated by yet another. If the table structure changes and does not allow nulls, at least you will get an error message that the insert failed because columnX does not allow null values and you can explicitly fix that.

    So, back to the answer, by explicitly including the columns to be inserted. I also try NOT to use exact column name as @ parameter, again for ambiguity. Dont want to mentally confuse a column vs parameter, so I would do @parmColumnName — but again, that’s just me. I would have

    var queryToDB = new SqlCommand(
@"INSERT INTO [dbo].[FileUploadDBs] 
   ( Name, 
     Path, 
     Blasted, 
     CreatedBy, 
     CreateDate )
   values 
   ( @parmName, 
     @parmPath, 
     @parmBlasted, 
     @parmCreatedBy, 
     @parmCreatedDate)", cons);
                            

         
queryToDB.Parameters.Add("@parmName", SqlDbType.VarChar) { Value = yourVarContainingTheName };
queryToDB.Parameters.Add("@parmPath", SqlDbType.VarChar) { Value = yourVarForThePath };
// guessing on data type for blasted and created by
queryToDB.Parameters.Add("@parmBlasted", SqlDbType.Int) { Value = yourVarForBlasted };
// guessing on data type for created by, could be a foreign key to user lookup table
queryToDB.Parameters.Add("@parmCreatedBy", SqlDbType.Int) { Value = yourVarForCreatedBy };
queryToDB.Parameters.Add("@parmCreateDate", SqlDbType.DateTime2) { Value = DateTime.Now };

    Also, since naming the parameters to match, even if you added them out-of-order, but matching the given SQL command, it would find them and be available, but better practice and habit is to add in the same order.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search