|
I have an sql statement comparing dates in the Where clause.
This is my code:
[CODE]
CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2[/CODE]
The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?
|
|
|
|
|
toink toink wrote: The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?
That is correct. A NULL in a set based operation always yields NULL.
toink toink wrote: CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2
Why are you converting Db.Spd_deldateans to an NVARCHAR?
If D.Spd_deldateanes is already a DATETIME or SMALLDATETIME then
WHERE
(D.Spd_deldateans >= @DATE1 OR @DATE1 IS NULL) AND
(D.Spd_deldateans <= @DATE2 OR @DATE2 IS NULL)
What this does is say the equivalent of
IF D.Spd_deldateans is greater than or equal to @DATE1 OR @DATE1 IS NULL
which evaluates to TRUE if @DATE1 is null which means that the >= operation is effectively overriden
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
how to use now() function in the Access when I write SQL?
look at this:
insert into table(LoginTime)values(now())
it's right?
KSCsoft
|
|
|
|
|
insert into table(LoginTime)values(now())
Instead of this u can use following statement
insert into table(LoginTime)values(now)
Kumar
|
|
|
|
|
insert into table(LoginTime)values(now())
Instead of this u can use following statement
insert into table(LoginTime)values(now)
Kumar
|
|
|
|
|
I have a table with data from five sources, and a column specifying which one.
I need to compare this data for missing rows (Present in one source but not another one), and for different values. I would like to do this entirely in SQL.
The database is in Sybase, in which the SQL syntax differs slightly from Access (eg, no semicolons)
|
|
|
|
|
I having problems with getting Front Page 98’s hover buttons to function. So i need your help.i hope anyone can solve my problem qickly.please help me.i really need your help.this is the code.
Applet codes of my web page is as follow:
<applet code="fphover.class" codebase="./" width="120" height="24">
<param name="color" value="#000080" />
<param name="hovercolor" value="#0000FF" />
<param name="textcolor" value="#FFFFFF" />
<param name="text" value="C Programming" />
<param name="effect" value="glow" />
<param name="url" valuetype="ref" value="http://metalab.uniten.edu.my/~azhana/indexCProg.html" />
thank a alot to whom reply it....
|
|
|
|
|
Uhhh, what does this have to do with SQL / ADO / ADO.NET?
|
|
|
|
|
Try this forum...[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I'm teaching myself ADO .NET, and am struggling with how to programmatically update a dataset after I have cached it and recovered it in an ASP .NET web app.
I want to use a DataAdapter to populate a DataSet, cache the DataSet in Session, then recover the DataSet in a different page, work on it, and update the database. I can populate the DataSet, cache it, recover it, and make changes. However, I cannot get the adapter.update(dataset, "table") command to work properly. Have tried multiple solutions, with multiple errors revolving around lack of INSERT commands, uninitialized connection strings, etc.
What programmatic steps must I take in a new page in order to reconnect that dataset with the origin database? Do I have to instantiate a new DataAdapter, build new commands, open a new connection, etc? Can I cache and recover any of the relevant objects (DataAdapter, SqlCommand, commandBuilder) and use them?
Here's the code to populate and cache the DataSet. It works fine (but may be excessive):
using (SqlConnection connection = new SqlConnection(connStr))
{
try
{
SqlDataAdapter pointAdapter = new SqlDataAdapter();
pointAdapter.TableMappings.Add("Table", "UserPoints");
SqlCommand pointCommand = new SqlCommand();
pointCommand.CommandText = "SELECT * FROM Points WHERE userID = @userID";
pointCommand.Parameters.AddWithValue("@userID", userIDstr);
pointCommand.Connection = connection;
pointCommand.CommandType = CommandType.Text;
pointAdapter.SelectCommand = pointCommand;
SqlCommandBuilder pointCmdBuilder = new SqlCommandBuilder(pointAdapter);
pointAdapter.Fill(ds);
Session["UserDS"] = ds;
And here's what I'm trying to do in a different page:
dataset = (DataSet)Session["UserDS"];
using (SqlConnection connection = new SqlConnection(connStr))
{
try
{
SqlDataAdapter pointAdapter = (SqlDataAdapter)Session["pointAdapter"];
pointAdapter.Update(dataset, "UserPoints");
With this code I get a "connection string uninitialized" error. But I've gotten several different errors, enough to convince me I'm going about this the hard way. Is there a standard pattern for this sort of thing, or a tutorial someone can point me to?
|
|
|
|
|
In your second code you're creating the instance of the SqlConnection class (initialised with the connection string) and never use it due to the fact that SqlDataAdapter object is restored from the session state.
The standard aproach in hadling data with the ADO.NET is that simple:
*Create and open connection
*Populate and update data
*Close connection
There's no reason to store SqlConnection, DataAdapter classes because you'll not benefit from it - just recreate them when you're need them. Another hint to you - no DataAdapter neither SqlConnection is serializable therefore they're not designed to be stored persistently in the session state. To prove it right - change the session state model from "InProc" to "StateServer" or "SqlServer" and you'll get exception while putting this objects into state.
To sum up that I wrote above: don't try to cache anything from the data. When you decide to cache the data in the session state - make sure it not going to exhaust web server memory as for the every user session there'll be and cached dataset in the memory.
Best regards,
-----------
Igor Sukhovhttp://sukhov.net
|
|
|
|
|
Thanks Igor. As you suggested, I ditched the attempt to store SqlConnection and DataAdapter classes, and created a new insance when I needed them. It works fine now.
I am still caching the dataset, because it is relatively small and I think I'd take a bigger hit from loading it every time I need it (which is often). But I'll watch out for this as I scale my app.
|
|
|
|
|
the Max Size of "text" is 255,but it's so short to me.
how should I do?
thank you.
KSCsoft
|
|
|
|
|
if you need a larger test field, use Memo.
|
|
|
|
|
i want to make transaction on disconnected database what shall i do
ma_refay
|
|
|
|
|
You can't. You'll have to reconnect to start a transaction in the connection's context.
|
|
|
|
|
I'm having an issue with combining data rows returned by a query. I am working with sales data from a table joined to several other tables(confusing I know). When run the query returns multiple instances of the same company name(chain of stores), all from different areas of the country.
My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.
Thanks.
Cheers,
Lil Turtle
|
|
|
|
|
If you are using SQL server then the best idea is to have a view which will return all company names. Once you are done with this you can write another SQL select on view with group by clause for any further data selection.
Hope this helps.
Difficult - > Challenging, this simple replacement made me take my life little easy;)
|
|
|
|
|
A view in this instance is not needed as the data returned is for a report that I'm doing and not part of a permanent data store. Thanks for the tip though I can apply this to other endevors.;)
And I do appreciate the help, Thanks.
A humble novice,
Lil Turtle
|
|
|
|
|
Lil Turtle wrote: I am working with sales data from a table joined to several other tables(confusing I know).
Not really - Joining tables will be a regular occurrance if your database is normalised properly.
Lil Turtle wrote: My question is whether or not there is a way to combine all the data for one company name and be able to get totals for ie: price, # of sales, etc. I've tried using a group by statement, but as I need all the column data from the original table I'm having to group by every column name.
Then what you might try to do is to perform the GROUP BY in a subquery and then join the subquery to the table which contains all the other data that you want. If you want an example, you might like to show your existing code.
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
The GROUP BY in the sub query worked like a dream. Thanks for the response, I appreciate the help.
A humble novice,
Lil Turtle
|
|
|
|
|
I am only one administator of SQL 2005 database , I want to divide database in seven administration levels !
GETEL - General Telecom
|
|
|
|
|
|
Colin Angus Mackay wrote:
My question is return is: What do you mean by an "administration level"?
Thank you, Colin. I don't quite follow what GETEL - General Telecom is asking for. Would this be different user roles?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
