insert into sql select statement

[plusplus]

Here I am again with my next sql problem. I'm not sure if I can use insert into for what I need. What I want to do is the following.
My tables

I have tblreminder with userid workid workdate
I have tbluser with userid and username

My textbox
I have txtuser

Variables
idwork
mydate

I want to add new record to tblreminder
tblreminder.userid = select userid from tbluser where username = txtuser

Here is my code that's not working

adoreminder.RecordSource = "INSERT INTO tblreminder(user-id) SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'"

Can anybody help me?

[ryan_vietnow]

adoreminder.RecordSource="INSERT INTO tblreminder(userid)(SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "')";

if not put a duoblequote on the subquery,like this:

adoreminder.RecordSource="INSERT INTO tblreminder(userid)("SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'")";

[plusplus]

the first gives me syntax error at runtime
the second gives me error already when I leave the row

[QVeen72]

Hi PlusPlus,

What is AdoReminder....?
if it is a Connection object, then use Execute method..

say:

adoreminder.Execute = "INSERT INTO tblreminder([user-id]) SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'"

Wrap complicated column names with square breackets.. (- in ur case)
always UnderScore ( _ ) is better than -


REgards
Veena

[plusplus]

adoreminder is adodc and it doesn't recognize execute. I have code that's working with two ado, I thought I can do it in one, but I can't get it right. Here is my code with two ados that's working.
Can you help to put it into one(if that's more correct)
adouser.RecordSource = "SELECT * FROM tbluser WHERE Name = '" & txtname.Text & "'"
adouser.Refresh
If adouser.Recordset.EOF = True Then
Call MsgBox("Kontrollieren Sie den Namen", , "Ungültiger Name")
Exit Sub
End If
adoreminder.RecordSource = "SELECT * FROM tblreminder"
adoreminder.Refresh
adoreminder.Recordset.AddNew
adoreminder.Recordset("User-Id") = adouser.Recordset("id")
adoreminder.Recordset("Bau-Id") = frmBaukalender.adobau.Recordset("ID")
adoreminder.Recordset("Erinnerungsdatum") = MonthView1.Value
adoreminder.Recordset("Notizen") = txtNotiz.Text
adoErinnerung.Recordset.Update

[QVeen72]

HI,

ADODC acts as a Combination of Connection Object+ Recordset object,
"Insert Into " Statements are executed on the Connection /Command Object, It cannot support these statements.
Even if your Statement is correct, you will get error.. Just use a onnection object and execute it..

Regards
Veena

[plusplus]

what do you mean by connection object

[QVeen72]

Hi,

Go thru this link

Regards
Veena

[plusplus]

Thanks,I got it. Just one more question, is it not good to do it the way I did (with two ado), and why?

[QVeen72]

Hi,

Data Controls, may be OK, for beginners, but when you are developing complete Client-Server system, you need to have a Robust connection object, which you can have shared throught out your whole project ( multiple Forms).. Data Controls are Form Level.. Though you can access in other forms also. But having a Connection Object can give more flexibility in writing Execute Statements(DML Statements to any Table). It works on the database as whole, where as DataControls work on Table-/SQL Statemnt level.

Regards
Veena