I am using the asp.net ajax photo slide show to show images from the database.
Images belonging to each user are idenitified by UserId Parameter (guid) which is a column in the Images Table
What I want to do is run a select query that gets all the userId's from the Images table that have no images
associated with that UserId
Code Behind
string strSQL = "SELECT [ImageUrl],[FirstName], [LastName] FROM [Images] WHERE (UserId] = @UserId)";
SqlConnection conn = new SqlConnection("data source connection xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
conn.Open();
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.Parameters.Add("@ModelId", SqlDbType.NVarChar).Value = sName;
SqlDataAdapter da = new SqlDataAdapter(comm);
tblData = new DataTable();
da.Fill(tblData);
conn.Close();
// set the initial image
if (tblData.Rows.Count > 0)
{
imgShowImage.ImageUrl = tblData.Rows[0]["ImageUrl"].ToString();
imageLabel1.Text = tblData.Rows[0]["FirstName"].ToString();
SELECT UserIDTable.UserID, UserIDTable.FirstName, UserIDTable.LastName
FROM UserIDTable LEFT OUTER JOIN
ImageTable ON UserIDTable.UserID = ImageTable.UserID
WHERE (ImageTable.FilePath IS NULL)
prontonet
Member
243 Points
484 Posts
Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 09:09 PM|LINK
Hi,
I am using the asp.net ajax photo slide show to show images from the database.
Images belonging to each user are idenitified by UserId Parameter (guid) which is a column in the Images Table
What I want to do is run a select query that gets all the userId's from the Images table that have no images
associated with that UserId
Code Behind
string strSQL = "SELECT [ImageUrl],[FirstName], [LastName] FROM [Images] WHERE (UserId] = @UserId)";
SqlConnection conn = new SqlConnection("data source connection xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
conn.Open();
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.Parameters.Add("@ModelId", SqlDbType.NVarChar).Value = sName;
SqlDataAdapter da = new SqlDataAdapter(comm);
tblData = new DataTable();
da.Fill(tblData);
conn.Close();
// set the initial image
if (tblData.Rows.Count > 0)
{
imgShowImage.ImageUrl = tblData.Rows[0]["ImageUrl"].ToString();
imageLabel1.Text = tblData.Rows[0]["FirstName"].ToString();
}
oned_gk
All-Star
31814 Points
6507 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 10:47 PM|LINK
prontonet
Member
243 Points
484 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 11:13 PM|LINK
just to clarify that if i may.
I want to display in gridview first name, last name and UserId of all the Users who have NOT uploaded any images
In the images table there is a FilePath column that links to the folder on server where images are stored.
So as an example I have 1000 Users in total. 900 have uploaded an image but 100 have not and I want to get to the 100 that
have not so I can ask them to.
oned_gk
All-Star
31814 Points
6507 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 11:34 PM|LINK
prontonet
Member
243 Points
484 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 11:34 PM|LINK
it may be helpful to explain more
I have a UserId Table and Image Table.
Image table is
ImageId - int
FilePath - varchar
UserId - int - f/k - UserId table
when the user registered some uploaded images and if so the UserId GUID is in the Image table.
if they did NOT -then the UserId wont exist so I want to run a query that will show me all the Users
that do not have images uploaded.
oned_gk
All-Star
31814 Points
6507 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 07, 2013 11:45 PM|LINK
SELECT UserIDTable.UserID, UserIDTable.FirstName, UserIDTable.LastName FROM UserIDTable LEFT OUTER JOIN ImageTable ON UserIDTable.UserID = ImageTable.UserID WHERE (ImageTable.FilePath IS NULL)prontonet
Member
243 Points
484 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 08, 2013 09:18 AM|LINK
thanks! it worked - to take this one stage further -
I have now added a "Pictures" Column to the UserId Table of Type Boolean.
Now I would like to run a SQL update query from Management studio that
adds a (NO or False) bit to the Picture Coumn if the UserId does not have pictures in the Image Table
Please could you demonstrate?
mani2009it
Member
207 Points
82 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 08, 2013 09:45 AM|LINK
UPdate UserIDTable set UserIDTable.Pictures = 1
where UserIDTable.UserID in (SELECT distinct UserIDTable.UserID FROM UserIDTable INNER JOIN ImageTable ON UserIDTable.UserID = ImageTable.UserID )
GO
UPdate UserIDTable set UserIDTable.Pictures = 0
where UserIDTable.UserID not in (SELECT distinct UserIDTable.UserID FROM UserIDTable INNER JOIN ImageTable ON UserIDTable.UserID = ImageTable.UserID )
oned_gk
All-Star
31814 Points
6507 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 08, 2013 11:25 AM|LINK
If your problem solved then mark as answer, if any new qestion create new threat
prontonet
Member
243 Points
484 Posts
Re: Want to run select query that gets UserId's from DataTable with no Images??
Jan 08, 2013 12:38 PM|LINK
hi yes problem resolved thanks!