I have field_1, the data format store in the field is about "Mary, Amy, Billy, Ma"
how can i extract the record that the field_1 has Mary.
I can't use the like'%Mary%' because it can extract 'Ma' too.
I would like to extract exactly Mary. or can i split the field_1 by ',' in sql?
I have tried to break the below string like
DECLARE
@strFilterCond Varchar(Max)
set @strFilterCond=''
SET @strFilterCond=' ISNULL(Cast(EmpID as Varchar(1500)),''Undefined'') = ''000012946'' AND ISNULL(Cast(EmpName
as Varchar(1500)),''Undefined'') = ''Abosch, Karen'' AND ISNULL(Cast(tblEmployees.JobLookupID as Varchar(1500)),''Undefined'') = ''Undefined'' '
The Result String are like:
ISNULL(Cast(EmpID as Varchar(1500)),''Undefined'')
= ''000012946''
ISNULL(Cast(EmpName as Varchar(1500)),''Undefined'')
= ''Abosch, Karen''
ISNULL(Cast(tblEmployees.JobLookupID as Varchar(1500)),''Undefined'')
= ''Undefined''
I am unable to Break the string. break this string in sp. I do not know how to loop through the string.
I have field_1, the data format store in the field is about "Mary, Amy, Billy, Ma"
how can i extract the record that the field_1 has Mary.
I can't use the like'%Mary%' because it can extract 'Ma' too.
I would like to extract exactly Mary. or can i split the field_1 by ',' in sql?
I've a database table contains survey questions and answers. But the problem is both 'question' and 'answer' are saved in the same column(like below).
SubmissionDate Submission
2010-02-15 14:53:59.657 ***What's your pet's name?*** Lili ***Tell us about yourself*** I'm a teacher
2010-02-15 14:53:59.657 ***What's your pet's name?*** Lucy ***Tell us about yourself*** I'm a five star cook
I'd like to use a simple datagrid to display the survey result, but I need to separate the submission column into multiple columns ('question' as column name, 'answer' as data) the number of questions or questions being asked are different for each survey so they are not fixed. Is it even possible to get something like this below in a gridview?
SubmissionDate What's your pet's name? Tell us about yourself
2010-02-15 14:53:59.657 Lili I'm a teacher
2010-02-15 14:53:59.657 Lucy I'm a five star cook
I retrieve the date & time from datetime field in sql using a reader:
[Code]....
This value returns e.g. 15/04/2010 8:00:00 but I would like to split the day value (15), month value (4) etc from the overall datetime value, so I can assign dropdowns with corresponding (preselected) day, hour selection ..I know the dropdown assignment process, but I can't get the datetime field/value split ..
I have a column with a random length of text, basically it's an old crappy mfg system that stores data in a tilde delimited list. I need to output this column into each seperate column for a view. Column names don't matter, they can all be unnamed but so long as the data returns as seperate columns is what I'm after.
[Code]....
comparing integer values which are actually in string (varchar(9)) right now...I'm trying to do the following in a stored procedure..
SET @MinProjectYear = SELECT ProjYear FROM P_A WHERE ID = @ID
SET @MaxProjectYear = SELECT ProjYear FROM P_A WHERE UserId = @UserID AND...
IF EXISTS(SELECT 1 FROM P_A WHERE MinProjectYear <= MaxProjectYear)
return true
The MinProjectYear and MaxProjectYear are varchar(9) values stored as "2007-2008", "2009-2010", etc.
How do I split and compare the years?? i.e. check if 2007-2008 <= 2009-2010 ??
I am using asp.net3.5,sqlserver2008. I have retrieving data from Accounts,BUDGETS like this.
select a.AccountLink,a.Master_Sub_Account,b.* from Accounts a inner join BUDGETS b
on a.AccountLink=b.GLLink where a.AccountLink=1
output of my query...
AccountLink Master_Sub_Account GLLink AccType Budget01 Budget02 Budget03
[Code]....
I have table that's contains 2 columns Name and IDs (VarChar(10)) values are stored as
Name IDs
Test1 Test2 1
Test2, Test3 2
Test3 Test4 3
Mohd Farooq 1,2,5
My requirement is to display as below
Name IDs
Test1 Test2 1
Test2, Test3 2
Test3 Test4 3
Mohd Farooq 1
Mohd Farooq 2
Mohd Farooq 5
here my code-
<asp:TemplateField HeaderText="HIGH RISK (10-12)" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblHighrisk" runat="server" Text='<%# Eval("URANGE").ToString().Split('-')[0] %>' />
</ItemTemplate>
</asp:TemplateField>
but that is giving compile time error 'Server tag is not well formed'
I wanna join all the field as a new col.
but how can i ignore the empty field because i always join the field and separate by space like the following.
select (field_1+' '+field_2+' '+field_3) as new_col from TABLEA
but if the field is empty, then there is the double space
how can i ensure all the space is one space only.
I have a 20 to 30 fields on a form. Most of them are optional. However, the users can fill out what they want. If a user dosen't fill in a field when data is sent to the database it's sent as blank, instead of NULL. Is there a way I can generically say "for all textboxes if Equals("") then DBNull.Value". Without going through each and every textbox?
View 2 RepliesI am facing problem in my update statement,
Dim abc As String
abc = Request.QueryString("str")
Dim userName As String = CStr(Session("user"))
Label2.Text = userName
Dim MyConnection As New OdbcConnection(ConnStr)
MyConnection.Open()
Dim MyCommand As New OdbcCommand
MyCommand.Connection = MyConnection
If MyCommand.CommandText = "Select userid from week where userid ='" & userName & "'" Then
MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"
Else
MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"
End If
MyCommand.ExecuteNonQuery()
MyConnection.Close()
I used sqldatasouce and storeprocedure to populate DetailView. I need update few tables by pass parameters to my storeprocedure. but some value always lost (most of them is keyfield). when I checked those value through sqldatasource_updating event they are null . How can I get the correct value?
<asp:ControlParameter ControlID="DropDownList1" Name="Fname"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="DropDownList2" Name="Lname"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="eid" Type="Int32" /> <--? why become null
<asp:Parameter Name="Fname" Type="String" />
<asp:Parameter Name="Lname" Type="String" />
<asp:Parameter Name="Room" Type="String" />
<asp:Parameter Name="Active" Type="String" />
<asp:Parameter Name="bid" Type="Int32" />
<asp:Parameter Name="bname" Type="String" />
<asp:Parameter Name="vid" Type="Int32" />
</UpdateParameters>
At the top of my form I have some code that calculates a field
[Code]....
In my form I have this code. What changes below do I need to make to have the value of Date in XXXX?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:sqlConnection %>"
SelectCommand="SELECT NumPeople, Date1,Time1,Venue FROM People_Count WHERE ((Date1=@ArvDate and Time1 >= '06:00:00.000') or (Date1=XXXX and Time1 <= '06:00:00.000')) and Venue='F & B' ORDER BY Date1 ASC, Time1 ASC">
<SelectParameters>
<asp:QueryStringParameter Name="ArvDate" QueryStringField="ArvDate" />
</SelectParameters>
</asp:SqlDataSource>
is there some language to add to SQL that will hide a field?
View 5 RepliesOnce again, I find myself stuck!
I am attempting to compare one of ASP.NET's membership userID's with the current userID in an SQL query, however because of the "hyphens -" in the uniqueidentifier field type of userID, i am unable to compare the ids. is there a way around this? (in my example, family_id from tblFamily is the userID that has type uniqueidentifier). my code as it stands is:
Dim UserID As String = New String(Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString())
Response.Write(UserID)
Dim strConn As String = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString
Dim SQLI As New Data.SqlClient.SqlCommand("SELECT family_name FROM tblFamily WHERE [family_id] IS " & UserID & "")
Dim sqlCon As New Data.SqlClient.SqlConnection(strConn)
sqlCon.Open()
SQLI.Connection = sqlCon
Dim RSI As Data.SqlClient.SqlDataReader = SQLI.ExecuteReader()
RSI.Read()
Dim FamilyName As String = "0"
While RSI.Read
FamilyName = Convert.ToString(RSI("family_name"))
End While
Response.Write(FamilyName)
RSI.Close()
sqlCon.Close()
I have a form that submits one entry to a 'Listings' table and multiple entries to a 'ListingLocation' table depending on how many cities the user selected. When the listing is created, I need the auto generated 'id' field to input into each of the the listing rows of the 'ListingLocation' table. This seems like it should be simple but I cannot figure it out. I currently have it hardcoded to enter '22' where I need it to enter the listing id.
<script runat="server">
What is the best practice regarding an integer primary key that reach it's maximum possible value at some point of time?
Is there any standard action that needs to be taken so such a situation is never reached?
This is a very practical problem, and I am sure many people out there must have addressed it already.
I have a table in which a field consists of a multiple values like CSE, INF, ECE etc., I want to take only one value from the field like
When an user selects a particular branch like CSE, the user should get the list of Colleges assosiated with the branch CSE. How to do this?
For example: INSERT INTO table (col1, col2, col3) VALUES ('value1','value2,'value3')But I would only like to insert the row if value3 isnt already present in col3 in some previous row. What is the best way to do that?And the insert command is run 50+ times in a loop (with different values each time for value3)
View 4 Repliesi need to update one field in a tabel just to +2 and i can not find out how to do it the code i have write until now is just to find the field i has to update this i the code
[Code]....
write the last code there can +2 to a.point field in my db
I have a table "Table1" which has mani 3 fields ID: int,
Date1: datetime,
Date2: datetime
All fields are filled except Date2, so I want to copy the records in Date1 to Date2I tried this query, but it didn't work
[Code]....
I have a field that may or may not contain comma.
if the field contain some word and separated by comma
how can i remove specific word in the field.
e.g. the field contain --> 11,12,13
how can i remove the number 12 and remain 11,13 in the field?
I am using:
href='<%# Eval("Directory")+eval("Agenda")%>
to provide a link to documents held in directory so directory contains [URL] or [URL] Agenda contains
00209-FebruaryMtgMin%5E_.pdf
If I declare Directory as VarChar(nnn) where nnn is large enough to accomodate the longer field, The statement <%# Eval("Directory")+eval("Agenda")%> returns
[URL]self.aspx/.Public/ 00209-FebruaryMtgMin%5E_.pdf
How can I get rid of the Pad characters?