|
Try This
<br />
filter = "COL1 = 1 AND IsNull(COL2,-1)=-1" (if COL2 is numeric)<br />
<br />
filter = "COL1 = 1 AND IsNull(COL2,'')=''" (if COL2 is string)<br />
Free your mind...
|
|
|
|
|
Shouldn't it be "COL2 IS NULL" ?
|
|
|
|
|
thanks to everyone.... 
it's so easy .... 
|
|
|
|
|
Hi,
I am new to OLE DB. I created a simple stored procedure that insert
given data to a table. when I tried to call Open function of the wizard
generated ole db class, it fails with a HRESULT value : [-2147217887]. (the
error lookup tool returns 'No such Message' for this value). While debuging
i found, the Execute function is returning this value.
My code is given below. Is it correct ?
CdbospTestTable1 m_db;
m_db.m_test1 = 111;
m_db.m_test2 = 222;
HRESULT hr = m_db.Open();
if(FAILED(hr))
AfxMessageBox("Error : Open");
When I created OLE DB consumer class, selecting table, then the insertion
succeeded.
The creation of the class was :
Insert => New Atl Object .
Data Access : Consumer
OLE DB provider : OLE DB provider for Sql Server
Type property : Command
Support : change, insert and delete
Then selected the Stored Procedure name.
Thanks and Regards
Sudheer
|
|
|
|
|
Suppose if you are discussing about error message then you need to include what that message is. Without that we can't figurout that. what is the reason. Please try to include the error message.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
sreejith ss nair wrote:
if you are discussing about error message then you need to include what that message is. Without that we can't figurout that. what is the reason. Please try to include the error message.
He did include the error message: "it fails with a HRESULT value : [-2147217887]. (the error lookup tool returns 'No such Message' for this value)."
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.
|
|
|
|
|
i made uploading tool using freeaspupload component and i customized it to store the uploaded files to a DB using ADO, i had the webserver (IIS) and the sqlserver 2000 on the same machine and it was working fine ,no problems at all , but when i moved the DB to the production server (the webserver and the DB on separate machines) the upload failed , it started to give me a syntax error msg:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'
(file is the field i use to store my files to) i tried to use appendChunk but still the same error
|
|
|
|
|
Hello everyone, I'd like your opinions! I'm rewriting a program that I did back in 1996; it takes a large "external-system" text file, and compares it against our database. If there's a difference between the file entry and database entry, we'll take the entry in the file and put it into the DB, and make note of it on a report. We don't run this program often, and we always take the file as the "correct" data. I'd like to load the file into an ADO.NET dataset, but I'm a little hesitant because of the average file size.
The size of the flat file is usually between 2 and 4 MB large. Is this an okay size for a dataset in memory? I'd want to load up the entire file at one time into a dataset. Then I'd set up a DataAdapter and DataSet and etc for my database, and compare both datasets together. I could just call DataAdapter.Update on the database's dataset to make the changes. So not only would I have the 2-4 MB flatfile in memory, but I'd also potentially have the same size (2-4MB) from the database! The benefit is that I'd really be "using" ADO.NET... comparisions would be easier, and the update would be cleaner.
My alternative is to open the file, and read it one record at a time. I'd then do an executescalar to check our database, and another executescalar if I had to update our database. This method seems memory-friendly, but database-intensive and network-intensive because of all the individual transactional updates... I'd have to update the database one-record-at-a-time!
Do any of you load up huge 2-4 MB text files into a dataset? If so, do you have any problems with this approach? I really want to use datasets- but this program will be running on workstations, on an as-required basis- it won't be running on a server, so I don't want to write a program that takes forever to run. The workstations are semi-powerful; but this "might" have to run on an XP Pro box with only 256MB of memory. (Most of the XP Pros have 512 mb ram.)
Oh, I'll be using C#.NET and ADO.NET under .NET Framework version 1.1. The file isn't in XML, and I think it would be pointless to convert it to XML first, and then process it. Correct me if I'm wrong, though!
Thanks for any ideas!
-Thomas
|
|
|
|
|
Hi folks,
Pretty newbie on everything and here goes my first question.
I could not find similar question, but if someone has asked this allready before, please give me a link where to find answer to my problem 
So I have this problem that I'm trying to get Year from a database into a dataset. So how can I separate year from the datetime value in the sql statement?
I have tried SUBTRING and EXTRACT but those are not working at all.
I'm doing my project with vb.net
Below is the sample I've tried with no luck.
select firstname + ' ' + Lastname As Name, EXTRACT(YEAR FROM Birthday) as Year, Email from CardTable ORDER By Cardnumber Asc
Gamoss, Icq: 32048647
|
|
|
|
|
What database are you using? If you are using MS SQL Server then you would have the following:
<br />
SELECT firstname + ' ' + Lastname as Name, Year(Birthday) as Year, Email <br />
FROM CardTable<br />
ORDER BY Cardnumber Asc<br />
Jeremy Oldham
|
|
|
|
|
Yes that was the server and it worked. That was really a fast reply. Thanks a lot!
Gamoss, Icq: 32048647
|
|
|
|
|
Is it possible to write a script or other program that shutdown and restart a SQL server?
[Edit]
Actually, what I want is detach and reattach one database on the SQL server from a program, not shutting down all the databases.
I already know shutting down the whole SQL server is equivalent to shutting down the corresponding windows service, which I cannot do in this case.
[/Edit]
My articles and software tools
|
|
|
|
|
Try using SQLDMO, it's a set of COM classes for managing SQL Server you can find all the info in MSDN. Check out specifically the AttachDB & DetachDB methods of the SQLServer object or class. Hope this helps.
|
|
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
