Using Date Field In Search String To Bind To Repeater
Apr 5, 2004
i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:
sub page_load(sender as object, e as eventargs)
dtmDate=DateTime.Now.ToString("M")
con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")
cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)
con.open()
dtrBday = cmd.ExecuteReader()
rptBday.DataSource=dtrBday
rptBday.DataBind()
dtrBday.Close()
I know this isnt quite right. i get errors when it hits my repeater.
the error i am getting is :Syntax error converting string to smalldatetime data type.
if someone could give me a push in the right direction here it would be greatly appreciated.
I'm about ready to pull my hair out. I have a repeater control, and a date field. If the field is a valid date, I'll use it to calculate and display the person's age. Otherwise, I want to display "N/A". My problem is trying to determine if the date field is null or not.I'm using SQL Server 2005 which allows Nulls in the date field, and for many records I don't have a birth date. It makes sense for these records to leave the date Null. This is my code:Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim LblAge As Label = CType(e.Item.FindControl("LblAge"), Label) Dim inmate As WAP.prisonmembersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.prisonmembersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & inmate.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If If inmate.DATE_OF_BIRTH Is DBNull.Value Then If IsDate(inmate.DATE_OF_BIRTH) Then LblAge.Text = "Age: N/A" Else LblAge.Text = "Age: " & GetBirthdate(inmate.DATE_OF_BIRTH) End If End If End IfEnd Sub How can I resolve this?Diane
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail: SELECT * FROM Expression WHERE ExpText LIKE '%≤%' -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText) -- no records However, SELECT PATINDEX('%≤%', 'y ≤ 200') will return 3. Any suggestions? Thanks in advance.
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 running a converted DTS package which executes a stored procedure which extracts fields from a SQL database table and puts them to a flat file. Two of the fields are datetime fields. The stored procedure does not convert the dates, and under SQL 2000 DTS the fields extract as strings in format yyyy-mm-dd hh:mms. When this gave me an error in SSIS, I added a data conversion task between the Ole DB Source and the Text file destination to convert the two fields to DT_STR format and used the converted fields in my mapping for the text file. I am still getting an error on this:
-1071636319,0x,Data conversion failed. The data conversion for column "mm_hdr_process_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
The target code page is 1252 (ANSI) and I have tried several variations of length, and I still get this error. Any suggestions? I would prefer not to have to change every stored procedure we have which may do this. Thanks for any help. MaryKate
It may not have date records which are just string like in this example is 12345678900. I need a SQL script which can identify those records which are not of date type. Please help.
Hi all, I have three textboxes in a form view 1- Qty textbox 2- price textbox 3-TotalPrice textbox which value is the result of multiplying the previous two values and then I want to Bind() the third value to the totalprice field in the database how can I do that?? (without making the database field as a computed column)
I have tried to code a href link to bind the data from a Stored Procedure, as shown below.This herf string failed. How to use these special characters, ' < " > ) ?Or how can I use sb(string builder), which I have used successfully for display the data. TIA,Jeffrey<%cmd.CommandText = "MyProgramsA"cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.Add(New SqlParameter("@meid", SqlDbType.Int))cmd.Parameters("@meid").Value = EmpIdcmd.Connection = sqlConnection2sqlConnection2.Open()reader4 = cmd.ExecuteReader()While reader4.Read()%> <a href="http://sca3a56/'<% reader4(i).ToString() %>' "><% Response.Write(sb3) %></a><br /> <br />
Hi,What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.thanks,Bruce
We are using lookup transformation in SSIS 2012. The lookup transformation queries a table with two date columns. When we hover the mouse over the two columns in the 'columns' tab of the lookup transformation editor, the two columns show as DT_WSTR instead of DT_DBDATE. This causes the SSIS package to fail due to data type mismatch.A similar abandoned thread is available at: URL....
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
Hi, I have an old db with streets names and another one with employees. I have to use both tables to check if an employee adress is ok, but I have the following problem: Employee adress : 47th street nº12 2nd door b street in db 47th street
The question is how to say to sql that searchs for the employee street in the streets db cause the employee addres is bigger than any streets in db and I have no results with street like '%47th street nº12 2nd door b%' or something like that.
I have a parameter that is a varchar(50) and I want to know if there are any percent(%) signs in the string, what function can I use for this? I am looking for the T-SQL equivalent of InStr() Thanks,
Hi everybodyI have this query which need to extract names where I use the wildcard character % before and after the string to indicate that the string should not contain these characters but it is not working right .. Is there any way I could do that..SELECT name, famnameFROM inftable WHERE NAME NOT IN ('%socius%', '%rector%', '%superior%')In other words my result should not include those names that contains socius, rector, superior, this characters could begin with or in a middle of the field...thanks
Was wondering how you do the equivalent of an Oracle "Instr" function in TSQL, which returns the position of a string pattern in a specified string? I've looked through all the functions and can't find the equivalent.
Is there a T-SQL equivalent of the Oracle "Instr" function, whereby I can retrieve the position of a one specified charactier string within another? I have looked through the list of string functions and can't find an equivalent, which suprises me!
I want to search all the people in a database that names mary
I now that the sintax is something that looks like this.
SELECT Id, name FROM Socios WHERE (name LIKE '%string%') But how i input this in the vs2008 query builder for it creates an automatic searchbox toolstrip?
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
