|
|
I am developing a web application and using mssql as backend.
This is my requirement where i need to create tables so that it will meet my requirment.
I am looking for a efficient method.
I have a usertable where user will get registered Free.They can submit their articles for monthly payment scheme.month calculation is based on the day they have submitted their article.So if they pay for that month, then thier article will be displayed in the web for that whole month and for the next month they have to pay again to intialize the process again.
Usertable articletable accountTable
usercode articlecode AccountCode
since user have to pay every month the relation between article table and account table is 1 ->many, but the problme here is not about inserting the data into the account table but generating it after the end of the month.
i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status.
Thanks and regards
vimal
Help in need is the help indeed
|
|
|
|
|
So, let's get this straight.
1 user can have many articles.
1 article can have many accounts (one for each month)
The bill payment date is based on the day of the month of when the article was first submitted. What happens if the user submits two articles, one on the 5th of some month and one on the 20th of some month? According to what you have above they receive two bills, one for each article? Is this right?
percyvimal wrote:
i need a process to generate where it checks the users date and calculate the month and based on that month have to create a unpaid status.
What do you mean by this? Are you looking for a process to set some flag on the article table to say whether the article has been paid for or not?
Tip: Can I suggest that you use positive names for flags, because that way, when you come to use the value of flags it becomes less confusing - e.g. if(!IsUnpaid) is harder to read than if (IsPaid)
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Thank you mr Colin Angus Mackay
I hope u have understood my requirement.
1. It is a continous process, where each day new user will come and they will register and they will upload articles.
2. So assume yourself as a user.
3.now u are submitting an article and paying on say 10/10/2004
usercode articlecode acccountcode paymentdate expirydate amount Status
10--------23------------123--------10/10/2004----10/11/2004----50$----Paid
This is not a table structure but just i just put to explain.
4.now i check ur status and see that as paid and so i will show ur article in the web.
5.But it should be a continous process where after end of expiry date i need to create a new status as unpaid so that only after paying for next month term i can show that article
6.I need to keep track of all his account details also and hence i cant overwrite on a single record.
7. I hope u have understood my requirment
Usertable article table and accounttable
these are the tables i think i need to create but i dont know how i create the structure and relationship between these tables and what are the new tables i need to create to susport the same
i need your help in designing the tables so that i can manage this process easily and efficetively and ur help in how to go about this process also really welcome
thanks and regards
vimal
Help in need is the help indeed
|
|
|
|
|
The following function will return the ExpiryDate for any article for which the article code is known.
CREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetime
SELECT @ExpiryDate = MAX(ExpiryDate)
FROM Accounts
WHERE ArticleCode = @ArticleCode
RETURN @ExpiryDate
END
When you are creating your article table you can add a calculated column, so that its value is created anytime it is asked for.
The table stubs needed to support this, and the other information you've given me are:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Accounts_Articles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Accounts] DROP CONSTRAINT FK_Accounts_Articles
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Articles_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Articles] DROP CONSTRAINT FK_Articles_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpiryDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ExpiryDate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Accounts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Articles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Articles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.ExpiryDate(@ArticleCode int)
RETURNS datetime
AS
BEGIN
DECLARE @ExpiryDate datetime
SELECT @ExpiryDate = MAX(ExpiryDate)
FROM Accounts
WHERE ArticleCode = @ArticleCode
RETURN @ExpiryDate
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Accounts] (
[AccountCode] [int] NOT NULL ,
[ArticleCode] [int] NOT NULL ,
[Amount] [money] NOT NULL ,
[PaymentDate] [datetime] NOT NULL ,
[ExpiryDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Articles] (
[ArticleCode] [int] NOT NULL ,
[UserCode] [int] NOT NULL ,
[IsPaid] AS (case when (getdate() < [dbo].[ExpiryDate]([ArticleCode])) then 1 else 0 end)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users] (
[UserCode] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Accounts] ADD
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
(
[AccountCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Articles] ADD
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[ArticleCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Accounts] ADD
CONSTRAINT [FK_Accounts_Articles] FOREIGN KEY
(
[ArticleCode]
) REFERENCES [dbo].[Articles] (
[ArticleCode]
)
GO
ALTER TABLE [dbo].[Articles] ADD
CONSTRAINT [FK_Articles_Users] FOREIGN KEY
(
[UserCode]
) REFERENCES [dbo].[Users] (
[UserCode]
)
GO
You obviously need to fill in the other details that you need for other parts of your application, but the above shows the relationships between Users/Articles/Accounts and how to calculate a payment flag.
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Thank you very much,
Your idea abour expiry date really gave me a way to identify a solution for this problem
We will have a menu for admin where on clicking that one ,
i select all the articles whose status is active
i will have the expirydate field for every articles.
I check if expirydate > Currentdate then
1.create a new accountcode and ask the user to pay against that accountcode
2.make the expirtydate.month= expirydate.moth+1(increments the month)
3.make the status of the article inactive
4.when user pay the amount for that account make the article active.
else
no problem with that record for it is to be active
end if
I need your help to find out any problem associated with this method
Thanks a lot mate for your help
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
percyvimal wrote:
I check if expirydate > Currentdate then
1.create a new accountcode and ask the user to pay against that accountcode
So, meanwhile the article goes off line, because it has exprired. I would suggest that you contact the user, say, two weeks in advance of an expiry. And that you have an additional flag in the new account record to say whether it is paid up or not, if not it isn't used in the calculation of the expiry date, if it is paid up the record will reflect that and the expiry date will reflect that.
It really sounds like you need to work on your business model, or converting your business model into a logical model for your system.
If you are not familiar with Use Cases, or don't want to go through the formality of them, can I suggest that you create short stories (about a paragraph each) of what various users of the system will want to do. In each make sure you write in exactly what information is needed and the actions that need to be performed are to achieve the goal. From that you will end up of a list of bits of information, the business rules and the relationships between them.
For instance: A user receives an email telling them their account is going to expire in two weeks time. The user logs on to the system, goes to their account page and renews their subscription.
So, you've got a user, an email address, an account, an expiry date, log-on information, details about a subscription.
A user has an email address.
A user has a username and password (the log-on information)
An account is a synonym for subscription? or not?
And so on.
It is also important to be careful with the terminology. For instance, the relationships between user, account and subscription are (to me):
one user ==> 1..n accounts ==> 1..n subscriptions.
So, if you see in your user stories some ambiguity in the names of things try and straighten it out. The idea of having a different account each month is confusing,
IMO.
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
In a .NET application, I use a DataAdapter to fill a DataTable. I then add a Column to the DataTable. At certain points in the program, I loop through the DataTable and set the value for the newly added column for each DataRow. Although the information stored in this column is within the program only and is not stored in the actual database, setting the value causes the DataRow.RowState property to become DataRowState.Modified. Thus, when the DataAdapter Update method is called, all rows are treated as having been modified, when, in fact, the "real" data is unchanged.
Question:
Is it possible to mark a DataTable Column in some manner that data changes to this column do not cause the DataRow.RowState property to be changed?
One work-around it to note the DataRow.RowState property prior to changing the data in the specific column and to AcceptChanges if the DataRow had been DataRowState.Unchanged prior to the adding of the data in the specific column. (Pretty ugly...)
Thanks for your help!
----
Gerry Roston
Pair of Docs Consulting
www.pairofdocs.net
|
|
|
|
|
<%@ Page CompilerOptions='/R:"C:\Program
Files\Microsoft.NET\Odbc.Net\Microsoft.data.odbc.dll"'%>
<%@ import namespace="System.Web.UI" %>
<%@ import namespace="System.Web.SessionState" %>
<%@ import namespace="System.Web" %>
<%@ import namespace="System.Drawing" %>
<%@ import namespace="System.ComponentModel" %>
<%@ import namespace="System.Collections" %>
<%@ import namespace="System.Text" %>
<%@ import namespace="System.Data" %>
<%@ Import Namespace="Microsoft.Data.Odbc" %>
<%@ Register TagPrefix="mspos" TagName="header"
Src="~/Engine/Controls/header.ascx" %>
<%@ Register TagPrefix="mspos" TagName="footer"
Src="~/Engine/Controls/footer.ascx" %>
<title>
bool CustomAutenticate (string username, string password)
{
OdbcCommand objcmd;
OdbcConnection oConn;
OdbcDataReader objreader;
string strSQL ="select * from TBUSERS where USERNAME='" + username +
"' and PASSWD='" + password + "'";
string sConnString = "DSN=POS;UID=system;PWD=system";
OdbcConnection myConn = new OdbcConnection(sConnString);
OdbcCommand myCmd = new OdbcCommand(strSQL, myConn);
OdbcDataReader dr = null;
try
{
myConn.Open();
dr = myCmd.ExecuteReader();
if(dr.Read()) {
if(dr.GetString(0).Trim() == username.Trim()) {
FormsAuthentication.RedirectFromLoginPage(username,
false);
return(true);
}
else
Output.Text = "Sorry! Your login or password is
incorrect. <br>Please log in again.";
}
else
{
Output.Text = "Sorry! Your login or password is incorrect.
<br>Please log in again.";
}
}
catch(Exception myException) {
Response.Write("Oops. The error: " + myException.Message);
}
finally {
myConn.Close();
}
return false;
}
void OnSubmit (Object sender, EventArgs e)
{
bool Check=false;
Check=CustomAutenticate;
if (Check == true)
Response.Redirect("default.aspx");
}
<mspos:header id="Header1" runat="server">
|
|
| Please
Enter your Username and Password
| |
| User Login
| | User
Name:
| <asp:textbox id="UserName" runat="server"
textmode="SingleLine" maxlength="32" width="150px">
| | Password:
| <asp:textbox id="Password" runat="server"
textmode="Password" maxlength="40" width="150px">
|
| <asp:button text="Submit" onclick="OnSubmit"
runat="server" id="Button">
|
|
<asp:label id="Output" runat="server">
|
|
|
|
|
|
|
I have to create a user Login and Registration Web form. I am having a hard time finding what I need. If anyone on here can stear me in the right direction I would so appreciate it. Thank You
Matt
|
|
|
|
|
//************************************************************
// Get Stores and populate dataset with STOREID and Store
private DataSet BindStores()
{
// Populate the ddlDataSet
const string strSQLDDL = @"SELECT STOREID, STORE FROM TBSTORES ORDER BY STOREID";
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(strSQLDDL, myConnection);
myDataAdapter.Fill(ddlDataSet1, "ddlStores");
myConnection.Dispose();
return ddlDataSet1;
}
//************************************************************
//************************************************************
// Get Stores and populate dataset with ACCCESSLEVELID and ACCESSLEVEL
private DataSet BindAccessLevels()
{
// Populate the ddlDataSet
const string strSQLDDL = @"SELECT ACCESSLEVELID, ACCESSLEVEL From TBACCESSLEVELS ORDER BY ACCESSLEVELID";
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(strSQLDDL, myConnection);
myDataAdapter.Fill(ddlDataSet2, "ddlAccessLevels");
myConnection.Dispose();
return ddlDataSet2;
}
//************************************************************
//************************************************************
// Get Stores and populate dataset with UserName
private DataSet BindUserNames()
{
// Populate the ddlDataSet
const string strSQLDDL = @"SELECT USERNAME From TBUSERS ORDER BY USERNAME";
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(strSQLDDL, myConnection);
myDataAdapter.Fill(ddlDataSet3, "ddlUserNames");
myConnection.Dispose();
return ddlDataSet3;
}
//************************************************************
|
|
|
|
|
ALTER PROCEDURE Blog_GetTopXEntries
(
@num int
)
AS
SELECT TOP @num *
FROM
Blog_Entries
WHERE
(Active = 1)
ORDER BY
Date DESC
I am trying to pass a int to the SP that is a int so that i can select the TOP XX number of records from the table. I am getting incorrect syntax near @num after the top. I am not exactly sure how to handle this. any ideas?
|
|
|
|
|
Apparently, you can't use a variable to set TOP. Put SET ROWCOUNT @num before your select, and remove the TOP clause.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Thank you very much, sir.
|
|
|
|
|
|
I'm currently moving a project over to OLE DB and I've run into a bit of a dilema. I've gotten the basics of OLE down and how to use the accessor to insert into a table. Here's my problem, the table that I'm entering into will be very large once the program starts being used so using the accessor to insert will more than likely kill the computer. The question, is there any way to use the accessor to do the insert without populating it?
|
|
|
|
|
I take it you're using Visual C++, and native OLE DB with ATL? I've inserted rows into large tables before without any problem. You're using a "real" database, right? (I don't consider Microsoft Access or FoxPro as a real database.) I'm confused by this statement:
errenden wrote:
The question, is there any way to use the accessor to do the insert without populating it?
What is it that you don't want to populate? Are you talking about the "generic" query that the accessor uses by default, where it selects every row in the table? And you're concerned about the program selecting every row first, and then adding your new row to the end?
-Thomas
|
|
|
|
|
I'm using Oracle 9i on the backend so no worries there. But yes I'm concerned about the memory usage/performance with the accessor selecting every row just to do an insert. I've thought about just doing performing a specific SELECT statement to pull some set default row in the database but that solution seems clunky at best.
|
|
|
|
|
Beautiful! I use Oracle also. (I also had better luck using Oracle's provider, instead of the Microsoft OLE DB for Oracle provider.)
I've actually used that method (pull a default row)- I take the default "take everything" accessor, and change it into a "get me the row with this primary key" parameterized query. If the accessor returns a row, I know that the data already existed in the table. If the rowset is empty, then I set the values up and update the row. Of course, you can handle this by checking out HRESULT and etc to determine if the row already exists...
You probably want to create a command object instead of a rowset object. That way you can do direct insert/update/deletes, as well as run stored procedures. In fact, you might want to turn your insert into a stored procedure! Either way, at the top of the accessor, add in all your member variables (TCHARs, DOUBLEs, etc) just as if you were dealing with a rowset accessor. In fact, you might already have this if you created a default rowset accessor! Then, define your parameter map...
BEGIN_PARAM_MAP(CNAMEOFACCESSORAccessor)<br />
COLUMN_ENTRY(1, mFIRSTFIELD)<br />
COLUMN_ENTRY(2, mSECONDFIELD)<br />
COLUMN_ENTRY(ETC...)<br />
END_PARAM_MAP()
Make sure you defined mFIRSTFIELD and mSECONDFIELD and etc first.
Now, change up the default "rowset" command that you were given.
DEFINE_COMMAND(CNAMEOFACCESSORAccessor, _T("INSERT INTO MYTABLE VALUES (?, ?)"))
Look down at the in the command class that ATL built for the OpenRowset method. Look for
return CCommand<CAccessor<CNAMEOFACCESSORAccessor>>::Open(mSession);
You've gotta change the CCommand::Open parameters. (Look it up in the MSDN, search for "CCommand::Open".) Leave the first parameter as m_session. You can set the second parameter, szCommand, to NULL because you're using the Accessor for the command. The third parameter would be for a property set, if you defined any. (If you're calling an insert, I'd think you would... I used stored procedures so I can't say for sure, but I think you'd need DBPROP_UPDATABILITY.) The fourth parameter's important, that's a pointer (pRowsAffected) to the number of rows that got DML performed. In your case, this should come back as 1 for a successful insert. (Negative 1 means no rows affected, as does a NULL pointer.)
Hope this helps; or at least gives you something to search the VC++ help files and the web for!
Oh- for the code where I queried first for the data, and if I got no rows back, then I'd insert the data- I sorta cheated. (laughs) I defined an LPCSTR inside of the CCommand called MyQuery. Then, in OpenRowSet, instead of the CCommand<CAccessor<blah blah>>::Open(mSession) I would do CCommand<CAccessor<blah blah>>::Open(mSession, MyQuery) instead.
In my main code, before I did a m_MyObject.Open(), I'd first do m_MyObject.MyQuery = "select these,columns from this.table where pk1=? and pk2=?". (You need ?'s for placeholders.) Then I'd assign values for my m_commandMyObject's parameters- the ones in the param list- like _tcscpy(m_commandMyObject.m_PK1,W2T(BstrVariable)). You've gotta do that for each parameter! Finally I'd do m_commandMyObject.Open(). This would load up my accessor's column map with the row matching pk1 and pk2, OR, it would have null rows. Once it was opened successfully, I'd check the rowset by doing: if (m_MyObject.MoveFirst()==S_OK) then (show an error because the row existed already) else (update rowset). And to update the rowset, all I had to do was set the column values of the accessor, and call m_MyObject.SetData.
This allowed me to work with just one row at a time; fetch the row from the database based on the order number; if the rowset comes back empty, then it's okay to insert the row!
One more thing- if you do decide to use stored procedures, you'll need to add extra lines to your param_map. BEFORE each COLUMN_ENTRY(...) line in the PARAM_MAP, you need to tell the provider which direction that parameter will go. Here's an example, first variable is sent up to Oracle, second variable comes back from Oracle:
BEGIN_PARAM_MAP(CMYTABLEAccessor)<br />
SET_PARAM_TYPE(DBPARAMIO_INPUT)<br />
COLUMN_ENTRY(1, mFIRSTFIELD_IN)<br />
SET PARAM_TYPE(DBPARAMIO_OUTPUT)<br />
COLUMN_ENTRY(2, mRESULTVAR_OUT)<br />
END_PARAM_MAP()
So I hope all that helps; if you search the Visual Studio help files (or even msdn.microsoft.com) for CCommand::Open , all of this should be more clear. But you're correct, you don't have to do anything with a rowset if you're just inserting data. And you might want to use stored procedures; that way, you let Oracle take care of wondering if a row already exists or not. If you can find it, look for the "Microsoft OLE DB 2.0 Programmer Reference and Data Access SDK" book- it's been out of print for a while, so it's hard to find. To paraphrase every ADO book, "OLE DB is the best for database access. Since it's so hard, we'll use ADO in this book." That killed off the OLE DB books pretty quickly! I found mine on eBay for cheap. The ISBN is 0-7356-0590-4, but the info in the book is still available on msdn.microsoft.com, in the VC++ 6.0 help files, and I just checked my .NET MSDN help files- it's in there, too.
Let me know how it goes! Also, just curious- why are you using OLE DB? It seems like the .NET stuff has become faster than OLE DB; I'm actually transitioning my OLE DB VC++ programming to the .NET Framework. (I'm working right now with C#.NET and ADO.NET, once I get a little better with it, I'll turn it into Managed C++ code.)
Wow, this is one long reply.
-Thomas
|
|
|
|
|
I found some stuff yesterday on binding paramaters in OLE DB and started to tinker with it. Your reply helps parse out some of what I've read, especially with the pRowAffected variable (I had completely missed it the first time around). I'll check out that book later today and see about picking it up.
Maybe you could also answer this question since finding info good solid info on OLE DB seems to be very elusive. I've been doing select statements using the Create command and binding two parameters, which I added to accessor .h file. Now I've attempted to expand this out to do a insert which requires four parameters, I added the other two parameters to the accessor file and now the select won't work unless I include the other two parameters I just added in, which defeats the purpose of the select. Thanks for all the help so far, I appreciate it.
To answer your last question, the reason we haven't moved over to C# and the whole .NET framework is due to the fact that .NET may not run on all of the client machines (some being very old and some may be running unix) and the fact that some of the current code uses lisp and we don't have the time to make sure C#.NET will run with it. Fun stuff. Once again, thanks for the help.
|
|
|
|
|
I'm doing some homework (well kind of homework, its not graded or handed in, but recommended that you do it) and I know how do all the basic stuff but I want to put it all in a transaction that can rollback if something goes wrong. I know some of the keywords BEGIN TRANSACTION, ROLLBACK, COMMIT but I just don't know how to form it etc. Any help would be appreciated.
Matt Newman
...armed with what? spitballs!? - Zell Miller
|
|
|
|
|
Well, it depends on what you are doing. However the basics are the same regardless
BEGIN TRANSACTION
-- Do stuff
IF @SOME_SUCCESS_CONDITION
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
Obviously you can check for failures at each point you do something with the database and rollback and exit there if it didn't work. The idea being that either everything works, or it appears that nothing ever happened.
Do you want to know more?
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Ok, basically I'm just doing insert commands, do I have to check if the inserts succeeded or if it fails will it automatically go to ROLLBACK? I guess I'm just not sure how to get the success condition.
Matt Newman
...armed with what? spitballs!? - Zell Miller
|
|
|
|
|
If it is a single command then you don't need to transact it. SQL Server will do that for you (hence Transacted-SQL).
If you have a stored procedure with more than one command then you can check each command has succeeded e.g.
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"
IF @@ERROR = 547
print "A check constraint violation occurred"
Remember that the @@ERROR is only for the last command, so if you want to check its value a number of times in order to, say, perform different actions depending on what the error is you need to store it in the following statement.
DECLARE @errorCode int
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"
SELECT @errorCode = @@ERROR
IF @errorCode = 0
COMMIT TRANSACTION
IF @errorCode = 547
ROLLBACK TRANSACTION
Does this help?
Do you want to know more?
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
