944,141 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > insert into sql select statement
Ad:
Permalink
Oct 16th, 2007
0

insert into sql select statement

Expand Post »
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?
Similar Threads
Reputation Points: 10
Solved Threads: 16
Posting Whiz in Training
plusplus is offline Offline
207 posts
since Jul 2007
Permalink
Oct 16th, 2007
0

Re: insert into sql select statement

vb Syntax (Toggle Plain Text)
  1. 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:

vb Syntax (Toggle Plain Text)
  1. adoreminder.RecordSource="INSERT INTO tblreminder(userid)("SELECT tbluser.id FROM tbluser WHERE tbluser.Name = '" & txtname.Text & "'")";
Reputation Points: 28
Solved Threads: 71
Posting Pro
ryan_vietnow is offline Offline
578 posts
since Aug 2007
Permalink
Oct 17th, 2007
0

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
Reputation Points: 10
Solved Threads: 16
Posting Whiz in Training
plusplus is offline Offline
207 posts
since Jul 2007
Permalink
Oct 17th, 2007
1

Re: insert into sql select statement

Hi PlusPlus,

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

say:

vb Syntax (Toggle Plain Text)
  1. 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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
Oct 17th, 2007
0

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)
Quote ...
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.
Reputation Points: 10
Solved Threads: 16
Posting Whiz in Training
plusplus is offline Offline
207 posts
since Jul 2007
Permalink
Oct 17th, 2007
0

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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
Oct 17th, 2007
0

Re: insert into sql select statement

what do you mean by connection object
Reputation Points: 10
Solved Threads: 16
Posting Whiz in Training
plusplus is offline Offline
207 posts
since Jul 2007
Permalink
Oct 17th, 2007
0

Re: insert into sql select statement

Hi,

Go thru this link

Regards
Veena
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
Oct 17th, 2007
0

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?
Last edited by plusplus; Oct 17th, 2007 at 3:42 pm.
Reputation Points: 10
Solved Threads: 16
Posting Whiz in Training
plusplus is offline Offline
207 posts
since Jul 2007
Permalink
Oct 18th, 2007
0

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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Help with Message Boxes
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: ms flex grid





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC