Data selection not happening for a date because of time format

hello all,

i am making a query which select the data again a particuler date.

I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.

I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.

Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?

Please anyone help me!

Thanks in advance!

to get all of today's rows use this

SELECT *
FROM YourTable
WHERE YourDateColumn >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks you SQL_Menace for writing to me!

your query did exactly that I needed.

Thanks indeed!

|||

SQL_Menace, I have another question that is closely related to the previous one.

I am searching for the records between two dates.

Here is my query(modified after your reply):

SELECT * FROM Mytable WHERE(DateEntered >=DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(srchdate) + "')+0, 0) AND DateEntered <= DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(todate) + "')+0, 0))

DateEntered is my date column, and Convert.ToDateTime(srchdate), Convert.ToDateTime(todate) are the values given by the user at runtime.

Problem here is the same. I insert 10 records today(2006-07-14 18:16:33) but when I provide yesterday(2006-07-13 19:16:33) as FROM DATE and today(2006-07-14 18:16:33) as TO DATE as parameter to search beteen these two dates, query returns nothing i.e. count is zero(0).

How can I achieve this?

Thank you!

Data question..

How can I add a data from a textbox into a ms sql database?
Because everywhere I read, it tells me how to display data, but not how to insert data...

How are you connection to your database...ADO, ODBC...whatever the case...if you write queries to select and diplay data...then you can also write queries to insert data...just make sure your database table allows insert...

if you are using some control and not codebehind, then they have Insert option...

Please be more specific of what you want to use and you will get more help...

thanks,

bullpit

|||

Im using ado...

For example lets say I create a table in mssql that allows insertion.

How can send the data from my textbox to the table.

I dont want to use a control...

From a textbox into the database...

Thank you

|||This may help you get started, hope it helps -- jp

string valueToInsert = yourTextBox.Text;string insertStr ="insert into yourTable (fieldTo) values (" + valueToInsert +")";SqlConnection conn =new SqlConnection(yourConnectionString);SqlCommand cmd =new SqlCommand(conn, insertStr);using(conn){using(cmd) { conn.Open(); cmd.ExecuteNonQuery(); }}

Data Migration - Insert Script / Fresh install

Hi

I have some tables in my SQL Server 2005 database. I need to create insert scripts for them. Since these tables contain the CLOB and BLOB values and the number of records is huge, I need to devise a way to install this table data on any new database(Assuming the new database is created and the table structure exists) .Can some one suggest me how I can do this ?

DTS or SSIS is out of question because least manual interference is required.

Thanks & Regards
ImtiazYou could use BCP to export the data into text/binary files and then import it back. This is the most efficient way. Generating insert statements is not cumbersome (there are some 3rd party tools that does this). But the files will still be huge and difficult to manage.

Data manipulation with One-to-many-to-one relationship

Is it possible to INSERT, UPDATE, DELETE data in this type of relationship? If so, how in VS2005? Not having issues with SELECT, even without joins.

Thanks

Hi,

Try providing more information about what you are trying to do. Do you have tables set up in a DB already? What columns do you have in those tables, and what are the references that make a given row of data in each table unique (primary keys)?

If you don't have a schema yet, provide (in your own words), what facts you are trying to convey - such as 'A customer places an order. Order has line items. Each product has a product number.' etc... This will give viewers here a better understanding of what you need.

Good luck, in any event. BRN..

|||

Let's say for example:

Table 1 has Table1ID (PK) and Table1Data

Table 2 has Table2ID (PK), Table1ID (FK for Table1.Table1ID), Table3ID (FK for Table3.Table3ID)

Table 3 has Table3ID (PK), and Table3Data

Relationship is one-to-many for Table1.Table1ID to Table2.Table1ID and one-to-many for Table3.Tat.ble3ID to Table2.Table3ID, creating a many to many relationship for Table1 to Table 3 using Table 2 as an intersection table. I do not have any cascading.

I am able to query the database and gather the information, but need to be able to add, edit, and delete in a C# VS2005 project. Starting off small with something like this would enable me to conquer a bigger project that I would like to create. This is the first time I have had to work with data that required this type of relationship.

I'd prefer to see the SQL.

Is this enough information?

Many thanks

|||OK, let take these 3 tables for example:

create table Employee (EmplNumber int not null primary key,EmplName nvarchar(20) not null)

create table Department (DeptNumber int not null primary key,DeptName nvarchar(50) not null)

-- relationship between Employee and Department

create table DeptEmpl (DeptNumber int not null foreign key references Department(DeptNumber)
ON DELETE CASCADE ON UPDATE CASCADE,
EmplNumber int not null foreign key references Employee(EmplNumber)
ON DELETE CASCADE ON UPDATE CASCADE)

insert into Employee values (1, 'John')
insert into Employee values (2, 'Terry')
insert into Employee values (3, 'Andrew')

insert into Department values (1, 'Marketing')
insert into Department values (2, 'Sales')
insert into Department values (3, 'Accounting')

insert into DeptEmpl values (1, 1)
insert into DeptEmpl values (1, 3)
insert into DeptEmpl values (2, 1)
insert into DeptEmpl values (2, 2)
insert into DeptEmpl values (2, 3)
insert into DeptEmpl values (3, 1)

?
There should be no problem when you try to insert data-- the PK/FK contraint will maintain the reference integrity for you. The key point here is theON DELETE/UPDATE CASCADE, they will help to maintain reference integrity during DELETE/UPDATE, so no extra work need to be done manually. If you have other concerns, feel free to post them|||

Thanks Iori_Jay

It is completely clear to me what you are doing in this example. How does one handle user input variables from a web application?

Let's say the user wants to insert a new Employee 'Edgar' that belongs to Department 'Sales'.

|||OK, typical steps for such INSERT should be:

1. Insert the information for the new employee 'Edgar' toEmployee table, and get theEmplNumber for the new inserted employee
2. Check theDepartment table to see whether there is a 'Sales' department, if not then add the 'Sales' department.
3. Insert a row toDeptEmpl table to represent the relationship between 'Edgar' and 'Sales'.

So a sample stored procedure looks like:

CREATE PROCEDURE usp_InsertNewEmployee @.EmpName nvarchar(20), @.DeptName nvarchar(50)
AS
IF((LEN(@.EmpName)=0) OR (LEN(@.DeptName)=0))
RAISERROR('Please input both Employee name and the department name which he(she) belongs to',16,1)
DECLARE @.newEmpNumber INT,@.DeptNumber INT
SELECT @.newEmpNumber=max(EmplNumber)+1 FROM Employee
SELECT @.DeptNumber=DeptNumber FROM Department WHERE DeptName=@.DeptName

INSERT INTO Employee SELECT @.newEmpNumber, @.EmpName
IF (@.DeptNumber IS NULL)
BEGIN
SELECT @.DeptNumber=max(DeptNumber)+1 FROM Department
INSERT INTO Department SELECT @.DeptNumber,@.DeptName
END
INSERT INTO DeptEmpl SELECT @.newEmpNumber,@.DeptNumber
go

EXEC usp_InsertNewEmployee 'Edgar','Sales'|||

Iori_jay,

Thanks so much for your help. I've learned many things from this post. I appreciate you sharing your talents.

ICE|8

|||It's my pleasure to help with this issue Wish you a happy festival!

Data lost after INSERT command

I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.Are you sure that when User B selects dates that are in the set selected by User A you are not using an Update statement instead of an Insert statement?|||It would be very helpful if you would post the queries involved, as well as the table DDL.|||Thank you for taking the time to respond. I have a bit of code where I do a DELETE prior to doing the INSERT. Your question led me to examine my DELETE statement again, only to discover that I had made it too broad so that it was deleting all records having a particular date, not just the one of the user currently logged in. I will adopt the suggestion to include code in future postings. Thanks again.

Data Lost , Help !!!

i make an application to insert data to sdf file and save it

when i colse the application and open it again i find my stored data on the sdf file well

and if i closed the application 1000 times i'll find my data ok

that's ok and that's not the problem

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

Any Ideas Here ?

I had a similar problem when I first used SQL CE

1. Copied the demo Northwind.sdf into its own directory
2. Added it to my VS2005 project
3. Made changes during testing
4. Data not there when viewed from SQL Server Management tool

After that initial moment of panic I realised that VS2005 takes a COPY of the sdf file and puts it into the project directory. Tra la - that is where the updates went.

In VS2005 project put mouse over the .sdf file to get the address and then look there for the data.

Good luck|||

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

do u c what i wrote above in my case

ur answer isn't related to the problem i'm facing

but thx alot for help

but i know that vs2005 take a copy to the local folder and use it and it's optional cause vs2005 asks u if u wanna do this or not before copying the file to the local folder

thx alot

hey there any ideas

i'm gonna crazy

Data Lost , Help !!!

i make an application to insert data to sdf file and save it

when i colse the application and open it again i find my stored data on the sdf file well

and if i closed the application 1000 times i'll find my data ok

that's ok and that's not the problem

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

Any Ideas Here ?

I had a similar problem when I first used SQL CE

1. Copied the demo Northwind.sdf into its own directory
2. Added it to my VS2005 project
3. Made changes during testing
4. Data not there when viewed from SQL Server Management tool

After that initial moment of panic I realised that VS2005 takes a COPY of the sdf file and puts it into the project directory. Tra la - that is where the updates went.

In VS2005 project put mouse over the .sdf file to get the address and then look there for the data.

Good luck|||

the problem that when i open this sdf file from sql server management studio and make a select on it i don't find any result and when i try to open the db from my application again i found it empty with no data

i realized that management studio delete's the data from my db

do u c what i wrote above in my case

ur answer isn't related to the problem i'm facing

but thx alot for help

but i know that vs2005 take a copy to the local folder and use it and it's optional cause vs2005 asks u if u wanna do this or not before copying the file to the local folder

thx alot

hey there any ideas

i'm gonna crazy

Data Insert via SQL Express using VB Issue

I've been doing a lot of research lately to determine the most secure way to issue an insert statement to insert my data into my SQL database. I'm coding this following using Visual Basic:

Protected Sub submitButton_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Dim strConnAs String ="server=.\SQLEXPRESS;database=C:\SJ-site\SJ\App_Data\SJDb.mdf; User Id=user; password=password"Dim MyQueryAs String ="Insert into sjTable (PostID, Category, TitleofPost, Description, Price, Loc, ContactInfo, DatePosted, Disclaimer) Values (@.Category, @.TitleofPost, @.Description, @.Price, @.Loc, @.ContactInfo, @.DatePosted)"Dim MyConnAs New Data.SqlClient.SqlConnection(strConn)Dim cmdAs New Data.SqlClient.SqlCommand(MyQuery, MyConn)Dim curDateAs String On Error Resume Next MyConn.Open()If Err.Number <> 0Then MsgBox("There was an error connecting to the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If curDate = Now()With cmd.Parameters .Add(New System.Data.SqlClient.SqlParameter("@.Category", categoryDropDown.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.TiteofPost", titleTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Description", descriptionTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Price", priceTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Loc", locationTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.ContactInfo", contactTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.DatePosted", curDate))End With If Err.Number <> 0Then MsgBox("There was an error inserting the data into the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If MyConn.Close()If Err.Number <> 0Then MsgBox("There was an erro closing the database connection.", MsgBoxStyle.OkOnly) Err.Number = 0End If Server.Transfer("submitted.aspx")End Sub

The code gives no errors, it directs me straight to thesubmitted.aspx page but when I query the database it is not insertingany data into my database. .

My Error log shows the following:
2006-06-27 12:14:16.68 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SJDB.MDF'.
2006-06-27 12:14:26.93 Logon Error: 17828, Severity: 20, State: 3.
2006-06-27 12:14:26.93 Logon The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]

I am able to log into the database using my account via SQL Server Management Studio Express. I've been all over the forums and google looking for a solution but everything I've been trying to mock up from other people's code hasn't helped. Thanks in advance for your assistance

Hi, do you have 17190 error in your SQL ERRORLOG when SQL startup?

2006-03-24 10:37:09.80 Server Error: 17190, Severity: 16, State: 1.
2006-03-24 10:37:09.80 Server FallBack certificate initialization failed with error code: 1.
2006-03-24 10:37:09.81 Server Warning:Encryption is not available, could not find a valid certificate to load.

And is your SQL service startup account a local account whose password has been changed recently?

If so, try to delete the file in folder

C:\Documents and Settings\<SQL Server startup
account>\Application Data\Microsoft\Crypto\RSA\S-1-5-21-963106725-2405722570-177647575-1023

The last part (S-1-5-21-963106725-2405722570-177647575-1023) may be different for
every account. Then restartSQL Server services to take effect.

|||I went ahead and upgraded to SQL Express 2005 SP1 w/ Advanced Services. I re-built my user accounts and what not. Now, I am still getting no error messages but also when I open the log file the last thing it says is:
2006-06-28 09:06:57.93 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SUGARJACKS.MDF'.

The data from the forms still isn't inserting into the database. The code hasn't changed and like I said the only thing I did was upgrade the SQL Express and re-build the user account.
|||On a side note I just wanted to say that yes I did change the database name and yes I did update the connection string accordingly in the code.

Data Getting truncated as I insert i.e. instead of inserting Hello it inserts H

m inserting some data in big-sized fieldand small-sized fields, data of varchar type, int's, dateTime, andothers... i am using something called a stored procedure to add datainto a table.. now when i execute the stored procedure my data getstruncated (although i made the sizes for my fields ridiculously biglike 1000 just in case) for example if i want to enter Jasmine it onlyinserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I amguessing it's a problem of using single vs. double quotes and stufflike that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@.lName varchar,
@.fName varchar,
@.mName varchar,
@.title varchar,
@.craft varchar,
@.lastFour varchar,
@.SSN varchar,
@.dateOfHire varchar,
@.currentProj varchar,
@.status tinyInt,
@.project_id int,
@.updateBy varchar,
@.updateDate varchar,
@.address varchar,
@.email varchar,
@.phone varchar,
@.zip varchar,
@.myfeedBack varchar,
@.ethnicity varchar,
@.userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @.newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,lastFour, SSN, dateOfHire, currentProj, status, project_id,updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@.lName , @.fName , @.mName , @.title , @.craft , @.lastFour , @.SSN ,@.dateOfHire , @.currentProj ,
@.status, @.project_id , @.UpdateBy, @.UpdateDate , @.address , @.email , @.phone , @.zip , @.ethnicity )
SELECT @.newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@.newid ,+'
+@.myfeedBack + ',
+@.userID ,
@.project_id )
SET NOCOUNT ON
END
GO

When i use 2 single quotes it insert the following string: "@.lName" not the actual value of the variable @.lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman','1234','keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==','6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/20052:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345','123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
All of your parameters are declared like this:
@.lName varchar,
Should be like this:

@.lName varchar(1000),
a type of varchar is like varchar(1), meaning a variable length string with a max of 1 character. The number used should likely match the underlying length of the field in the table

Data flow task to delete records and then insert records in transaction

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
SunilTry setting RetainSameConnection to TRUE on the connection manager.

|||It was already set to TRUE. Please help meeee..

|||Based on some other threads about similar issues, this may not be SSIS, but related to DTC and the core relational engine. You might try checking some of the other forums to see if they have any suggestions.

|||

Have you tried using a SQL Task (set based) for the deletes followed by your Insert Data Flow Task?

Larry

|||

Hey Larry,

Yes. I did use it.but still the blocking happens. Please suggest

Thanks,

Sunil