SQL Codes don't work for me.. i can't figure out what im doing wrong.

[Hijazzer]

I'm trying to eliminate the unwanted data from a recordset and write the pure ones to another database table .. i didn't take any error but when i check info from acces i cant see the records that should be where i checked.

I added my full project here:
http://ul.to/wv3pfw8c
u can see my database's password from login form's code window.(its alil' bit nasty i dont want to write it down here. ) Any helps appreciated..

Dim conn As New ADODB.Connection
Dim TempSet1 As New ADODB.Recordset
Dim TempSet2 As New ADODB.Recordset
Dim TempSet3 As New ADODB.Recordset
Dim TempSet4 As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Math.accdb;Jet OLEDB:Database Password=****you216;"
conn.Open

TempSet1.ActiveConnection = conn
TempSet1.CursorLocation = adUseClient
TempSet1.Open ("select OGRNO,Ad,Soyad from Ogrenci")

TempSet2.ActiveConnection = conn
TempSet2.CursorLocation = adUseClient
TempSet2.Open "SELECT ogrenci,Adsoyad FROM Notlar WHERE DersKodu=" & Ana_Sayfa.dk

TempSet3.ActiveConnection = conn
TempSet3.CursorLocation = adUseClient

Do While Not TempSet1.EOF
 TempSet2.MoveFirst
 Do While Not TempSet2.EOF
 
 If TempSet1.Fields("OGRNO") <> TempSet2.Fields("Ogrenci") Then
    TempSet3.Open "insert into TempOgrenci (Ad,Soyad,OGRNO) values (" & TempSet1.Fields("OGRNO") & ",'" & TempSet1.Fields("Ad") & "','" & TempSet1.Fields("Soyad") & "')"
 End If
 
TempSet2.MoveNext
Loop
TempSet1.MoveNext
Loop

TempSet4.ActiveConnection = conn
TempSet4.CursorLocation = adUseClient
TempSet4.Open "select OGRNO,Ad,Soyad from TempOgrenci"

DataGrid1.DataSource= TempSet4

[ColinE66]

    TempSet3.Open "insert into TempOgrenci (Ad,Soyad,OGRNO) values (" & on =

You're using Open to perform an insert. Use Execute instead.

[Hijazzer]

Tnx colinE66 but now im taking a new error at least

"Method or data member not found" on the same row.

TempSet3.Execute "INSERT INTO TempOgrenci (OGRNO,Ad,Soyad) values (" & TempSet1.Fields("OGRNO") & ",'" & TempSet1.Fields("Ad") & "','" & TempSet1.Fields("Soyad") & "')"

[ColinE66]

TempSet3.Execute "INSERT INTO TempOgrenci (OGRNO,Ad,Soyad) values ("'" & TempSet1.Fields("OGRNO") & "', '" & TempSet1.Fields("Ad") & "','" & TempSet1.Fields("Soyad") & "')"

You were missing some single quotes...

[Hijazzer]

TempSet3.Execute "INSERT INTO TempOgrenci (OGRNO,Ad,Soyad) values ("'" & TempSet1.Fields("OGRNO") & "', '" & TempSet1.Fields("Ad") & "','" & TempSet1.Fields("Soyad") & "')"

You were missing some single quotes...

Tnx for reply my friend but i guess u didn't try ur string before reply post. it shows

'" & TempSet1.Fields("OGRNO") & "', '" & TempSet1.Fields("Ad") & "','" & TempSet1.Fields("Soyad") & "')"

line as comment also in my database TempSet1.Fields("OGRNO") column is Long type variable not string.. :S

[ColinE66]

No I didn't try it, sorry about that - thought I saw a problem immediately and reported that...

But anyway, TempSet3 is declared as a RecordSet. I believe that you need a Connection object to do the insert.

i.e. conn.execute not TempSet3 .execute

This is all from memory btw - I haven't used ADO for a long time....!

[jcis]

But anyway, TempSet3 is declared as a RecordSet. I believe that you need a Connection object to do the insert.
i.e. conn.execute not TempSet3 .execute

This is correct. Recordsets are used when you need to return data from DB (SELECT). When you're executing INSERT or UPDATE operations nothing returns so these have to be run using ConnectionObject.Execute or using a Command Object.

@Hijazzer, you said you want to remove unwanted data but it's not clear what's the criteria to know what's unwanted data, you want to keep rows on table Ogrenci when Ogrenci.OGRNO corresponds with Notlar.ogrenci and DersKodu = Ana_Sayfa.dk, or you want to remove this rows from table Ogrenci? If you want to keep this rows then try something like:

    Dim conn As ADODB.Connection, rs As ADODB.Recordset, StrSql As String

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\Math.accdb;Jet OLEDB:Database Password=****you216;"
    conn.Open
        
    StrSql = "INSERT INTO TempOgrenci (Ad,Soyad,OGRNO)" & _
             "     SELECT A.OGRNO, A.ad, .A.Soyad " & _
             "       FROM Ogrenci A INNER JOIN Notlar B ON A.OGRNO = B.ogrenci " & _
             "      WHERE B.DersKodu = " & Ana_Sayfa.dk
    conn.Execute StrSql
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "select OGRNO,Ad,Soyad from TempOgrenci", conn
    Set DataGrid1.DataSource = rs
    
    rs.Close
    Set rs = Nothing
    Set conn = Nothing

If what you need is the opposite (excluding registers where DersKodu = Ana_Sayfa.dk) then you need to change the red = in previous code to <>
I'm assuming there is only 1 key needed to joins these tables: [Ogrenci.OGRNO] should match [Notlar.ogrenci], if there are more then they should be added to this query.
If this is not what you need then please specify what's your criteria to understand which rows should be excluded and which not.

[Hijazzer]

First thank to you all guyz for helping me out.
this project about Students and the lessons they take. This program is written for the teachers who wants to track the lessons they teach and the students who take the lessons.

I have 4 tables on my database named
Academicians (Akademisyen)
Students (Ogrenci)
Grades (Notlar)
Lessons (Dersler)

my problem starts when i tried to add lesson for a student. in fact there is no problem but when i click the "&#246;ğrenci ekle" button which means (add students) i want to show the students who has not take the lesson i choose from Ana_Sayfa form's Datagrid. i identify it as Dk(every lesson has its own Derskodu(lesson code) as integer in dersler table).

the algorythm that i think is pick the list of the students and eliminate the rows includes students that takes specified lesson.

[Hijazzer]

@jcis can we use only one Sql string to do read and write process at the same time ?

[jcis]

@jcis can we use only one Sql string to do read and write process at the same time ?

Not recommended, In the Sql String in my previous code i'm just inserting registers not selecting them, in fact i'm inserting what i select, it's another kind of insert, it can insert many rows not only one. Then I used another SELECT to read data from VB into the grid.

It's better if you can learn SQL, you're logic would be in your queries, not in vb code.

I recommend (before writing this Application) reading some ADO tutorials and also SQL tutorials, you'll find some here in the FAQ forums for VB and Database Subforums, It would also be a good idea learning to use a better grid control like Msflexgrid or Listview, and use them without bounding them to a Recordset, i mean processing data manually looping the recordset, not binding results to a grid. You'll learn a lot this way.

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)