99% there - FAQ Paul Whitham

[Andy]

Hi Paul

Please see my reply to one of my earlier posts called "FAO Nancy Gill"
The page with you code on is now inserting one product after inserting the
new retailer.
Why isn't it inserting all of them ?

Andy

[Nancy Gill]

> Why isn't it inserting all of them ?

Sounds like you didn't loop it. A repeat region perhaps?
I didn't see the code he sent you .. but that's a thought.

Nancy

[Andy]

Sorry nancy i know i'm being a pain!

Here is the code:


<%
'Create a connection to your database
dim strConn
strConn= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=E:\work\websites\dynamic_brochure\dbase_saf e\brochure.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 Prices"
set adoRs=adoCn.Execute(strSQL)


If not adoRs.EOF then
sProductID=adoRs("productID")
sPrice=adoRs("Price")

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


"Nancy Gill" <nancy@webwish.com> wrote in message
news:d57tnm$ihc$1@forums.macromedia.com...

>

>> Why isn't it inserting all of them ?

>
>
> Sounds like you didn't loop it. A repeat region perhaps?
> I didn't see the code he sent you .. but that's a thought.
>
> Nancy
>
>
>
>

[Paul Whitham TMM]

Andy

Slight error in the insert code. Try this

<%
'Create a connection to your database
dim strConn
strConn= "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=E:\work\websites\dynamic_brochure\dbase_saf e\brochure.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 Prices"
set adoRs=adoCn.Execute(strSQL)


Do While not adoRs.EOF
sProductID=adoRs("productID")
sPrice=adoRs("Price")

'Carry out inseert
strSQL="Insert into Prices(UserID,productID,Price) values ("&
session("UserID") & "," & sProductID &"," & sPrice & ")"
adoCn.Execute strSQL
adoRS.MoveNext
Loop

adoRs.Close
set adoRs=nothing
adoCn.Close
set adoCn=nothing
%>

--
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:d587jo$4eb$1@forums.macromedia.com...

> Sorry nancy i know i'm being a pain!
>
> Here is the code:
>
>

> >

>
> "Nancy Gill" <nancy@webwish.com> wrote in message
> news:d57tnm$ihc$1@forums.macromedia.com...

> >

> >> Why isn't it inserting all of them ?

> >
> >
> > Sounds like you didn't loop it. A repeat region perhaps?
> > I didn't see the code he sent you .. but that's a thought.
> >
> > Nancy
> >
> >
> >
> >

>
>
>

[Andy]

Oooooo Sooo close

Hi Paul

This is getting very exciting now :-) thanks to you.

What's happening now is that when i create a new retailer, he/she is
assigned mulitple occurances of the same product. All products seem to be
assigned, it's just that each product is there numerous times.

There ae about 450 products, but when i checked the database, over 62,000
products were inserted for the new retailer.

I can smell the light at the end of the tunnel - errr!

Thanks
Andy



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

> Andy
>
> Slight error in the insert code. Try this
>
> <%
> 'Create a connection to your database
> dim strConn
> strConn= "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=E:\work\websites\dynamic_brochure\dbase_saf e\brochure.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 Prices"
> set adoRs=adoCn.Execute(strSQL)
>
>
> Do While not adoRs.EOF
> sProductID=adoRs("productID")
> sPrice=adoRs("Price")
>
> 'Carry out inseert
> strSQL="Insert into Prices(UserID,productID,Price) values ("&
> session("UserID") & "," & sProductID &"," & sPrice & ")"
> adoCn.Execute strSQL
> adoRS.MoveNext
> Loop
>
> adoRs.Close
> set adoRs=nothing
> adoCn.Close
> set adoCn=nothing
> %>
>
> --
> 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:d587jo$4eb$1@forums.macromedia.com...

>> Sorry nancy i know i'm being a pain!
>>
>> Here is the code:
>>
>>

>> >

>>
>> "Nancy Gill" <nancy@webwish.com> wrote in message
>> news:d57tnm$ihc$1@forums.macromedia.com...

>> >
>> >> Why isn't it inserting all of them ?
>> >
>> >
>> > Sounds like you didn't loop it. A repeat region perhaps?
>> > I didn't see the code he sent you .. but that's a thought.
>> >
>> > Nancy
>> >
>> >
>> >
>> >

>>
>>
>>

>
>
>

[mysteriousmongoose]

i may not know much about coding or even about what your coding but in this
paragraph shouldnt the insert be spelt with one E?
'Carry out inseert
strSQL="Insert into Prices(UserID,productID,Price) values ("&
session("UserID") & "," & sProductID &"," & sPrice & ")"
adoCn.Execute strSQL
End If
adoRs.Close
set adoRs=nothing
adoCn.Close
set adoCn=nothing
%>

[Andy]

Thanks Mysteriousmongoose ?

It would make a difference if it were the actual code, but it's just a
comment.

Thanks anyway :-)

Andy

"mysteriousmongoose" <webforumsuser@macromedia.com> wrote in message
news:d5a2hs$l3g$1@forums.macromedia.com...

>i may not know much about coding or even about what your coding but in this
> paragraph shouldnt the insert be spelt with one E?
> 'Carry out inseert
> strSQL="Insert into Prices(UserID,productID,Price) values ("&
> session("UserID") & "," & sProductID &"," & sPrice & ")"
> adoCn.Execute strSQL
> End If
> adoRs.Close
> set adoRs=nothing
> adoCn.Close
> set adoCn=nothing
> %>
>
>
>

[Andy]

Hi Paul

Just wanted to thank you for your patience and help.
The page is now working :-) :-) :-)

I changed the code very slightly which stopped it inserting too many
records. The code below shows the slight change:

I'm sure this code will help with other projects in the future.

Kind Regards and Thanks again

Andy

<%
'Create a connection to your database
dim strConn
strConn= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("../dbase_safe\brochure.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 from Products"
set adoRs=adoCn.Execute(strSQL)


Do While not adoRs.EOF
sProductID=adoRs("productID")

'Carry out insert
strSQL="Insert into Prices(UserID,productID) values ("& session("UserID") &
"," & sProductID &")"
adoCn.Execute strSQL
adoRS.MoveNext
Loop

adoRs.Close
set adoRs=nothing
adoCn.Close
set adoCn=nothing
%>



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

> Thanks Mysteriousmongoose ?
>
> It would make a difference if it were the actual code, but it's just a
> comment.
>
> Thanks anyway :-)
>
> Andy
>
> "mysteriousmongoose" <webforumsuser@macromedia.com> wrote in message
> news:d5a2hs$l3g$1@forums.macromedia.com...

>>i may not know much about coding or even about what your coding but in
>>this
>> paragraph shouldnt the insert be spelt with one E?
>> 'Carry out inseert
>> strSQL="Insert into Prices(UserID,productID,Price) values ("&
>> session("UserID") & "," & sProductID &"," & sPrice & ")"
>> adoCn.Execute strSQL
>> End If
>> adoRs.Close
>> set adoRs=nothing
>> adoCn.Close
>> set adoCn=nothing
>> %>
>>
>>
>>

>
>
>
>

[Paul Whitham TMM]

Andy

Only too happy to be off help.

--
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:d5abnr$51c$1@forums.macromedia.com...

> Hi Paul
>
> Just wanted to thank you for your patience and help.
> The page is now working :-) :-) :-)
>
> I changed the code very slightly which stopped it inserting too many
> records. The code below shows the slight change:
>
> I'm sure this code will help with other projects in the future.
>
> Kind Regards and Thanks again
>
> Andy
>
> <%
> 'Create a connection to your database
> dim strConn
> strConn= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> Server.MapPath("../dbase_safe\brochure.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 from Products"
> set adoRs=adoCn.Execute(strSQL)
>
>
> Do While not adoRs.EOF
> sProductID=adoRs("productID")
>
> 'Carry out insert
> strSQL="Insert into Prices(UserID,productID) values ("& session("UserID")

&

> "," & sProductID &")"
> adoCn.Execute strSQL
> adoRS.MoveNext
> Loop
>
> adoRs.Close
> set adoRs=nothing
> adoCn.Close
> set adoCn=nothing
> %>
>
>
>
> "Andy" <AndyjhughesNOSPAM@ntlworld.com> wrote in message
> news:d5a3ka$mlb$1@forums.macromedia.com...

> > Thanks Mysteriousmongoose ?
> >
> > It would make a difference if it were the actual code, but it's just a
> > comment.
> >
> > Thanks anyway :-)
> >
> > Andy
> >
> > "mysteriousmongoose" <webforumsuser@macromedia.com> wrote in message
> > news:d5a2hs$l3g$1@forums.macromedia.com...

> >>i may not know much about coding or even about what your coding but in
> >>this
> >> paragraph shouldnt the insert be spelt with one E?
> >> 'Carry out inseert
> >> strSQL="Insert into Prices(UserID,productID,Price) values ("&
> >> session("UserID") & "," & sProductID &"," & sPrice & ")"
> >> adoCn.Execute strSQL
> >> End If
> >> adoRs.Close
> >> set adoRs=nothing
> >> adoCn.Close
> >> set adoCn=nothing
> >> %>
> >>
> >>
> >>

> >
> >
> >
> >

>
>
>

[mysteriousmongoose]

yay it says insert! :)