Database queries

[LISATOMAN]

I was wondering if you could help me--i'm trying to perform a query that will search for a grooms or brides surname and then produce the list of items that couple have on their wedding list.

The tables i hve include:
Goods Details: Goods_Id, Categ_Id, Goods_Name,Goods_Desc, Goods,Price, Goods_Make,WeddingCouple#

WeddingCouple: WeddingCouple#,Grooms_Surname, Brides_Surname, DateOfWedding


Basically what i'm trying to is to develop an online wedding list--whereby you can go on the net and search by the grooms or brides surname and it will produce ther wedding list which you can then purchase from.

I'd be so grateful, i've run out of ideas

Many Thanks
ps:i'm using vb6 webclasses

Thanks

[JHausmann]

How about:

SQLString = "select * from GoodsDetails where WeddingCouple# in (select Weddingcouple# from WeddingCouple where Grooms_Surname='" & surname & "' or Brides_Surname='" & surname & "')"

[LISATOMAN]

Originally posted by JHausmann
How about:

SQLString = "select * from GoodsDetails where WeddingCouple# in (select Weddingcouple# from WeddingCouple where Grooms_Surname='" & surname & "' or Brides_Surname='" & surname & "')"

Thanks for your reply--unfortunately i'm getting an error to the effect that-invalid sql statement;expected delete, insert, procedure, select or update"

Any suggestions

Thankyou

LT

[JHausmann]

Can you post the code where you're using this?

[LISATOMAN]

Originally posted by LISATOMAN
Originally posted by JHausmann
How about:

SQLString = "select * from GoodsDetails where WeddingCouple# in (select Weddingcouple# from WeddingCouple where Grooms_Surname='" & surname & "' or Brides_Surname='" & surname & "')"

Thanks for your reply--unfortunately i'm getting an error to the effect that-invalid sql statement;expected delete, insert, procedure, select or update"

Any suggestions

Thankyou

LT


This i swhat i'm using at the minute which simply returns one item that is there and only by the grooms name, i need to search for either bride and groom and show their entire wedding list. I'm using (or should i say tryiny really hard, to use vb6 webclasses!!).

Public Sub GetProductIdsBySearchKeyword(ByVal strKeyWord As String)
Dim i As Integer
Dim sql As String
Dim vntProductIds() As Variant
'**********************************************************
'Purpose: Get product Ids by searching keyword.
'Input: Keyword as strKeyWord.
'**********************************************************

On Error GoTo Error_handler
mvntBookIds = Empty

''This what i have at the minute
sql = "SELECT Goods_id FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _
strKeyWord & "%'" & " ORDER BY Goods_ID"

Set mAdoRst = mAdoConn.Execute(sql, , adCmdText)
mAdoRst.CacheSize = 10
If mAdoRst.RecordCount > 0 Then
mAdoRst.MoveFirst
mAdoRst.PageSize = 10

Dim TotalPages
TotalPages = mAdoRst.PageCount
mAdoRst.AbsolutePage = 1


End If

i = 0
Do While Not mAdoRst.EOF And i < mAdoRst.PageSize
i = i + 1
ReDim Preserve vntProductIds(i)
vntProductIds(i) = mAdoRst!Goods_ID
mAdoRst.MoveNext
Loop
' Do While mAdoRst.EOF <> True
' i = i + 1
' ReDim Preserve vntProductIds(i)
' vntProductIds(i) = mAdoRst!Book_ID
' mAdoRst.MoveNext
' Loop

If i > 0 Then mvntBookIds = vntProductIds()

If mAdoRst.State = adStateOpen Then mAdoRst.Close
Set mAdoRst = Nothing
Exit Sub

Error_handler:
If mAdoRst.State = adStateOpen Then mAdoRst.Close
Set mAdoRst = Nothing
Err.Clear
Err.Raise vbObjectError + 60, "Get product ID", "Error!"

End Sub

Much appreciated

thankyou

LT

[JHausmann]

Your current query indicates that groom_surname is part of goods_details where your first post indicated it was part of another table. Is Bride_surname part of goods_details too? IF so, the following should work:

sql = "SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID"

[LISATOMAN]

Originally posted by JHausmann
Your current query indicates that groom_surname is part of goods_details where your first post indicated it was part of another table. Is Bride_surname part of goods_details too? IF so, the following should work:

sql = "SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID"



Thanks JHausmann i'll try that and get back to you--thanks for taking the time :0)

LT

[LISATOMAN]

Originally posted by LISATOMAN
Originally posted by JHausmann
Your current query indicates that groom_surname is part of goods_details where your first post indicated it was part of another table. Is Bride_surname part of goods_details too? IF so, the following should work:

sql = "SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID"



Thanks JHausmann i'll try that and get back to you--thanks for taking the time :0)

LT

Thankyou you are a wee honey that worked a treat--thanks again.

I thought i'd push my luck and ask you if you knew how to flag the products or remove them--basically if someone orders a gift then i don't want that product to be available anymore. Any suggestions (i'm a novice at this as you can probably tell)

Much appreciated

LT

[LISATOMAN]

Originally posted by LISATOMAN
Originally posted by LISATOMAN
Originally posted by JHausmann
Your current query indicates that groom_surname is part of goods_details where your first post indicated it was part of another table. Is Bride_surname part of goods_details too? IF so, the following should work:

sql = "SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID"



Thanks JHausmann i'll try that and get back to you--thanks for taking the time :0)

LT

Thankyou you are a wee honey that worked a treat--thanks again.

I thought i'd push my luck and ask you if you knew how to flag the products or remove them--basically if someone orders a gift then i don't want that product to be available anymore. Any suggestions (i'm a novice at this as you can probably tell)

Much appreciated

LT




Me again that code worked great it allowed me to search for either the bride or grooms name--the problem is this isn't unique enough i need to to have a text box where you enter the grooms name, the brides name and the date of the wedding. Taking these 3 things producing the list of goods which match all these 3 things.

The code i have for reading in either the bride or grooms surname is:

Private Sub wciSearch_Respond()

'Purpose: Search product by keywords and show the results of search.


Dim strKeyWord As String
Dim vntId As Variant
Dim strProductName As String
Dim strUnitPrice As String
Dim strFilePath As String
Dim intCategoryId As Integer

With Response
.Write "<body BGColor=#FFFFFF>"
.Write "<p>"
.Write "<form action= """ & URLFor(wciSearch) & """ method=POST>"
.Write "<BR>"
.Write "<IMG SRC =Graphic/Search.gif>"
.Write "<font face = arial>To search for a particual Wedding List, type the Grooms or Brides Surname in the box and click on the Search button"
.Write "<p>"
.Write "<B><font face = Arial>Enter Grooms Surname : <input type=text name=Search size=20></font> "
'this is a new line to take in brides name
.Write "<B><font face = Arial>Enter Brides Surname : <input type=text name=Search size=20></font> "
.Write "</font>"
.Write "<input type=submit value=Search> <input type=reset value=Reset>"
.Write "<p>"
.Write "</form>"
End With

strKeyWord = Request.Form("Search")

If strKeyWord <> "" Then
' Dim CurPage
' Const NumPerPage = 10
'
' If Request.QueryString("CurPage") = "" Then
' CurPage = 1
' Else
' CurPage = Request.QueryString("CurPage")
' End If


Call objStore.OpenConnection
Call objStore.GetProductIdsBySearchKeyword(strKeyWord)

If VarType(objStore.ProductIds) <> vbEmpty Then

For Each vntId In objStore.ProductIds

Call objStore.GetProductById(vntId)
Call objStore.CreateProductPicById(vntId)

strProductName = objStore.ProductName
strUnitPrice = objStore.ProductUnitPrice
strFilePath = objStore.PicFilePath
intCategoryId = objStore.CategoryId

With Response
.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=30%> <IMG ALIGN=Right SRC=" & "'" & strFilePath & "'" & ">"
.Write "</td>"
.Write "<td width=70%><p align=center><font color=navy size=4><b>" & strProductName & "</font></B>"
.Write "<p align=center>" & objStore.ProductDescription
.Write "</td>"
.Write "<tr>"
.Write "</TABLE>"
.Write "<form action=""" & URLFor(wciViewItem) & """ method=POST>"
.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=10%> <p align=left>"
.Write "</td>"
.Write "<td width=50%><p align=left><B> Unit Price: " & " £" & strUnitPrice
.Write "</td>"
.Write "<td width=40%> <p align=left>Quantity to Order: "
.Write "<input type=text size=3 name=Quantity Value=1>"
.Write "<input type=hidden size=0 name=ProductName value=" & "'" & strProductName & "'" & ">"
.Write "<input type=hidden size=0 name=UnitPrice value=" & "'" & strUnitPrice & "'" & ">"
.Write "<input type=hidden size=0 name=CurrentCategID value=" & "'" & intCategoryId & "'" & ">"
.Write "</td>"
.Write "</tr>"
.Write "</TABLE>"

.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=10%> <p align=left>"
.Write "</td>"
.Write "<p>"
.Write "<td width=50%><p align=left><B>Product Manfacturer: " & objStore.ProductUnitQuantity
.Write "</td>"
.Write "<td align=left width=40%>"
.Write "<p><input type=submit value=" & """ Add to Cart """ & " > "
.Write "</td>"
.Write "</form>"
.Write "<tr>"
.Write "</TABLE>"
.Write "<HR>"
.Write "<p>"


End With

Next
' If CInt(CurPage) <> 2 Then
' Response.Write "<INPUT type = button NAME=Value=Next Onclick="" document.location.href='Store.ASP? curpage=" & CurPage + 1 & "';"">)"
' End If
'If CInt(CurPage) <> 2 Then
'Response.Write "<Input type = button Value=Next ONCLICK= "" document.location.href='Paging.asp?curpage=" & CurPage + 1 & "';" > ") "
' Response.Write "<Input type = button Value=Next ONCLICK= "" document.location.href='Store.ASP?WCI=wciSearch&WCU & Curpage=" & CurPage + 1 & "';"" > "
' Response.Write "<Input type = Submit NAME=cmdNext Value=Next>"

'End If
Else

With Response

.Write "<p>"
.Write "Sorry, no Surname has been found that matches the keyword you entered."

End With

End If

Call objStore.CloseConnection

End If

End Sub




and the code of the database class is the same as above but with the extra code you give me.


I'd really really appreciate it if you could help me here please.

Thanks for your time

LT

[JHausmann]

Originally posted by LISATOMAN

Thankyou you are a wee honey that worked a treat--thanks again.

I thought i'd push my luck and ask you if you knew how to flag the products or remove them--basically if someone orders a gift then i don't want that product to be available anymore. Any suggestions (i'm a novice at this as you can probably tell)

Much appreciated

LT [/B]

Post your product query.

[JHausmann]

Originally posted by LISATOMAN
Originally posted by LISATOMAN
Originally posted by LISATOMAN
Originally posted by JHausmann
Your current query indicates that groom_surname is part of goods_details where your first post indicated it was part of another table. Is Bride_surname part of goods_details too? IF so, the following should work:

sql = "SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID"



Thanks JHausmann i'll try that and get back to you--thanks for taking the time :0)

LT

Thankyou you are a wee honey that worked a treat--thanks again.

I thought i'd push my luck and ask you if you knew how to flag the products or remove them--basically if someone orders a gift then i don't want that product to be available anymore. Any suggestions (i'm a novice at this as you can probably tell)

Much appreciated

LT




Me again that code worked great it allowed me to search for either the bride or grooms name--the problem is this isn't unique enough i need to to have a text box where you enter the grooms name, the brides name and the date of the wedding. Taking these 3 things producing the list of goods which match all these 3 things.

The code i have for reading in either the bride or grooms surname is:

Private Sub wciSearch_Respond()

'Purpose: Search product by keywords and show the results of search.


Dim strKeyWord As String
Dim vntId As Variant
Dim strProductName As String
Dim strUnitPrice As String
Dim strFilePath As String
Dim intCategoryId As Integer

With Response
.Write "<body BGColor=#FFFFFF>"
.Write "<p>"
.Write "<form action= """ & URLFor(wciSearch) & """ method=POST>"
.Write "<BR>"
.Write "<IMG SRC =Graphic/Search.gif>"
.Write "<font face = arial>To search for a particual Wedding List, type the Grooms or Brides Surname in the box and click on the Search button"
.Write "<p>"
.Write "<B><font face = Arial>Enter Grooms Surname : <input type=text name=Search size=20></font> "
'this is a new line to take in brides name
.Write "<B><font face = Arial>Enter Brides Surname : <input type=text name=Search size=20></font> "
.Write "</font>"
.Write "<input type=submit value=Search> <input type=reset value=Reset>"
.Write "<p>"
.Write "</form>"
End With

strKeyWord = Request.Form("Search")

If strKeyWord <> "" Then
' Dim CurPage
' Const NumPerPage = 10
'
' If Request.QueryString("CurPage") = "" Then
' CurPage = 1
' Else
' CurPage = Request.QueryString("CurPage")
' End If


Call objStore.OpenConnection
Call objStore.GetProductIdsBySearchKeyword(strKeyWord)

If VarType(objStore.ProductIds) <> vbEmpty Then

For Each vntId In objStore.ProductIds

Call objStore.GetProductById(vntId)
Call objStore.CreateProductPicById(vntId)

strProductName = objStore.ProductName
strUnitPrice = objStore.ProductUnitPrice
strFilePath = objStore.PicFilePath
intCategoryId = objStore.CategoryId

With Response
.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=30%> <IMG ALIGN=Right SRC=" & "'" & strFilePath & "'" & ">"
.Write "</td>"
.Write "<td width=70%><p align=center><font color=navy size=4><b>" & strProductName & "</font></B>"
.Write "<p align=center>" & objStore.ProductDescription
.Write "</td>"
.Write "<tr>"
.Write "</TABLE>"
.Write "<form action=""" & URLFor(wciViewItem) & """ method=POST>"
.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=10%> <p align=left>"
.Write "</td>"
.Write "<td width=50%><p align=left><B> Unit Price: " & " £" & strUnitPrice
.Write "</td>"
.Write "<td width=40%> <p align=left>Quantity to Order: "
.Write "<input type=text size=3 name=Quantity Value=1>"
.Write "<input type=hidden size=0 name=ProductName value=" & "'" & strProductName & "'" & ">"
.Write "<input type=hidden size=0 name=UnitPrice value=" & "'" & strUnitPrice & "'" & ">"
.Write "<input type=hidden size=0 name=CurrentCategID value=" & "'" & intCategoryId & "'" & ">"
.Write "</td>"
.Write "</tr>"
.Write "</TABLE>"

.Write "<TABLE border=0 cellpadding=0 cellspacing=0 width=100%>"
.Write "<tr>"
.Write "<td width=10%> <p align=left>"
.Write "</td>"
.Write "<p>"
.Write "<td width=50%><p align=left><B>Product Manfacturer: " & objStore.ProductUnitQuantity
.Write "</td>"
.Write "<td align=left width=40%>"
.Write "<p><input type=submit value=" & """ Add to Cart """ & " > "
.Write "</td>"
.Write "</form>"
.Write "<tr>"
.Write "</TABLE>"
.Write "<HR>"
.Write "<p>"


End With

Next
' If CInt(CurPage) <> 2 Then
' Response.Write "<INPUT type = button NAME=Value=Next Onclick="" document.location.href='Store.ASP? curpage=" & CurPage + 1 & "';"">)"
' End If
'If CInt(CurPage) <> 2 Then
'Response.Write "<Input type = button Value=Next ONCLICK= "" document.location.href='Paging.asp?curpage=" & CurPage + 1 & "';" > ") "
' Response.Write "<Input type = button Value=Next ONCLICK= "" document.location.href='Store.ASP?WCI=wciSearch&WCU & Curpage=" & CurPage + 1 & "';"" > "
' Response.Write "<Input type = Submit NAME=cmdNext Value=Next>"

'End If
Else

With Response

.Write "<p>"
.Write "Sorry, no Surname has been found that matches the keyword you entered."

End With

End If

Call objStore.CloseConnection

End If

End Sub




and the code of the database class is the same as above but with the extra code you give me.


I'd really really appreciate it if you could help me here please.

Thanks for your time





Can you profile your table structures (table name with all the fields [and data types])?

[LISATOMAN]

1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number


Thanks for your help


LT

Speak to you soon

[LISATOMAN]

Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number


Thanks for your help


LT

Speak to you soon




i JHaussman would it be easier if i sent you a zipped up version to your e-mail?

[JHausmann]

Originally posted by LISATOMAN
Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number


Thanks for your help


LT

Speak to you soon




i JHaussman would it be easier if i sent you a zipped up version to your e-mail?

Sure, go ahead.

[LISATOMAN]

Originally posted by JHausmann
Originally posted by LISATOMAN
Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number


Thanks for your help


LT

Speak to you soon




i JHaussman would it be easier if i sent you a zipped up version to your e-mail?

Sure, go ahead.

THANKYOU

fraid e-mail giving me gip--i'll try to be a s quick as i can get this working--don't give up on me!:o)

[JHausmann]

Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number

Speak to you soon


In order to exclude items from selection, you'll need a table that contains all items(GoodsDetails only contains a subset of all possible items for order) for laughs, I'll call it "GoodsMaster". It then becomes a simple matter to exclude prior selected items by doing a sub-query. For example:

sSql= "select * from GoodsMaster where goods_id not in(SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID)"

[LISATOMAN]

Originally posted by JHausmann
Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number

Speak to you soon


In order to exclude items from selection, you'll need a table that contains all items(GoodsDetails only contains a subset of all possible items for order) for laughs, I'll call it "GoodsMaster". It then becomes a simple matter to exclude prior selected items by doing a sub-query. For example:

sSql= "select * from GoodsMaster where goods_id not in(SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID)"


The problem is i need to search for grooms surname and brides surname and the date of the wedding to produce one name so that it is unique?

CAn you send me your e-mail address and i 'll show you the code it'll be easier that way. Thanks time is flying--lisatoman@hotmail.com. Much appreciated

[LISATOMAN]

Originally posted by JHausmann
Originally posted by LISATOMAN
1.CategoryDetails:
Categ_ID(number),Cat_Name(text)

2.GoodsDetails:
Goods_ID, Categ_ID, Goods_Name, Gods_Desc, Goods_Price, Goods_Make, Goods_Pic, Grooms_Surname, Brides_Surname
Number, Number, Text, memo, currency, text, oleextract,text,text

3.OrderDeatails:
OrderNo, FirstName, second name etc(just address details)

4.OrderLines
OrderNo, Goods_ID, QTYOrdered, Price
Number number number currency

5.OrderNumber
LastOrderNo
Number

Speak to you soon


In order to exclude items from selection, you'll need a table that contains all items(GoodsDetails only contains a subset of all possible items for order) for laughs, I'll call it "GoodsMaster". It then becomes a simple matter to exclude prior selected items by doing a sub-query. For example:

sSql= "select * from GoodsMaster where goods_id not in(SELECT * FROM GoodsDetails WHERE Grooms_Surname LIKE " & "'%" & _ strKeyWord & "%' or brides_Surname LIKE " & "'%" & _ strKeyWord & "%'" " ORDER BY Goods_ID)"


The problem is i need to search for grooms surname and brides surname and the date of the wedding to produce one name so that it is unique?

CAn you send me your e-mail address and i 'll show you the code it'll be easier that way. Thanks time is flying-- Much appreciated

[AKA]

I fell that you haved missed something about relational databases. I suggest some small changes to your database.

Add a table holding the wedding information :

Wedding
Wedding_Id, Grooms_Surname, Brides_Surname, Wedding_Date

Use this table for the user to sellect the wedding he is intrested in.

Replace Grooms_Surname, Brides_Surname in GoodsDetails with Wedding_Id and use it from the selected wedding to get all GoodsDetails.

I can give more tips if you want but then you have to tell more about the application. For example are the wedding couple chosing presents from a fixed list or do they put in unique goods.

It also seems strange with price both in GoodsDetails and OrderLines.

[LISATOMAN]

Originally posted by AKA
I fell that you haved missed something about relational databases. I suggest some small changes to your database.

Add a table holding the wedding information :

Wedding
Wedding_Id, Grooms_Surname, Brides_Surname, Wedding_Date

Use this table for the user to sellect the wedding he is intrested in.

Replace Grooms_Surname, Brides_Surname in GoodsDetails with Wedding_Id and use it from the selected wedding to get all GoodsDetails.

I can give more tips if you want but then you have to tell more about the application. For example are the wedding couple chosing presents from a fixed list or do they put in unique goods.

It also seems strange with price both in GoodsDetails and OrderLines.



if you want i can send you the code but in order to do that you'll have to give me your e-mail--thanks for your help appreciated as you may have noticed i'm a complete novice--problem is i ahve to have it finished by tuesday!!hope to hear from you

[LISATOMAN]

Originally posted by LISATOMAN
Originally posted by AKA
I fell that you haved missed something about relational databases. I suggest some small changes to your database.

Add a table holding the wedding information :

Wedding
Wedding_Id, Grooms_Surname, Brides_Surname, Wedding_Date

Use this table for the user to sellect the wedding he is intrested in.

Replace Grooms_Surname, Brides_Surname in GoodsDetails with Wedding_Id and use it from the selected wedding to get all GoodsDetails.

I can give more tips if you want but then you have to tell more about the application. For example are the wedding couple chosing presents from a fixed list or do they put in unique goods.

It also seems strange with price both in GoodsDetails and OrderLines.



if you want i can send you the code but in order to do that you'll have to give me your e-mail--thanks for your help appreciated as you may have noticed i'm a complete novice--problem is i ahve to have it finished by tuesday!!hope to hear from you


the idea is that a user goes into the site enters bride and grooms surnmae and date of the weding to make the search unique which then produces a list of all the goods on theit list which you can order. At the moment i can search by bride or groom surnmae but this obviously isn't unique enough.

so any help is much appreciated

thanks

[LISATOMAN]

Originally posted by LISATOMAN
Originally posted by LISATOMAN
Originally posted by AKA
I fell that you haved missed something about relational databases. I suggest some small changes to your database.

Add a table holding the wedding information :

Wedding
Wedding_Id, Grooms_Surname, Brides_Surname, Wedding_Date

Use this table for the user to sellect the wedding he is intrested in.

Replace Grooms_Surname, Brides_Surname in GoodsDetails with Wedding_Id and use it from the selected wedding to get all GoodsDetails.

I can give more tips if you want but then you have to tell more about the application. For example are the wedding couple chosing presents from a fixed list or do they put in unique goods.

It also seems strange with price both in GoodsDetails and OrderLines.



if you want i can send you the code but in order to do that you'll have to give me your e-mail--thanks for your help appreciated as you may have noticed i'm a complete novice--problem is i ahve to have it finished by tuesday!!hope to hear from you


the idea is that a user goes into the site enters bride and grooms surnmae and date of the weding to make the search unique which then produces a list of all the goods on theit list which you can order. At the moment i can search by bride or groom surnmae but this obviously isn't unique enough.

so any help is much appreciated

thanks

you'll be glad to hear that i've got it to search using those 3 variables--but suggestions are still welcomed and those fro how you remove or flag those goods that have already been purchased.

[AKA]

I still does not understand how you intend to use GoodsDetail, OrderDetail and OrderLine.

But to get information if something is purchased just add a boolean (integer) flag named Purchased to your table and let assume true or false (0 or 1).

Have you moved out the wedding information to a separate table ?

Do you have a web adress for your project ?

[LISATOMAN]

Originally posted by AKA
I still does not understand how you intend to use GoodsDetail, OrderDetail and OrderLine.

But to get information if something is purchased just add a boolean (integer) flag named Purchased to your table and let assume true or false (0 or 1).

Have you moved out the wedding information to a separate table ?

Do you have a web adress for your project ?

THIS IS MY HOTMAIL SEND ME YOUR E-AMIL AND I'LL FORWARD ONTO YOU MY PROJECT-LISATOMAN@HOTMAIL.COM

[LISATOMAN]

Originally posted by AKA
I still does not understand how you intend to use GoodsDetail, OrderDetail and OrderLine.

But to get information if something is purchased just add a boolean (integer) flag named Purchased to your table and let assume true or false (0 or 1).

Have you moved out the wedding information to a separate table ?

Do you have a web adress for your project ?