Code 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? Thanks! 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();
}
 
R

Reva Baum

What datatype do you use in your database for the checkbox values like
phone, furn and htr?


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? Thanks! 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

Hi,
Thanks for the reply!
I use :
EstPhone CHAR(1) NOT NULL CHECK(EstPhone IN('Y','N')),
EstFurn CHAR(1) NOT NULL CHECK(EstFurn IN('Y','N')),
EstHeating CHAR(1) NOT NULL CHECK(EstHeating IN('Y','N')),
 
R

Reva Baum

Is there a reason that your dropdownlist value is being returned as a tiny
int? Isn't a dropdownlist value a string/varchar?

your code:

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



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? Thanks! 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...
 
D

Dharmesh

I see at places you have used the parameter's datatype SqlDbType.Int. But
when you are putting the value you say

cmd.Parameter["ParamName"].Value = txtSomething.Text.Trim();

instead try
Convert.ToInt32(txtSomething.Text.Trim());

Thanks.

Dharmesh
 
V

Viktor Popov

Hi again,

I use tinyint because I input these values as a tinyint in the tables.
I have table:
Est_Type
================
EstTypeId EstType
1 house
2 apartment
3 farm
...............
So I'd like to take from the DropDownList the Value which is EstTypeID Not
the Text and then input it in the table using the stored procedure..


Reva Baum said:
Is there a reason that your dropdownlist value is being returned as a tiny
int? Isn't a dropdownlist value a string/varchar?

your code:

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



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? Thanks! 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...
Click to expand...
 
V

Viktor Popov

I have corrected that but the error is the same
Thanks
Dharmesh said:
I see at places you have used the parameter's datatype SqlDbType.Int. But
when you are putting the value you say

cmd.Parameter["ParamName"].Value = txtSomething.Text.Trim();

instead try
Convert.ToInt32(txtSomething.Text.Trim());

Thanks.

Dharmesh

Viktor Popov said:
Hi,
Thanks for the reply!
I use :
EstPhone CHAR(1) NOT NULL CHECK(EstPhone IN('Y','N')),
EstFurn CHAR(1) NOT NULL CHECK(EstFurn IN('Y','N')),
EstHeating CHAR(1) NOT NULL CHECK(EstHeating IN('Y','N')),
Click to expand...
Click to expand...
 
R

Reva Baum

hi,

I think that even though there are numbers in the dropdownlist, you still
need to cast the values to an int.

something like this:

cmd.Parameters["@TYPEOFFERID"].Value = ctype(ddl1.selecteditem.value,
integer)

or you can use the index of the selected value:

cmd.Parameters["@TYPEOFFERID"].Value = ddl1.selectedindex + 1 ' +1
because it starts at 0



Viktor Popov said:
Hi again,

I use tinyint because I input these values as a tinyint in the tables.
I have table:
Est_Type
================
EstTypeId EstType
1 house
2 apartment
3 farm
..............
So I'd like to take from the DropDownList the Value which is EstTypeID Not
the Text and then input it in the table using the stored procedure..


Reva Baum said:
Is there a reason that your dropdownlist value is being returned as a tiny
int? Isn't a dropdownlist value a string/varchar?

your code:

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



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? Thanks! 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...
Click to expand...
Click to expand...
 
R

Reva Baum

Actually - I just tried it out, and you're right. You don't need to cast it
into an integer. Sorry.

Reva Baum said:
hi,

I think that even though there are numbers in the dropdownlist, you still
need to cast the values to an int.

something like this:

cmd.Parameters["@TYPEOFFERID"].Value = ctype(ddl1.selecteditem.value,
integer)

or you can use the index of the selected value:

cmd.Parameters["@TYPEOFFERID"].Value = ddl1.selectedindex + 1 ' +1
because it starts at 0



Viktor Popov said:
Hi again,

I use tinyint because I input these values as a tinyint in the tables.
I have table:
Est_Type
================
EstTypeId EstType
1 house
2 apartment
3 farm
..............
So I'd like to take from the DropDownList the Value which is EstTypeID Not
the Text and then input it in the table using the stored procedure..


Reva Baum said:
Is there a reason that your dropdownlist value is being returned as a tiny
int? Isn't a dropdownlist value a string/varchar?

your code:

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



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? Thanks! 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...
Click to expand...
Click to expand...
 
R

Reva Baum

Actually - I just tried it out, and you're right. You don't need to cast it
into an integer. Sorry.

Reva Baum said:
hi,

I think that even though there are numbers in the dropdownlist, you still
need to cast the values to an int.

something like this:

cmd.Parameters["@TYPEOFFERID"].Value = ctype(ddl1.selecteditem.value,
integer)

or you can use the index of the selected value:

cmd.Parameters["@TYPEOFFERID"].Value = ddl1.selectedindex + 1 ' +1
because it starts at 0



Viktor Popov said:
Hi again,

I use tinyint because I input these values as a tinyint in the tables.
I have table:
Est_Type
================
EstTypeId EstType
1 house
2 apartment
3 farm
..............
So I'd like to take from the DropDownList the Value which is EstTypeID Not
the Text and then input it in the table using the stored procedure..


Reva Baum said:
Is there a reason that your dropdownlist value is being returned as a tiny
int? Isn't a dropdownlist value a string/varchar?

your code:

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



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? Thanks! 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...
Click to expand...
Click to expand...
 
V

Viktor Popov

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 = Int32.Parse(DDL2.SelectedItem.Value);
cmd.Parameters.Add(new SqlParameter("@TYPEOFFERID", SqlDbType.TinyInt));
cmd.Parameters["@TYPEOFFERID"].Value = Int32.Parse(DDL1.SelectedItem.Value);
cmd.Parameters.Add(new SqlParameter("@TYPECONSTRID", SqlDbType.TinyInt));
cmd.Parameters["@TYPECONSTRID"].Value =
Int32.Parse(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();





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

}
 
D

Dharmesh

Viktor,
Check all the datatypes in the database with the datatypes of the same
columns you are using in your code. There should be a mismatch. Or there
will be a mismatch between datatypes in your database and the datatype used
in stored procedure. Just check it carefully.

Dharmesh

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? Thanks! 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...
 
R

Reva Baum

What is your exact error?
Maybe your error is in the stored procedure?
Can you copy and paste the code from the stored procedure?

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...
 
V

Viktor Popov

Yes here it is:

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'prSVOFFRDATA' AND type = 'P')
DROP PROCEDURE prSVOFFRDATA
GO

CREATE PROCEDURE prSVOFFRDATA
@CITY VARCHAR(20),
@DISTR VARCHAR(20),
@PHONE CHAR(1),
@FURN CHAR(1),
@HTR CHAR(1),
@EstTypeID TINYINT,
@TypeOfferID TINYINT,
@TypeConstrID TINYINT,
@USRNAM INT,
@Price INT,
@Rooms INT,
@SqMeters INT,
@Floors INT,
@PIC VARCHAR(55),
@DESCR VARCHAR(255)
AS
DECLARE @ADVDAT SMALLDATETIME, @IDENT INT, @USERID INT
SELECT @USERID=U.USERID FROM BLEK.USERS U WHERE U.USERNAME=@USRNAM
SELECT @ADVDAT = GETDATE()
INSERT INTO
BLEK.Estates(EstCity,EstDistr,EstPhone,EstFurn,EstHeating,AdDate,EstTypeID,T
ypeOfferID,TypeConstrID)

VALUES(@CITY,@DISTR,@PHONE,@FURN,@HTR,@ADVDAT,@EstTypeID,@TypeOfferID,@TypeC
onstrID)
SELECT @IDENT = SCOPE_IDENTITY()
INSERT INTO BLEK.Offers(EstateID,UserID,Price,Rooms,SqMeters,Floors)
VALUES(@IDENT,@USERID,@Price,@Rooms,@SqMeters,@Floors)
IF (@PIC IS NOT NULL)
INSERT INTO BLEK.Pictures(EstateID,PICEST) VALUES(@IDENT,@PIC)
IF (@DESCR IS NOT NULL)
INSERT INTO BLEK.Descriptions(EstateID,Descr) VALUES(@IDENT,@DESCR)







Reva Baum said:
What is your exact error?
Maybe your error is in the stored procedure?
Can you copy and paste the code from the stored procedure?

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...
Click to expand...
 
V

Viktor Popov

I've done it. It was an type mismatch betwenn stored procedure parameters
and theparameters from the webform.
Thank you Dharmesh & Reva Baum!
 
R

Reva Baum

i think i see it - finally!

@USRNAM INT

in your stored procedure should be @USERNAM VARCHAR(20)

because that's how it is in your code:

cmd.Parameters.Add(new SqlParameter("@USRNAM", SqlDbType.VarChar,20));


Viktor Popov said:
Yes here it is:

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'prSVOFFRDATA' AND type = 'P')
DROP PROCEDURE prSVOFFRDATA
GO

CREATE PROCEDURE prSVOFFRDATA
@CITY VARCHAR(20),
@DISTR VARCHAR(20),
@PHONE CHAR(1),
@FURN CHAR(1),
@HTR CHAR(1),
@EstTypeID TINYINT,
@TypeOfferID TINYINT,
@TypeConstrID TINYINT,
@USRNAM INT,
@Price INT,
@Rooms INT,
@SqMeters INT,
@Floors INT,
@PIC VARCHAR(55),
@DESCR VARCHAR(255)
AS
DECLARE @ADVDAT SMALLDATETIME, @IDENT INT, @USERID INT
SELECT @USERID=U.USERID FROM BLEK.USERS U WHERE U.USERNAME=@USRNAM
SELECT @ADVDAT = GETDATE()
INSERT INTO
BLEK.Estates(EstCity,EstDistr,EstPhone,EstFurn,EstHeating,AdDate,EstTypeID,T
ypeOfferID,TypeConstrID)
Click to expand...
VALUES(@CITY,@DISTR,@PHONE,@FURN,@HTR,@ADVDAT,@EstTypeID,@TypeOfferID,@TypeC
onstrID)
SELECT @IDENT = SCOPE_IDENTITY()
INSERT INTO BLEK.Offers(EstateID,UserID,Price,Rooms,SqMeters,Floors)
VALUES(@IDENT,@USERID,@Price,@Rooms,@SqMeters,@Floors)
IF (@PIC IS NOT NULL)
INSERT INTO BLEK.Pictures(EstateID,PICEST) VALUES(@IDENT,@PIC)
IF (@DESCR IS NOT NULL)
INSERT INTO BLEK.Descriptions(EstateID,Descr) VALUES(@IDENT,@DESCR)







Reva Baum said:
What is your exact error?
Maybe your error is in the stored procedure?
Can you copy and paste the code from the stored procedure?

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...
Click to expand...
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

System.ApplicationError in Data access class 4
Getting Return Value From Stored Proccedure (Part 2) 2
newbie: my code crashe, NULL value 5
return value from stored proc using MS Data Access Application Block 0
Error Message could not find a non-generic method 6
Output value stored procedure dotnet 1
Error: Object must implement IConvertible 1
Save blank fields in database (date and integer) - Error 5

Members online

Total: 28 (members: 3, guests: 25)
Robots: 69

Forum statistics

Threads
473,770
Messages
2,569,586
Members
45,088
Latest member
JeremyMedl

Latest Threads

Top