Error ...

V

Viktor Popov

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();
}
 
K

Kai Brinkmann [MSFT]

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 said:
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();
}
Click to expand...
 
V

Viktor Popov

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();

}
 
K

Kai Brinkmann [MSFT]

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 said:
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();

}
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Error 16
Command or Adapter weird error 2
System.ApplicationError in Data access class 6
sql parameter 5
Caching Parameters Performance Issue When Using SQLHELPER 2
Stored procedure parameter output value 4
Datalist not displaying records from stored procedure 2
VS 2003 to VS 2005 problems 5

Top