Searching all columns without using column name

Hi, 

I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
0
snellog
12/30/2003 11:23:02 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
870 Views

Similar Articles

[PageSpeed] 8

If the data is in a DataSet, you can access rows and columns using indexes:

myDS.Tables[0].Rows[0][0]
will get the first row, first column. .Rows and .Columns have a .Count property that can allow you to get all columns and rows:

if ( myDS.Tables.Count>0 )
{
for ( int loop=0 ; loop<myDS.Tables.Count ; loop++ )
{
this.textBox1.Text+="\r\nTable: " + myDS.Tables[loop].TableName + "\r\n";
for ( int row=0 ; row<myDS.Tables[loop].Rows.Count ; row++ )
{
for ( int col=0 ; col<myDS.Tables[loop].Columns.Count ; col++ )
{
this.textBox1.Text+=myDS.Tables[loop].Columns[col].ColumnName + " : " ;
this.textBox1.Text+=myDS.Tables[loop].Rows[row][col].ToString();
this.textBox1.Text+="\r\n";
}
}
}
}

Starting with ASP.NET 2.0? Look at:
Programming Microsoft Web Forms
My Blog
0
douglas
12/31/2003 1:32:06 PM
Exec sp_columns @table_name = 'customers'


-----
sp_columns
Returns column information for the specified tables or views that can be queried in the current environment.
Syntax
sp_columns [ @table_name = ] object
[ , [ @table_owner = ] owner ]
[ , [ @table_qualifier = ] qualifier ]
[ , [ @column_name = ] column ]
[ , [ @ODBCVer = ] ODBCVer ]
Arguments
[@table_name =] object
Is the name of the table or view used to return catalog information. object_name is nvarchar(384), with no default. Wildcard pattern matching is not supported.
[@table_owner =] owner
Is the object owner of the table or view used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is not supported. If owner is not specified, the default table or view visibility rules of the underlying DBMS apply.
In Microsoft® SQL Server™, if the current user owns a table or view with the specified name, that table's columns are returned. If owner is not specified and the current user does not own a table or view with the specified object, sp_columns looks for a table or view with the specified object owned by the database owner. If one exists, that table's columns are returned.
[@table_qualifier =] qualifier
Is the name of the table or view qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
[@column_name =] column
Is a single column and is used when only one column of catalog information is wanted. column is nvarchar(384), with a default of NULL. If column is not specified, all columns are returned. In SQL Server, column represents the column name as listed in the syscolumns table. column can include wildcard characters using the underlying DBMS's wildcard matching patterns. For maximum interoperability, the gateway client should assume only SQL-92 standard pattern matching (the % and _ wildcard characters).
[@ODBCVer =] ODBCVer
Is the version of ODBC being used. ODBCVer is int, with a default of 2, indicating ODBC Version 2. Valid values are 2 or 3. Refer to the ODBC SQLColumns specification for the behavior differences between versions 2 and 3.
Return Code Values
None
Result Sets
The sp_columns catalog stored procedure is equivalent to SQLColumns in ODBC. The results returned are ordered by TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME.

Kay Lee
MySpace.com - http://www.myspace.com/kragie
Infrastructure Group
MySpace.com

- Code to live, but Live to code.
0
KraGiE
12/31/2003 5:49:36 PM
I need the same thing that snellog does, although I'm willing to be constrained to a single table.  But I'd rather keep the logic in a stored procedure if possible.

I could use a sp like...
~~~~~~~~~~~~~
CREATE PROCEDURE sprSelectPeople_Text
(
@strSearch nvarchar(255)
)
AS
SELECT
PeopleID,
LastName,
FirstName,
Salutation,
BirthDate,
BillAmount
FROM
tblPeople
WHERE
LastName LIKE '%' + @strSearch + '%'
OR FirstName LIKE '%' + @strSearch + '%'
OR Salutation LIKE '%' + @strSearch + '%'
GO
~~~~~~~~
....but that only searches the string fields. I could do this previously with a sql string compiled in page code, like...
~~~~~~~~
Dim scmPeople As SqlCommand
scmPeople = New SqlCommand( _
"SELECT tblPeople.* FROM tblPeople " + _
"WHERE tblPeople.LastName LIKE ""%" + strSearch + "%"" + _
"OR tblPeople.FirstName LIKE ""%" + strSearch + "%"" + _
"OR tblPeople.Salutation LIKE ""%" + strSearch + "%"" + _
"OR tblPeople.BirthDate = #" + strSearch + "# " + _
"OR tblPeople.BillAmount = " + strSearch, _
scnCnn _
)
~~~~~~~~
....but I'm trying to give up my bad old ways. Isn't there some stored procedure syntax (short of a full text search that indexes every table column) that will either (1) accept the @strSearch parameter, convert it appropriately and search for it every column, like...
~~~~~~~~
SELECT tblPeople.* FROM tblPeople
WHERE tblPeople.* = strSearch
~~~~~~~~
.... or (2) a more manual approach where I can test each column individually and code the appropriate data type conversion in the sp, like...
~~~~~~~~
SELECT ...
WHERE ...
... OR BirthDate = cast( strSearch, SmallDate )
OR BillAmount = cast( strSearch, Money )
~~~~~~~~
What's the best way to do this?
0
memcduff
3/20/2004 1:42:13 PM
Two questions here subtly different. The more generic question about seatch any table on all columns has been answered, well a strong clue with sp_columns...I'm not about to spoon feed either ;)

The 2nd question about using a stored proc for a specific table is pretty easy too. The stored proc will implement your "bad old days" method, there is no short cut.
An alternative is to use Full Text/Index searching. That's a different technique and will require a bit of reading up on.
0
pkr
3/20/2004 7:41:03 PM
Reply:

Similar Artilces:

How to use a full column name (table name and column name) with a DataReader
Hi, I have a problem retrieving the data with a DataReader from a stored proc that performs a join of two table with the same column's names but different data. Let me explain with an example: I have two tables: Table1: Id, Name Table2: Id, Name, IdTable1 The sproc is something like this: CREATE PROCEDURE [dbo].[SearchByName] (@Name nvarchar(40)) AS BEGIN SELECT * FROM dbo.Table1, dbo.Table2 WHERE Table1.Id = Table2.IdTable1 AND Table1.Name LIKE '%' + @Name + '%' END And my c# code is something like this: Database db = Databas...

Need query to get distinct column name and details based on that column using sql server 2000
hi, iam having three tables as shown below table 1                                                      table 2                                     &...

DW Designer Column Name vs. Database Column name use in expressions
Can you access the DW Designer column name in expressions (I have used the database column name, but have had no luck figuring out how to access the Designer column name). thanks, David I'm not sure you can. What are you trying to accomplish? There might be another way of doing it. Regards, Dave Fish Sybase TechWave 2005! www.sybase.com/techwave Download an evaluation version of DataWindow .NET 1.5 Today!: www.sybase.com/datawindow.net DataWindow.NET code examples available on CodeXchange: http://datawindownet.codexchange.sybase.com PowerBuilder 10.5 Bet...

Using @column = [column] SQL Server 2000
Should the following select statement return ALL the records including the records where [district] is null?  Currently it is only returning non null records.  I tried it with a default value and without with the same results. SelectCommand="SELECT [ID], [last], [first], [district], [TA] FROM [regd] Where ([district] Like case when @district is null then [district] else @district end)" <SelectParameters ><asp:ControlParameter ControlID="Last_textbox" Name="district" defaultvalue="%" PropertyName="Text" Type="Str...

Get Column Number Using Column Name
This ones burning me because, I am sure i've done it before and It's simply slipping my mind. I need to be able to get a column number using the column name. i.e. Column name = 'effective_date'. Using effective date - get the column number that belongs to effective date. Any help is appreciated!! Thanks!! William dw.Describe("effective_date.ID") Good Luck Michael Walker Cascadia Software William_Shiers wrote: > This ones burning me because, I am sure i've done it before and It's simply > slipping my mind. > ...

Showing my own column names instead of database column names in datagridview-windows app. C#.net
HiI already bind a DataGridView to a databse. My DataGridView is showing column names as it is in the table. I want to show user-friendly column names in the grid instead of column names in the database table. Please help me in this reagard. Thanx in advance Your statement indicates to me that your columns are dynamically added to the grid, that's why it's showing the database column names. You can show user-friendly column names by changing your query like so: SELECT fName as 'First Name' FROM Users.DarmarkMark as Answer, if this reply answers your post. Go to "E...

validation message using column label, not column name
win2k / pb8.01 When I forget to enter a required value on a dw, I get a MessageBox like this Required value missing for COLUMN NAME on row N Please enter a value For legacy reasons my column name is called lineval and can't be changed I have set a column label in the datawindow painter to "Line Value" which would look a lot nicer in an error message. Is there anyway to specify in the dw to use the column label instead of the column name in the error message ? thanks Alex Well that's not a PB-generated error, so it must be in your code somewhere...

how to fetch column using column index in sql server.
Hi, I have several columns in my table, but I want to fetch data using column index instead of column name in select  command. is there any way to do this.Please remember to click “Mark as Answer” on the post that helps youJasim AkhtarNew Delhi ( INDIA ) Yes there is.It's called dynamic SQL and it's a complicated process.See http://www.sommarskog.se/dynamic_sql.html Please click "Mark as Answer" if my reply solved your problem.CheersRammohan...

SQL Msg 107 Error... The column prefix does not match with a table name or alias name used in the query.
Can someone please answer a problem that I've run into.  I know that it's probably something stupid.  I keep getting this error:Server: Msg 107, Level 16, State 3, Line 1The column prefix 'vFirstTimeEntered' does not match with a table name or alias name used in the query.Here is my query:-----------------------------------------------------------------Update  TimeSheetSectionSet TimesheetSection.SECSTARTDT = vFirstTimeEntered.schlstuidWhere timesheetsection.schlstuid = vFirstTimeEntered.schlstuid AND timesheetsection.sectionid = vFirstTimeEntered.sectionid AND&...

get column value in DW expression without column name
Hi everyone! I've got a common experssion that I'd like to put on every bold column property: I'd like set the column font to bold if the previous column value is equal to the current. As I have many columns, even dynamic columns, I don't know their name in order to set an expression like this: "if (col_name = col_name[-1], ...)" So any idea? Hi Matthieu, get all object names using the expression "datawindow.objects", split this tab separated list into single object names, check every object wether it is a column using the expression &quo...

When is a column not a column?
I have several extremely similar datawindows that I have to maintain that go against the same table. Recently, I had to add a column to that table, and also add the column to ALL of these datawindows that go against this table. I added the column to the first datawindow, set up the edit control style, etc. I then did a copy/paste of the columns edit control to the remaining dw's (of course remembering to first add the column in SQL). Everything (update props, column specs, column props) seems to indicate that the pasted column is linked to the column I selected, HOWEVER, when I...

Hiding columns in the ItemDataBound event without using the column index
Lately I've needed to hide certain columns in DataGrids depending on the specific data source so I create a property at the top of the page like so: private string DisplayProjDesc //DisplayProjDesc property - for ItemDataBound event of the DataGrids to determine whether to display project description { set { this.lblBoolDispProjDesc.Text = value; } get { return this.lblBoolDispProjDesc.Text.ToUpper(); } }Then, in the ItemDataBound event of the DatGrid, I use something like: if(this.DisplayProjDesc=="TRUE") { this.dgProject.Columns[7].Visible=true; } else { this.dgProject....

VB.NET 1.1 Searching through DataList Column by Column
Hi,I am trying to search through a DataList column by column.  I am doing this so that if each cell within that sector is empty then I will hide that column.  I have implemented a quick function in order to do this, however, it is searching through the datalist row by row.  Below is my code, with explanations of what the code is trying to do.  I think the problem is to do with the String sector.  Is there anyway that I can change the sector string so that it will iterate through the table (dataset) column by column.Code:Dim myData As Data.DataSet = dbAccess.getChildr...

How to place in DataGrids’s first column table’s column name, in second Datagrid’s column table’s column value?
Hello I use C#. I’m interesting are there samples solved my problem. Andrew. You will need to loop through the datasource of the datagrid or through the datagrid items and fetch the values for the second datagrid.Sonu Kapoor [MVP] Feel free to ask me any .NET question | Ajax Data Controls Forum Thank you! I solved my problem at the server cite. Andrew. ...

Web resources about - Searching all columns without using column name - asp.net.sql-datasource

Help:Searching - Wikipedia, the free encyclopedia
Wikipedia has a search engine built in, which can be used to locate material on Wikipedia. The search box is located at the top right on every ...

Spotify Searching, Sharing, Stats Added To Musical Artists’ Facebook Pages
Musical artists tied in with Spotify are seeing some new features on their Facebook pages, including the ability by Facebook users to search ...

Facebook Searching For Ways to Break Into Mobile Apps
Today’s mobile panel at the Facebook f8 developer conference was notable for the subject it didn’t cover: Facebook itself. With four panelists ...

App Store - RyeBooks: Little Tadpoles Searching for Mommy -by Rye Studio™
Get RyeBooks: Little Tadpoles Searching for Mommy -by Rye Studio™ on the App Store. See screenshots and ratings, and read customer reviews.

Searching for the color of moss VII - Flickr - Photo Sharing!
Explore tillwe's photos on Flickr. tillwe has uploaded 11549 photos to Flickr.

Skinfan Does Minecraft - Episode 10: Searching for 1.3 - YouTube
Hello everyone! Welcome to Skinfan Does Minecraft. In this let's play series I build some things, show you some cool stuff in Minecraft, and ...

Adam Scott searching for missing link
Adam Scott ascended to world No.1, won an event, had 10 top 10 finishes and extended his cut streak to 44 events but wants more from his game. ...

Toronto police searching for man after crash near Dufferin and Dundas
Toronto police are searching for a suspect after they found a gun in his car at the scene of a collision near Dufferin Street and Dundas Street ...

Searching ‘bubble level’ in Google Search now presents you with an interactive level
One Googler took to Reddit last week to tell the world about a new feature that his team (presumably) added to Google search. Now, if you search ...

LAPD searching for missing Hollywood producer
Los Angeles police are searching for a Hollywood producer who has not been seen since before Thanksgiving.

Resources last updated: 1/2/2016 3:59:25 AM