insert using sqlparameters

[nbohana]

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
{
sqlInsertComman d1.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 =
sqlInsertComman d1.Parameters.A dd("@Param1",Sq lDbType.Int);
param.Value = txtServiceCode. Text;

param = sqlInsertComman d1.Parameters.A dd("@Param2",Sq lDbType.VarChar );
param.Value = txtServiceDesc. Text;
param = sqlInsertComman d1.Parameters.A dd("@Param3",Sq lDbType.Money);
param.Value = Convert.ToDecim al(cbLargeAnima lCost.Text);
param = sqlInsertComman d1.Parameters.A dd("@Param4",Sq lDbType.Money);
param.Value = Convert.ToDecim al(cbMediumAnim alCost.Text);
param = sqlInsertComman d1.Parameters.A dd("@Param5",Sq lDbType.Money);
param.Value = Convert.ToDecim al(cbSmallAnima lCost.Text);

sqlInsertComman d1.Connection = sqlConnection1;
if (sqlConnection1 .State != ConnectionState .Open)
sqlConnection1. Open();
sqlInsertComman d1.ExecuteNonQu ery();

this.sqlConnect ion1.Close();

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

}

--
Norm Bohana

[Das]

RE: insert using sqlparameters

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.Execu teNonQuery();

co.Close();

HTH

Regards,
das

"nbohana" wrote:
[color=blue]
> 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
> {
> sqlInsertComman d1.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 =
> sqlInsertComman d1.Parameters.A dd("@Param1",Sq lDbType.Int);
> param.Value = txtServiceCode. Text;
>
> param = sqlInsertComman d1.Parameters.A dd("@Param2",Sq lDbType.VarChar );
> param.Value = txtServiceDesc. Text;
> param = sqlInsertComman d1.Parameters.A dd("@Param3",Sq lDbType.Money);
> param.Value = Convert.ToDecim al(cbLargeAnima lCost.Text);
> param = sqlInsertComman d1.Parameters.A dd("@Param4",Sq lDbType.Money);
> param.Value = Convert.ToDecim al(cbMediumAnim alCost.Text);
> param = sqlInsertComman d1.Parameters.A dd("@Param5",Sq lDbType.Money);
> param.Value = Convert.ToDecim al(cbSmallAnima lCost.Text);
>
> sqlInsertComman d1.Connection = sqlConnection1;
> if (sqlConnection1 .State != ConnectionState .Open)
> sqlConnection1. Open();
> sqlInsertComman d1.ExecuteNonQu ery();
>
> this.sqlConnect ion1.Close();
>
> MessageBox.Show ("Insert Complete. [services] " + txtServiceNbr.T ext +
> " Successful.","" );
>
> }
>
> --
> Norm Bohana[/color]

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

Re: insert using sqlparameters

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


"Das" <Das@discussion s.microsoft.com > wrote in message
news:6552BE31-9376-4D30-9139-4ACE900023F0@mi crosoft.com...[color=blue]
> 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.Execu teNonQuery();
>
> co.Close();
>
> HTH
>
> Regards,
> das
>
> "nbohana" wrote:
>[color=green]
>> 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
>> {
>> sqlInsertComman d1.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 =
>> sqlInsertComman d1.Parameters.A dd("@Param1",Sq lDbType.Int);
>> param.Value = txtServiceCode. Text;
>>
>> param = sqlInsertComman d1.Parameters.A dd("@Param2",Sq lDbType.VarChar );
>> param.Value = txtServiceDesc. Text;
>> param = sqlInsertComman d1.Parameters.A dd("@Param3",Sq lDbType.Money);
>> param.Value = Convert.ToDecim al(cbLargeAnima lCost.Text);
>> param = sqlInsertComman d1.Parameters.A dd("@Param4",Sq lDbType.Money);
>> param.Value = Convert.ToDecim al(cbMediumAnim alCost.Text);
>> param = sqlInsertComman d1.Parameters.A dd("@Param5",Sq lDbType.Money);
>> param.Value = Convert.ToDecim al(cbSmallAnima lCost.Text);
>>
>> sqlInsertComman d1.Connection = sqlConnection1;
>> if (sqlConnection1 .State != ConnectionState .Open)
>> sqlConnection1. Open();
>> sqlInsertComman d1.ExecuteNonQu ery();
>>
>> this.sqlConnect ion1.Close();
>>
>> MessageBox.Show ("Insert Complete. [services] " + txtServiceNbr.T ext +
>> " Successful.","" );
>>
>> }
>>
>> --
>> Norm Bohana[/color][/color]