Dear Sir,
I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.
Can you provide me a strong SQL Query for that. Or is there any other way of doing that.
Please help me.
Thanking You
Suraj
i have a column of dates.
i make an index or key for this column so that
i can perform binary search i hope.
If the search is not succesful how do
i get the query to return the two dates
surrounding the date i was searching for.
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Hi,
How do you do a case sensitive searching without having the database case sensitive or is this even possible. There are times we want to perform case sensitive searching and case insensitive searching.
I found this article, but is that the suggested way. seems a bit bad, but if it is the way I am willing to use it.
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
Thanks,
Matt
hi;
i want to get some results for new my stored proce is below;
create Procedure HaberleriGetir
@Kelime varchar(50),
@tarih1 smalldatetime,
@tarih2 smalldatetime,
@KatID int,
@lang char(5)
as
if @lang = 'Hepsi'
select * from Haberler where HKatID = @KatID and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
else
select * from Haberler where HKatID = @KatID and lang = @lang and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
incorrect near @Kelime....
and i can't get a result between two dates like @tarih1 & @tarih2
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?
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
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.
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
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.
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 would like to search MySQL table for names by selecting the first letter of the name.
eg: SELECT * FROM names WHERE lastName "begins with" M (or Mi, etc)
Right now if I use LIKE I get all names that contain "M".
Thanks.
PeterM
I am interested in creating a query that will test if a value is the same in a particular field.
For example, if the value is "0", or "000", or "000000" or "333", "444444", I would like to extract it. Otherwise omit the value.
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
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
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" ....
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.
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
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
Maybe a totally newbie question - I need to find some text in SQL database, I have no idea in which table it may be. Can I do it through SQL Management Studio 2005 or do I need some other utility? What would you suggest then?
Thanks.
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
hihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
View 1 Replies View RelatedHi!
I found a problem while retrieving/searching/filtering Myanmar Text with a Select Statement, In a Myanmar Text table there in a column called HeadWords.
It's Sample Data
HWID,HeadWords
1,|က|
2,|ကာ|
3,|ကိ|
4,|ကီ|
5,|ကဲ|
I want to make the following search:
SELECT * FROM TableName WHERE HeadWords LIKE '%|က|%' this should give me all entries that have a "|က|" *ANY* place in the HeadWords column. Right?
However, it gives me unproper results. you may see last two records have 2 characters between pipe(|).
HWID,HeadWords
1,|က|
3,|ကိ|
5,|ကဲ|
Since the wildcard character % means no or all characters it should work. And I've tried pipe, comma, forward slash and back slash.
The problem only seems to occur when the wildcard character is used for the any part of character. Let me know alternative way to search that matters. I've tried in MSAccess. There are same problem like MSSQL.
It's any problem in searching support for National Characters (UTF8). I've tried in OpenOffice Database with those data.
It's work fine.
If you not see Myanmar characters, please download fonts from http://www.fontmm.com/font_downloads.htm
Does anybody have an explanation to this, please let me know.
Thanks in advance!
Ngwe Tun
Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
Database design is well covered in undergrad CS. But does anyone know ofa textbook that deals with the design of database servers (and probablytouches on databases themselves), and perhaps deals with interpreting SQLinquiries?The motivation for this is that I wish to write a database server for aproprietaryoperating system (it resembles POSIX, but is not completely compliant)for an embedded processor system that exchanges queries from clients alsoemploying embedded processors. Stored data is all in one place.I'm a little familiar with the MySQL source code, but I don'twish to replicate something that complex. The sort of investmentI'm talking about should involve no more than three man, er, person-monthsby an experienced C++ programmer with plenty of experience in systemsand database programming for both Microsoft and Linux platforms.
View 1 Replies View RelatedI am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection
SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String
While datareader2.Read
intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString
'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()
End If
End While
datareader2.Close()
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
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 '%._%'
Dear ASP.NET
How can I find records that contain a STRING from some (more than one) other fields ?
for example, I have:
Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"
Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)
How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?
Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)
Thanks in advance, Yovav.
Let say a user wants to search for the name Joe Soap
I have two column's in my table, firstname and lastname
So if I do:
Code:
SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'
it returns nothing! So do I have to split the string Joe Soap or something ?
my app use a registration table
StudReg
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stRegDt smalldatetime,
stRegNo bigint,
courseId smallint
)
the application registers the student details to a course.
each student gets a new registration no during registration.
the app should identify repeaters to a particular course by checking another
table RegHistory, which stores the details of student registrations for the previous 5 years.
RegHistory
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stPrevRegDt smalldatetime,
stPrevRegNo bigint,
courseId smallint
)
the application must search the RegHistory table and list out those students who are the repeaters.
the sample entries in the two tables are as follows
StudReg
stName stDOB stGuardianName stRegDt stRegNo courseid
-------------------------------------------------------------------------
abc 01/01/1979 def 20/11/2004 12345 1
def 01/01/1976 xyz 20/11/2004 12346 1
... ..... ... ...... .... ...
mno 24/18/1976 pqr 20/11/2004 12400 1
RegHistory
stName stDOB stGuardianName stPrevRegDt stPrevRegNo courseId
abc 01/01/1979 def 20/11/2001 2345 1
ghi 01/01/1976 xyz 20/11/2001 2346 1
... ..... ... ...... .... ...
dfg 24/18/1976 pqr 20/11/2001 2400 1
to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).
here is my question,
if there are 100,000 students registering in each academic year, we will have
500,000 records in RegHistory Table and 100,000 records in studReg table
if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.
is there any other options to search for repeaters ?
pl discuss
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.