|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
September 24th, 2003, 11:27 AM
|
|||
|
|||
|
insert into table1 based on record in table2
I have a simple access DB with two tables. I have an .asp page w/ a form and I want to insert into one table based on a value in the other table. (i'm sure that makes no sense...)
table one is called STREAMS and has a field called StreamNames. Table two is called StreamData and has fields called 'userName', yesNo', 'userTime' and 'streamID'. streamID is connected to the first table so it is a foreign key. my sql statement looks like Code:
sql = "insert into streamData(datechecked,yesno,username,usertime)values(" & now & ",'" & yesno & "','" & username & "','" & usertime & "') select streamName from streams where stream =" & stream
the rest of my code looks like Code:
dim ors
set ors = server.CreateObject("adodb.recordset")
sql = "insert into streamData(datechecked,yesno,username,usertime)values(" & now & ",'" & yesno & "','" & username & "','" & usertime & "') select streamName from streams where stream =" & stream
ors.Open sql, "DSN=checker2", adOpenKeyset,adLockOptimistic
I didn't include the part of code that I'm using to grab the data from the form. the error message is Code:
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. /streamchecker2/inserter2.asp, line 25 line 25 is the ors.open sql, "dsn=..." line what am I doing wrong? any suggestions are welcome. thank you
|
|
#2
September 24th, 2003, 11:52 AM
|
|||
|
|||
|
Where/When, in your code, did you connect to your database?
I don't see it? You must FIRST connect to your database thus getting a Connection Object and only then can you execute your SQL Query! I've also noticed that you EXPLICITLY create a Recordset Object but you don't really need it since your SQL Query is an INSERT query, and like we all know(just kidding), INSERT queries DO NOT RETURN ANYTHING so you basically create yourself extra code. Use the Connection Object to execute your Query! Tell me if you need an example cause I have to leave right now...back in 10 min Hope this helps! Sincerely Vlince
|
|
#3
September 24th, 2003, 12:19 PM
|
|||
|
|||
|
yea, if you have some code samples that would be great.
I'm not sure when or how I connect to the DB. I have a DSN but where do I put it? or rather what is the syntax I need? my code now looks like this Code:
Set Conn = Server.CreateObject("ADODB.Connection")
sql = "insert into streamData(datechecked,yesno,username,usertime)values(" & now & ",'" & yesno & "','" & username & "','" & usertime & "') select streamName from streams where stream =" & stream
Conn.Open(sql)
Conn.Execute(sql)
and my DSN is called checker2 I'm not sure where I need to connect or even how i connect using my DSN Thanks!
|
|
#4
September 24th, 2003, 12:37 PM
|
|||
|
|||
|
Well you first need to create your SQL Query so for example do something like this:
<% Const adExecuteNoRecords = 128 Dim strSql Dim objConn strSql = "INSERT INTO..." 'FOR DEBUG ONLY 'Response.Write strSql & "<hr>" 'Response.End Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open CONNECTION_STRING objConn.execute strSql, , adExecuteNoRecords objConn.Close Set objConn = nothing %> Notice the I've declared a Const but if you have the adovbs file included then you don't need to declare the Constant...anyway... Also notice that I *FIRST* create the SQL Query, I also add a 'FOR DEBUG ONLY section, this way you can't uncomment the two lines under and it will print to the screen the SQL Query *YOU ARE ABOUT TO EXECUTE* You can then copy/paste the query and paste it inside your database, run it see if the results are what you're expecting...if not then correct your SQL Query within your ASP page Then I create a connection object, I then use a CONNECTION_STRING that's where, in your case, you put the DSN thing...Then once my connection is opened, I use the Execute method of the Connection Object. Notice **I'M NOT** creating a Recordset object. Then I execute the Query and close/free the Connection Object Hope this helps! Sincerely Vlince
|
|
#5
September 24th, 2003, 01:03 PM
|
|||
|
|||
|
code now looks like this
Code:
sql = "insert into streamData(datechecked,yesno,username,usertime)values(" & now & ",'" & yesno & "','" & username & "','" & usertime & "') select streamName from streams where stream =" & stream
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN =checker2"
Conn.execute sql, , adExecuteNoRecords
I am using the adobvs.inc file. I still get a sql error. I will try running it in my DB as you mentioned. do you see anything that could be wrong w/ my sql statement? thanks again!
|
|
#6
September 24th, 2003, 02:36 PM
|
|||
|
|||
|
Code:
sql = "insert into streamData(datechecked,yesno,username,usertime)values
(" & now & ",'" & yesno & "','" & username & "','" & usertime & "')
select streamName from streams where stream =" & stream
I donot understand the use of this part in your sql statement Code:
select streamName from streams where stream =" & stream You should be knowing the streamid from the input screen I guess. In that case you would do, "insert into streamData(datechecked,yesno,username,usertime,streamid) values (" & now & ",'" & yesno & "','" & username & "','" & usertime & "' & "," & streamid & ")" well, what if you donot know the ID from the previous screen. then you would do have to get it in a seperate sql. assign it to a variable and then use the variable in the insert sql like strmid="select streamid from streamnames where name='" & stream &"'" and then your statement for streamdata would become Code:
"insert into streamData(datechecked,yesno,username,usertime,streamid)
values
(" & now & ",'" & yesno & "','" & username & "','" & usertime & "' & "," & strmid & ")"
__________________
If you ask a question you are a fool for a second. But if you dont ask, you are a fool for a life time.
|
|
#8
September 24th, 2003, 04:02 PM
|
|||
|
|||
|
ok. i think i may be going about this all wrong....
I think I should be able to just put in all the data into my StreamData table. then when I want to get it back out, I pull out the data based on what is in my Streams table. so if my Streams table has say MTV Discovery VH1 FoxNews and I just want the data out of my StreamData table that is linked to the FoxNews in my Streams table I could do something like Code:
dim whatStream whatStream=[whatever stream the user selects] select * from StreamData where streamID = whatStream I think this would work. I don't think I need to put the data into the database in any order like I am trying to do. I just need to put in the StreamID into the StreamData table then pull it out all data based on the StreamID that matches my StreamName in the STREAMS table. does that make sense? I think i over complicate things sometimes...
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > insert into table1 based on record in table2 |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
