insert using sqlparameters

[Guest]

I am trying use the following code to update sql. The problem is it only
works once, then I get a message that the '@Param1' can only be used once. I
need to know how to reuse these things or reset them!! Can any one please
help me, you see I am new to this type of code.

try
{
sqlInsertCommand1.CommandText = "INSERT INTO services([service-code],
[service-description], [large-animal-cost], [medium-animal-cost],
[small-animal-cost]) " values(@Param1,@Param2,@Param3, @Param4,@Param5)";

SqlParameter param =
sqlInsertCommand1.Parameters.Add("@Param1",SqlDbType.Int);
param.Value = txtServiceCode.Text;

param = sqlInsertCommand1.Parameters.Add("@Param2",SqlDbType.VarChar);
param.Value = txtServiceDesc.Text;
param = sqlInsertCommand1.Parameters.Add("@Param3",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbLargeAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param4",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbMediumAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param5",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbSmallAnimalCost.Text);

sqlInsertCommand1.Connection = sqlConnection1;
if (sqlConnection1.State != ConnectionState.Open)
sqlConnection1.Open();
sqlInsertCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

MessageBox.Show("Insert Complete. [services] " + txtServiceNbr.Text +
" Successful.","");

}

 

[Guest]

Hi,

I think you are trying to insert the record into the table. You can simply
write the query & then use execute .

string str="INSERT INTO services values(" + txtServiceCode.Text +"," +
txtServiceDesc.Text ...+")";

SqlConnection co =initConnection(); //get the connection

//new command
//str is the insert query that we created
SqlCommand myCommand = new SqlCommand(str, co);

//execute command
myCommand.ExecuteNonQuery();

co.Close();

HTH

Regards,
das

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

In fact you should not do this NEVER, the code below is prone to SQL
injection attach , take a look at :
http://www.securiteam.com/securityreviews/5DP0N1P76E.html

if you are not using SP use a parameterized query instead. take a look at
this article, it discuss both concepts
http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Hi,

I think you are trying to insert the record into the table. You can simply
write the query & then use execute .

string str="INSERT INTO services values(" + txtServiceCode.Text +"," +
txtServiceDesc.Text ...+")";

SqlConnection co =initConnection(); //get the connection

//new command
//str is the insert query that we created
SqlCommand myCommand = new SqlCommand(str, co);

//execute command
myCommand.ExecuteNonQuery();

co.Close();

HTH

Regards,
das

I am trying use the following code to update sql. The problem is it only
works once, then I get a message that the '@Param1' can only be used
once. I
need to know how to reuse these things or reset them!! Can any one
please
help me, you see I am new to this type of code.

try
{
sqlInsertCommand1.CommandText = "INSERT INTO services([service-code],
[service-description], [large-animal-cost], [medium-animal-cost],
[small-animal-cost]) " values(@Param1,@Param2,@Param3,
@Param4,@Param5)";

SqlParameter param =
sqlInsertCommand1.Parameters.Add("@Param1",SqlDbType.Int);
param.Value = txtServiceCode.Text;

param = sqlInsertCommand1.Parameters.Add("@Param2",SqlDbType.VarChar);
param.Value = txtServiceDesc.Text;
param = sqlInsertCommand1.Parameters.Add("@Param3",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbLargeAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param4",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbMediumAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param5",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbSmallAnimalCost.Text);

sqlInsertCommand1.Connection = sqlConnection1;
if (sqlConnection1.State != ConnectionState.Open)
sqlConnection1.Open();
sqlInsertCommand1.ExecuteNonQuery();

this.sqlConnection1.Close();

MessageBox.Show("Insert Complete. [services] " + txtServiceNbr.Text +
" Successful.","");

}