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


ADVERTISEMENT

How To Insert Null Values Into A DataBase Field

May 9, 2005

I have a function that updates a table in sql server
If a field is left blank I want the database field set to Null. I use:sqlCmd.Parameter.Add("@somefield, someintvalue) to pass all my values to the database.
if someintvalue is null I want to set @somefield to dbnull
How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?
Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

View 2 Replies View Related

ASP Returning Null Sized Field From MS SQL Database

Jul 20, 2005

We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data up to 400characters and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant

View 1 Replies View Related

How To Pass Null Value To The Database If A Form Field Is Empty?

May 31, 2004

How can I pass into the database (@User_fax = null) if the fax form field is empty, from a command type Stored Procedure? For example:

Dim CmdUpdate As New SqlCommand("Form2_NewUser", strConnection)
CmdUpdate.CommandType = CommandType.StoredProcedure

CmdUpdate.Parameters.Add("@User_fax", SqlDbType.char, 9)
CmdUpdate.Parameters("@User_fax").Value = fax.Text()

...

strConnection.open()
CmdUpdate.ExecuteNonQuery
strConnection.close()



And, the stored procedure inside Sql server:

USE market1
GO
ALTER PROC Form2_NewUser
@User_id bigint, @User_fax char(9),...

AS

SET NOCOUNT ON
UPDATE Users
SET User_fax = @User_fax, ...

WHERE User_id = @User_id
SET NOCOUNT OFF
GO



Thank you,
Cesar

View 4 Replies View Related

Problem With Adding A Date Field To A Database When It Is Null

Jun 21, 2005

I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database:  '---------------------------------------------         ' name: Button_Click()         '---------------------------------------------        Sub Button_Click( s As Object, e As EventArgs )                        Dim strConnect As String              Dim objConnect As SQLConnection              Dim strInsert As String             Dim cmdInsert As SqlCommand            
             'Get connection string from Web.Config             strConnect = ConfigurationSettings.AppSettings("ConnectionString")
             objConnect = New SqlConnection(strConnect)                            strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )"             cmdInsert = New SqlCommand( strInsert, objConnect)             cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value )             cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text )             cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text )             cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text )
             objConnect.Open()             cmdINsert.ExecuteNonQuery()             objConnect.Close()                                  'Display the results "page"                  DisplayResults() 
        End Sub Here's the code for the form:         <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">         <div align="center">    <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">     <tr>      <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>      <td>&nbsp;</td>     </tr>              <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Client's Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <asp:dropdownlist id="dropClient" runat="server" />        </p>      </td>     </tr>    <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Domain Name:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />               </p>      </td>                         <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Registrar:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />       </p>      </td>     </tr>     <tr>      <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>          Expiration Date:</nobr></strong></font></td>      <td colspan="2" valign="top" bgcolor="#e8e8e8">       <p>        <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />       </p>      </td>     </tr>               
     <TR>      <TD>      </TD>      <TD align="center">       <asp:Button                            Text="Submit"                            OnClick="Button_Click"                            Runat="Server" />      </TD>     </TR>    </table>  </form>  </div>

View 1 Replies View Related

Searching For Records With Null Values

Oct 9, 2007

I'm hoping someone can help me with my problem.
I'm reading in records from a 'flat file' and loading them into sql.
I have 5 values I'm loading in.  I first check my sql db, if all 5 values match a current record in sql, I don't want to load the record, because it's already there.  If it doesn't exist, I need to load it.
It works fine as long as none of my values are NULL.  But if I have a record with a field, say Gender, that is null, if its a new record it loads fine, setting gender to NULL in sql.  But then when I encounter another record, which is identical, I'm testing to see if it already exists by  doing a 'select where Gender = @Gender'   and it always returns that the record does not exist in the db - even though it exists (because its using the = instead of is null)! 
I need some mechanism where if the value is null it tests 'Gender is null'  but if there is a value, it tests gender=gender, [and I need this for all my parameters].  Or is there some other way to do this? 
 
 Here is my code for looking to see if the record exists:  
public static int ExistInsured(int CaseID, object InsuredLastName, object DateOfBirth, object CurrentAge, object Gender)
{SqlConnection conn = new SqlConnection(connStng.ToString());
SqlCommand cmd = new SqlCommand("SELECT top 1 InsuredID from Insured Where PolicyID = (Select PolicyID from Policy where CaseID = @CaseID) and LastName = @InsuredLastName and DateofBirth = @DateofBirth and Gender = @Gender", conn);
 int result = -1;
cmd.CommandType = CommandType.Text;cmd.Parameters.AddWithValue("@CaseID", CaseID);
cmd.Parameters.AddWithValue("@InsuredLastName", InsuredLastName);cmd.Parameters.AddWithValue("@DateOfBirth", DateOfBirth);
cmd.Parameters.AddWithValue("@CurrentAge", CurrentAge);
cmd.Parameters.AddWithValue("@Gender", Gender);
 
 
conn.Open();
try
{object ret = cmd.ExecuteScalar();
if ((ret != null) && (ret is int))result = (int)ret;
}
finally
{
conn.Close();
}return result;
 
}
 

View 6 Replies View Related

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

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 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

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

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

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

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.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 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

Searching Database

Oct 11, 2005

i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description,           Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName,          3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+          3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+          3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+          3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+          3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)           AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC  
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,   Product.ImageALT, Artist.ArtistName,          (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) *          CASE              WHEN @Word2 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)          END *          CASE              WHEN @Word3 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)          END *          CASE              WHEN @Word4 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)          END *          CASE              WHEN @Word5 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)          END          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0  AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1                     AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0  BEGIN     SELECT @NextSpacePos = CHARINDEX(' ', @Phrase)     IF @NextSpacePos = 0       BEGIN         SELECT @ExtractedWord = @Phrase         SELECT @Phrase=''       END     ELSE       BEGIN         SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1)         SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos)       END
     IF @SoundexWord = SOUNDEX(@ExtractedWord)       SELECT @Matches = @Matches + 1  END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?

View 2 Replies View Related

Searching A Database...

Jul 26, 2006

Hi,

This is the first database I have ever created, so please bear with me.

I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).

I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)

SELECT
word
FROM dbo.words
WHERE word= 'hello'

This finds the word hello.

In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)

This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.

What I am trying to do is then take all of these possibilities and compare them against the dictionary.

I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:

WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'

I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.

I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.

Thanks in advance

View 5 Replies View Related

Searching A Database

Oct 4, 2007

Im trying to do a database query based on user input from a text field. I pulled apart the string that was
entered into the search form and stored it in textArray. The problem I
am having is when I include commmon words such as the, near, view and so
on into the search field I end up with zero results. when I only search
for less common words such as rocky and argentina for example the search
works fine. at first I thought that maybe it was generating too many
results and was screwing up but when i search for rocky it works and
when i search for near rocky it doesnt.

I realize the code might not be the most efficient but here it is...


@searches_pages, @searches = paginate(:searches, :per_page => 10,
:conditions => getSearch(textArray,params[:country]))

def getSearch(textArray,selectedCountry )
result = []
string = ""
if selectedCountry != "Optional Field"
string << "country = (?) and "
end
textArray.each do |x|
if textArray[textArray.length - 1] == x
string << "match(country,caption, keywords, notes) against
(?) "
else
string << "match(country, caption, keywords, notes) against
(?) and "
end
end
result << string
if selectedCountry != "Optional Field"
result << selectedCountry
end
textArray.each do |x|
result << x
end
result
end

Im not sure if i supplied enough information but I am trying to finish
this project soon so any type of responses would help. Also, if there
is an easier way to do an sql search based off of what is entered into
the search field please let me know. The reason I did this is because I
wasn't sure how many words the user would be entering into the field.
And without knowing this I could not hard code the conditions => so I
wrote a helper method.

View 2 Replies View Related

Searching Database Through Asp.net

Oct 12, 2007

OK I have a search page and the query that is being send from the search box is

"SELECT * FROM [problems] WHERE ((problemBody LIKE '%' + @search_id + '%')OR @search_id IS NULL)"

Now say I have in the column for problemBody "Search the database" If i Type in the search field search the, or the database, or data, ot search, or even just s it will bring back records, But if I do not use exact keywords such as "search database" it will not bring back anything. How do I make it search all the keywords used?? like a normal search engine.

Thanks

View 20 Replies View Related

Database Searching

Jan 28, 2008


I am finishing up my senior project application but I wanted to include a search function that would search all the tables of the database and look for matching text that is input by the user. I am not really looking for code or anything just some pointers in the right direction. I was thinking that I would have to create a view that is populated by a sub-query. My problem is how do I output the results of a search of every relevent table in the database when they all have very different column names and data types? Im guessing column aliases are involved somehow but I am not really sure where I should start. Any suggestions would be appreciated.

View 9 Replies View Related

Null In Bit Field?

Jan 19, 2000

sorry, this is probably a really basic question ..........

is there any way to enable the bit data type in SQL Server to accept nulls?


thanks

nyoobee

View 1 Replies View Related

How To Add NULL To INT Field

Jun 12, 2007

Hi all,

I have a table, named systems, with fields as follows.

field datatype
___________________
system varchar(50)
modelID int
.... ....
... .... etc...

The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.

In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.

My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.

Is this a typical issue that can be resolved with a simple property change?

Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?

Thank you for any help in advance.

View 11 Replies View Related

How To Set A Field Value To Null

Mar 26, 2014

I have a column called person.last_name, and I want to set this field to NULL if person.last_name ='test'///

I have tried this but don't work: ISNULL ({person. last_name},'<>"test'))

View 7 Replies View Related

Set Field Value To Null

Apr 22, 2007

May i know how to generate a SQL query to set a field of row to Null? Thank you.

View 2 Replies View Related

Searching In Sql Server Database

Mar 31, 2008

hi there,i have a textbox in my page and a button,when the button is clicked the application will search for the text written in the textbox in my database which is sql server, it works fine in my system but when i upload my website in the web it doesn't work correctly i mean that it dosent find all the matches, why this happens? is it possible that this problem occur because of the different fonts which is used in sql server and the font is used in my application?
thanks for help

View 3 Replies View Related

Searching In All Tables In A Database

Apr 7, 2005

Whats the easiest way to search for a keyword in all the tables present in the database.
Or searching in 5-6 tables.
Thanks,
AzamSharp

View 1 Replies View Related

Searching For Values Within A Database?

Oct 2, 2014

I've been given read access to a database and I also am looking at a GUI which draws data from the database. I am trying to map the results I see from the GUI to find the columns that the data is in... So.. big database, takes a long time to search the entire thing so I try to narrow it down by the following:

Code:
select * from information_schema.columns where table_schema = 'db19' and table_catalog = 'masterdb' and
table_name in(select table_name from information_schema.tables where table_type = 'base table' and table_catalog = 'masterdb' and table_schema = 'db19' and data_type in ('text' and 'varchar'))

This essentially gives me a list of tables and columns whos data type is either text or varchar. Once I have this list... I then run the following on each:

Code:
select top 1 [col_name] from [table_name] where [col_name] like ' (here is the value I want to search) '

So this runs through each table, looking to see if a value exists and if so, prints the result.

I am then left with a much much smaller list that I can manually look through and find the one I am looking to specifically find.

Is it possible to do this running only one query... where the output gives me all the columns in with a specific data type that contain a value I enter Anything else to make this more efficient

I am aware that there are data mining programs that could probably do this however I only have read access on the database which often causes a problem. The application I am using is "Aqua Data Studio" ....

View 3 Replies View Related

Searching Entire Database

Mar 11, 2004

Is there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.

Any ideas?

Thanks in advance.

View 6 Replies View Related

Searching The Entire Database

Aug 28, 2005

Hi friends

Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?

Can anyone tell me how can i search that



Vicky

View 2 Replies View Related

Searching An Entire Database

Dec 28, 2005

Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.

I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.

My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:

My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.

If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.

Thanks a lot,
Jack Smith

View 2 Replies View Related







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