SQL VisualBasic 2010 View Result From table1 using table2 where id listview.item(0).text

Question

0.00/5 (No votes)

See more:

Hello I need help listview1 displays the contents of the table Youtube but if the table viewed are the same values, they will not be visible to the logged user where his id is ListView2.Items(0).SubItems(0).Text but will be visible to other users. How to do?

Table1(youtube)
 
    id  user    userlogin   url     
    1   22      12345       http/...1
    2   23      tommy       http/...2
 
Table2(viewed)
 
    user_id     site_id
    22          1     
    23          2
 
listview1
 
    id user url         userlogin 
    1  22   http:/...1 12345
 
listview2
    id
    22(user id)

VB

ds = New DataSet
    da = New MySqlDataAdapter("SELECT * from youtube where id not in (Select site_id from viewed) and user not in (Select user_id from viewed) where user=ListView1.Items(0).SubItems(0).Text", sqlcon) 
da.Fill(ds, "youtube")
lvDisplay.Items.Clear()
If ds.Tables("youtube").Rows.Count > 0 Then
 
    For i As Integer = 0 To ds.Tables("youtube").Rows.Count - 1
        With lvDisplay.Items.Add(ds.Tables("youtube").Rows(i).Item(0).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(3).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(4).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(5).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(6).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(7).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(13).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(2).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(1).ToString)
            .SubItems.Add(ds.Tables("youtube").Rows(i).Item(14).ToString)

http://obrazki.elektroda.pl/1681222700_1409155841.png[^]

Posted 27-Aug-14 6:41am

Łuki Gra

Updated 27-Aug-14 13:59pm

Nelek

v2

Add a Solution

Comments

Ashi0891 27-Aug-14 13:15pm

Can you explain a little more. I didn't really get "Hello I need help listview1 displays the contents of the table Youtube but if the table viewed are the same values, they will not be visible to the logged user where his id is ListView2.Items(0).SubItems(0).Text but will be visible to other users.". how to do "what"?

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

PhilLenoir · Accepted Answer · 2014-08-27T09:06:00

Solution 1

If I understand your requirement correctly, your select clause is flawed. Instead of using 2 subqueries in your where clause, use a join between the two tables in the subquery. Also, you can't embed the object itself in your SQL string, you need to concatenate:

SQL

SELECT *
 FROM youtube
 WHERE id NOT IN(SELECT site_id 
      FROM youtube INNER JOIN viewed 
         ON youtube.id=viewed.site_id 
            AND youtube.user=viewed.user_id
      WHERE user_id=" & ListView1.Items(0).SubItems(0).Text & ")

I'd strongly advise switching to a command object and parameterizing your query to avoid SQL injection.

Posted 27-Aug-14 9:06am

PhilLenoir

Updated 28-Aug-14 0:38am

v2

Comments

Łuki Gra 27-Aug-14 15:56pm

works:) Thank you very much.

PhilLenoir 27-Aug-14 15:56pm

You are very welcome!

PhilLenoir 27-Aug-14 16:17pm

Problems? (It looks like you unaccepted the solution)

Łuki Gra 27-Aug-14 16:41pm

hmm i find problem please look this http://obrazki.elektroda.pl/8720483500_1409171974.png

PhilLenoir 27-Aug-14 17:07pm

Luki, clearly something is working correctly. When the row is removed for the list, is that you refreshing the list from the database or do you remove it directly. Is it possible that the list is being populated from one query and refreshed from another? Here are some debugging suggestions:
> Run the queries you have manually directly on the backend (SQL Management Studio). Hard code in the dynamic values that you are using for testing.
> Add another record in the table to show multiple rows. Click the new record in your application. Do both rows now disappear?
> Check the contents of your "viewed" table before and after you run the test and re-run your manual queries.

If the query works, then the problem is something else (they don't work sometimes and not others!) :)

Łuki Gra 28-Aug-14 5:32am

hello the error was in my code when clicked button changed the youtube user ID that is assigned to the person who added the link.

And removed from your code is "youtube.user =" and now works fine thank you.

PhilLenoir 28-Aug-14 9:08am

Great!

Łuki Gra · Accepted Answer · 2014-08-27T23:33:00

Solution 2

"SELECT *FROM(youtube) WHERE id NOT IN(SELECT site_id FROM youtube INNER JOIN viewed ON youtube.id=viewed.site_id AND viewed.user_id WHERE user_id=" & ListView2.Items(0).SubItems(0).Text & ")"

Posted 27-Aug-14 23:33pm

Łuki Gra