By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,190 Members | 1,555 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,190 IT Pros & Developers. It's quick & easy.

Error ...

P: n/a
Hi,
I'm trying to execute stored procedure and I collect its parameters from
WebForm. There is an error: Error converting data type varchar to int.

What could be the problem? Here it is the code:

private void Button2_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=----;Initial
Catalog=---; User ID=---; Password=----");
SqlCommand cmd = new SqlCommand("prSVOFFRDATA", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CITY", SqlDbType.VarChar,20));
cmd.Parameters["@CITY"].Value = DDL4.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@DISTR", SqlDbType.VarChar,20));
cmd.Parameters["@DISTR"].Value = DistrTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PHONE", SqlDbType.Char,1));
if(PhoneCB.Checked)
{
cmd.Parameters["@PHONE"].Value = "Y";
}
else
{
cmd.Parameters["@PHONE"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@FURN", SqlDbType.Char,1));
if(FurnCB.Checked)
{
cmd.Parameters["@FURN"].Value = "Y";
}
else
{
cmd.Parameters["@FURN"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@HTR", SqlDbType.Char,1));
if(HtrCB.Checked)
{
cmd.Parameters["@HTR"].Value = "Y";
}
else
{
cmd.Parameters["@HTR"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@ESTTYPEID", SqlDbType.TinyInt));
cmd.Parameters["@ESTTYPEID"].Value = DDL2.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = DDL1.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value = DDL3.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@USRNAM", SqlDbType.VarChar,20));
cmd.Parameters["@USRNAM"].Value = Session["usrName"].ToString();
cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = PriceTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = RoomsTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = SQMetersTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = FloorsTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PIC", SqlDbType.VarChar,55));
cmd.Parameters["@PIC"].Value = PicTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@DESCR", SqlDbType.VarChar,255));
cmd.Parameters["@DESCR"].Value = DescrTB.Text.Trim();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 a.
Nov 16 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Your problem is with the following lines of code:

cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = PriceTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = RoomsTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = SQMetersTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = FloorsTB.Text.Trim();

You are creating 4 SQL parameters of type SqlDbType.Int, but then you are trying to assign a string to their Value property. I don't know what you expect to find in the text boxes PriceTB, RoomsTB, SQMetersTB, and FloorsTB. If they do contain numbers, you still need to convert those numbers to integers, because the text boxes' Text property returns a string. These cannot be implicitly converted to an integer (the parameters' data type), and that's causing your error. Try this:

cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = Int32.Parse(PriceTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = Int32.Parse(SQMetersTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = Int32.Parse(FloorsTB.Text.Trim());

Unless you are sure that the text boxes contain numbers (maybe you already validated the controls' input prior to reaching this part of your code) you should include this code in a try-catch block as the Int32.Parse() method might throw an exception (ArgumentNullException, FormatException, or OverflowException). By the way, SqlDbType.Money or SqlDbType.SmallMoney might be more approprite for the parameter @PRICE.

Hope this helps.

--
Kai Brinkmann [MSFT]

Please do not send e-mail directly to this alias. This alias is for newsgroup purposes only.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Viktor Popov" <vi****@yahoo.com> wrote in message news:es*************@TK2MSFTNGP10.phx.gbl...
Hi,
I'm trying to execute stored procedure and I collect its parameters from
WebForm. There is an error: Error converting data type varchar to int.



What could be the problem? Here it is the code:

private void Button2_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=----;Initial
Catalog=---; User ID=---; Password=----");
SqlCommand cmd = new SqlCommand("prSVOFFRDATA", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CITY", SqlDbType.VarChar,20));
cmd.Parameters["@CITY"].Value = DDL4.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@DISTR", SqlDbType.VarChar,20));
cmd.Parameters["@DISTR"].Value = DistrTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PHONE", SqlDbType.Char,1));
if(PhoneCB.Checked)
{
cmd.Parameters["@PHONE"].Value = "Y";
}
else
{
cmd.Parameters["@PHONE"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@FURN", SqlDbType.Char,1));
if(FurnCB.Checked)
{
cmd.Parameters["@FURN"].Value = "Y";
}
else
{
cmd.Parameters["@FURN"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@HTR", SqlDbType.Char,1));
if(HtrCB.Checked)
{
cmd.Parameters["@HTR"].Value = "Y";
}
else
{
cmd.Parameters["@HTR"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@ESTTYPEID", SqlDbType.TinyInt));
cmd.Parameters["@ESTTYPEID"].Value = DDL2.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = DDL1.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value = DDL3.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@USRNAM", SqlDbType.VarChar,20));
cmd.Parameters["@USRNAM"].Value = Session["usrName"].ToString();
cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = PriceTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = RoomsTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = SQMetersTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = FloorsTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PIC", SqlDbType.VarChar,55));
cmd.Parameters["@PIC"].Value = PicTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@DESCR", SqlDbType.VarChar,255));
cmd.Parameters["@DESCR"].Value = DescrTB.Text.Trim();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
conn.Close();
}


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 a.

Nov 16 '05 #2

P: n/a
Thanks for the reply!
The code is like this now, but the problem is the same:

private void Button2_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=BLEK;Initial
Catalog=Estate; User ID=blek; Password=banderas");
SqlCommand cmd = new SqlCommand("prSVOFFRDATA", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CITY", SqlDbType.VarChar,20));
cmd.Parameters["@CITY"].Value = DDL4.SelectedItem.Text;
cmd.Parameters.Add(new SqlParameter("@DISTR", SqlDbType.VarChar,20));
cmd.Parameters["@DISTR"].Value = DistrTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PHONE", SqlDbType.Char,1));
if(PhoneCB.Checked)
{
cmd.Parameters["@PHONE"].Value = "Y";
}
else
{
cmd.Parameters["@PHONE"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@FURN", SqlDbType.Char,1));
if(FurnCB.Checked)
{
cmd.Parameters["@FURN"].Value = "Y";
}
else
{
cmd.Parameters["@FURN"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@HTR", SqlDbType.Char,1));
if(HtrCB.Checked)
{
cmd.Parameters["@HTR"].Value = "Y";
}
else
{
cmd.Parameters["@HTR"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@ESTTYPEID", SqlDbType.TinyInt));
cmd.Parameters["@ESTTYPEID"].Value = DDL2.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = DDL1.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value = DDL3.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@USRNAM", SqlDbType.VarChar,20));
cmd.Parameters["@USRNAM"].Value = Session["usrName"].ToString();
cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = Int32.Parse(PriceTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = Int32.Parse(SQMetersTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = Int32.Parse(FloorsTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@PIC", SqlDbType.VarChar,55));
cmd.Parameters["@PIC"].Value = PicTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@DESCR", SqlDbType.VarChar,255));
cmd.Parameters["@DESCR"].Value = DescrTB.Text.Trim();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
conn.Close();

}
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 .
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 .
Nov 16 '05 #3

P: n/a
You also have some parameters of type SqlDbType.TinyInt (didn't notice that before). You are trying to assign DDL.SelectedItem.Value to these parameters, but this property also returns a string. Since TinyInt is a byte you once again need to convert to the correct data type. Use:

cmd.Parameters.Add(new SqlParameter("@ESTTYPEID", SqlDbType.TinyInt));
cmd.Parameters["@ESTTYPEID"].Value = Byte.Parse(DDL2.SelectedItem.Value);
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = Byte.Parse(DDL1.SelectedItem.Value);
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value = Byte.Parse(DDL3.SelectedItem.Value);

Make sure you scan your code for any additional data type mismatches.

--
Kai Brinkmann [MSFT]

Please do not send e-mail directly to this alias. This alias is for newsgroup purposes only.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Viktor Popov" <vi****@yahoo.com> wrote in message news:eg**************@TK2MSFTNGP09.phx.gbl...
Thanks for the reply!
The code is like this now, but the problem is the same:

private void Button2_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=BLEK;Initial
Catalog=Estate; User ID=blek; Password=banderas");
SqlCommand cmd = new SqlCommand("prSVOFFRDATA", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CITY", SqlDbType.VarChar,20));
cmd.Parameters["@CITY"].Value = DDL4.SelectedItem.Text;
cmd.Parameters.Add(new SqlParameter("@DISTR", SqlDbType.VarChar,20));
cmd.Parameters["@DISTR"].Value = DistrTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@PHONE", SqlDbType.Char,1));
if(PhoneCB.Checked)
{
cmd.Parameters["@PHONE"].Value = "Y";
}
else
{
cmd.Parameters["@PHONE"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@FURN", SqlDbType.Char,1));
if(FurnCB.Checked)
{
cmd.Parameters["@FURN"].Value = "Y";
}
else
{
cmd.Parameters["@FURN"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@HTR", SqlDbType.Char,1));
if(HtrCB.Checked)
{
cmd.Parameters["@HTR"].Value = "Y";
}
else
{
cmd.Parameters["@HTR"].Value = "N";
}
cmd.Parameters.Add(new SqlParameter("@ESTTYPEID", SqlDbType.TinyInt));
cmd.Parameters["@ESTTYPEID"].Value = DDL2.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = DDL1.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value = DDL3.SelectedItem.Value;
cmd.Parameters.Add(new SqlParameter("@USRNAM", SqlDbType.VarChar,20));
cmd.Parameters["@USRNAM"].Value = Session["usrName"].ToString();
cmd.Parameters.Add(new SqlParameter("@PRICE", SqlDbType.Int));
cmd.Parameters["@PRICE"].Value = Int32.Parse(PriceTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@SqMeters", SqlDbType.Int));
cmd.Parameters["@SqMeters"].Value = Int32.Parse(SQMetersTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@Floors", SqlDbType.Int));
cmd.Parameters["@Floors"].Value = Int32.Parse(FloorsTB.Text.Trim());
cmd.Parameters.Add(new SqlParameter("@PIC", SqlDbType.VarChar,55));
cmd.Parameters["@PIC"].Value = PicTB.Text.Trim();
cmd.Parameters.Add(new SqlParameter("@DESCR", SqlDbType.VarChar,255));
cmd.Parameters["@DESCR"].Value = DescrTB.Text.Trim();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
conn.Close();

}


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 .


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 .

Nov 16 '05 #4

P: n/a
I've done it!
Thanks a lot!
I couldn't do that without you!

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.729 / Virus Database: 484 - Release Date: 27.7.2004 .
Nov 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.