The following is a VB code which I use it to check the existence of the record before I insert a new record
into my SQL 7.0 database. When I run the vb code, I got an error which is
"-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I couldn't figure out what's wrong with my code especially the WHERE clause.
Please help me look into this problem. TQ.
Private Sub AddDate()
Dim rstDate As ADODB.Recordset
Dim myDate As Date
Dim Criteria As String
Criteria = "SELECT Date " _
& "FROM DateGen " _
& "WHERE Date = '" & myDate & "'"
Set rstDate = New ADODB.Recordset
With rstDate
.ActiveConnection = cn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open Criteria
End With
If rstDate.RecordCount > 0 Then
RecordExist = True
Else
RecordExist = False
rstDate.AddNew
rstDate("Date") = Format(myFileDate, "dd-mm-yyyy")
rstDate.Update
End If
rstDate.Close
End Sub
I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.
following is the code which i am trying to use it throws an error and dosent work. error details: Unable to cast object of type 'System.Data.Linq.DataQuery`1[tbl_temp_bank]' to type 'tbl_temp_bank'. source code(aspx.vb file) Dim c As New temp_business_bankDataContextDim tag = From t In c.tbl_temp_banks Where t.TIN = Convert.ToInt32(tin.Text) Select t c.tbl_temp_banks.DeleteOnSubmit(tag) c.SubmitChanges()
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.
Say I have a table with items, prices, and dates.
PriceTable
ITEM PRICE DATE
A 1.00 5/5/2000
B 3.00 1/1/2000
A 2.50 6/5/2004
....
This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.
Now say I have a table of transactions with items, amount bought, and dates.
TransactionTable
ITEM AMOUNT DATE
A 5 6/6/2003
A 1 8/5/2003
A 2 8/5/2004
The total for A should come out to be 11.00. There are multiple Items and multiple price changes.
If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.
My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".
I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".
04/06/2005 12:15:300====0 04/06/2005 12:10:300====1 04/06/2005 12:20:300====0 04/06/2005 12:20:300====1 04/06/2005 12:20:300====0 ============================================ I want to count number of Active and Inactive Items per day. How can i do this. Following is the desired result set according to the above data.. DATE====InActive_count(0)====ActiveCount(1)===Total 04/04/2008====3===2===5 04/05/2008====4===1===5 04/06/2008====3===2===5
========================== I can get count total count per day using following query. Select Dateadd(day, Datediff(day, 0, date), 0) as date, Count(id) as [Count] from myTable group by Dateadd(day, Datediff(day, 0, date), 0) order by date desc ========== how does i change this query to get desired result....?
himy self avicurrently i am developing one application in vb 6 and back end assqlserver 7 which is used on lan.i have some problem. like in my database i have one table salesvoucherwhich has 'voucherno' field. when 2-3 user will work on salesform at atime [since the softwrae will run on lan] then the same voucherno willsave for all users data which is wrong i need to save different vouchernofor each no. is there any way in sqlserver to apply some condition ondatabase or to set some its property[tables] so that at a time only oneusers data will save depend on firstcome first serve base or is there anyway to make changes in programme.plz help me regarding this i need it very badly.since its my first lan based software plz give me some books nameregarding this software for vb6 and sqlserver7
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
Hi, I have a sql server database with 250 tables in it, i wanted to find one particular value from one of the tables in the database. How to find that appreciate your help.. Thanks
hi I have 2 fields in my table by names "usercode" and "pass" and the "usercode" is the primarykey. I want when the user writes the usercode and leave the textbox, check if there is a usercode like the textbox content return true to the user.
I have been given a task to locate duplicate and report duplicate records and am trying to determine the best way to do this with databases that have 1 million records plus.
Say I have a table with 20 columns, I need to check to see if 3 of 10 specific columns match.
So if 2 columns are the same its no problem however if 3 or more match, they are considered duplicate.
I have student table where duplicate student exist by name with there fathers name and mothers name. I need to search those duplicate records. I do not need ti count them but If there is 5 same student with name then the query will show 5 name then I will delete individually. Below I am trying to show the scenario.
Student_name _____________ Rocky Albert Rocky Williams Albert Robert
The query will show
Student_name ______________ Rocky Rocky Albert Albert
Hi,I have a application that I’m trying to do a search in a SQL database with a dateI can add, update or delete the date but when I do a search it doesn’t work. I did a store procedure to do the search and its working but it’s only working when I pass the parameter between quote like that ‘09/15/2006’ if I remove the quote it doesn’t work How do I configure my store procedure to automatically put the quote?OrHow do I configure my vb.net code to automatically add the quote? Thanks in advance Soft
Hi guys, Am developing an application where I store the date in the database. I would like to search the date from the database table. Can anyone tell me how would the search string would look like? The current string am using is: select Pid "ID", PName "Name", Address, DateOfBirth "Date Of Birth", Phone, emergencyContact "Emergency Contact", RegistrationDate "Registration Date", DischargeDate "Discharge Date" from patient_177681 Where RegistrationDate Like '%2005-3-4%' But this string does not return any results and so does any other data in the Like % % string. Can anyone let me know how to compare two dates and return a result? Is there any in-built function to do this?
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??
thank you
ALTER PROCEDURE dbo.Search_ActiveConditionals (@Enter_TM# int) AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND FROM dbo.ACTIVE_CONDITIONALS WHERE (TM# = @Enter_TM#)
I am pretty new to SQL server 2005 and have the following issue: I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format. I am having trouble composing the stored procedure to do that. Any help would be apreciated. Sincerely d
I am trying to search for stored files "for example from date: 15/12/2003 to: 24/6/2006" and when i press search no results appeare the following is the database code: 1 public DataTable searchData(string fileNo, string Title, string dFrom, string dTo, string brief)2 {3 string str = "";4 5 str = "select * from Tb_File where Active = 1 ";6 7 if (fileNo != "")8 str += " and FileNo='" + fileNo + "'";9 if (Title != "")10 str += " and Title like '%" + Title + "%' ";11 if (brief != "")12 str += " and Brief like '%" + brief + "%' ";13 if (dFrom != "")14 str += " and DFrom >= convert(datetime,'" + Convert.ToDateTime(dFrom).ToShortDateString() + "',103) ";15 if (dTo != "")16 str += " and DTo < convert(datetime,'" + Convert.ToDateTime(dTo).ToShortDateString() + "',103) ";17 18 ole.Open();19 SqlDataAdapter DA = new SqlDataAdapter(str, ole);20 DataTable DT = new DataTable();21 DA.Fill(DT);22 ole.Close();23 return DT;24 25 } i am using sql 2000, with Visual Studio 2005.
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
I have the following table and have included some sample data. Basically, the records are grouped by opid and it shows ownership changes for the opids. It only shows the owner change date which is the date that the owner was assigned to the opid. What I want to figure out is the date that the owner ceased to be assigned to the opid as well. If it is still the current owner then the end date should just be the current date.
Code Snippet CREATE TABLE #op_owner_history( [opshid] [int] IDENTITY(1,1) NOT NULL, [opid] [int] NOT NULL, [role] [varchar](30) NOT NULL, [rep] [char](20) NOT NULL, [percentage] [int] NOT NULL, [ownerchg_date] [datetime] NOT NULL, [ownerchg_rep] [char](20) NOT NULL, CONSTRAINT [PK_#op_oppshare_history] PRIMARY KEY CLUSTERED ( [opshid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] SET IDENTITY_INSERT #op_owner_history ON GO INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4653, 23795, 'Sales','KOHLENBERG', 100, '9/26/2006 3:57:22 PM', 'Kohlenberg') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8576, 23795, 'Sales', 'Haber', 100, '1/3/2007 10:40:00 AM', 'ADMIN') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (9363, 23795, 'Sales', 'KOHLENBERG', 100, '1/4/2007 12:40:14 PM', 'Kohlenberg') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (2919, 24623, 'Sales', 'COLEMAN', 100, '9/1/2006 12:02:03 PM', 'Dunkel') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (15475, 24623, 'Sales', 'COLEMAN', 100, '5/8/2007 8:10:51 AM', 'Woodruff') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8580, 24623, 'Sales', 'Woodruff', 100, '1/3/2007 10:40:00 AM', 'ADMIN') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4310, 5548, 'Sales', 'KOHLENBERG', 100, '9/19/2006 1:53:47 PM', 'KOHLENBERG') INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (10649, 5548, 'Sales', 'HABER', 100, '2/7/2007 3:50:40 PM', 'Kohlenberg')
Based on this sample data, here is what I want to see in the results: (My apologies, I couldn't get it formated very well to make it more readable.)
opshid opid role rep percentage ownerchg_date End Date ownerchg_rep 4310 5548 Sales KOHLENBERG 100 9/19/2006 1:53:47 PM 2/7/2007 3:50:40 PM KOHLENBERG 10649 5548 Sales HABER 100 2/7/2007 3:50:40 PM GETDATE() Kohlenberg 4653 23795 Sales KOHLENBERG 100 9/26/2006 3:57:22 PM 1/3/2007 10:40:00 AM Kohlenberg 8576 23795 Sales Haber 100 1/3/2007 10:40:00 AM 1/4/2007 12:40:14 PM ADMIN 9363 23795 Sales KOHLENBERG 100 1/4/2007 12:40:14 PM GETDATE() Kohlenberg 2919 24623 Sales COLEMAN 100 9/1/2006 12:02:03 PM 1/3/2007 10:40:00 AM Dunkel 8580 24623 Sales Woodruff 100 1/3/2007 10:40:00 AM 5/8/2007 8:10:51 AM ADMIN 15475 24623 Sales COLEMAN 100 5/8/2007 8:10:51 AM GETDATE() Woodruff
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
how to select record from the table where the data between a range. example between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks
With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?
I want to get latest updated date on each transid and only for status =approved .
------------------- out put would be ------------ idtransid date status 31013/1/2014 approved 61031/2/2014 approved
table ----------- idtransid date status 11011/1/2014 approved 21012/1/2014 close 31013/1/2014 approved 41021/2/2014 approved 51022/2/2014 close 61031/2/2014 approved
