Database

How should my database like

29-Apr-06 5:23

Toasty0 wrote:
Maybe it doesn't come with the VS2006 version he says he is using.

Of course! I completely forgot about that! Roll eyes | :rolleyes:

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

LovelyHelp28-Apr-06 20:06

28-Apr-06 20:06

I am using sql 2000. I have a scenario is 1 user can buy many or 0 'package'.
and 1 'package' can have 0 or many users. If the user didnt buy the 'package' then he/she will not allow to use the package.

How should I create my database. How is my database look like.

Colin Angus Mackay28-Apr-06 21:08

28-Apr-06 21:08

In other words a many-to-many join

Here are the key columns in use and their tables

User: UserID
Package: PackageID
UserPackage: UserID, PackageID

The UserPackage table is the intermediate table in the many-to-many join. It has a compound key consisting of both sets of primary keys. When you need to join a user to a package you put a row in the UserPackage table.

Does this help?

LovelyHelp29-Apr-06 3:45

29-Apr-06 3:45

I am actually creating my project in .net.
ok now i have my sql command as like this

SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX

for XXXXXXXX what should I put? my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.

Colin Angus Mackay29-Apr-06 4:03

29-Apr-06 4:03

LovelyHelp wrote:
I am actually creating my project in .net

I never doubted it.

LovelyHelp wrote:
SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX

Using SELECT * is a bad idea - if your data model changes the application that is built on top may break becuase of columns being returned that it did not expect, or columns that no longer exist. You should always list the columns you want - This also has the potential benefit of reducing network bandwidth necessary to transmit columns that will get discarded on reaching the application.

Also, when you join two or more tables together you will often get situations where there are columns with the same name. If you list columns explicitly you get the opportunity to rename columns.

The other point about this code is that it looks like it is susceptable to a SQL Injection Attack and you don't want your database to be compromised. See here on how to prevent SQL Injection Attacks[^]

LovelyHelp wrote:
for XXXXXXXX what should I put?my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.

I don't know what to put for the XXXXX because it is your application and you haven't told me how it handles products. I'll assume that your application knows the productID (because that's what the code looks like it should know)

Also, from your code it looks like the user name is the key on the user table. (I would recommend keying on a number as it is more efficient than keying on a string)

Anyway your query is very simple:

SELECT COUNT(*) FROM t_userPackage WHERE userID = @userName AND packageID = @packageID

I'm assuming that if the user pays for a package that a join beween the user and package tables will exist. That being the case, the code above will return 0 if the user has not paid for the package. It will return 1 if the user has paid for the package.

I've also used parameters in the query as a part defence against SQL Injection Attacks. In your .NET code you can add parameters to your SqlCommand object like this:

cmd.Parameters.Add("@userName", User.Identity.Name);
cmd.Parameters.Add("@productID", productID);

If any of the assumptions I have made are wrong then you will have to explain your business logic and data model further because I'm using very limited information to work out how to answer your question.

Does this help?

LovelyHelp29-Apr-06 4:31

29-Apr-06 4:31

I am very new to asp.net and database. Thanks alots and I have learn from you.

actually for my function it have something like when user click on the link of particular package then it will run the sql command to check whether have the user pay for the particular package.

I am trying to use dataList to build a set of my list
<asp:datalist id="DataList" runat="server">
<itemtemplate>
<asp:buttonlink id="mylist" text="<%#container.dataitem("packageName") %>">

so what will be my XXXXXX?

is this two line of code
cmd.Parameters.Add("@userName", User.Identity.Name);
cmd.Parameters.Add("@productID", productID);
same as
cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name
cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX

I think you are using c right. anyway your technique is new to me.

Paul Conrad29-Apr-06 4:52

Paul Conrad

29-Apr-06 4:52

LovelyHelp wrote:
so what will be my XXXXXX

Whatever the value of your productID is going to be.

LovelyHelp wrote:
I think you are using c right.

He is using C# which is in the same family of languages as C/C++. Many similarities as well as differences.

Paul

Colin Angus Mackay29-Apr-06 5:11

29-Apr-06 5:11

LovelyHelp wrote:
is this two line of code
cmd.Parameters.Add("@userName", User.Identity.Name);
cmd.Parameters.Add("@productID", productID);
same as
cmdSelect.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = User.Identity.Name
cmdSelect.Parameters.Add("@productID", SqlDbType.Int, 4).Value = XXXXXX

Your two lines of code are slightly more efficient because the application does not need to do a roundtrip to the database to get the types of the parameter because you supply them. The code I put was for simplicity.

Also, I don't see your code because you have not escaped the < symbols - You need to replace them with < so they show up.

LovelyHelp wrote:
I think you are using c right

I'm using C#

LovelyHelp29-Apr-06 5:32

29-Apr-06 5:32

oh sorry..
my code is like

<ItemTemplate>
<asp:buttonlink id="mylist" text='<%#container.dataitem("packageName") %>' ></asp:buttonlink>

</ItemTemplate>

</asp:DataList>

but i think asp:buttonlink is not allow inside itemtemplate

LovelyHelp29-Apr-06 4:36

29-Apr-06 4:36

I have some doubted.

why are u using SELECT count(*)...? Does count mean counting?.

and

userID = @userName AND packageID = @packageID
userID is int and can it compare with @username which it is varchar?

in my database table. I have create something like
t_package: packageID (pk), packageName
t_user: userID (pk), userName, password, gender....
t_userPackage: userID, packageID

Colin Angus Mackay29-Apr-06 5:19

29-Apr-06 5:19

LovelyHelp wrote:
Does count mean counting?

Yes, I want the number of links between the tables for a secific package and user combination.

LovelyHelp wrote:
userID is int and can it compare with @username which it is varchar?

Right - We are finally beginning to get somewhere with what the data model is.

Assumptions:
The application knows about user names and package names and nothing about IDs.

SELECT COUNT(*)
FROM t_userPackage AS up
INNER JOIN t_user AS u ON up.userID = u.userID
INNER JOIN t_package AS p ON up.packageID = p.packageID
WHERE u.userName = @userName
AND p.packageName = @packageName

From the .NET application, add parameters for @userName and @packageName with information in the application.

The result from the query is 0 (there is NO association between the user and package - assumming this means they have not paid for it) or 1 (there IS an associtation between the user and package - assuming this means they have paid for it)

If an association can exist between a user and package with the user NOT paying for the package then you need to explain that so the SQL can be modified to get that result.

Does this help?

LovelyHelp29-Apr-06 5:41

Urgent help from an expert!

29-Apr-06 5:41

OH many to many is such a complicated relationship for me.

maybe should we start with the UI first. To display my item, should I use datalist or datagrid?

I need to show something like:

Tutorial's name
Tutorial description but is optional
2 button or maybe link. Where 1st link will be proceed to tutorial if user already pay for it and another link will be purchasing link where it purchase the tutorial.

so there is different tutorial.

so when example like i have paid for tutorial1 and not paid for tutorial2 then when i click on link 'proceed' for tutorial1 then I will automatically go to tutorial1's pages if i click on tutorial2's 'proceed' button then it will redirect to purchasing pages.

JUNEYT28-Apr-06 14:16

JUNEYT

28-Apr-06 14:16

Hello!

This sample procedure extracts email addresses and adds to database. However, it is successfully adding the first address and creating empty records for the others. I decided to test the procedure with vb.net and took out adding the database section. It worked perfectly. So I would like to know what is causing not to add other emails except the first one. if you think xadd="" causing. The answer is no.

Thank You

Sub AddEmails()

Dim GetEmails
Dim SrchKey
Dim xPos
Dim x1, x2
Dim xAddr

GetEmails = Request.Form("emails")

xPos = 1
x1=Instr(xpos,GetEmails,VbCrLf)

DO WHILE xpos <= len(Getemails)

x1=Instr(xPos,GetEmails,vbCrLf)
x2=Instr(xPos+1 ,GetEmails,vbCrLf)

If x1 = 0 or x2 = 0 Then Exit Do

If xPos = 1 Then
xAddr = Mid(GetEmails,1,x1-1)
Elseif xpos > 1 Then
xAddr = Mid(GetEmails, x1, x2 - x1)
End If

If len(xAddr) > 4 Then

'check here if email address is not exist in the database

SrchKey = "SELECT * FROM emails WHERE email="& "'" & xAddr & "';"

Call InitializeConnection()
Call Establish_Recording(SrchKey)

If ObjectRecord.RecordCount <= 0 Then

ObjectRecord.Close
ObjectConnection.Close
Set ObjectRecord = Nothing

Call InitializeConnection()
Call InitializeRecordSet("Emails")

'PROBLEM IS CAUSED BY THE FOLLOWING STATEMENTS
'IT IS ADDING THE FIRST ADDRESS BUT NOT THE OTHERS

ObjectRecord.AddNew
ObjectRecord("email") = xAddr
ObjectRecord("Accept") = True

ObjectRecord.update

ObjectRecord.Close
ObjectConnection.Close
Set ObjectRecord = Nothing

Else
ObjectConnection.Close
End If

xAddr = ""
xpos = x2

End If
LOOP

Response.Write ("Email have been added!")
Response.End

End Sub

MS Access and C# Help

achrafus28-Apr-06 11:38

achrafus

28-Apr-06 11:38

hello every body, I usually work with sql server so if anybody may help with MS Access I'd be thankful.
here 's my C# potion of code:
string selectId = "select categoryId from Category where categoryName = @catName";
myCommand = new OdbcCommand(selectId,myConn);
myCommand.Parameters.Add("@catName",categoryName.ToString());
myCommand.prepare();
myReader = myCommand.ExecuteReader();
at this level an exeption is thrown at the level of the odbc driver it says not enough parameters or something. 1 parameter missing. In my string i'm having only one parameter and it's @catName.
in the definition of the Add()function it says that the second parameter is the value of the named parameter. The function that contains this code receives the value of categoryName however it's not set! can anybody help pls

Re: MS Access and C# Help

Colin Angus Mackay28-Apr-06 21:14

How to FTP a File using SQL Server 2000?

28-Apr-06 21:14

achrafus wrote:
the odbc driver it says not enough parameters or something.

That's a bit vague - What is the EXACT error message? (From other things you've said I think I know what the problem is, but this kind of vague reference to an error message really is not useful and can irritate many people. There are often two similar error messages where the actual root cause is substantially different)

achrafus wrote:
In my string i'm having only one parameter and it's @catName

Access does not use named parameters, use ? (the question-mark) and add the parameters in the order they appear in the query.

Access Databases

QC_200028-Apr-06 4:00

QC_2000

28-Apr-06 4:00

Hi,

I'm writing a Windows application that's a front end interface for an Access database. I'm using VB.NET and I'm getting a very strange error when I try to edit a record in one of my tables. The error says:

"5 - Cannot set 'Discrepancy'"

Discrepancy is the column name.

The strange thing is that the line of code that's raising the error is within a Try, Catch, End Try block but the error is raised without the code execution passing through the Catch part of the block! And even stranger than that, the new value that I'm setting to the 'Discrepancy' field is being set, even though the error message says that it can't set it!!

I've checked, double-checked, and triple-checked all of the usual suspects (spelling, table mapping, schema, oleadapter configuration, etc) and everything seems fine. I can't for the life of me see what is wrong.

Can anyone help please?

I've included my code below. The line that's generating the error is the one with the EndEdit() method. I've also tried checking for errors on the line immediately before the EndEdit() method and there were none.

Try
Dim Record As DataRow = Me.dtsRent.Tables(0).Rows(Me.cmbWeek.SelectedIndex)
Dim Balanced as String
If CInt(Me.txtRentDisc.Text) = 0 Then Balanced = "Yes" Else Balanced = "No"
Record.BeginEdit()
Record.Item("Modified By") = Profile("Officer").ToString
Record.Item("Date Modified") = DateTime.Now.ToString
Record.Item("Reconciled") = Balanced
Record.Item("Total Payments") = CDec(Me.txtRentPayments.Text)
Record.Item("HBP19 Controls") = CDec(Me.txtRentHB19.Text)
Record.Item("Discrepancy") = CDec(Me.txtRentDisc.Text)
Record.EndEdit()
Me.adpRent.Update(Me.dtsRent)
Catch ex As Exception
MsgBox(Err.Number & " - " & Err.Description)
End Try

Thanks in advance.
QC

Re: Access Databases

Rob Graham28-Apr-06 5:41

Rob Graham

28-Apr-06 5:41

Try surrounding the field name with square brackets - "[Discrepancy]". Access has some weird keywords...

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Bulls make money, bears make money, pigs get slaughtered.
Jim Cramer

Re: Access Databases

QC_200028-Apr-06 5:59

QC_2000

28-Apr-06 5:59

Thanks Rob,

However, the field/column name is a string data type in VB. Including the square brackets in the string would make them part of the field/column name, rendering it unrecognisable (if that's a real word). Square brackets can only be used in an SQL command.

QC

-- modified at 11:59 Friday 28th April, 2006

pubududilena28-Apr-06 3:57

pubududilena

28-Apr-06 3:57

hi all,

I need to FTP a File from another Computer.But the problem is we have to do it using SQL Server 2000.

Can anyone tell me that How can I do it using Sql Server 2000 and SQL?

thanks in advance.

Unpredicted behaviour i IN clause

qur28-Apr-06 2:42

qur

28-Apr-06 2:42

Please see my problem below:

Table structure:
tmpItemPrice(ItemPriceID bigint not null , Thickness varchar(255))

Row1
ItemPriceID: 1
Thickness: xyz

Select ItemPriceID
From tmpItemPrice
Where IsNumeric(Thickness) = 1 and Cast(Thickness as float) <= 0

When above query runs it give my type conversion error which is logical as Thickness is not numeric. But when we use this query in IN clause as shown below it works fine.

select *
from tmpItemPrice
Where ItemPriceID in ( Select ItemPriceID
From tmpItemPrice
Where IsNumeric(Thickness) = 1 and Cast(Thickness as float) <= 0 )

So my question are:
- what is the reason behind it?
- Is it right to use it in this way?

Thanks

search case insensitive in access

klakero28-Apr-06 1:16

klakero

28-Apr-06 1:16

does anyone know how to search with case insensitive in access 2000 ?

Re: search case insensitive in access

Eric Dahlvang28-Apr-06 5:47

Eric Dahlvang

28-Apr-06 5:47

Do you mean how to search case sensitive?

I believe MS Access is case insensitive by default.

What kind of search exactly are you trying to do?

----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin

Enterprise library

WDI27-Apr-06 23:33

WDI

27-Apr-06 23:33

how much does Enterprise library affect on performance of the application????

Has anyone used it ?

Query on UNION Query

DiligenZ27-Apr-06 19:00

DiligenZ

27-Apr-06 19:00

Hi,
will the usage of UNION query affect the program throug put efficiency?how to over come that?
Thanks,
DiligenZ

Re: Query on UNION Query

Colin Angus Mackay27-Apr-06 20:38