Filling Data Set Creating Problem when joining two Table

Question

0.00/5 (No votes)

See more:

I have one DropDown List I want to bind it from Two table i.e id from one table and description in another table
on Filling the data Set giving Sql Exception
The Query For binding Drop Down list is

SQL

SELECT     
CustomerGroupContacts.cusg_id,
CustomerGroupContacts.cusg_id+'___'+CustomerIDs.cusid_name as customername
FROM        
CustomerGroupContacts INNER JOIN
CustomerIDs ON CustomerGroupContacts.cusg_id = CustomerIDs.cusg_id

Posted 27-Dec-11 18:35pm

waqar ahmad (Dev)

Updated 27-Dec-11 18:38pm

thatraja

v2

Add a Solution

Comments

thatraja 28-Dec-11 0:38am

What's the Sql Exception? Always mention it in your question

waqar ahmad (Dev) 28-Dec-11 0:41am

System.Data.SqlClient.SqlException: Incorrect syntax near 'CustomerGroupContacts'.

Source Error:

Line 30: {
Line 31: da.SelectCommand.CommandType = CommandType.Text;
Line 32: da.Fill(ds, "CustomerGroupContacts");
Line 33: if (ds.Tables.Count > 0)
Line 34: {

nagendrathecoder 28-Dec-11 0:43am

What is the exception?

Zubair Alie 28-Dec-11 0:48am

query should not provide any exception. try this query in query editor of management studio.
focus on the connection string or (DB credentials/Domain Name)
may you have any problem in connection.

Zubair Alie 28-Dec-11 0:51am

try writing your query in simpler way.. try this

SELECT
a.cusg_id,
(a.cusg_id+'___'+b.cusid_name) as customername
FROM
CustomerGroupContacts a INNER JOIN
CustomerIDs b ON a.cusg_id = b.cusg_id

waqar ahmad (Dev) 28-Dec-11 0:54am

Query is working in Sql server Query Editer and also shows data

Zubair Alie 28-Dec-11 1:25am

if running fine in editor then for sure you have some problem at code end.
try the simple query...

Select * from CustomerGroupContacts

see what results it provides to you. catch the exception as well to see what the actual exception is.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Answer 1 · 2011-12-27T18:40:00

Solution 1

I think CustomerGroupContacts.cusg_id is integer, convert this to varchar and then append to customer name.

SQL

SELECT     
CustomerGroupContacts.cusg_id,
CAST(CustomerGroupContacts.cusg_id AS varchar(20)) + '___' + CustomerIDs.cusid_name as customername
FROM        
CustomerGroupContacts INNER JOIN
CustomerIDs ON CustomerGroupContacts.cusg_id = CustomerIDs.cusg_id<pre lang="SQL">

Posted 27-Dec-11 18:40pm

Om Prakash Pant

Comments

waqar ahmad (Dev) 28-Dec-11 0:45am

Customer cusg_id char(4) its not integer

anushripatil 28-Dec-11 0:58am

is the query returning you the value in SQL query analyzer ?? if yes then plz check ur code.

anushripatil · Answer 2 · 2011-12-27T19:01:00

Solution 3

is the query returning you the value in SQL query analyzer ?? if yes then plz check ur code.U might b missing something.
Pls chk the following sample code

C#

string strConnection = "Server=;Database=Testing;user ID=;password=; ";
           SqlConnection sqlConn = new SqlConnection(strConnection);
           string strQuery = "UR QUERY ";
           sqlConn.Open();
           SqlCommand sqlComm = new SqlCommand(strQuery,sqlConn);
           SqlDataAdapter sqa= new SqlDataAdapter(sqlComm);
           DataSet  ds=new DataSet();
           sqa.Fill(ds);

Posted 27-Dec-11 19:01pm

anushripatil

Updated 27-Dec-11 19:06pm

v2

Comments

waqar ahmad (Dev) 28-Dec-11 1:09am

My Code is for Filling Customer DropDownlist is
it giving error on dataset
public void FillCustomer(DropDownList ddsuplier)
{
DataSet ds = new DataSet();
using (SqlConnection con = ConnectionManager.GetConnection())
{
using (SqlDataAdapter da = new SqlDataAdapter("SELECT " +
"CustomerGroupContacts.cusg_id, " +
"CustomerGroupContacts.cusg_id+'___'+CustomerIDs.cusid_name as customername" +
"FROM " +
"CustomerGroupContacts INNER JOIN " +
"CustomerIDs ON CustomerGroupContacts.cusg_id = CustomerIDs.cusg_id", con))
{

da.SelectCommand.CommandType = CommandType.Text;

da.Fill(ds, "CustomerGroupContacts");
if (ds.Tables.Count > 0)
{
ddsuplier.DataSource = ds;
ddsuplier.DataTextField = ds.Tables["CustomerGroupContacts"].Columns["customername"].ToString();
ddsuplier.DataValueField = ds.Tables["CustomerGroupContacts"].Columns["CustomerGroupContacts.cusg_id"].ToString();
ddsuplier.DataBind();
}

}
}
}

anushripatil 28-Dec-11 1:13am

sqlConn.Open(); missing
//u r missing the connection string
ConfigurationManager.ConnectionStrings[connectionName]

anushripatil 28-Dec-11 1:24am

//u r missing the connection string
ConfigurationManager.ConnectionStrings[connectionName]

waqar ahmad (Dev) 28-Dec-11 2:19am

I open it but still giving exception
System.Data.SqlClient.SqlException: Incorrect syntax near 'CustomerGroupContacts'.
Actuallay i have two tables
The id is one table1 and description in another table2 and i have to bind ID value dd.DataValueField from table1 and and description dd.DataTextField from Table2

anushripatil 28-Dec-11 2:28am

u can have id from 1 table & desciption from other table ... the problem is not in the query as far as i feel .... Pls debug & chk if u r getting a valid connection string . Coz i guess its probl;em with connection string

anushripatil 28-Dec-11 2:32am

TRY this code
public void FillCustomer(DropDownList ddsuplier)
{
DataSet ds = new DataSet();
SqlConnection con = ConfigurationManager.ConnectionStrings["UR CONNECTION KEY"];
string strQuery="SELECT CustomerGroupContacts.cusg_id, CustomerGroupContacts.cusg_id +'___'+CustomerIDs.cusid_name as customername FROM CustomerGroupContacts INNER JOIN CustomerIDs ON CustomerGroupContacts.cusg_id = CustomerIDs.cusg_id";
SqlCommand sqlComm = new SqlCommand(strQuery,sqlConn);
SqlDataAdapter da = new SqlDataAdapter(sqlComm, con)
da.SelectCommand.CommandType = CommandType.Text;
da.Fill(ds, "CustomerGroupContacts");
if (ds.Tables.Count > 0)
{
ddsuplier.DataSource = ds;
ddsuplier.DataTextField = ds.Tables["CustomerGroupContacts"].Columns[1].ToString();
ddsuplier.DataValueField = ds.Tables["CustomerGroupContacts"].Columns[0].ToString();
ddsuplier.DataBind();
}
}

anushripatil 28-Dec-11 2:41am

ddsuplier.DataTextField = ds.Tables["CustomerGroupContacts"].Columns[1].ToString();
ddsuplier.DataValueField = ds.Tables["CustomerGroupContacts"].Columns[0].ToString();

waqar ahmad (Dev) 28-Dec-11 5:57am

Thanks Anushripatil

Now Its giving No Exception, but also not populating values in Drop Downlist. I think we are near to solution

anushripatil 28-Dec-11 6:24am

Also, 1 suggestion, use try catch block . It helps to easily find out what & where the problem is.

anushripatil 28-Dec-11 6:18am

its nice to hear that ur problem is solved :) Just debug and check if it goes in that (ds.Tables.Count > 0) loop . Hope u will get your answer

mmvr2k11 · Answer 3 · 2011-12-27T18:51:00

Solution 2

i think dropdown access only one value..

Posted 27-Dec-11 18:51pm

mmvr2k11

Comments

Zubair Alie 28-Dec-11 1:32am

to DropDown you can assign the front end value (TEXT) and back end id (VALUE)
see
ddl.DataTextField
dd.DataValueField