Postal code search and LIKE statement

I'm trying to create a form that allows someone to find anaddress from a postal code search in the database. My query works exactly as I’dlike in query builder using the following select:

Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@.POSTCODE+’%’

I then pass the user entered post code to the select statementwhich is executed. However, I’m getting some odd behaviour. Assuming there isan address in the database with the post code “LS11 0ES”...

If I search for LS11, nothing is returned;

If I search for LS11%, nothing is returned;

If I search for %LS11%, nothing is returned;

If I search for %LS11%%, the data is returned.

If I search for %LS%1%%, the data is returned (as is LS210ES etc. etc.).

However, I want the user to be able to enter shorter searchstrings and it to pull all the data back out, so they can enter a substringsuch as LS and it will pull out all the data without the users needing to enterthe full pattern of % symbols.

If I go into query builder (in visual web developer) andenter just “LS” it works as I’d want, but not when pulled from a web page.

Any ideas?

Thanks

Look for other causes. I'm guessing somewhere in your code, you are removing the last % in postcode either before assigning it to the parameter, or modifying it during the selecting event.|||Thanks.At the moment I have a details view bound to an objectsource which in turn is bound to the above query and the parameter taken in from the text box’s .text property which the user types in.How can I trace where its failing?Thanks|||

A) response.write all your variables

or

B) Use a debugger like visual studio

sql

POST BACK TO SERVER

hi all,

i want to filter data from a database using parameters supplied by the user via textboxes. i've been able to write the select statement. my problem now is, the code behind for the "view data" button. do i do "sqldatasource1.select" orpost the databack to theserver? if i'm topostback to theserver, whats the code i should use?

protected void button1_Click(object sender, Eventargs e)

{

????

}

---

I guess it depends. Are you simply displaying data within something like a GridView? If so, then just use GridView.DataBind() and set your Parameters within the SqlDataSource.Selecting event. You could also set up your Parameters to be ControlParameters and point them directly to your TextBoxes.

|||

well i had done that already. it was just the code behind i needed. i didnt put any code and at runtime i clicked the button and it posted to the server. so i guess thats all i need. thanks for the input though

possibly merge join bug?

i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr,
(select max(sub.eff_dt_from) min_eff_dt_from, div_ord_no
from qctrl_div_ord_header sub
group by div_ord_no
) tmp
where hdr.eff_dt_from = tmp.min_eff_dt_from
and hdr.div_ord_no = tmp.div_ord_no

having that sql statement, merging will come out with 0 rows

however, having a simple query like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr

merging will come out with 2 rows

you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements

i believe this is a problem with SSIS, anyway around this?

Are the inputs to the MERGE JOIN sorted? I is a requirement that they are for MERGE JOIN to work correctly.

Note that setting IsSorted=Yes on the input does not mean that the data gets sorted for you!

-Jamie

|||yes, the input are sorted, everything should be setup correctly, hence, i got the merge to run and work as expected with the simple query

Possible to parse a column in a Select statement?

I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring statements.
Is there an easier way?Hi

Charindex is the usual way to segment strings even when it is like:
http://www.users.drew.edu/skass/sql...unction.sql.txt

John
"grok" <joe.hurzeler@.verizon.net> wrote in message
news:y36Wc.6100$O%4.4380@.nwrddc04.gnilink.net...
I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring
statements.
Is there an easier way?|||see
http://www.nigelrivett.net/f_GetEntryDelimiitted.html

It's a function that returns entries from a csv string.
I use it for gettig fields from data after bulk inserting but it can be
used for a single string too.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Possible tempdb problems

I have a simple TOP X select statement that is returning
one less than the TOP X number. If I check the records
affected verses records returned it is always 1 less. I
ran DBCC CHECKDB on all databases and I got some errors
when I ran it on tempdb. I can't set single user access to
tempdb so I can't repair it. Any suggestions? Here are
the errors:
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB
processing.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:106) in database ID 2 is allocated in the SGAM
(1:3) and PFS (1:1), but was not allocated in any IAM. PFS
flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3096) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:3176) in database ID 2 is marked allocated in
the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 3 allocation errors and 0 consistency errors
not associated with any single object.
CHECKDB found 3 allocation errors and 0 consistency errors
in database 'tempdb'.
repair_allow_data_loss is the minimum repair level for the
errors found by DBCC CHECKDB (tempdb ).Restart SQL Server, and tempdb will re-create itself from scratch.
"Brian Tax" <brian.tax@.milliman.com> wrote in message
news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
> I have a simple TOP X select statement that is returning
> one less than the TOP X number. If I check the records
> affected verses records returned it is always 1 less. I
> ran DBCC CHECKDB on all databases and I got some errors
> when I ran it on tempdb. I can't set single user access to
> tempdb so I can't repair it. Any suggestions? Here are
> the errors:
> Server: Msg 8999, Level 16, State 1, Line 1
> Database tempdb allocation errors prevent further CHECKDB
> processing.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:106) in database ID 2 is allocated in the SGAM
> (1:3) and PFS (1:1), but was not allocated in any IAM. PFS
> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3096) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> Server: Msg 8905, Level 16, State 1, Line 1
> Extent (1:3176) in database ID 2 is marked allocated in
> the GAM, but no SGAM or IAM has allocated it.
> CHECKDB found 3 allocation errors and 0 consistency errors
> not associated with any single object.
> CHECKDB found 3 allocation errors and 0 consistency errors
> in database 'tempdb'.
> repair_allow_data_loss is the minimum repair level for the
> errors found by DBCC CHECKDB (tempdb ).|||We rebooted twice yesterday and it was still doing the
same thing. Now this morning the SELECT TOP X was working,
but it still gave errors in tempdb.
>--Original Message--
>Restart SQL Server, and tempdb will re-create itself from
scratch.
>
>"Brian Tax" <brian.tax@.milliman.com> wrote in message
>news:0c0a01c36757$cf15e480$a001280a@.phx.gbl...
>> I have a simple TOP X select statement that is returning
>> one less than the TOP X number. If I check the records
>> affected verses records returned it is always 1 less. I
>> ran DBCC CHECKDB on all databases and I got some errors
>> when I ran it on tempdb. I can't set single user access
to
>> tempdb so I can't repair it. Any suggestions? Here are
>> the errors:
>> Server: Msg 8999, Level 16, State 1, Line 1
>> Database tempdb allocation errors prevent further
CHECKDB
>> processing.
>> Server: Msg 8906, Level 16, State 1, Line 1
>> Page (1:106) in database ID 2 is allocated in the SGAM
>> (1:3) and PFS (1:1), but was not allocated in any IAM.
PFS
>> flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3096) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> Server: Msg 8905, Level 16, State 1, Line 1
>> Extent (1:3176) in database ID 2 is marked allocated in
>> the GAM, but no SGAM or IAM has allocated it.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> not associated with any single object.
>> CHECKDB found 3 allocation errors and 0 consistency
errors
>> in database 'tempdb'.
>> repair_allow_data_loss is the minimum repair level for
the
>> errors found by DBCC CHECKDB (tempdb ).
>
>.
>

Possible memory leak definite error when using CONVERT statement

Hi all,
I have a problem with my SQL Server 2000 installation that I can only think
might be a memory leak.
I was wondering if any of you have experienced similar problems and if so
how you overcame them?
Within 10 days of the server running we are having to restart the server to
flush a variable test out of memory.
The error appears when running SQL from Query Analyser, Stored procedures
called from aspx pages etc.
The error looks something like
"Unable to convert variable test to an integer variable"
As I'm sure you can guess this error only occurs when using CONVERT but rest
assured there is no variable test declared by any of the scripts we are
running on the server.
BRB
That's strange
Can you show us this script?
"BPBForum" <BPBForum@.discussions.microsoft.com> wrote in message
news:CE1DC59F-5035-429D-887A-0DAA55C0E102@.microsoft.com...
> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only
think
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server
to
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but
rest
> assured there is no variable test declared by any of the scripts we are
> running on the server.
|||Hi
SQL Version and SP level, and we need to see the code where this occurs?
Don't forget, SQL Server does a lot of implicit conversions so explicit use
of CONVERT is not required.
Regards
Mike
"BPBForum" wrote:

> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only think
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server to
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but rest
> assured there is no variable test declared by any of the scripts we are
> running on the server.

Possible memory leak definite error when using CONVERT statement

Hi all,
I have a problem with my SQL Server 2000 installation that I can only think
might be a memory leak.
I was wondering if any of you have experienced similar problems and if so
how you overcame them?
Within 10 days of the server running we are having to restart the server to
flush a variable test out of memory.
The error appears when running SQL from Query Analyser, Stored procedures
called from aspx pages etc.
The error looks something like
"Unable to convert variable test to an integer variable"
As I'm sure you can guess this error only occurs when using CONVERT but rest
assured there is no variable test declared by any of the scripts we are
running on the server.BRB
That's strange
Can you show us this script?
"BPBForum" <BPBForum@.discussions.microsoft.com> wrote in message
news:CE1DC59F-5035-429D-887A-0DAA55C0E102@.microsoft.com...
> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only
think
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server
to
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but
rest
> assured there is no variable test declared by any of the scripts we are
> running on the server.|||Hi
SQL Version and SP level, and we need to see the code where this occurs?
Don't forget, SQL Server does a lot of implicit conversions so explicit use
of CONVERT is not required.
Regards
Mike
"BPBForum" wrote:
> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only think
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server to
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but rest
> assured there is no variable test declared by any of the scripts we are
> running on the server.

Possible memory leak definite error when using CONVERT statement

Hi all,
I have a problem with my SQL Server 2000 installation that I can only think
might be a memory leak.
I was wondering if any of you have experienced similar problems and if so
how you overcame them?
Within 10 days of the server running we are having to restart the server to
flush a variable test out of memory.
The error appears when running SQL from Query Analyser, Stored procedures
called from aspx pages etc.
The error looks something like
"Unable to convert variable test to an integer variable"
As I'm sure you can guess this error only occurs when using CONVERT but rest
assured there is no variable test declared by any of the scripts we are
running on the server.BRB
That's strange
Can you show us this script?
"BPBForum" <BPBForum@.discussions.microsoft.com> wrote in message
news:CE1DC59F-5035-429D-887A-0DAA55C0E102@.microsoft.com...
> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only
think
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server
to
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but
rest
> assured there is no variable test declared by any of the scripts we are
> running on the server.|||Hi
SQL Version and SP level, and we need to see the code where this occurs?
Don't forget, SQL Server does a lot of implicit conversions so explicit use
of CONVERT is not required.
Regards
Mike
"BPBForum" wrote:

> Hi all,
> I have a problem with my SQL Server 2000 installation that I can only thin
k
> might be a memory leak.
> I was wondering if any of you have experienced similar problems and if so
> how you overcame them?
> Within 10 days of the server running we are having to restart the server t
o
> flush a variable test out of memory.
> The error appears when running SQL from Query Analyser, Stored procedures
> called from aspx pages etc.
> The error looks something like
> "Unable to convert variable test to an integer variable"
> As I'm sure you can guess this error only occurs when using CONVERT but re
st
> assured there is no variable test declared by any of the scripts we are
> running on the server.