insert into sql select statement
 

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?

Re: insert into sql select statement
 

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 & "'")";

Re: insert into sql select statement
 

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

Re: insert into sql select statement
 

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

Re: insert into sql select statement
 

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)

Re: insert into sql select statement
 

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

Re: insert into sql select statement
 

what do you mean by connection object

Re: insert into sql select statement
 

Hi,

Go thru this link

Regards
Veena

Re: insert into sql select statement
 

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

Re: insert into sql select statement
 

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