Using Variable Number Of Search Strings With VBA
Mar 5, 2015
I have created a kind of search function in my Excel sheet where the user can select multiple items from different listboxes.
To save the items I use an array. So in one case only 1 items off the array is filled, but another time maybe 3 items are filled in the array.
I use the array to search in a SQL database (this is string rva(i) in the code). But when the array is empty it results in an error.
This is what I have so far. (the rule at the bottom won't work also )
rc.Open "SELECT [Datum],[RvA_Nr], [RvA_Letter], [Afdeling], [EVAL], [Matrix], [Component], [AS3000], [AP04], [Rec] FROM dbo.QHSE_2ndline_history " _
& "WHERE [Afdeling] = '" & afd & "' AND [Datum] >= '" & datum1 & "' AND [Datum] <= '" & datum2 & "' AND " _
& "CASE WHEN '" & rva(1) & "' <> '' then [RvA_Nr] = '" & rva(1) & "' END AND" _
& "([Matrix] LIKE '%" & matrix(1) & "%' OR [Matrix] LIKE '%" & matrix(2) & "%' OR [Matrix] LIKE '%" & matrix(3) & "%' OR
[Code] .....
View 1 Replies
ADVERTISEMENT
Dec 4, 2003
Hi,
I have a repeater control which I populate with search results from SQL Server.
But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?
I'm using a stored procedure with parameters:
input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords
Am using a temporary table to store all records before Select-ing those required for the particular page.
If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.
Am hoping that stored procedures can cope with an unknown number of parameters.
View 3 Replies
View Related
Nov 29, 2007
Hello folks
I'm tring to create a search function on a page that searches a database fields. But the problem I am having is that, instead of a user typing in a key word, the have to type in the exact phrase to get a result from the db.
For example...
With my current code, if a user wants to look for "newbie .net developers in ireland", they would have to type in that exact phrase. But i need to create a function, where the user only has to type in keywords to get a result. For example "developers london"
Heres my current codepublic static DataSet Searchgroup (string group_name)
{
SqlCommand cmd = new SqlCommand("select * from TG_User_Created_Groups where group_name =@group_name", conn);cmd.Parameters.Add(new SqlParameter("@group_name", group_name));
DataSet dst = new DataSet();SqlDataAdapter dtr = new SqlDataAdapter(cmd);
dtr.Fill(dst);return dst;
}
can anyone offer advise or know of any tutorials around that might demonstrate how I need to do this?
Cheers!
View 3 Replies
View Related
Dec 21, 2007
Hey, i have multiple search strings and I don't want to keep track of the search strings in SQL format.
As in, i don't want to do this anymore:
Select *
From tbl_Name
where FirstName = 'John' or
FirstName = 'Mike' or
FirstName Like 'Jen%' or
...
and the list continues for at least a 100...
what i want to do is store all the search values in a table and then join on that table as a search...
Create Table as tbl_SearchValues
[FirstName] as Varchar(50)
Insert Table tbl_SearchValues values ('John', 'Mike', 'Jen%')
Select *
From tbl_name n Join tbl_SearchValues s
on n.FirstName Like s.FirstName
This doesn't work cause I tried it... but does anyone know a different workaround??
View 2 Replies
View Related
Mar 4, 2008
I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies
View Related
Jul 22, 2013
I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
View 6 Replies
View Related
Aug 22, 2007
Hello guys!
I am looking for the best DataType that makes it possible to store strings of variable length (from 15 to 300 caracters, rarely longer).
Thanks a lot for any help !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
View 3 Replies
View Related
Aug 23, 2007
HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
View 1 Replies
View Related
Apr 2, 2008
Hi,
I'm seeing a problem with printing very long strings using the PRINT
command on a VARCHAR(MAX) variable. After a certain amount of
characters the string is truncated....it looks like the limit is at
around 8,000 characters.
Does anyone know of a solution or a workaround for this?
View 16 Replies
View Related
Aug 2, 2007
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
View 6 Replies
View Related
Apr 10, 2008
Hi All,
Table structure is Column1 - Column2 - column3 - column4.
E.g. 0-134-00-99, thus the number is 013400 to 013499.
consider the following scenario:
0-1803-0000-9999.
User enters a number as 018030015* --> * means any number starting with this and contained in the range. Hence the above range should be returned. i.e. (0-1803-0000-9999). The issue is to do this search, i will have to check all 1000 NUMBERS because using 'LIKE' will only search for all numbers starting with 018030015.
(SELECT * FROM table1 WHERE Column1+Column2+Column3 LIKE '018030015%') this query won't return the row 0-1803-0000-9999
If i write a cursor to check for 1000 such numbers, i can get the result, but is there a better solution?
View 19 Replies
View Related
Nov 6, 2007
Hi Team Members,
I am planning to develop application using VB.NET and MS SQL Server Express.
Complete application (Forms and Reports) will be in Arabic,
1. Can I use SQL Server Express for this senario
2. Can I perform search in Arabic (Numeric and Text)
3. Can I take back up of Database
4. Just to make sure, can I have Stored Procedures, Views and security features in SQL Server Express
Regards,
Mustaq
View 1 Replies
View Related
Jun 7, 2007
I have two simple tables:
CREATE TABLE summaries ( summaryId int identity, summary varchar)CREATE TABLE phrasesToSearchFor ( phrase varchar)
I want to do this:
SELECT summaryFROM summariesWHERE CONTAINS(summary, <any of the values in phrasesToSearchFor>)
I've tried every weird permutation of the query I can think of, but can't find a way to make it work. Can anybody help?
Bonus Question:
I've got a function dbo.getPhraseToSearchFor(userId) that returns a phrase depending on the value of userId. I want to get a result table of (userId, summary) where for every user, I use dbo.getPhraseToSearchFor(userId) to figure out what to search the full-text catalog for. The docs say you can use a scalar function anywhere a scalar value is expected, but apparently that doesn't apply in this case -- you can't do CONTAINS(summary, dbo.getPhraseToSearchFor(23))
View 1 Replies
View Related
Mar 20, 2012
How can I run a query that searches for a reference number that may appear in two different columns? I want to pull all records for idenification numbers that can also show up in a secondary identification column.
View 4 Replies
View Related
Jun 22, 2007
I googled on the latter error number and got nothing back
Error: 0xC0048006 at Set Destination File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x016F9EA4.
The former only returns one result which does not explain my problem
View 4 Replies
View Related
May 31, 1999
dear friend,
the problem i am finding is moreover a concatenation problem
what i did is that have stored procedure which is accepting any character and i have to show all the records where this character is existing
it is like this only
********************************
name1 like '% @str %'
but then it is not treating the str as a variable and treating it as a string so if you have any solution please let me know early
please give the answer with example
waiting for reply
ashish bhatnagar
View 1 Replies
View Related
May 15, 2008
Hello,
I use the full-text search utility in SQL Server 2005 to find word in PDFs document.
This is my 'Documents' table:
id (PK), data (VarBinary(max)), extension (nvarchar(4))
My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft');
1 | 0x255044.... | .pdf
But I need to know how many times 'Microsoft' word appears in this document.
Do you have any idea how can I retrieve this information?
Thanks in advance!
View 1 Replies
View Related
Apr 3, 2015
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh',
@SearchString VARCHAR(10)= '_',
@OccuranceNumber SMALLINT = 1
DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0
WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
View 9 Replies
View Related
Feb 19, 2007
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
View 2 Replies
View Related
Nov 30, 2004
I'm stumped how to achieve my goal using stored procedures. I have a front end application that calls stored procedures to insert data. For example, customer pays a bill. To complete the payment process a command in the app is run which calls a stored procedure to insert a tender record and a second stored procedure to insert a transaction record. A customer can pay one to many bills at one time so the number of times the insert stored procedures are called is variable. I want to be able to use the begin transaction, commit and rollback statements so if there's a problem inserting a record all the inserts are rolled back. Previously the app used the visual basic begin transaction, commit, rollback, statements however I want to now use SQL stored procedures for inserts and updates. I've been struggling with this for some time and hope someone can help me.
View 4 Replies
View Related
Jun 14, 2007
I have a SP search_post (e.g)
@id int,
@author varchar(40),
@keyword varchar(40),
select * from
posts
where
id = @id and author =@author and message like @keyword
in my case a user can choose to search by one or more of this elements.
what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled
one approach I have thought out is this
if (@id IS NOT null)
select * from
posts where id = @id
if (@author IS NOT null)
select * from
posts where author= @author
if (@keyword IS NOT null)
select * from
posts where keyword = (@keyword
but this does not really take care of the posibility that all of them or some of them will be null while others will not
any suggestions ?
View 5 Replies
View Related
Nov 9, 2007
Background: Customer sale records are kept in table (userHistory) that records: bookID, userID, and salesDate.
Objective: To find all the customers that have purchased the same books as any given customer, say customer1. For example, customer1 bought books bookA, bookB, and bookC; the goal is to find all of the other customers who also bought these books.
My First Attempt:
SELECT DISTINCT UH1.userID
FROM userHistory as UH1, userHistory as UH2, userHistory as UH3
WHERE UH1.userid = UH2.userID
ANDUH2.userid = UH3.userID
ANDUH1.bookID = ‘bookA’
ANDUH2.bookID = ‘bookB’
ANDUH3.bookID = ‘bookC’
AND UH1.userID <> ‘customer1’
The problem here is that it cannot handle any more/less than 3 books. The above should work well for 3 books, but what if there are 2 books or 12 books? Does anybody have any suggested queries to handle a variable amount of books (eg joins)?
Thank you kindly.
Adam
View 6 Replies
View Related
May 2, 2007
We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.
The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?
Thank You
View 3 Replies
View Related
Feb 6, 2008
Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.
I can get a table of which fields I need to update using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and I can do the update with UPDATE tblxxx SET column = LOWER(column)
But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?
Thanks,
Dave Stephens
View 3 Replies
View Related
Feb 6, 2008
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
View 1 Replies
View Related
Feb 6, 2008
Hi,
I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.
I know that I can get a lit of columns using:
SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'
and do the update using:
UPDATE tblABCDE SET column = LOWER(column).
In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.
If so, how do you do it?
Thanks,
Dave Stephens
View 1 Replies
View Related
Sep 20, 2006
I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam
View 3 Replies
View Related
Apr 20, 2007
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray) strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method? How? If I can't, what are my options?Diane
View 6 Replies
View Related
Jun 20, 2001
I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows
A statement with two clauses :-
Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
View 2 Replies
View Related
Nov 8, 1999
How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.
View 1 Replies
View Related
May 15, 2006
Using Microsoft query analyser SQL version 8, I believe.
Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!
________________
Update dbo.NSTS
Set dbo.NSTS.Number = NSTS_1.Number
FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)
WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')
________________
Example of data table here:
ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
_________________
I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).
Help would be very much appreciated!
View 6 Replies
View Related
Feb 19, 2007
Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:
SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt
this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks
View 1 Replies
View Related
May 4, 2008
I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :)
Thanks
ORIGINAL DATA
EventID StartDate EndDate
1 1/1/08 1/1/08
2 20/1/08 22/1/08
REQUIRED DATA
EventID EventDate
1 1/1/08
2 20/1/08
2 21/1/08
2 22/1/08
View 2 Replies
View Related
