Storing And Searching A Field

Dec 2, 2003

I need to find a better way to find all parts from a particular category. Each part can appear in multiple categories.

Currently, I use the id of a catagory and search the txtCatagory field in the position of the id.

I have included the query string for you to see.

SELECT * FROM tblParts
WHERE SUBSTRING(txtCategory,@CatId, 1) = '1'
ORDER BY PartNum ASC

txtCategory is Data Type Text

This currently does work, however is causing some performance problems with my ISP.

Any help would be greatly appreciated.

View 1 Replies


ADVERTISEMENT

Searching For A Field Name

Jul 26, 2007

Hi all,

I have a very big database with me. This database has large number of tables and each table has n number of fields.

My trouble now is that I desire to search for a field name called 'empname'. Is there any query possible for me to search all tables and return me the table names which contain fields which match with 'empname'. Adding essence to the porridge I would like to know if I specify something like '%pnam%' it should match any field name which contains 'pnam' as a portion of its name. Is all these possible ??

Thanx for any help in advance,

Regards,

tvks

View 6 Replies View Related

Searching Text In A Db Field

Sep 10, 2006

Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')

View 1 Replies View Related

Searching By A Date Field

Mar 10, 2005

I'm using the .NET Framework 1.1 together with SQL Server 2005 Beta.

I have a search page where you can search a db using a number of fields or combination thereof. Three are text fields (patient id, surname, forename) and I can do any search using one criteria or any combination without any problem.

However, I'd also like to search on a dob (datetime field in SQL Server) and I'm having real problems in making this work. The dates are stored in the following way in SQL Server: '1938-05-31 00:00:00.000', whilst they are displayed as 'dd/mm/yyyy' on a web page, which I guess corresponds to my regional settings.

My code is shown below. I 've tried any number of combinations for the dob string with no success. I do not get any error, just no records are returned.

Does anyone have any idea what I'm doing wrong and how to correct it before this drives me crazy?!


TIA for any help.

P.S. chxPatID, chxSurname etc are all checkboxes.



<code starts>

If chxPatID.Checked Then
strSQL = strSQL & " patid= '" & txtPatID.Text & "' "
strAND = " AND "
End If

If chxSurname.Checked Then
strSQL = strSQL & strAND & " surname ='" & txtSurname.Text & "' "
strAND = " AND "
End If

If chxForename.Checked Then
strSQL = strSQL & strAND & " forename='" & txtForename.Text & "' "
strAND = " AND "
End If

If chxDoB.Checked Then
strSQL = strSQL & strAND & " dob= " & CDate(Year(txtDoB.Text) & "-" & Month(txtDoB.Text) & "-" & Day(txtDoB.Text)) & ""
End If

</code ends>

View 1 Replies View Related

Searching In A Varchar Field

Jul 12, 2001

I have a varchar column which containd comma delimited values like
Rec# Fruits
1 Apple, Peach, Strawberry
2 Orange, Mango
3 Banana, Grape
...........

Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.

Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??

I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?


Thanks
Sumit.

View 4 Replies View Related

Searching A Database Field For Null Value

Feb 27, 2008

Hi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima

View 7 Replies View Related

Searching For A Word On Each Table On Very Field

Feb 9, 2006

Hi everybody,

is it possible using SQL Server search for a word on each
table on each field?

Thanks

Fabio

View 6 Replies View Related

Searching A Datetime Field By Time

Jan 12, 2006

I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.

View 3 Replies View Related

Searching For A Partial Match In A Varchar Field

Jan 12, 2006

I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.

View 2 Replies View Related

Efficiency Of Sql Server On Searching On Text Field

Jul 26, 2007

Hi

We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?

The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?

Please advise, thanks for your help!

Li

View 4 Replies View Related

Running A LIKE Statement When Searching For A Date Field...

Feb 14, 2006

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

View 7 Replies View Related

Searching Varchar Field That Contains Future Dates Error

Feb 25, 2008



I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query

SELECT [CIFPan]

,[CIFMemNum]

,[CIFLName]

,[CIFFName]

,[CIFExpDate]



FROM [FutureSoft].[dbo].[zCIFRecord]

WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'


I get error:


Msg 295, Level 16, State 3, Line 1

Conversion failed when converting character string to smalldatetime data type.


I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?

View 4 Replies View Related

Storing Pdfs In An Image Field

Jul 23, 2005

I am using an image datatype to store multiple filetypes includeing plaintext data. it works except for pdf Data. When I retrieve the the filecouldn't be opened in acrobat reader and the file was 3KB bigger than theoriginal.Any suggestions would be helpful.

View 3 Replies View Related

Storing Data In Image Field

Jul 20, 2005

I have a very large array of floating point numbers which I am tryingtostore in an image type field. I am using the dataset class todirectly enter the data but am having trouble trying to get this typeinto the image field. I guess the question is how do I get a largefloating point array into an image (byte)field of a database using thedataset class for new row entry.Thanks

View 1 Replies View Related

Storing XML In Datatype Field In A Table

Apr 23, 2015

I am storing xml in an xml datatype field in a table. But when I am generating physical file from the xml field, '&' in the xml field is getting converted into '&' in the physical file. But I want '&' to be intact.

View 5 Replies View Related

Storing Zip Files Read As Binary In A Field

Feb 16, 2004

i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?

View 1 Replies View Related

Storing Text Data In A Binary Field

Jul 23, 2005

This may be a stupid question but I can't find an easy answer for what Iwant to do. I need a blob data field that can store both binary and textdata like the sql_variant field. I am using vb.net to populate a sql table.I want to store both string and binary data.

View 1 Replies View Related

Which Is Better? Storing Data In The Database OR Storing It In The File System

Dec 29, 2006

Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?    

View 6 Replies View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

Searching For ._

Apr 24, 2008

I am trying to find all the email addresses with a " ._"    I use '%._%' but it returns all records.  What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton
SELECT DISTINCT fname, lname, inet
FROM ocadbo.notes
where inet like '%._%'

View 6 Replies View Related

Need Some Help In Searching

May 8, 2004

Dear ASP.NET

How can I find records that contain a STRING from some (more than one) other fields ?

for example, I have:

Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"

Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)

How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?

Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)


Thanks in advance, Yovav.

View 3 Replies View Related

Searching With LIKE

Feb 13, 2006

Let say a user wants to search for the name Joe Soap

I have two column's in my table, firstname and lastname

So if I do:


Code:


SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'



it returns nothing! So do I have to split the string Joe Soap or something ?

View 1 Replies View Related

Searching...

Nov 28, 2004

my app use a registration table

StudReg
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stRegDt smalldatetime,
stRegNo bigint,
courseId smallint
)


the application registers the student details to a course.
each student gets a new registration no during registration.

the app should identify repeaters to a particular course by checking another
table RegHistory, which stores the details of student registrations for the previous 5 years.

RegHistory
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stPrevRegDt smalldatetime,
stPrevRegNo bigint,
courseId smallint
)


the application must search the RegHistory table and list out those students who are the repeaters.


the sample entries in the two tables are as follows

StudReg
stName stDOB stGuardianName stRegDt stRegNo courseid
-------------------------------------------------------------------------
abc 01/01/1979 def 20/11/2004 12345 1
def 01/01/1976 xyz 20/11/2004 12346 1
... ..... ... ...... .... ...

mno 24/18/1976 pqr 20/11/2004 12400 1



RegHistory

stName stDOB stGuardianName stPrevRegDt stPrevRegNo courseId
abc 01/01/1979 def 20/11/2001 2345 1
ghi 01/01/1976 xyz 20/11/2001 2346 1
... ..... ... ...... .... ...

dfg 24/18/1976 pqr 20/11/2001 2400 1


to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).


here is my question,

if there are 100,000 students registering in each academic year, we will have
500,000 records in RegHistory Table and 100,000 records in studReg table

if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.

is there any other options to search for repeaters ?

pl discuss

View 11 Replies View Related

Searching

Mar 16, 2004

hi all,
I need help in selecting records from a table based on the given search criteria.
spec:
select * from table where col1='x' and col2='y'... and col6='q'
i may give any combination of column values.
I mean I can't provide 6 values in 'where' condition all the time i submit query.
help me with a stored proc which has 6 input parameters for the 6 columns.

View 9 Replies View Related

Searching

Dec 11, 2006

Maybe a dumb question.. but is there a way to search all tables in a database for a particular word or phrase?

View 4 Replies View Related

Searching For Add-In

Feb 18, 2008

I am searching for an add-in to ssms which lets me choose which server to deploy my code.

For example, I have a complete statement with CREATE PROCEDURE and all code in my query window.

Now I want to run/deploy this code to many servers at once.
Instead of having to reconnect to all server (5), I want an add-in which have checkboxes for me to select.

Anyone heard of this?


E 12°55'05.25"
N 56°04'39.16"

View 6 Replies View Related

XML And Searching

Jul 23, 2005

Hi,I have come to point in my db design where I'm trying to figure whichis the best approach in making it generic (does this really matter?!?)Senario - I have a table called JOBS and this table contains fieldsuch as JobTitle, JobDescription, Salary etcI want to add to this table other attributes which are specific to acertain Job Industries.Solution - Add a join table for each type of industry containingattributes (db is now not generic) OR add a new table with a IndustryType field and a XML field containing the industry specificattributes.If I go the XML way will this just make it complex and slow to query?If not, what is the best way to query an XML field?Thanks,Jack

View 2 Replies View Related

Searching

Jul 23, 2005

I am having a problem with seaching my tables. On my asp.net page ihave 3 text boxes. One for an ID and NAME and ADDRESS. I want to beable to search a table by using all or any combination of the 3 tosearch. But i cant get it right. i am using c# and sql server.any help ideas would be a great helpthanks in advanceructions

View 1 Replies View Related

Searching With '

May 26, 2008

How do we make a search in dbase where in the data you are searching has a " ' " for example Int'l Airport or other wildcards


Regards

View 4 Replies View Related

Searching A Databse

Aug 31, 2006

i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want  4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have  one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks 

View 1 Replies View Related

Best Searching Criteria

Sep 30, 2007

I have a table
 GO
 CREATE TABLE [dbo].[Speech] (  [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY,  [UniqueName] [varchar](52) NOT NULL,  [NativeName] [nvarchar](52) NOT NULL,  [Place] [nvarchar](52) NOT NULL,  [Type] [smallint] NOT NULL,  [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE,  [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE,  [IsFavorite] [bit] NOT NULL,  [IsVisible] [bit] NOT NULL,  [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),  [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
 CREATE PROCEDURE [dbo].[sprocGetSpeech]
  @speechId int = NULL,  @uniqueName varchar(52) = NULL,  @nativeName nvarchar(52) = NULL,  @place nvarchar(52) = NULL,  @type smallint = NULL,  @languageId char(2) = NULL,  @speakerId int = NULL,  @isFavorite bit = NULL,  @isVisible bit = NULL
 AS
  SELECT   SpeechId,   UniqueName,   NativeName,   Place,   Type,   LanguageId,   SpeakerId,   IsFavorite,   IsVisible,   CreatedDate,   ModifiedDate  FROM   Speech  WHERE   SpeechId = @speechId   AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END   AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END   AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END   AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END   AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END   AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END   AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END   AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?

View 1 Replies View Related

Good Searching

Jan 7, 2008

 hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet        Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a")        Dim ds As New DataSet()          Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con)        Try            con.Open()            adapter.Fill(ds, "user")            Return ds        Catch ex As Exception            Console.Write(ex.Message)        Finally            con.Close()            con = Nothing        End Try        ' Next        Return ds    End Functionand class  my item in database is containning  dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx... 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved