Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...
.NET Forum / .NET Framework / ADO.NET / May 2005

Tip: Looking for answers? Try searching our database.

Insert using C#

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
nbohana - 20 May 2005 20:42 GMT
When I try to insert the second record I get the message "The Variable name
"@Parm1" has already been declared. What can I do to prevent this.  The code:

try
{
  sqlInsertCommand1.CommandText = "INSERT INTO services([service-code],
[service-description]" +
"  ,[large-animal-cost], [medium-animal-cost], [small-animal-cost])" +
"  values(@Parm1,@Parm2,@Parm3, @Parm4,@Parm5)";

sqlInsertCommand1.Parameters.Add("@Parm1",SqlDbType.Int).Value =
txtServiceCode.Text;
sqlInsertCommand1.Parameters.Add("@Parm2",SqlDbType.VarChar).Value =
txtServiceDesc.Text;
sqlInsertCommand1.Parameters.Add("@Parm3",SqlDbType.Money).Value =
Convert.ToDecimal(cbLargeAnimalCost.Text);
sqlInsertCommand1.Parameters.Add("@Parm4",SqlDbType.Money).Value =
Convert.ToDecimal(cbMediumAnimalCost.Text);
sqlInsertCommand1.Parameters.Add("@Parm5",SqlDbType.Money).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.","");


Signature

Norm Bohana

Reply to this Message
Miha Markic [MVP C#] - 20 May 2005 21:26 GMT
Hi,

I guess you are trying to add all those parameters again and that's wrong.
You have to reuse them if you are reusing SqlCommand instance.
So, instead of adding you should do:
sqlInsertCommand1.Parameters.["@Parm1"].Value = ..

Signature

Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

> When I try to insert the second record I get the message "The Variable
> name
[quoted text clipped - 28 lines]
> MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
> " Successful.","");
Reply to this Message
nbohana - 22 May 2005 03:12 GMT
Miha, I made the change that you suggested and I could get it to compile.  
The message was "identifier required".  I tried several ways with '(' , '(['
and it did not work.

> Hi,
>
[quoted text clipped - 35 lines]
> > MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
> > " Successful.","");
Reply to this Message
Andrew Robinson - 23 May 2005 17:07 GMT
sqlInsertCommand1.Parameters.["@Parm1"].Value = ..

should read

sqlInsertCommand1.Parameters["@Parm1"].Value = ..

remove the period after Parameters

-Andrew

> Hi,
>
[quoted text clipped - 35 lines]
> > MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
> > " Successful.","");
Reply to this Message
Miha Markic [MVP C#] - 23 May 2005 19:45 GMT
Yes, I was just testing if anybody will spot the error ;-)

Signature

Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

> sqlInsertCommand1.Parameters.["@Parm1"].Value = ..
>
[quoted text clipped - 46 lines]
>> > MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
>> > " Successful.","");
Reply to this Message
nbohana - 23 May 2005 23:11 GMT
This compiles but does not run?? The message that I receive:
"System.IndexOutOfRangeException:An SqlParameter with ParameterName "@Param1"
is not contained by this SqlParameterCollection". I also cannot convert
'@param1 to SqlDbType.Int'.??

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

sqlInsertCommand1.Parameters["@Param1"].Value = txtServiceCode.Text;
sqlInsertCommand1.Parameters["@Param2"].Value = txtServiceDesc.Text;
sqlInsertCommand1.Parameters["@Param3"].Value =
Convert.ToDecimal(cbLargeAnimalCost.Text);
sqlInsertCommand1.Parameters["@Param4"].Value =
Convert.ToDecimal(cbMediumAnimalCost.Text);
sqlInsertCommand1.Parameters["@Param5"].Value =
Convert.ToDecimal(cbSmallAnimalCost.Text);

> Yes, I was just testing if anybody will spot the error ;-)
>
[quoted text clipped - 48 lines]
> >> > MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
> >> > " Successful.","");
Reply to this Message
Miha Markic [MVP C#] - 24 May 2005 08:44 GMT
Look,

You have to create a SqlCommand instance and then *add* all those
parameters.
When/If you want to reuse the same instance, you shouldn't add parameters
again but you have to use them as we told you.
Now, you don't have any code that adds parameters...
Signature

Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

> This compiles but does not run?? The message that I receive:
> "System.IndexOutOfRangeException:An SqlParameter with ParameterName
[quoted text clipped - 72 lines]
>> >> > +
>> >> > " Successful.","");
Reply to this Message
nbohana - 25 May 2005 17:31 GMT
Miha, I must be dumb! I coded the insert as per your samples. I don't
understand what you mean by adding the parameters again?  This is what I have:

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.","");

> Look,
>
[quoted text clipped - 79 lines]
> >> >> > +
> >> >> > " Successful.","");
Reply to this Message
nbohana - 23 May 2005 23:10 GMT
This compiles but does not run?? The message that I receive:
"System.IndexOutOfRangeException:An SqlParameter with ParameterName "@Param1"
is not contained by this SqlParameterCollection". I also cannot convert
'@param1 to SqlDbType.Int'.??

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

sqlInsertCommand1.Parameters["@Param1"].Value = txtServiceCode.Text;
sqlInsertCommand1.Parameters["@Param2"].Value = txtServiceDesc.Text;
sqlInsertCommand1.Parameters["@Param3"].Value =
Convert.ToDecimal(cbLargeAnimalCost.Text);
sqlInsertCommand1.Parameters["@Param4"].Value =
Convert.ToDecimal(cbMediumAnimalCost.Text);
sqlInsertCommand1.Parameters["@Param5"].Value =
Convert.ToDecimal(cbSmallAnimalCost.Text);

> sqlInsertCommand1.Parameters.["@Parm1"].Value = ..
>
[quoted text clipped - 45 lines]
> > > MessageBox.Show("Insert Complete. [services]  " + txtServiceNbr.Text +
> > > " Successful.","");
Reply to this Message
Steve Goodyear - 21 May 2005 04:49 GMT
It's calling Add twice using the property right off the method. Try creating
a Parameter object and then assigning the value:
SqlParameter param = sqlInsertCommand1.Parameters.Add("@Parm1",
SqlDbType.Int);
param.Value = txtServiceCode.Text;
param = sqlInsertCommand1.Parameters.Add("@Parm2",SqlDbType.VarChar);
param.Value = txtServiceDesc.Text;
... etc.

Cheers,
Steve Goodyear
Reply to this Message
nbohana - 22 May 2005 03:06 GMT
Steve, I hope that I understand, here is what I did:
sqlInsertCommand1.CommandText = "INSERT INTO [service-task]
([service-order-task], [service-date], [animal-id] ,[owner-id],
[animal-size-ind], [service-nmr], [service-code], [service-description]
,cost) VALUES (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7,
@Param8 ,@cost)";

SqlParameter Param = sqlInsertCommand1.Parameters.Add("@Param1",
SqlDbType.Int);
Param.Value = txtServiceOrderTask.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param2", SqlDbType.DateTime);
Param.Value = txtServiceDate.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param3", SqlDbType.Int);
Param.Value = txtAnimalId.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param4", SqlDbType.Int);
Param.Value = txtOwnerId.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param5", SqlDbType.VarChar);
Param.Value = cbAnimalSize.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param6", SqlDbType.Int);
Param.Value = txtServiceNumber.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param7", SqlDbType.Int);
Param.Value = txtServiceCode.Text;
Param = sqlInsertCommand1.Parameters.Add("@Param8", SqlDbType.VarChar);
Param.Value = txtServiceDesc.Text;
Param = sqlInsertCommand1.Parameters.Add("@cost", SqlDbType.Money);
Param.Value = Convert.ToDecimal(txtServiceCost.Text);

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

I got the error message "the variable @Param1 has already been declaired.

> It's calling Add twice using the property right off the method. Try creating
> a Parameter object and then assigning the value:
[quoted text clipped - 7 lines]
> Cheers,
> Steve Goodyear
Reply to this Message
Steve Goodyear - 24 May 2005 00:08 GMT
Hi nobohana,

Try narrowing your code to ensure the problem is not coming from another
area. Here's a simplier, yet more complete example for parameter usage which
may help:

private void button1_Click(object sender, System.EventArgs e){
 SqlConnection db = new SqlConnection("Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI;");
 SqlCommand insertCommand = new SqlCommand();
 insertCommand.CommandText = "INSERT INTO [Region] ([RegionID],
[RegionDescription]) VALUES (@Param1, @Param2)";
 insertCommand.Connection = db;
 SqlParameter param = insertCommand.Parameters.Add("@Param1", SqlDbType.Int);
 param.Value = int.Parse(textBox1.Text);
 param = insertCommand.Parameters.Add("@Param2", SqlDbType.NVarChar, 50);
 param.Value = textBox2.Text;
 db.Open();
 insertCommand.ExecuteNonQuery();
 db.Close();
}

Cheers,
Steve Goodyear
Reply to this Message

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.