I required to build the search feature for my application which contains combination of at least 20 search fields e.g firstname, lastname. date of birth, sign up date ,etc... I am just wondering what is the best way to do it ,should I create stored procedure with 20 input parameters or should I build it based on each search fields. I need to provide the search results via web services. Could anyone help me?
Thank you
Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
Hi! Maybe I
ask simple question. But for me it’s not so. The matter’s I plan organize
search at my website. It has catalog of firm’s and good’s in SQL Server. I plan
to build dictionary table with two fields’ search_item and link. But problem is
that I should put all search variant’s in field search_item(name of firm, name
of boss, name of good etc.) Maybe there’s another concept to build search?
Thank’s for any answer.
Andrey.
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies View RelatedI'm running SQL Server 2000. I have an interesting form I am creating for a client which has on it literally 20,500 fields that need to be stored in the system which I then have to create reports off of for statistics and trends.
I'm not sure how I should go about storing that large amount of information in SQL Server with the limitations of the size of a table. Would it be best to create 20 some tables to store it, or is there a better fashion to store it. 90% of the fields are numbers ranging from 0 to 100.
Thanks for any suggestions you can come up with!
I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.
I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies View RelatedHi,
I have a report (rdlc) in my WinForm project that the data are filtered in accordance with two dates: Initial and End. These two dates, the user inform in a Form of the project. Well, what I need I am to inform in the report these two dates. How that I make to pass these two dates of form for the report?
Thank you!
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'
[code]....
i am trying to build a dynamic where "or" clause finding difficulties.
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View 3 Replies View RelatedHi, I'm new around here and am stumped. I have created a form where I submit the information into a SQL Server 2000 table. When I submit the information through the form, only the first character of each field populates inside of the SQL table. This happens for every field except for two, zip and comments. The zip field is numeric and the comments field is text. All the other fields are varchar fields; and these are the fields with the problems. I have attempted to change the data type to text, but that did not work either. I am doing this through a stored procedure I created. Here is the asp code:
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents txtFirstName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtLastName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress1 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents txtEmail As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtComments As System.Web.UI.WebControls.TextBox
Protected WithEvents ddlState As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift2 As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift3 As System.Web.UI.WebControls.DropDownList
Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=Bluestreak;integrated security=SSPI;persi" & _
"st security info=False;workstation id=NDAVENPORT2;packet size=4096"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "dbo.[InsertFreeOrders]"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@firstname", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lastname", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@address1", System.Data.SqlDbType.VarChar, 100))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@address2", System.Data.SqlDbType.VarChar, 100))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@city", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@zip", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@email", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@phone", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item1", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item2", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item3", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@comments", System.Data.SqlDbType.VarChar, 2147483647))
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If IsValid Then
SqlCommand1.Parameters("@firstname").Value = txtFirstName.Text
SqlCommand1.Parameters("@lastname").Value = txtLastName.Text
SqlCommand1.Parameters("@address1").Value = txtAddress1.Text
SqlCommand1.Parameters("@address2").Value = txtAddress2.Text
SqlCommand1.Parameters("@city").Value = txtCity.Text
SqlCommand1.Parameters("@state").Value = ddlState.SelectedItem.Text
SqlCommand1.Parameters("@zip").Value = txtZip.Text
SqlCommand1.Parameters("@email").Value = txtEmail.Text
SqlCommand1.Parameters("@phone").Value = txtPhone.Text
SqlCommand1.Parameters("@item1").Value = ddlGift1.SelectedItem.Text
SqlCommand1.Parameters("@item2").Value = ddlGift2.SelectedItem.Text
SqlCommand1.Parameters("@item3").Value = ddlGift3.SelectedItem.Text
SqlCommand1.Parameters("@comments").Value = txtComments.Text
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
SqlConnection1.Close()
Response.Redirect("Success.aspx")
End If
End Sub
End ClassMy stored procedure looks like this:CREATE PROCEDURE dbo.InsertFreeOrders ( @firstname varchar( 50 ), @lastname varchar( 50 ), @address1 varchar( 100 ), @address2 varchar( 100 ), @city varchar( 50 ), @state varchar( 50 ), @zip numeric, @email varchar( 50 ), @phone varchar( 50 ), @item1 varchar( 50 ), @item2 varchar( 50 ), @item3 varchar( 50 ), @comments text )ASInsert FreeOrders ( o_first_name, o_last_name, o_address_1, o_address_2, o_city, o_state, o_zip, o_email, o_phone, o_item_1, o_item_2, o_item_3, o_comments ) Values ( @firstname, @lastname, @address1, @address2, @city, @state, @zip, @email, @phone, @item1, @item2, @item3, @comments )GOSo it is working, just not completely and I am stumped here. Any help would be great! Thanks.
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
View 1 Replies View RelatedDear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" > Sub Page_Load(Src As Object, e As EventArgs) If Page.IsPostBack Then Dim ConLath As SqlConnection Dim comLath As SqlCommand Dim insertcmd conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password") ConLath.Open() insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)" comLath = New SqlCommand(insertcmd, ConLath) comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12)) comLath.Parameters("@t_d").Value = trade_date.Text comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8)) comLath.Parameters("@s_p").Value = sett_price.Text comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8)) comLath.Parameters("@p_s").Value = post_close.Text comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8)) comLath.Parameters("@v_oq").Value = vol_oq.Text comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8)) comLath.Parameters("@i_oq").Value = oi_oq.Text comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8)) comLath.Parameters("@v_qn").Value = vol_qn.Text comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8)) comLath.Parameters("@v_qw").Value = vol_qw.Text comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8)) comLath.Parameters("@i_qn").Value = oi_qn.Text comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8)) comLath.Parameters("@i_qw").Value = oi_qw.Text
Try comLath.ExecuteNonQuery() Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
comLath.Dispose() ConLath.Close() End If End Sub
</script>
/*Subject: How to build a procedure that returns differentnumbers of columns as a result based on a parameter.You can copy/paste this whole post in SQL Query Analyzeror Management Studio and run it once you've made surethere is no harmful code.Currently we have several stored procedures which finalresult is a select with several joins that returns manycolumns (150 in one case, maybe around 50 the average).We have analyzed our application and found out that mostof the time not all the columns are used. We haveidentified 3 different sets of columns needed indifferent parts of the application.Let's identify and name these sets as:1- simple set, return the employee list for example2- common set, return the employee information (whichinclude the simple set)3- extended set, return the employee information (whichinlude the common set which itself includes the simpleset) + additional information from other tables, maybeeven some SUM aggregates and so on (I don't know forexample, how much sales the employee did so far).So the bigger sets contain the smaller ones. Please keepreading all the way to the bottom to better understandtechnically what we are trying.Here is a code sample of how our current procedureswork. Please note that the passing parameter we can eitherpass a Unique Identifier (PK) to retrieve a single record,or if we pass for example -1 or NULL we retrieve all theemployee records.*/create table a ( apk int primary key, af1 int, af2 int, af3 int, af4int, af5 int, af6 int)create table b ( bpk int primary key, bf1 int, bf2 int, bf3 int, bf4int, bf5 int, bf6 int)create table c ( cpk int primary key, cf1 int, cf2 int, cf3 int, cf4int, cf5 int, cf6 int)create table d ( dpk int primary key, df1 int, df2 int, df3 int, df4int, df5 int, df6 int)insert a values (1,1111,1112,1113,1114,1115,1116)insert a values (2,1211,1212,1213,1214,1215,1216)insert a values (3,1311,1312,1313,1314,1315,1316)insert a values (4,1411,1412,1413,1431,1415,1416)insert a values (5,1511,1512,1513,1514,1515,1516)insert a values (6,1611,1612,1613,1614,1615,1616)insert b values (1,2111,2112,2113,2114,2115,2116)insert b values (2,2211,2212,2213,2214,2215,2216)insert b values (3,2311,2312,2313,2314,2315,2316)insert b values (4,2411,2412,2413,2431,2415,2416)insert b values (5,2511,2512,2513,2514,2515,2516)insert b values (6,2611,2612,2613,2614,2615,2616)insert c values (1,3111,3112,3113,3114,3115,3116)insert c values (2,3211,3212,3213,3214,3215,3216)insert c values (3,3311,3312,3313,3314,3315,3316)insert c values (4,3411,3412,3413,3431,3415,3416)insert c values (5,3511,3512,3513,3514,3515,3516)insert c values (6,3611,3612,3613,3614,3615,3616)insert d values (1,4111,4112,4113,4114,4115,4116)insert d values (2,4211,4212,4213,4214,4215,4216)insert d values (3,4311,4312,4313,4314,4315,4316)insert d values (4,4411,4412,4413,4431,4415,4416)insert d values (5,4511,4512,4513,4514,4515,4516)insert d values (6,4611,4612,4613,4614,4615,4616)gocreate procedure original_proc @pk int asif @pk = -1set @pk = nullselecta.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4fromajoin b on a.apk = b.bpkjoin c on b.bpk = c.cpkjoin d on c.cpk = d.dpkwherea.apk = ISNULL(@pk, a.apk)goexec original_proc 1go/*Currently the above SP is a single SP that is basicallyreturning ALL possible needed data. However most of thetime we might need to call and retrieve a simple employeelist.So we thought about modifying the stored procedure byadding an extra parameter that will indicate which setof columns to return.For modifying the stored procedure in order to get avariable name of columns returned and avoidingrepeating code, we built 4 objects: the storedprocedure being called, one table function and 2 views.One table function so that we are able to pass a parameter.The views since they do not accept parameters they arealways joined at least with the inline table function.The stored procedure generates in its body a dynamicSQL statement, where it queries the table function andthe views, depending which set is required. Here is acode sample of our current design (you need to run theprevious code in order for this to work).*/create function _1_set(@pk int)returns tableas return(select a.apk, a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2from ajoin b on a.apk = b.bpkwhere a.apk = ISNULL(@pk, a.apk))gocreate view _2_set asselect b.bpk, b.bf3, b.bf4, c.cf1, c.cf2from bjoin c on b.bpk = c.cpkgocreate view _3_set asselect c.cpk, c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4from cjoin d on c.cpk = d.dpkgocreate procedure new_proc @pk int, @set int asdeclare @sql nvarchar(4000)if @pk = -1set @pk = nullset @sql = 'select * from _1_set(@pk) fs 'if @set 1set @sql = @sql + 'join _2_set ss on fs.apk = ss.bpk 'if @set 2set @sql = @sql + 'join _3_set ts on ss.bpk = ts.cpk 'exec sp_executesql @sql, N'@pk int', @pkgoexec new_proc 1, 3go/*For executing the new procedure, we pass parameter 1for the smaller set, 2 for the medium size set or 3for the complete set.For example when we want to retrieve the common setwe pass the Unique Identifier of the employee to theSP and then we pass the type of set we want to useas the second parameter (1 for simple set, 2 forcommon set and 3 for extended set).The SP has the IF and dynamic SQL to add more JOINs.We would like to know what you think of this approachand if you know a simpler way of doing it.For cleaning up the test objects run the following code.*/drop procedure original_procdrop procedure new_procdrop function _1_setdrop view _2_setdrop view _3_setdrop table adrop table bdrop table cdrop table dAs always I would appreciate any feedback, opinion,comments, ideas and suggestions.Thank you
View 9 Replies View RelatedHi,
Can this be done? Please advise. Thanks.Is there any template available in sql for creating forms?
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
View 2 Replies View Related
Hi,
I would like to know how to call a report from a report viewer control in a web application. The reporting services is forms authenticated. I've done this authentication using the sample solution given by microsoft (adding custom security extension). Now am able to view the reports from Report manager and Report server URL. But i dont know how to authenticate the user from my web application and pass the credentials to the report server to view the report in report viewer control. Can somebody help in this? its bit urgent. Thanks in advance
I've done some simple sql's for searching a field using Like,But this one is different.
I am adding a param named @searchText
I would like to bring back all records in all the fields listedbelow that has that string in the field...
WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0)
So, each line/field above I want to search for the string and includein the dataset.
Anyone can point me in the right direction?
Thanks,
Zath
I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
Hi,I want to run queries on a table that has binary fields in it. How do Ifilter on a binary field? E.g. One of the fields is called'Account_Manager_ID' which is binary - I would like to do a simple Select *from company where Account_Manager_ID = 'blah blah blah'When I do this, it returns no data. How do I get round this?Thanks!
View 14 Replies View RelatedDoes anyone know how to write a stored procedure that will search through every field of every table of every table of every database to look for a specific data that matches a certain pattern and return the database, table, and field name?
Thanks
I apologize for the newbie sort of question, but I could not find an answer in an SQL book nor via Google.
I wish to search for a text string in ALL fields of a table. This will be used to provide a simple search box in a web application.
So far, the only method I've found to accomplish this is follows:
SELECT *
FROM Inventory
WHERE SerialNumber LIKE '%searchstring%' OR UserName LIKE '%searchstring%' OR Location LIKE '%searchstring%' ... etc
My goal is to accomplish something like the following. This, of course, does not execute properly since * can only be used following SELECT, but you can get an idea of the target behavior:
SELECT *
FROM Inventory
WHERE * LIKE '%searchstring%'
I'm using MSDE with Visual Basic .Net. Any suggestions on how to accomplish this?
Thanks for all help,
Kieran
I hava at large database and I need to search all tables for the string 'NaN' and replace it to null to enable conversion.
Is there any way to do this so that i do not need to write an update query for every column?
Would appretiate an example if anyone knows how to manage this.
Thanks!
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
View 2 Replies View RelatedGreetings,I used to do this kind of query in Ingres, where I concatenate(+)various fields as one field and search accordingly.Is there an equivalent method in SQL server ?SELECT a.rsrcid,a.rsrchqnumber,c.perslastname,c.persfirst name,b.asgtid,b.asgtactualstartdate,b.asgtactualenddate ,CASE b.enumstateWHEN '2' THEN 'Running'WHEN '3' THEN 'Cancelled'WHEN '4' THEN 'Closed'WHEN '6' THEN 'Open'END AS statusFROM pblocal.dbo.resources aINNER JOIN pblocal.dbo.assignments b ON b.asgtrsrcguid = a.rsrcguidINNER JOIN pblocal.dbo.persons c ON c.persguid = a.rsrcpersguidWHERE a.rsrcid+a.rsrchqnumber+c.perslastname+c.persfirst name not in(SELECT e.rsrcid+e.rsrchqnumber+g.perslastname+g.persfirst nameFROM dtlocal.dbo.resources eINNER JOIN dtlocal.dbo.assignments f ON f.asgtrsrcguid = e.rsrcguidINNER JOIN dtlocal.dbo.users h ON h.userguid = e.rsrcuserguidINNER JOIN dtlocal.dbo.persons g ON g.persguid = h.userpersguid)
View 6 Replies View Relatedi am Create a StoreProcedure That Search in All Tables And Filds by Keyword
and return one result????????
I am trying to write a simple search page that will search all the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:
SELECT Table.*
FROM Table
WHERE * = @USERINPUT
But thats not working. Can someone help.? Thanks..
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
View 1 Replies View RelatedI have a table having multiple fields but I need to extract data based on the two fields.
Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758
I want the result with 2 cases
1.) distinct in Field 1 but multiple in field 2
Expected result:
Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758
2.) multiple in field 1 but distinct in field 2
Expected Result :
Field 2 | Column1 | Column2
123 | 1 | 2
There may be more columns added according to the data available.
writes "Hi,
I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me.
I would like to create a new table that does calculations on some of the same fields.
•IPA number (from the IPA_Num field)
•Total number of Rx's (sum of rx field )
•Total Paid( sum of amt_paid field)
•Total generic paid (If the field drugs = 1 then sum the amt_paid field)
•Percent generic (If the field drugs = 1 then sum and divide by the sum of the field rx)
•Percent formulary (if the formulary field = A then sum and divide by the rx field)
•Cost difference ( Average amount paid per brand Rx - average amount paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
Yvonne"
Hi,
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
rfpsector_nameproposalscurrent_year
fpname_08_rfpid
NOTE:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname.
I am at a loss and would greatly appreciate any help.
Thanks
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECT TXPRCL, TXALTR, TXANAM
FROM dbo.PCWEBF21
WHERE (TXALTR > 0)
UNION
SELECT TXPRCL, TXTAXP, TXTNAM
FROM dbo.PCWEBF21
WHERE TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.