|
.net
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
.net VS 05 HELL!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? 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. Show quote "NuB" wrote: > 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? > > > 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 Show quote "TJ Nelson" <TJNel***@discussions.microsoft.com> wrote in message news:10E6A206-D054-4323-8C5D-A03380B56DB6@microsoft.com... >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" wrote: > >> 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? >> >> >> 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 Show quote "TJ Nelson" <TJNel***@discussions.microsoft.com> wrote in message news:10E6A206-D054-4323-8C5D-A03380B56DB6@microsoft.com... >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" wrote: > >> 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? >> >> >> 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 Show quote "ME" <trash.trash@comcast.netREMOVETHIS> wrote in message news:9MSdnVXZ1MvTrEzeRVn-ug@comcast.com... > 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 > > "TJ Nelson" <TJNel***@discussions.microsoft.com> wrote in message > news:10E6A206-D054-4323-8C5D-A03380B56DB6@microsoft.com... >>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" wrote: >> >>> 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? >>> >>> >>> > > 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 Show quote "NuB" <m*@me.com> wrote in message news:%23KFSMUfHGHA.2704@TK2MSFTNGP15.phx.gbl... > 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" <trash.trash@comcast.netREMOVETHIS> wrote in message > news:9MSdnVXZ1MvTrEzeRVn-ug@comcast.com... >> 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 >> >> "TJ Nelson" <TJNel***@discussions.microsoft.com> wrote in message >> news:10E6A206-D054-4323-8C5D-A03380B56DB6@microsoft.com... >>>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" wrote: >>> >>>> 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? >>>> >>>> >>>> >> >> > > 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 Show quote "ME" <trash.trash@comcast.netREMOVETHIS> wrote in message news:I9CdnQyTo-oa20zenZ2dnUVZ_t-dnZ2d@comcast.com... >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 > > "NuB" <m*@me.com> wrote in message > news:%23KFSMUfHGHA.2704@TK2MSFTNGP15.phx.gbl... >> 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" <trash.trash@comcast.netREMOVETHIS> wrote in message >> news:9MSdnVXZ1MvTrEzeRVn-ug@comcast.com... >>> 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 >>> >>> "TJ Nelson" <TJNel***@discussions.microsoft.com> wrote in message >>> news:10E6A206-D054-4323-8C5D-A03380B56DB6@microsoft.com... >>>>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" wrote: >>>> >>>>> 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? >>>>> >>>>> >>>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics