|
Thank you. I have the following situation
I have 9 fields some thing like first name, last name, age .... I need to search on these fields. I provided 9 textboxes. If first name and last name is filled then I need to check only that. Then my query will be something like this
select * from table where firstname like 'value' and lastname like 'value'
If user entered firstname and age then query should be generated according to that. So in that case any solution rather than going for dynamic queries ?
|
|
|
|
|
A parameterised query or stored procedure can be used
SELECT FirstName, LastName, Age, ...
FROM MyTable
WHERE (FirstName = @FirstName OR @FirstName IS NULL)
AND (LastName = @LastName OR @LastName IS NULL)
AND (Age = @Age OR @Age IS NULL)...
In your application:
if (string.IsNullOrEmpty(firstNameTextBox.Text))
myCommand.Parameter.Add("@FirstName", DbNull.Value);
else
myCommand.Parameter.Add("@FirstName, firstNameTextBox.Text);
// etc.
|
|
|
|
|
My MaintenanceLog table has an entry with following data in a row.
Name: Toney Mirra
ActualDateTime: 2007-02-16 12:55:02.867
PlannedDateTime: 2008-12-05 20:08:57.000
Comments: Sample Entry
But when I run the following query it doesnt return any value.
I think there is some problem in DateTime format. Can anyone help me fixing this?
select Comments from MaintenanceLog where
ActualDateTime = '2/16/2007 12:55:02 PM' and
PlannedDateTime = '12/5/2008 8:08:57 PM'
|
|
|
|
|
Try this
select Comments from MaintenanceLog where
convert(smalldatetime,ActualDateTime,101) = '2/16/2007' and
convert(smalldatetime,PlannedDateTime,101) = '12/5/2008'
|
|
|
|
|
Hi,
It's "Normal" because the milliseconds value of ActualDateTime is 868 and u are searching for '2/16/2007 12:55:02.000.
Two ways to fix this issue :
1- Always store 000 milliseconds in the database.
If u re using DateTime.Now function in .net set the milliseconds value to 0
If u re using GetDate function in sql, u can build the date without milliseconds using year, month, day, hour, ... functions.
2- modify ure search criteria to this :
ActualDateTime >= '2/16/2007 12:55:02' and ActualDateTime < '2/16/2007 12:55 3'
HTH.
|
|
|
|
|
How to implement a database server , in client-server local network,(we are using visual basic 6.0 and sqlserver 2000).
The problem is ,if the connection is not available than we can't get the master datas from the server.
If it is .net it is no problem. because it support connection less database.
But if we use visual basic 6.0 , what is the way to persist the data from server after terminate the connection.
Please can anybody help
Thanks in Advance
|
|
|
|
|
I think you have a bit of a misunderstanding of what connectionless means. While your program is running, data can be persisted into memory. If the application stops and restarts, then it needs to get the data again (this is simplified, and doesn't address out of process databases).
While DataSets are designed to be disconnected, it does not follow that .NET is connectionless. If I open a DataReader and don't close it, then we have a connection.
At some point you will need to get the data out of the database and this is where your problem kicks in. In both VB6 and .NET, if you can't get to the database because a connection isn't present then you are stuck.
If you want to store the information in memory, and you can get to it, then you can use exactly the same mechanism for VB6 as for .NET, i.e. you read the data into something that exists for a period of time. It could be that you store a recordset in memory, but however you do it, at some point you will need to persist these changes back into the database server. You will need to consider the implications of stale data and optimistic/pessimistic saves.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I'm trying to use PIVOT instead of CASE but can't make it, what's wrong with the pivot query below?
create table t1 (a int, b char(5))
go
insert t1 values(1, 'SVE')
insert t1 values(1, 'FIN')
insert t1 values(1, 'DAN')
insert t1 values(2, 'SVE')
insert t1 values(2, 'FIN')
insert t1 values(3, 'SVE')
select * from t1
a b
----------- -----
1 SVE
1 FIN
1 DAN
2 SVE
2 FIN
3 SVE
(6 row(s) affected)
-- SQL 2000 with case works
select a,
max(case when b='SVE' then b else '' end) as b_sve,
max(case when b='FIN' then b else '' end) as b_fin,
max(case when b='DAN' then b else '' end) as b_dan
from t1
group by a
a b_sve b_fin b_dan
----------- ----- ----- -----
1 SVE FIN DAN
2 SVE FIN
3 SVE
(3 row(s) affected)
-- SQL 2005 with pivot, this select doesn't work!
select a,
isnull(['SVE'], '') as b_sve,
isnull(['FIN'], '') as b_fin,
isnull(['DAN'], '') as b_dan
from (select a, b from t1) p
pivot (
max(b) for b in ( ['SVE'], ['FIN'], ['DAN'] )
) pvt;
a b_sve b_fin b_dan
----------- ----- ----- -----
1
2
3
(3 row(s) affected)
Håkan Nilsson
-- Swedish developer, DBA, C#, SQL Server, soccer trainer, old C64 player
|
|
|
|
|
i want to store oleobject in sql mysql oracle which data type i ahve to use for each
help me
thanks nikhil
|
|
|
|
|
nikhil1234 wrote: i want to store oleobject
What do you mean by "oleobject"? Is this just binary data?
nikhil1234 wrote: sql mysql oracle which data type i ahve to use for each
In SQL Server binary data can be stored in an image column in SQL Sever 2000 or a varbinary(max) column in SQL Server 2005.
MySQL and Oracle may have their own binary or blob (Binary Large OBject) types.
|
|
|
|
|
How to insert videos into database
Praveen
|
|
|
|
|
In SQL server you'll need to use one of the BLOB fields like 'image' and save the byte array of the video in here.
|
|
|
|
|
Sorry I was able to do it.
I made some silly mistakes.
Regards
Anil
|
|
|
|
|
Harini311 wrote: Better delete this forum
What about other people that want to use this forum?  (I suspect you meant "message" rather than "forum")
|
|
|
|
|
Hi G to U.R.U.S!
I want to return results based on entries on date range (Date From, Date To), the condition is that when the users doesn't fill up (leave blank) the date range (Date From and Date To), the SP should make use of oldest date and earliest date as date range.
Below is the SP I made which doesn't seem to work, please advice
<br />
(<br />
@DateFrom datetime =null,<br />
@DateTo datetime =null<br />
)<br />
as<br />
begin <br />
<br />
select <br />
PageName,<br />
count(PageName) as [Page Name]<br />
from<br />
PageViews<br />
where <br />
DateEntered > <br />
case when @DateFrom is null then (select convert(char(12),min(DateEntered)) from pageviews)<br />
<br />
else<br />
dateadd(day,-1,@DateFrom) <br />
end <br />
and <br />
DateEntered <<br />
case when @DateTo is null then (select convert(char(12),max(DateEntered)) from pageviews)<br />
else<br />
dateadd(day,1,@DateTo)<br />
end<br />
group by<br />
PageName<br />
order by <br />
[Page Name]<br />
<br />
end<br />
<br />
Thanks
Dom
|
|
|
|
|
I normally use:
select PageName, count(PageName)
from PageViews
where DateEntered between IsNull(@StartDate, DateEntered)
and IsNull(@EndDate, DateEntered)
group by PageName
order by PageName If either of the date variables is null then the IsNull function uses the value of the DateEntered column (and so the expression evaluates to true).
Regards
Andy
|
|
|
|
|
Hi Andy,
You nailed it, I have some question though, when i put in a blank date how will I know from these statement that the sp will pick on the oldest and latest date?
<br />
where DateEntered between IsNull(@StartDate, DateEntered)<br />
and IsNull(@EndDate, DateEntered)<br />
Please advice
Thanks
Dom
|
|
|
|
|
There are really two approaches that you can take. The first is this:
SELECT
PageName,
COUNT(PageName) AS PageCount
FROM
PageViews
WHERE
DateEntered BETWEEN
COALESCE(@StartDate, (SELECT MIN(DateEntered) FROM PageViews)) AND
COALESCE(@EndDate, (SELECT MAX(DateEntered) FROM PageViews))
GROUP BY PageName, DateEntered
While this works, it is largely inefficient because you are asking the system to re-evaluate the MIN/MAX every time. A better approach would be to retrieve these values initially, and then use them in your procedure like this:
DECLARE @MinDate DATETIME, @MaxDate AS DateTime
SELECT @MinDate = MIN(DateEntered), @MaxDate = MAX(DateEntered) FROM PageViews
SELECT
PageName,
COUNT(PageName) AS PageCount
FROM
PageViews
WHERE
DateEntered BETWEEN
COALESCE(@StartDate, @MinDate) AND
COALESCE(@EndDate, @MaxDate)
GROUP BY PageName
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
It cheats a bit - and so doesn't require knowledge of the oldest and latest dates.
As a worked example: If the @StartDate is blank and the @EndDate is "1 Mar 2007" then the where clause is equivalent to:
where DateEntered between DateEntered and '1 mar 2007' Pete's second answer (using the Coalesce function) is exactly the same. I tend to use "IsNull" because its easier to spell
Hope that helps.
Andy
|
|
|
|
|
Hi
For date filtering, try this:
From front-end, pass default value as Min: 01/01/1900 and Max: 01/01/9999 if date values are not entered or blank
In your select statement, add this statement
IsNull(DateCol,'01/01/1900') >= Convert(nvarchar(12),@FromDate,101) AND
IsNull(DateCol,'01/01/9999') <= Convert(nvarchar(12),@ToDate,101)
Change date format from '101' to anything based on the format (whether MM/dd/yyyy or dd/MM/yyyy) you are storing in the database.
IsNull function on the left side is required as if you are allowing NULL values for the date field.
That 's it.
Rate this message. Thank you. Harini
|
|
|
|
|
My vc++ application works fine till the SQL Server 2005 is not restarted(i mean if we stop the SQL Server 2005 and then start it again while application is running).
Could anyone please tell me how to establish connection(auto connection) for the application after the SQL Server 2005 is restarted.
Thanks!!
|
|
|
|
|
Hi
How can we can make DB2 and SQL server communicate with each other. It's not a kind of datamigration, these were need to be updating each other on regular basis.
Thanking u in advance,
Regards,
Ajit
-- modified at 0:51 Thursday 8th March, 2007
|
|
|
|
|
I have a service (which I have written in C#) that I want to run on 2 different computers however they will both be accessing the same database.
Is there a way to tell when one computer is writing to the database so I can stop the other from writing to it at the same time to avoid contention issues?
|
|
|
|
|
It would help to know what kind of database... Access, SQL Server, ...
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
