I got an error, sql exception unhandled by code:Incorrect syntax near "="

Posted by maninaanee-8287 under ASP.NET on 12/9/2011 | Points: 10 | Views : 1456 | Status : [Member] | Replies : 23
Hi, I got an error in ddl_Country_SelectedIndexChanged
protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e)
{

ddl_City.Items.Clear();

ddl_City.Items.Add(new ListItem("--Select City--", ""));

ddl_City.AppendDataBoundItems = true;

String strConnString = ConfigurationManager

.ConnectionStrings["ConnectionString"].ConnectionString;

String strQuery = "select ID,CityName from City" +

"where CountryID=@CountryID";

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.Parameters.AddWithValue("@CountryID",ddl_Country.SelectedItem.Value);

cmd.CommandType = CommandType.Text;

cmd.CommandText = strQuery;

cmd.Connection = con;

try
{

con.Open();

ddl_City.DataSource = cmd.ExecuteReader();

ddl_City.DataTextField = "CityName";

ddl_City.DataValueField = "ID";

ddl_City.DataBind();

if (ddl_City.Items.Count > 1)
{

ddl_City.Enabled = true;

}

else
{

ddl_City.Enabled = false;

}



}

catch (Exception ex)
{

throw ex;

}

finally
{

con.Close();

con.Dispose();

}
}




Responses

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Please change your below line of code
cmd.Parameters.AddWithValue("@CountryID",ddl_Country.SelectedItem.Value); 


as
if(ddl_Country.SelectedItem.Value != "" && ddl_Country.SelectedItem.Value != string.Empty)
{
cmd.Parameters.AddWithValue("@CountryID",ddl_Country.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@CountryID",DBNull.Value);
}

because whenever ddl_Country.SelectedItem value will be null or empty there will be systax error of "=".


Regards,
Sunil

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Varung on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
repalce this code

String strQuery = "select ID,CityName from City" +

"where CountryID='@CountryID'";

G.Varun

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi varun,
The same exception is repeated.
Thanks&Regards,
padma

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Try this one
protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e)
{

ddl_City.Items.Clear();

ddl_City.Items.Add(new ListItem("--Select City--", ""));

ddl_City.AppendDataBoundItems = true;

String strConnString = ConfigurationManager

.ConnectionStrings["ConnectionString"].ConnectionString;

String strQuery = "select ID,CityName from City" +

"where CountryID=@CountryID";

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.Parameters.AddWithValue("@CountryID",ddl_Country.SelectedValue);

cmd.CommandType = CommandType.Text;

cmd.CommandText = strQuery;

cmd.Connection = con;

try
{

con.Open();

ddl_City.DataSource = cmd.ExecuteReader();

ddl_City.DataTextField = "CityName";

ddl_City.DataValueField = "ID";

ddl_City.DataBind();

if (ddl_City.Items.Count > 1)
{

ddl_City.Enabled = true;

}

else
{

ddl_City.Enabled = false;

}



}

catch (Exception ex)
{

throw ex;

}

finally
{

con.Close();

con.Dispose();

}
}

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
The same exception is repeated.
Thanks&Regards,
padma

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Varung on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
i hope the code of himanshu works like charm.....
if not reply, we have to fix it as early as possible

G.Varun

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Sorry, it's not working.

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Varung on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
cmd.Parameters.Add("@CountryID", SqlDbType.VarChar).Value = ddl_Country.SelectedValue.ToString();

once try this

replace this at

cmd.Parameters.AddWithValue("@CountryID",ddl_Country.SelectedValue);

G.Varun

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
do one thing......set an country as default i.e.
ddl_City.Items.Add(new ListItem("--Select City--", "1"));

where 1 stands for any country_id.....

hope this will resolve ur problem

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,
Not working.

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi write this code at time of binding ddl_Country

ddl_Country.Items.Add(new ListItem("--Select Country--", "1"));

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Himanshu,
It's not working.

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Please my code i have mentioned at top post.

Regards,
Sunil

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi sunil,
I am also doing like this.
Thanks& Regards,
padma

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi, have you handled Postbacks?
Is your country selection remain same after postbacks?

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
My source code:
<div>
<table>
<tr><td><asp:DropDownList ID="ddl_Continent" runat="server" AutoPostBack="true"
onselectedindexchanged="ddl_Continent_SelectedIndexChanged"><asp:ListItem Text="--Select Continent--" Value=""></asp:ListItem></asp:DropDownList></td></tr>\
<tr><td></td></tr>
<tr><td><asp:DropDownList ID="ddl_Country" Enabled="false" runat="server"
AutoPostBack="true" onselectedindexchanged="ddl_Country_SelectedIndexChanged"><asp:ListItem Text="--Select Country--" Value=""></asp:ListItem></asp:DropDownList></td></tr>
<tr><td></td></tr>
<tr><td><asp:DropDownList ID="ddl_City" Enabled="false" runat="server"
AutoPostBack="true" onselectedindexchanged="ddl_City_SelectedIndexChanged"><asp:ListItem Text="--Select City--" Value=""></asp:ListItem></asp:DropDownList></td></tr>
<tr><td></td></tr>
</table>
<asp:Label ID="lblMessage" runat="server"></asp:Label>

</div>
---------------------------
.aspx.cs code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddl_Continent.AppendDataBoundItems = true;
string strcon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strSelect = "select ID,ContinentName from Continent";
SqlConnection cn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSelect;
cmd.Connection = cn;
try
{
cn.Open();
ddl_Continent.DataSource = cmd.ExecuteReader();
ddl_Continent.DataTextField = "ContinentName";
ddl_Continent.DataValueField = "ID";
ddl_Continent.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cn.Close();
cn.Dispose();
}


}

}
protected void ddl_Continent_SelectedIndexChanged(object sender, EventArgs e)
{
ddl_Country.Items.Clear();
ddl_Country.Items.Add(new ListItem("--Select Country--:", ""));
ddl_City.Items.Clear();
ddl_City.Items.Add(new ListItem("--Select City--:", ""));
ddl_Country.AppendDataBoundItems = true;
string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strQuery = "select ID,ContinentID,CountryName from Country" + " where ContinentID=@ContinentID";
SqlConnection con = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("ContinentID", ddl_Continent.SelectedItem.Value);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
ddl_Country.DataSource=cmd.ExecuteReader();
ddl_Country.DataTextField="CountryName";
ddl_Country.DataValueField="ID";
ddl_Country.DataBind();
if(ddl_Country.Items.Count>1)
{
ddl_Country.Enabled=true;
}
else
{
ddl_Country.Enabled=false;
ddl_City.Enabled=false;
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}



protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e)
{



ddl_City.Items.Clear();
ddl_City.Items.Add(new ListItem("--Select city--", "1"));
string strcon1 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
ddl_City.AppendDataBoundItems = true;
string strQry = "select ID,CityName from City" + "where CountryID=@CountryID";
SqlConnection con1 = new SqlConnection(strcon1);
SqlCommand cmd = new SqlCommand();
//cmd.Parameters.Add("@CountryID", SqlDbType.VarChar).Value = ddl_Country.SelectedValue.ToString();

cmd.Parameters.AddWithValue("@CountryID", ddl_Country.SelectedValue);
//if (ddl_Country.SelectedItem.Value != "" && ddl_Country.SelectedItem.Value != string.Empty)
//{
// cmd.Parameters.AddWithValue("@CountryID", ddl_Country.SelectedItem.Value);
//}
//else
//{
// cmd.Parameters.AddWithValue("@CountryID", DBNull.Value);
//}
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = con1;
try
{
con1.Open();
ddl_City.DataSource = cmd.ExecuteReader();
ddl_City.DataTextField = "CityName";
ddl_City.DataValueField = "ID";
//ddl_Country.Items.Add(new ListItem("--Select Country--", "1"));
ddl_City.DataBind();
if (ddl_City.Items.Count > 1)
{
ddl_City.Enabled = true;
}
else
{
ddl_City.Enabled = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con1.Close();
con1.Dispose();
}


}
protected void ddl_City_SelectedIndexChanged(object sender, EventArgs e)
{
lblMessage.Text = "You Selected" + ddl_Continent.SelectedItem.Text + "---->" + ddl_Country.SelectedItem.Text + "---->" + ddl_City.SelectedItem.Text;

}
}
---------------------------------------------------------------

ddl_Continent_SelectedIndexChanged --it's working 5ne.

only problem in ddl_Country_SelectedIndexChanged event.
once check my code.


maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Try this
My source code: 

<div>
<table>
<tr><td><asp:DropDownList ID="ddl_Continent" runat="server" AutoPostBack="true"
onselectedindexchanged="ddl_Continent_SelectedIndexChanged"><asp:ListItem Text="--Select Continent--" Value=""></asp:ListItem></asp:DropDownList></td></tr>\
<tr><td></td></tr>
<tr><td><asp:DropDownList ID="ddl_Country" Enabled="false" runat="server"
AutoPostBack="true" onselectedindexchanged="ddl_Country_SelectedIndexChanged"><asp:ListItem Text="--Select Country--" Value=""></asp:ListItem></asp:DropDownList></td></tr>
<tr><td></td></tr>
<tr><td><asp:DropDownList ID="ddl_City" Enabled="false" runat="server"
AutoPostBack="true" onselectedindexchanged="ddl_City_SelectedIndexChanged"><asp:ListItem Text="--Select City--" Value=""></asp:ListItem></asp:DropDownList></td></tr>
<tr><td></td></tr>
</table>
<asp:Label ID="lblMessage" runat="server"></asp:Label>

</div>
---------------------------
.aspx.cs code
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddl_Continent.AppendDataBoundItems = true;
string strcon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strSelect = "select ID,ContinentName from Continent";
SqlConnection cn = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSelect;
cmd.Connection = cn;
try
{
cn.Open();
ddl_Continent.DataSource = cmd.ExecuteReader();
ddl_Continent.DataTextField = "ContinentName";
ddl_Continent.DataValueField = "ID";
ddl_Continent.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cn.Close();
cn.Dispose();
}


}

}
protected void ddl_Continent_SelectedIndexChanged(object sender, EventArgs e)
{
ddl_Country.Items.Clear();
ddl_Country.Items.Add(new ListItem("--Select Country--:", "1"));
ddl_City.Items.Clear();
ddl_City.Items.Add(new ListItem("--Select City--:", "1"));
ddl_Country.AppendDataBoundItems = true;
string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strQuery = "select ID,ContinentID,CountryName from Country" + " where ContinentID=@ContinentID";
SqlConnection con = new SqlConnection(strconn);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("ContinentID", ddl_Continent.SelectedItem.Value);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
ddl_Country.DataSource=cmd.ExecuteReader();
ddl_Country.DataTextField="CountryName";
ddl_Country.DataValueField="ID";
ddl_Country.DataBind();
if(ddl_Country.Items.Count>1)
{
ddl_Country.Enabled=true;
}
else
{
ddl_Country.Enabled=false;
ddl_City.Enabled=false;
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}



protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e)
{



ddl_City.Items.Clear();
ddl_City.Items.Add(new ListItem("--Select city--", "1"));
string strcon1 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
ddl_City.AppendDataBoundItems = true;
string strQry = "select ID,CityName from City" + "where CountryID=@CountryID";
SqlConnection con1 = new SqlConnection(strcon1);
SqlCommand cmd = new SqlCommand();
//cmd.Parameters.Add("@CountryID", SqlDbType.VarChar).Value = ddl_Country.SelectedValue.ToString();

cmd.Parameters.AddWithValue("@CountryID", ddl_Country.SelectedValue);
//if (ddl_Country.SelectedItem.Value != "" && ddl_Country.SelectedItem.Value != string.Empty)
//{
// cmd.Parameters.AddWithValue("@CountryID", ddl_Country.SelectedItem.Value);
//}
//else
//{
// cmd.Parameters.AddWithValue("@CountryID", DBNull.Value);
//}
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = con1;
try
{
con1.Open();
ddl_City.DataSource = cmd.ExecuteReader();
ddl_City.DataTextField = "CityName";
ddl_City.DataValueField = "ID";
//ddl_Country.Items.Add(new ListItem("--Select Country--", "1"));
ddl_City.DataBind();
if (ddl_City.Items.Count > 1)
{
ddl_City.Enabled = true;
}
else
{
ddl_City.Enabled = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con1.Close();
con1.Dispose();
}


}
protected void ddl_City_SelectedIndexChanged(object sender, EventArgs e)
{
lblMessage.Text = "You Selected" + ddl_Continent.SelectedItem.Text + "---->" + ddl_Country.SelectedItem.Text + "---->" + ddl_City.SelectedItem.Text;

}
}
---------------------------------------------------------------


Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
I am also doing like that.
it's not working.
Really wht changes u made in that.
thanks,padma


maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/9/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi padma paste this code as it is
protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e) 

{



ddl_City.Items.Clear();
ddl_City.Items.Add(new ListItem("--Select city--", "1"));
string strcon1 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
ddl_City.AppendDataBoundItems = true;
string strQry = "select ID,CityName from City" + "where CountryID=@CountryID";
SqlConnection con1 = new SqlConnection(strcon1);
SqlCommand cmd = new SqlCommand();

if (ddl_Country.SelectedItem.Value != "" && ddl_Country.SelectedItem.Value != string.Empty)
{
cmd.Parameters.AddWithValue("@CountryID", ddl_Country.SelectedItem.Value);
}
else
{
cmd.Parameters.AddWithValue("@CountryID", "1");
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQry;
cmd.Connection = con1;
try
{
con1.Open();
ddl_City.DataSource = cmd.ExecuteReader();
ddl_City.DataTextField = "CityName";
ddl_City.DataValueField = "ID";
ddl_Country.Items.Add(new ListItem("--Select Country--", "1"));
ddl_City.DataBind();
if (ddl_City.Items.Count > 1)
{
ddl_City.Enabled = true;
}
else
{
ddl_City.Enabled = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con1.Close();
con1.Dispose();
}



Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Krishna17117 on: 12/10/2011 [Member] Starter | Points: 25

Up
0
Down
simply change this to
"where CountryID=@CountryID"

This

where CountryID==@CountryID

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 12/10/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi padma do this thing also

leave space between two string

string strQry = "select ID,CityName from City " + "where CountryID=@CountryID"; 


Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kamlesh420 on: 12/10/2011 [Member] Starter | Points: 25

Up
0
Down
use following condition on page load event i think ddl had load on every post back

page load event
if( page.ispostback=false)

{
//all your code
}



kamlesh kuamr saini
Sr. devloper (asp.net)
new delhi

maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: maninaanee-8287 on: 12/12/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Himanshu,
It's not working.Same error is repeated.
Thanks&Regards,
padma.


maninaanee-8287, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response