.net VS 05 HELL!

[NuB]

OK, here is the story, i have text boxes to allow the user enter information
for a search. I have my SQL that has LIKE in the where clause, in the query
builder the SQL works correctly, if nothing is entered all the data is
returned, if i enter in search criteria that data is returned. NOW, when i
try to do the same exact thing on my web form, it does not work. the grid
will only populate with data if I enter something in for search critia. AND
the grid will not work if I have more then 1 parameter in my where clause:
here is my SQL that works in the querybuilder

SELECT id, Officename, FirstName + ' ' + LastName AS FullName, Region,
FROM dbo.SalesTeam
WHERE (@FirstName IS NULL OR
FirstName LIKE '%' + @FirstName ) AND (@LastName IS
NULL OR
LastName LIKE '%' + @LastName ) AND (@Region IS NULL
OR Region LIKE '%' + @Region ) )

why will this work in query builder but not on the web form?

 

[Guest]

I don't have an answer on why it would be working in one place and not the
other, but have you tried putting your SQL into a stored procedure and then
trying to use that? Just something to use for troubleshooting.

 

[NuB]

yeah, i did that as well, and when i try and call the SP it does the same
thing, It executes in query builder but one the form it will not load the
Grid. This is driving me insane

 

[ME]

Perhaps the control is passing NULL and not DBNULL. You might try assigning
a default value to the parameter in the stored procedure or query.

Just my 2 cents.

Thanks,

Matt

 

[NuB]

tried that, I have it working with passing 1 parameter but anymore then
that, nothing.

I'm thinking of just coding this in the code behind and screw this inline
model

 

[ME]

I found that the default DataSets don't work well with the inline controls.
I went back to the AutoGenerated datasets and messed with the Advanced
options for configuring the dataadapters for each table solved my problems
(Open the Designer for the DataSet and right click on one of the table
ADAPTERS (lower part of each table in the designer), then choose Configure,
then Advanced.

-Optimistic Concurrency throws some logic into the queries that check for a
@ISNULL_Parameter. These caused me grief as well until I learned the
corralation between the queries and DataGridView's DataKeyNames property.

- Refresh the DataTable adds a "select query" after each update, delete and
insert, that requires an @KEYNAME parameter that may not be passed when
performing udpates from the Controls. The Controls tend to pass only
"@ORIGINAL_KEYNAME" as the key to update or delete and do not seem to pass
@KEYNAME.

Thanks,

Matt

 

[ME]

One last irratation. I noticed that if you use a SQL AutoGenerated ID the
DataSet does not correctly populate the Seed value. .NET starts its
autogeneration at Zero whereas SQL defaults to 1. I had to also update each
table's seed value to 1 after the Dataset was created.

Thanks,

Matt