i am trying to get a page up that enables me to enter a number into a box on a form, which then on clicking of a search button, queries the database, and returns 2 other fields that relate to the number found in the database, in an ASP datagrid.
the easiest way to do this would be to use OLEDB or ODBC, but the host i am using doesnt support either of them, and so i am trying to use their code, something using OBJDB, but to no avail. frankly, their code is rubbish, and im not sure how to go about doing the above.
i would be very grateful if someone could help me out with this problem,
I want to create a stored procedure which takes a project name and returns any project with a name similar or = to the input parameter. This is so the user can search for a project although the spelling may be incorrect or whatever.....
Any hints on how to start this? It seems that I could use SOUNDEX or DIFFERENCE, but I don't know where to start.
Hi I am developing windows application. I want to find records which has a particular number which is entered in textbox. and result is displayed in datagrid. If the entered number didnt find in database it must be displayed msg. i tried it like as below but not working. qlConnection conn = new SqlConnection(); conn.ConnectionString = "Server=EBSERVER;UID=sa;Database=Airport-Clearance;"; //SqlConnection sqlconn = objcs.GetConnection(); //MessageBox.Show("Connected"); //sqlconn.Open(); //Do what ever SqlDataAdapter filling = new SqlDataAdapter("select * from Airport where awb='" + txtawb.Text ,conn); DataSet displaying = new DataSet(); conn.Open(); filling.Fill(displaying); dataGrid1.DataSource = displaying.DefaultViewManager; conn.Close(); Waiting for reply. Warmest regards, ASIF
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;
Insert into #CallDetail VALUES(12123,1) Insert into #CallDetail VALUES(53423,1) Insert into #CallDetail VALUES(6532,1) Insert into #CallDetail VALUES(82323,1) Insert into #CallDetail VALUES(124235,1)
Insert Into #Call VALUES(111,1,12123) Insert Into #Call VALUES(112,1,53423) Insert Into #Call VALUES(114,1,6532) Insert Into #Call VALUES(123,2,6532) Insert Into #Call VALUES(134,1,124235)
Insert Into #request Values('324234','SA023',12,111,0); Insert Into #request Values('223452','SA023',12,112,0); Insert Into #request Values('456456','SA024',12,114,0); Insert Into #request Values('22322362','SA024',44,123,0); Insert Into #request Values('22654392','SA023',12,134,0);
Insert into #requestdetail values(1, 'Bill',23,'324234') Insert into #requestdetail values(2, 'Tom',25,'223452') Insert into #requestdetail values(3, 'Bobby',27,'456456') Insert into #requestdetail values(4, 'Guck',29,'22322362') Insert into #requestdetail values(5, 'Bobby',33,'22654392')
1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.
4. If found return the details of the original record :
[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]
In my asp.net web application I used two textboxes for date1 and date2 to search records I have used two variable in my stored procedure @OrderDate1 datetime,@OrderDate2 datetime,for todays record (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/20/2007 11:59 PM';for records between (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/24/2007 11:59 PM';I have to search the todays records OR records between these two days (OrderDate >= @OrderDate1 ) AND (OrderDate <= @OrderDate2) It works fine in my local server>>It finds the todays records by ginving same date (@OrderDate1 and @OrderDate2) and records between two dates but it does not work for sql server which is outside india ...if it is problem of date format how i can solve that.. Thanks
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a common word like "damage" I get like 32,000 records. Now, the server handles those records in about one second but the ASP page that returns the results takes about one MINUTE to download. When I save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below. However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help? Thanks, Andy.private bool searchProducts(string keywords) { ....... command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' "; command.Parameters.Add("@keywords", SqlDbType.VarChar, 100); command.Parameters["@keywords"].Value = keywords; ............. }
Hi,What is the best way to model this: Assume I have two objects: Agencyand Publisher, and both have a 1-to-n relationship to Employee. This isa true 1-to-n relationship, as each Employee can only work for oneAgency or one Publisher. Let's assume further that I cannot introduce asupertype (e.g. Employer) which holds the 1-to-n relationship.My preferrd solution is to have a foreign key in Emplyee that caneither link to a primary key of Agency or Publisher (all my primarykeys are 64-bit IDs that are unqiue across the database). However, nowI won't be able to map a bi-directional association, without indicatingin Employee whether this is an "Agency" or "Publisher" relationship(ala <ANY>).My other option is to use two tables, AgencyEmployee andPublisherEmployee, which can then be linked as traditional 1-to-nbidirectional associations.What do you guys consider best practice in this situation?Cheers,Jen
I'm going through the basics I don't mind doing my own research but I have an issue that needs attention and is beyond my current level.
I'm looking for a way to have any data entered into one database automatically copied to an archive database running on another machine. Items deleted in database one would be retained in database two. Database one would be clean and efficient and only reflect currently necessary information. Database two would grow indefinitely as an archive of what had/does exist in database one.
I'm sure this is quite mundane but I'm not sure what key words I'm looking for. Is there a title for this process?
Is this possible from within the SQL management studio or should I start those videos under the developer section?
I'm in the tall grass. Any pointers appreciated.
Both servers are Server2003 with SQL Server 2005 Standard Edition /Sp2
Hi,I have a requirement to design a query that identifies items soldbetween two dates. There is a 'SoldDate' datetime field used toregister what date the item was sold.The query needs to identify all sales between the last day of theprevious month and going back one year.What I would like to do is to design a query / stored procedure thatwill dynamically create the criteria to allow the client to simply runthe query or stored proc.I know how to establish the last day of the previous month part, I'mjust not sure of how best to design the remainder of the query.Thank in advance
OK,Here is my challenge.If I have a query that produces the followingItemSold_OnA01-10-2004 8:03A01-11-2004 10:05A01-12-20041:37A01-14-20047:16B01-10-20049:37B01-12-2004 11:42B01-13-20049:37But I need it to produce this insteadItemSold_On InstanceA01-10-2004 8:031A01-11-2004 10:052A01-12-20041:373A01-14-20047:164B01-10-20049:371B01-12-2004 11:422B01-13-20049:373So basically I need it to chronologically number the rows, but I needthe count to start over when the item changes.
I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:
I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.
I have a dataset with 3 fields:
Area ID RefNo
This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.
The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.
The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.
These numbers I create have to follow on from the highest RefNo for the Area.
For example:
Area ID RefNo A Z 1 A Y 2 A X 3 A W 10000 A V 10000 B N 1 B O 10000 B P 10000
So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.
Hi, I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table. However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal. So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table. TIA, Graham.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
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 '%._%'
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.
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 ?
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.
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.
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
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
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
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?
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...
hello everyone i need C# code in wh we sent "UserID" to table named "Users" and if such "UserID" exists in "User" table we get his name from the table else we got message that no such record exists. i have tried to write that code ,it works only if the value exists . in case of no value an exception is thrown. please do sent me that c# code. thanks for your consideration.
Just wanted to run this idea past u all before I have a go at it.
I have two tables A & B that are similar to the below
Table A Name1 Name2 Name3 Tom Bill John Gary Harry Eric
TableB Name1 Name2 Name3 John Bill Tom Tom Eric john Leslie Philip Colin
What I wanted to do is see if the the records from tableA row 1 exist in tableB
As you can see they can appear in any order, partially or not at all.
What I propose to do is take tableA Name1 and see if it matches tableb row1 name 1 OR name 2 OR name 3 and if I find a match use a variable to assign the the value 1 (so I can then see if the match is full (score three) partial socre 1 and two or not at all (0)
Then I will need do the same for tableA row 2
Then goto row 2 of table a and start again.
Does this make sense? Are there beter ways of doing this.
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
I have a products table with product attributes in a second table, together they describe a full product. I have a product title, a list of providers, description text, and keywords. I would like to do a search across these fields, and so far my research has shown that the Full Text Search component of SQL Server is the way to go. However, I am not sure this will be possible based on what is installed on the hosted server, so I am wondering if there is a unique, cool way of doing this without Full Text Search?
