Help! Making wildcard TableAdapter query work

[googlegroups.dsbl]

I'm really confused here, and am wondering if someone knows what could
be the issue with my TableAdapter query.

A few months ago, I created a really neat program that has th ability
to search by a client account number. In the TableAdapter, I created a
new query called "FillbyAccountN umber". When I created this, I set up
the SELECT statement so that it selected all the fields I wanted and
then used the following WHERE clause:

WHERE account LIKE @account + '%'

When a user clicked a button, it would load this FillbyAccountNu mber
query, and pull the value of a textbox for the @account variable - the
nice feature is that you could type "902" to return accounts "90201",
"90255" and so forth, because of the wildcard. A DataGridView would
display the results, and everything was great. Pretty simple, right?

Now, I'm designing another program that does the same thing, only with
a different database. I literally tried copying and pasting the query
from my old program into my new one, and THE WILDCARD DOESN'T WORK. If
I want to get account "90201", I have to physically type "90201", and
it will not show up if I type "902". The reason why this is a big issue
is because some accounts have "sub-accounts" like "90201-A", which will
now only show up if you search for them exactly, instead of using the
wildcard. In the TableAdapter, I've even gone in to the QueryBuilder
for this query and tested it using the "Test Query" button. It popped
up, asked for my variable, and worked fine, but it's not loading the
DataGridView until I search for the exact value. AAAAAAAAAAAAAAU GH!

I have been comparing code from the old program to the new one for the
last two hours and I'm at the end of my rope here. I can't figure out
why it would be doing this. I'm even using the same database and MSSQL
connection string as the old program, as the new tables I need to work
with are in the same database. Any help would be GREAT, as apparently
I'm not smart enough to figure out this simple issue.

THANKS IN ADVANCE!

P.S. - Random system info in case it matters
SQL Server: MSSQL 2000
Environment: VS2005

[Carl Daniel [VC++ MVP]]

go************* **@spamgourmet. com wrote:

I'm really confused here, and am wondering if someone knows what could
be the issue with my TableAdapter query.

A few months ago, I created a really neat program that has th ability
to search by a client account number. In the TableAdapter, I created a
new query called "FillbyAccountN umber". When I created this, I set up
the SELECT statement so that it selected all the fields I wanted and
then used the following WHERE clause:

WHERE account LIKE @account + '%'

When a user clicked a button, it would load this FillbyAccountNu mber
query, and pull the value of a textbox for the @account variable - the
nice feature is that you could type "902" to return accounts "90201",
"90255" and so forth, because of the wildcard. A DataGridView would
display the results, and everything was great. Pretty simple, right?

Now, I'm designing another program that does the same thing, only with
a different database. I literally tried copying and pasting the query
from my old program into my new one, and THE WILDCARD DOESN'T WORK. If
I want to get account "90201", I have to physically type "90201", and
it will not show up if I type "902". The reason why this is a big
issue is because some accounts have "sub-accounts" like "90201-A",
which will now only show up if you search for them exactly, instead
of using the wildcard. In the TableAdapter, I've even gone in to the
QueryBuilder for this query and tested it using the "Test Query"
button. It popped up, asked for my variable, and worked fine, but
it's not loading the DataGridView until I search for the exact value.
AAAAAAAAAAAAAAU GH!

I have been comparing code from the old program to the new one for the
last two hours and I'm at the end of my rope here. I can't figure out
why it would be doing this. I'm even using the same database and MSSQL
connection string as the old program, as the new tables I need to work
with are in the same database. Any help would be GREAT, as apparently
I'm not smart enough to figure out this simple issue.

What are the column definitions in the old and new databases?

From what you've described, your problem probably lies somewhere you're not
looking. For example, if the account number passed in @account has a
trailing space, then you'll see the symptoms you're describing.

-cd

[Boaz Ben-Porat]

What is the data type of [account] column ? Is it the same (data type and
size) in both databases ?

Boaz Ben-Porat
Milestone Systems

<go************ ***@spamgourmet .comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .

I'm really confused here, and am wondering if someone knows what could
be the issue with my TableAdapter query.

A few months ago, I created a really neat program that has th ability
to search by a client account number. In the TableAdapter, I created a
new query called "FillbyAccountN umber". When I created this, I set up
the SELECT statement so that it selected all the fields I wanted and
then used the following WHERE clause:

WHERE account LIKE @account + '%'

When a user clicked a button, it would load this FillbyAccountNu mber
query, and pull the value of a textbox for the @account variable - the
nice feature is that you could type "902" to return accounts "90201",
"90255" and so forth, because of the wildcard. A DataGridView would
display the results, and everything was great. Pretty simple, right?

Now, I'm designing another program that does the same thing, only with
a different database. I literally tried copying and pasting the query
from my old program into my new one, and THE WILDCARD DOESN'T WORK. If
I want to get account "90201", I have to physically type "90201", and
it will not show up if I type "902". The reason why this is a big issue
is because some accounts have "sub-accounts" like "90201-A", which will
now only show up if you search for them exactly, instead of using the
wildcard. In the TableAdapter, I've even gone in to the QueryBuilder
for this query and tested it using the "Test Query" button. It popped
up, asked for my variable, and worked fine, but it's not loading the
DataGridView until I search for the exact value. AAAAAAAAAAAAAAU GH!

I have been comparing code from the old program to the new one for the
last two hours and I'm at the end of my rope here. I can't figure out
why it would be doing this. I'm even using the same database and MSSQL
connection string as the old program, as the new tables I need to work
with are in the same database. Any help would be GREAT, as apparently
I'm not smart enough to figure out this simple issue.

THANKS IN ADVANCE!

P.S. - Random system info in case it matters
SQL Server: MSSQL 2000
Environment: VS2005