DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

insert into sql select statement

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

Thread Solved

•

Join Date: Jul 2007

Posts: 193

Reputation: plusplus is an unknown quantity at this point

Solved Threads: 16

plusplus

Offline

Junior Poster

insert into sql select statement

Oct 16th, 2007

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?

•

Join Date: Aug 2007

Posts: 570

Reputation: ryan_vietnow is an unknown quantity at this point

Solved Threads: 71

ryan_vietnow ryan_vietnow is offline

Offline

Posting Pro

Re: insert into sql select statement

Oct 16th, 2007

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
adoreminder.RecordSource="INSERT INTO tblreminder(userid)(SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "')";
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:

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
adoreminder.RecordSource="INSERT INTO tblreminder(userid)("SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'")";
adoreminder.RecordSource="INSERT INTO tblreminder(userid)("SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'")";

http://www.ryantetek.com/

•

Join Date: Jul 2007

Posts: 193

Reputation: plusplus is an unknown quantity at this point

Solved Threads: 16

plusplus

Offline

Junior Poster

Re: insert into sql select statement

Oct 17th, 2007

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

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: insert into sql select statement

Oct 17th, 2007

Hi PlusPlus,

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

say:

 Help with Code Tags 
 vb Syntax (Toggle Plain Text) 
adoreminder.Execute = "INSERT INTO tblreminder([user-id]) SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'"
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

•

Join Date: Jul 2007

Posts: 193

Reputation: plusplus is an unknown quantity at this point

Solved Threads: 16

plusplus

Offline

Junior Poster

Re: insert into sql select statement

Oct 17th, 2007

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

Last edited by plusplus; Oct 17th, 2007 at 7:12 am.

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: insert into sql select statement

Oct 17th, 2007

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

•

Join Date: Jul 2007

Posts: 193

Reputation: plusplus is an unknown quantity at this point

Solved Threads: 16

plusplus

Offline

Junior Poster

Re: insert into sql select statement

Oct 17th, 2007

what do you mean by connection object

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: insert into sql select statement

Oct 17th, 2007

Hi,

Go thru this link

Regards
Veena

•

Join Date: Jul 2007

Posts: 193

Reputation: plusplus is an unknown quantity at this point

Solved Threads: 16

plusplus

Offline

Junior Poster

Re: insert into sql select statement

Oct 17th, 2007

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

Last edited by plusplus; Oct 17th, 2007 at 3:42 pm.

•

Join Date: Nov 2006

Posts: 848

Reputation: QVeen72 is on a distinguished road

Solved Threads: 120

QVeen72

Offline

Practically a Posting Shark

Re: insert into sql select statement

#10

Oct 18th, 2007

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