Nearly There ! still stuck on look code

[Andy]

I've been following various tutorials from Jules's Website.

I've now got a page that inserts a new 'retailer' and then puts the UserID
into a session. On the next page the session is called.

I have a table named 'Prices' with four fields. ID - USerID - productID -
Price

I need to loop through and insert all of the ProductID's from the Products
Table AND THE Session UserID so that this new retailer has a full list of
products.

Can i do this using the MMInsert Server Behaviour?

Andy

[Paul Whitham TMM]

Andy

The standard MM_Insert statement is designed to be used with form values,
and it will be rather complicated to modify it. The code for an insert
statement inside of a recordset output is not that complicated. This should
do the trick for you.

<%
'Create a connection to your database
dim strConn
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\websites\Database\database.mdb"

'Read in the Products Table
dim adoCn
dim adoRs
dim strSQL
set adoCn = Server.CreateObject("ADODB.Connection")
adoCn.Open strConn
set adoRs = Server.CreateObject("ADODB.Recordset")
strSQL = "Select ProductID, Price From tblProducts"
set adoRs = adoCn.Execute(strSQL)
If not adoRs.EOF then
sProductID = adoRs("ProductID")
sPrice = adoRs("Price")

'Carry out an insert
strSQL = "Insert into Prices(USerID,productID,Price) values (" &
session("svUSerID") & "," & sProductID & "," & sPrice & ")"
adoCn.Execute strSQL
End If
adoRs.Close
set adoRs = nothing
adoCn.Close
set adoCn = nothing
%>

This assumes that ProductID is a numeric field. If it is a text value then
the line needs to be like this

strSQL = "Insert into Prices(USerID,productID,Price) values (" &
session("svUSerID") & ",'" & sProductID & "'," & sPrice & ")"



--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
news:d3oj70$b65$1@forums.macromedia.com...

> I've been following various tutorials from Jules's Website.
>
> I've now got a page that inserts a new 'retailer' and then puts the UserID
> into a session. On the next page the session is called.
>
> I have a table named 'Prices' with four fields. ID - USerID - productID -
> Price
>
> I need to loop through and insert all of the ProductID's from the

Products

> Table AND THE Session UserID so that this new retailer has a full list of
> products.
>
> Can i do this using the MMInsert Server Behaviour?
>
> Andy
>
>
>

[Andy]

Hi Paul
Thanks for the help!

I've only just checked the post. I've been away from the pc for a few days.
There IS a world out there! :-)

I'll post back when i've tried your advice out.

Thanks again
Andy


"Paul Whitham TMM" <paul@valleybiz.net> wrote in message
news:d3ulpb$73m$1@forums.macromedia.com...

> Andy
>
> The standard MM_Insert statement is designed to be used with form values,
> and it will be rather complicated to modify it. The code for an insert
> statement inside of a recordset output is not that complicated. This

should

> do the trick for you.
>
> <%
> 'Create a connection to your database
> dim strConn
> strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=c:\websites\Database\database.mdb"
>
> 'Read in the Products Table
> dim adoCn
> dim adoRs
> dim strSQL
> set adoCn = Server.CreateObject("ADODB.Connection")
> adoCn.Open strConn
> set adoRs = Server.CreateObject("ADODB.Recordset")
> strSQL = "Select ProductID, Price From tblProducts"
> set adoRs = adoCn.Execute(strSQL)
> If not adoRs.EOF then
> sProductID = adoRs("ProductID")
> sPrice = adoRs("Price")
>
> 'Carry out an insert
> strSQL = "Insert into Prices(USerID,productID,Price) values (" &
> session("svUSerID") & "," & sProductID & "," & sPrice & ")"
> adoCn.Execute strSQL
> End If
> adoRs.Close
> set adoRs = nothing
> adoCn.Close
> set adoCn = nothing
> %>
>
> This assumes that ProductID is a numeric field. If it is a text value then
> the line needs to be like this
>
> strSQL = "Insert into Prices(USerID,productID,Price) values (" &
> session("svUSerID") & ",'" & sProductID & "'," & sPrice & ")"
>
>
>
> --
> Regards
>
> Paul Whitham
> Macromedia Certified Professional for Dreamweaver MX2004
> Valleybiz Internet Design
> [url]www.valleybiz.net[/url]
>
> Team Macromedia Volunteer for Ultradev/Dreamweaver MX
> [url]www.macromedia.com/support/forums/team_macromedia[/url]
>
> "Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
> news:d3oj70$b65$1@forums.macromedia.com...

> > I've been following various tutorials from Jules's Website.
> >
> > I've now got a page that inserts a new 'retailer' and then puts the

UserID

> > into a session. On the next page the session is called.
> >
> > I have a table named 'Prices' with four fields. ID - USerID -

productID -

> > Price
> >
> > I need to loop through and insert all of the ProductID's from the

> Products

> > Table AND THE Session UserID so that this new retailer has a full list

of

> > products.
> >
> > Can i do this using the MMInsert Server Behaviour?
> >
> > Andy
> >
> >
> >

>
>

[Andy]

Hi Paul

I'm working through your advice but im getting this error

Microsoft JET Database Engine (0x80040E0C)
Command text was not set for the command object.
/MyWebs/Dynamic Brochure/adm1n/new_retailer_populate.asp, line 16

Line 16 is: set adoRs=adoCn.Execute(srtSQL)

Any idea what might be causing this?

Andy



"Andy" <andyjhughesSPAMBUSTER@ntlworld.com> wrote in message
news:d437a6$978$1@forums.macromedia.com...

> Hi Paul
> Thanks for the help!
>
> I've only just checked the post. I've been away from the pc for a few
> days.
> There IS a world out there! :-)
>
> I'll post back when i've tried your advice out.
>
> Thanks again
> Andy
>
>
> "Paul Whitham TMM" <paul@valleybiz.net> wrote in message
> news:d3ulpb$73m$1@forums.macromedia.com...

>> Andy
>>
>> The standard MM_Insert statement is designed to be used with form values,
>> and it will be rather complicated to modify it. The code for an insert
>> statement inside of a recordset output is not that complicated. This

> should

>> do the trick for you.
>>
>> <%
>> 'Create a connection to your database
>> dim strConn
>> strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> Source=c:\websites\Database\database.mdb"
>>
>> 'Read in the Products Table
>> dim adoCn
>> dim adoRs
>> dim strSQL
>> set adoCn = Server.CreateObject("ADODB.Connection")
>> adoCn.Open strConn
>> set adoRs = Server.CreateObject("ADODB.Recordset")
>> strSQL = "Select ProductID, Price From tblProducts"
>> set adoRs = adoCn.Execute(strSQL)
>> If not adoRs.EOF then
>> sProductID = adoRs("ProductID")
>> sPrice = adoRs("Price")
>>
>> 'Carry out an insert
>> strSQL = "Insert into Prices(USerID,productID,Price) values (" &
>> session("svUSerID") & "," & sProductID & "," & sPrice & ")"
>> adoCn.Execute strSQL
>> End If
>> adoRs.Close
>> set adoRs = nothing
>> adoCn.Close
>> set adoCn = nothing
>> %>
>>
>> This assumes that ProductID is a numeric field. If it is a text value
>> then
>> the line needs to be like this
>>
>> strSQL = "Insert into Prices(USerID,productID,Price) values (" &
>> session("svUSerID") & ",'" & sProductID & "'," & sPrice & ")"
>>
>>
>>
>> --
>> Regards
>>
>> Paul Whitham
>> Macromedia Certified Professional for Dreamweaver MX2004
>> Valleybiz Internet Design
>> [url]www.valleybiz.net[/url]
>>
>> Team Macromedia Volunteer for Ultradev/Dreamweaver MX
>> [url]www.macromedia.com/support/forums/team_macromedia[/url]
>>
>> "Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
>> news:d3oj70$b65$1@forums.macromedia.com...

>> > I've been following various tutorials from Jules's Website.
>> >
>> > I've now got a page that inserts a new 'retailer' and then puts the

> UserID

>> > into a session. On the next page the session is called.
>> >
>> > I have a table named 'Prices' with four fields. ID - USerID -

> productID -

>> > Price
>> >
>> > I need to loop through and insert all of the ProductID's from the

>> Products

>> > Table AND THE Session UserID so that this new retailer has a full list

> of

>> > products.
>> >
>> > Can i do this using the MMInsert Server Behaviour?
>> >
>> > Andy
>> >
>> >
>> >

>>
>>

>
>
>