Recently I've been working on my school assignment and I've stumbled with a problem. The scenario is like this, I'm trying to join 2 SQL tables in c# to display result using a datalist which works as a comment box.
Both StuId is set to INT data type. However when I execute the codes, it gives me an error of conversion failed when converting the (username which is StuNo) varchar value to data type int. Below is the code
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace MobloB1
{
public partial class Profile : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
String connectionString = WebConfigurationManager.ConnectionStrings["Test"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
string selectSQL = "SELECT b.*,a.StuName FROM [Comment] b, [Student] a WHERE b.StuId = " + Session["StuId"] + " AND b.CommName = a.StuId";
SqlConnection con = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, con);
try
{
con.Open();
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
for (int i = 0; i < DataList1.Items.Count; i++)
{
Label User = (Label)DataList1.Items[i].FindControl("Label3");
User.Text = ds.Tables[0].Rows[i]["StuName"].ToString();
Label Comment = (Label)DataList1.Items[i].FindControl("Label2");
Comment.Text = ds.Tables[0].Rows[i]["CommComment"].ToString();
Label Date = (Label)DataList1.Items[i].FindControl("Label2");
Date.Text = DateTime.Parse(ds.Tables[0].Rows[i]["CommDateTime"].ToString()).ToString("yy-MM-dd hh:mm:ss tt");
}
}
catch (Exception err)
{
Label1.Text += err.Message;
}
finally
{
con.Close();
}
}
}
}
From your description, StuNo is a varchar data type. Therefore, when you try to convert it to int, you need to make sure this value in your table must be numeric and not null. If any records include a null value, you will error out.
Suggest you check if the row is null value first before you do convertion.
"Object reference not set to an instance of an object"
juzzbuzz
0 Points
6 Posts
Conversion failed when converting the varchar value to data type int.
Aug 17, 2009 03:55 PM|LINK
Hello everyone,
Recently I've been working on my school assignment and I've stumbled with a problem. The scenario is like this, I'm trying to join 2 SQL tables in c# to display result using a datalist which works as a comment box.
The 2 tables are:
Student table and Comment table
Student table fields: StuId(Pk), StuNo, StuPassword, StuEmail, StuName
Comment table fields: CommId(Pk), StuId, CommComment, CommName, CommDateTime
Both StuId is set to INT data type. However when I execute the codes, it gives me an error of conversion failed when converting the (username which is StuNo) varchar value to data type int. Below is the code
using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Web.Configuration; namespace MobloB1 { public partial class Profile : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { String connectionString = WebConfigurationManager.ConnectionStrings["Test"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionString); string selectSQL = "SELECT b.*,a.StuName FROM [Comment] b, [Student] a WHERE b.StuId = " + Session["StuId"] + " AND b.CommName = a.StuId"; SqlConnection con = new SqlConnection(connectionString); SqlDataAdapter adapter = new SqlDataAdapter(selectSQL, con); try { con.Open(); DataSet ds = new DataSet(); adapter.Fill(ds); DataList1.DataSource = ds; DataList1.DataBind(); for (int i = 0; i < DataList1.Items.Count; i++) { Label User = (Label)DataList1.Items[i].FindControl("Label3"); User.Text = ds.Tables[0].Rows[i]["StuName"].ToString(); Label Comment = (Label)DataList1.Items[i].FindControl("Label2"); Comment.Text = ds.Tables[0].Rows[i]["CommComment"].ToString(); Label Date = (Label)DataList1.Items[i].FindControl("Label2"); Date.Text = DateTime.Parse(ds.Tables[0].Rows[i]["CommDateTime"].ToString()).ToString("yy-MM-dd hh:mm:ss tt"); } } catch (Exception err) { Label1.Text += err.Message; } finally { con.Close(); } } } }asp.net SQL C# datalist tables
che3358
Star
7941 Points
1357 Posts
Re: Conversion failed when converting the varchar value to data type int.
Aug 17, 2009 06:38 PM|LINK
From your description, StuNo is a varchar data type. Therefore, when you try to convert it to int, you need to make sure this value in your table must be numeric and not null. If any records include a null value, you will error out.
Suggest you check if the row is null value first before you do convertion.
Naom
All-Star
36004 Points
7901 Posts
Re: Conversion failed when converting the varchar value to data type int.
Aug 17, 2009 06:43 PM|LINK
NEVER use string contatenation - always use parameters.
This
string selectSQL = "SELECT b.*,a.StuName FROM [Comment] b, [Student] a WHERE b.StuId = " + Session["StuId"] + " AND b.CommName = a.StuId";
should be
string selectSQL = "SELECT b.*,a.StuName FROM [Comment] b, [Student] a WHERE b.StuId = @StudID AND b.CommName = a.StuId";
Also, it's better to use JOIN syntax than WHERE syntax just for clarity and maintenance, at least.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12