Insert Question

[ToniS]

I would like to insert to the PacketSent Table that has 2 fields in it. I
have the following insert statement (I know it will not work because I am
only selecting one field and there are two in the table)

INSERT INTO ShowPacketSent (ShowID,ExhibitorAddressID)
SELECT EA.ExhibitorAddressID
FROM Exhibitors E
INNER JOIN ExhibitorAddresses EA ON E.ExhibitorID =
EA.ExhibitorID
WHERE (E.ShowCategories = 2 OR E.ShowCategories = 3) AND
(EA.PacketSent = 1)

My question is the showID information is stored in a public variable and not
in a table, how do I go about inserting both the showID and
ExhibitorAddressID into the table?

Thanks
ToniS

 

[Sylvain Lafontaine]

First, this question would be better asked in a newsgroup dedicated to
SQL-Server programming, such a m.p.sqlserver.programming.

Second, I don't know what you mean exactly by public variable and from what
kind of language/environment that you running this query but probably you
want something like:

declare @ShowId int
set @ShowId = 12345

INTO ShowPacketSent (ShowID,ExhibitorAddressID)
SELECT @ShowId, EA.ExhibitorAddressID
FROM Exhibitors E
INNER JOIN ExhibitorAddresses EA ON E.ExhibitorID =
EA.ExhibitorID
WHERE (E.ShowCategories = 2 OR E.ShowCategories = 3) AND
(EA.PacketSent = 1)

 

[ToniS]

I am doing this in an .adp, and am not sure how to pass a parmater into an
insert that is not stored with in a table.... I have tried the following and
was unsuccessful


Dim objParmShowID As New ADODB.Parameter

' get object required error at runtime
Set ParmshowID = PubShowID


I also tried (get a snytext error)
set @showid = pubshowid


Any ideas on how to do this in an access .adp application?

 

[Sylvain Lafontaine]

So by public variable, you mean a local VBA variable in the Access frontend
application (your ADP project) ? The easiest way for you would be to build
a sql string and use it in a call to CurrentProject.Connection.Execute:

Dim sql as string

sql = "INTO ShowPacketSent (ShowID,ExhibitorAddressID) SELECT " _
& pubshowid _
& " , EA.ExhibitorAddressID FROM Exhibitors E INNER JOIN
ExhibitorAddresses EA ON E.ExhibitorID = EA.ExhibitorID WHERE
E.ShowCategories = 2 OR E.ShowCategories = 3) AND (EA.PacketSent = 1)"

CurrentProject.Connection.Execute (sql)

For using the Parameters collection, you must use a command object, set its
connection property to CurrentProject.Connection, add the paramater(s) and
then call its Execute method. Also, you can use named parameters only when
calling a stored procedure. If you want to use parameters with an ordinary
sql string like above, then you must the placement holder: « ? » (without
the «»). It's a much more complicated procedure than simply building the
sql string and the Parameters collection is usually used only when you are
calling a stored procedure (SP).

ADP is build on ADO; so if you want to work with ADP, you should study ADO.
Studying SQL-Server would also be a good idea.

 

[ToniS]

I finally got back to this project and I just wanted to say thank you so
very much for your help, this is exactly what I was looking for and it works!!

Thanks again
ToniS