|
Hi All,
I have a SQL database column, name "Closing Date" with datatype as Datetime. For example, the entries are like this:
1-Nov-2009
21-Dec-2009
10-Jan-2010
21-Mar-2010
11-Apr-2010
Now What I want is to select a date which is less than my requested date from a text box.
e.g. If I want to select a date which is less that 31-Mar-2010, then I should get 21-Mar-2010.
or, If I want to select a date which is less that 9-Jan-2010, then I should get 21-Dec-2009.
Presently I am using following in VB.net.
cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= textbox.text", con)
But I think I need to use something else. Hope My query is clear. Please suggest me how to go ahead?
Regards
R.S.
|
|
|
|
|
Try
cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= @selectedDate", con);
Dim param As New SqlParameter("selectedDate", DateTime.Parse(textbox.Text, CultureInfo.InvariantCulture));
cmd.Parameters.Add(param);
|
|
|
|
|
Hi,
try
"SELECT MAX([Closing Date]) FROM client_details WHERE [Closing Date] <= '"+textbox.text+"'"
(assuming C# is what you need). With some comments:
1. it is much easier when database field names don't contain special characters and spaces.
2. such SQL statement is vulnerable to SQL injection: users could enter parts of an SQL command, which your code then executes. Solution: verify the textbox content, or better yet, use SQLparameter.
3. the user must enter a string representing a date, according to the format your database is expecting. It is better to have the user enter data according to the system's regional settings, then to convert it to what the database needs. Again SQLParameter is the better approach: use DateTime.Parse(textbox.text) (or TryParse), then set the SQLParameter.
|
|
|
|
|
Here is another alternative - no better, no worse just different.
"SELECT TOP 1 [Closing Date]) FROM client_details
WHERE [Closing Date] <= '"+textbox.text+"'
order by [Closing Date] DESC"
As Luc suggested use parameterized queries - this is IMPORTANT.
There are usually a number of ways to sole a problem, try both and look at the execution plans to decide the better solution. Then throw an index on the close date field and see the difference!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This is for SQL Server 2008:
Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation?
This is the idea:
- stored proc spTest contains 2 insert statements
- exec spTest
- spTest executes 1st insert statement
- stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash)
Will the data in the 1st insert statement be rolled back?
Thanks in advance!
Rafferty
|
|
|
|
|
|
What do you mean?
Currently, these 2 insert statements are not enclosed in try catch blocks, but I didn't think that I need to because the error is caused by something else that is external to this stored procedure.
Or is there some DB configuration-wide that I should set?
Rafferty
|
|
|
|
|
Rafferty Uy wrote: Will the data in the 1st insert statement be rolled back?
How about wrapping them both in a single transaction[^].
I are Troll 
|
|
|
|
|
Hi Eddy, thanks for answering. I understand that it's better to wrap both in a single transaction.
My question is really what happens if i don't. I want to know the normal behavior of sql server in such a case, not how I can improve the code.
I'm investigating a bug and I suspect that this was the cause of the problem... but it seems far fetched because the server didn't crash... it just experienced some performance issues.
Thanks again!
Rafferty
|
|
|
|
|
Rafferty Uy wrote: My question is really what happens if i don't.
It will write up to the point of the crash, and execution will halt. No automatic recovery,
If it crashes midway, the first sproc might be saved, the second might not. If it crashes during a write-operation, your entire database might become corrupt.
I are Troll
|
|
|
|
|
Just the answer I'm looking for! Thanks a lot Eddy! This really helped
Rafferty
|
|
|
|
|
hello folks,
I got headache to figure out SSIS SQL 2005 error when windows task scheduler is triggered...
I setup the batch file like below to run it every morning,
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /f c:\BAT\SSIS\DATATRAN.dtsx >> c:\BAT\LOG\DATATRAN.log"
it had been working until last month, now I can run this batch file without any problem manually and no problem to run the scheduled task manually, but it shows errors when run the scheduled task every morning... SSIS is stuck in the process 43% completed,
Is there anyone can give me tips?
thanks!!
|
|
|
|
|
MARS (Multiple Active Results Sets), which simplifies the application design process
ALTER rename process for symmetry in renaming databases
Application and Multi-server management for Data-tier Applications which further streamlines application design and enables deployments of database applications directly from SQL Server 2008 R2 and Visual Studio 2010 to SQL Azure for database deployment flexibility
Details in the Blog Post http://blogs.msdn.com/sqlazure/archive/2010/04/16/9997517.aspx
|
|
|
|
|
|
Vista/64
VS2008
I'm to add a SQL Server database object to my project, but when I try, I get this error message:
Connecting to sql server files (*.mdf) require sql server express 2005 to function properly.
Well, it is installed, and when I run express manager studio, everything seems to be fine. I can also create connections in the server explorer in vsiual studio. When I googled the error, there was exactly one hit that I can't browse top (because of the restrictions on our network).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Can you post either the link you can't get to or the exact search phrase in case someone CAN get to it and then render help?
Tim
|
|
|
|
|
Hi John,
IMO the relevant part of http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6615124e-5501-4fb2-b498-638d3a7f01c6[^]
is this:
I encountered the same error message while trying to create a data connection from Visual Studio 2005 using the Server Explorer.
This is what I did:
# 1. Started Visual Studio 2005
# 2. Opened the Server Explorer (View | Server Explorer)
# 3. Right-clicked the Data Connections node
# 4. Selected Create New SQL Server Database... from the context menu
# 5. Selected my computer from the Server Name dropdown list
# 6. Selected authentication method (Use Windows Authentication)
# 7. Typed a name in the New Database Name field
# 8. Hit the OK button
That's when I received the following error message
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server)
It made absolutely no sense. I wasn't trying to connect to a remote computer. I was simply trying to connect to the SQL Server 2005 Express Edition which was installed on my local computer during the Visual Studio 2005 installation.
After numerous attempts on several computers, I finally realized what I was doing wrong. By adding the database instance name to the server name (# 5), it actually worked. Instead of selecting <server name=""> from the dropdown list, I entered <server name="">\SQLExpress manually. That was obviously the problem.
SQL Server 2005 Express Edition is obviously installed as a named instance, since one has to provide the instance name in addition to the server name (even when there is only one instance running on the requested server).
|
|
|
|
|
We're not running sqlexpress...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
|
Does it relate to SQL Express? 
|
|
|
|
|
Luc Pattyn wrote: Does it relate to SQL Express?
|
|
|
|
|
|
CREATE PROCEDURE .InsertTwoTables
@UserName nvarchar(50),
@ShopName nvarchar(50),
@Email nvarchar(300) ,
@time1 datetime,
@CategoryNameE nvarchar(50),
@ItemKey nvarchar(50),
@ItemKeyNameE nvarchar(50),
@CurrentQty nvarchar(50),
@SalesPrice nvarchar(50),
@Quantity nvarchar(50),
@Total nvarchar(50)
AS
BEGIN TRANSACTION
Set Nocount On
DECLARE @OrderID int
//primary key table
INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1)
SELECT @OrderID=@@IDENTITY
//foreign key table
INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total)
VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total)
COMMIT
when iam inserting values with these procedure
it is saving ordersno also repeated
i want one orderid with multiple values in TblDetails
can you give me example which helps me
|
|
|
|
|
The way the stored procedure is currently there will only be a one to one relationship. I would suggest removing the time from TblOrder then before an insert check to see if the values UserName, ShopName, Email already exist.
Something like (not tested)
DECLARE @there INT
SELECT * FROM TblOrder
WHERE UserName = @username
AND ShopName = @shopname
AND Email = @email
SET @there = @@rowcount
IF @there = 0
BEGIN
-- Your insert
END
Hope this helps
djj
|
|
|
|
|
Try using SCOPE_IDENTITY rather than IDENTITY - check BOL for the (significant) differences.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
