SQLDataSource And Null Values
Dec 20, 2006
I have a sproce that accepts null for one of its parameters I can execute the sproce and enter null and it works fine, it returns all rows. When I try doing this with my GridView and the SQLDataSource it does not work. I need some help in understanding how the SQLDatasource wants a null. Here is what the parameter row of the SQLDataSource looks like.
<asp:ControlParameter ControlID="EnteredByText" DefaultValue="Null" Name="EnteredBy" PropertyName="Text"
Type="String" ConvertEmptyStringToNull="true" />
In my sproce I have setup the parameter as follows;
@EnteredBy Nvarchar(50)=Null
In my WHERE Clause I have:
WHERE (tblClient.EnteredBy = @EnteredBy OR @EnteredBy IS NULL)
View 3 Replies
ADVERTISEMENT
Mar 20, 2008
I am attempting to create search parameters for a gridview control and I am experiencing a small issue. When I get to a date parameter I am unable to display null values.
I setup a sqldatasource and created the parameters below to handle the selections for minimum date required and the maximum date required for the date columns in the database. The problem is I do not know how to display null dates.
Is there a way to incorporate something into the search page to show null dataes?
Sql Where Clause 1 WHERE (LSS_Requests.TypeCode = @TypeCode) AND (LSS_Requests.PersonNo LIKE '%' + @PersonNo + '%') AND
2 (LSS_Requests.TicketNo LIKE '%' + @TicketNo + '%') AND (LSS_Requests.Name LIKE '%' + @Name + '%') AND
3 (LSS_Requests.RequestName LIKE '%' + @RequestName + '%') AND (LSS_Requests.RequiredDate >= @Fromrequireddate) AND
4 (LSS_Requests.RequiredDate <= @ToRequiredDate) AND (LSS_Requests.OriginationDate >= @SearchFromOriginationDate) AND
5 (LSS_Requests.OriginationDate <= @SearchToOriginationDate) AND (LSS_Requests.LastUpdated >= @SearchFromUpdatedDate) AND
6 (LSS_Requests.LastUpdated <= @SearchUpdatedToDate) AND (LSS_Users_1.userFullName LIKE @SearchDDLUsers) AND
7 (LSS_Users.userFullName LIKE @SearchddlCIAsignee) AND (LSS_Requests.TypeCode = 'CC') AND (LSS_lu_Status.stNm LIKE '%' + @StatusName + '%')
View 2 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Feb 23, 2007
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
 [CustomerId] [nvarchar](50) NULL,
 [CustomeName] [nvarchar](50) NULL,
 [CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Mar 29, 2006
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
View 1 Replies
View Related
Nov 9, 2000
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
View 1 Replies
View Related
Apr 12, 2008
hi
i am trying to get the output of the select statements of sqldatasource :
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) { DataView dv; dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty)); }
}
the problem is that dv returns null ?
and here is the sqldatasource definition in .aspx page
<asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>"
SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected">
<SelectParameters>
<asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" />
<asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
thanks for help
View 2 Replies
View Related
Aug 23, 2007
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>
View 3 Replies
View Related
Feb 17, 2006
How to pass a null to SelectParameters in SqlDataSource?
The type of "CreateDate" is DateTime, the following code can be run correctly!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "2006-11-12";
now I hope to pass null value to the Parameter "CreateDate", but the following 3 section codes don't work!
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = string.Empty;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue =null;
or
SqlDataSource1.SelectParameters["CreateDate"].ConvertEmptyStringToNull = true;SqlDataSource1.SelectParameters["CreateDate"].DefaultValue = "";
View 4 Replies
View Related
Aug 29, 2006
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
View 2 Replies
View Related
Sep 23, 2007
I'm developing a web app using VS2005. I have a webpage with panel containing a gridview populated by a SQLdatasource. The SQLdatasource in turn is populated by a stored procedure that can take up to 5 parameters. The user types in up to 5 separate words (searchterms) in a text box which are then parsed and passed to the stored proc in the datasource which performs a fulltext search. The gridview then becomes visible. My problem is that unless the user types in 5 searchterms (no less), the gridview returns zero rows. 5 searchterms returns the proper results. Somehow, I need to be able to pass in null or empty values for unneeded parameters.
I've tested the stored procedure in Query Analyzer and from within the SQLdatasource configuration (using Test Query) using 0 up to 5 parameters and it works fine, so that's not my problem. Here's the code that runs after the user types in their search term(s) and presses a button:Public Sub FTSearch_Command(ByVal sender As Object, ByVal e As CommandEventArgs) Handles btnFullText.Command
Dim x As Integer
pnlFullText.Visible = Falsefiltertext = Replace(txtSearchTxt.Text, "'", "''")
If Not filtertext Is Nothing Then
filtertext = filtertext.Trim
Else
Return
End IfDim arrayString() As String = filtertext.Split(" ")
Dim length As Integer = arrayString.LengthFor x = 0 To (length - 1)
If Not arrayString(x) Is Nothing ThenSelect Case x
Case 0 : lblFTParm1.Text = arrayString(0)Case 1 : lblFTParm2.Text = arrayString(1)
Case 2 : lblFTParm3.Text = arrayString(2)Case 3 : lblFTParm4.Text = arrayString(3)
Case 4 : lblFTParm5.Text = arrayString(4)
End Select
End If
Next
pnlFullText.Visible = "True"
End Sub
Any ideas?
Thanks in advance.
View 2 Replies
View Related
Mar 28, 2008
The problem I'm having is described below all this code.---------------------------------------------------------My content page has a SqlDataSource: <asp:SqlDataSource ID="sqlGetUserInfo" runat="server"
ConnectionString="<%$ ConnectionStrings:RemoteNotes_DataConnectionString %>" SelectCommand="SELECT [UserFirstName], [UserLastName] FROM [Users] WHERE ([UserGUID] = @UserGUID)"
onselecting="sqlGetUserInfo_Selecting"> <SelectParameters> <asp:Parameter Name="UserGUID" Type="String" /> </SelectParameters> </asp:SqlDataSource> -----------------------------------------------------Inside my OnSelecting event, I have: protected void sqlGetUserInfo_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { sqlGetUserInfo.SelectParameters["UserGUID"].DefaultValue = Membership.GetUser().ProviderUserKey.ToString(); } ---------------------------------------------------------------------And, inside my Page_Load, the relevant code is: //String strUserFirstName = ((DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty)).Table.Rows[0]["UserFirstName"].ToString();
DataView dvUserDetails = (DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty);
if ((dvUserDetails != null) && (dvUserDetails.Count > 0)) { DataRow drUserInfo = dvUserDetails.Table.Rows[0]; lblHelloMessage.Text = "Hello, " + drUserInfo["UserFirstName"].ToString() + ((drUserInfo["UserLastName"].ToString() == "") ? "" : " " + drUserInfo["UserLastName"].ToString()); } ---------------------Now, here's the problem. My "if" statement is never executing because "dvUserDetails" is null. However, when I break the execution and put awatch on the actual Select() statement, it shows a DataView with the correct return rows! You can see the commented line where I tried tobypass the DataView thing (just as a test), but I get an object reference is null error.The weird thing is that it was working fine one minute, then started getting "funky" (working, then not, then working, then not), and now it just doesn't work at all. All thiswithout me changing one bit of my code because I was checking out some UI flow and stopping and restarting the application. I've tracked downthe temporary files directory the localhost web server runs from, deleted all those files, and cleaned my solution. I've even triedrebooting, and nothing seems to make it work again.My relevant specs are VS2008 9.0.21022.8 RTM on Vista Enterprise x64.
View 3 Replies
View Related
Oct 16, 2006
Hi,I have an updatable DataGrid linked to a SQLDataSource in a web site developed using VS2005. Update works fine unless a value in the existing row is Null. Only one column in the database allows nulls.Putting a debug stop in the SqlDataSource1_Updating event, I checked the parameter value in the Immediate window and got the following result:?e.Command.Parameters(16){System.Data.SqlClient.SqlParameter}System.Data.SqlClient.SqlParameter: {System.Data.SqlClient.SqlParameter}DbType: Int32 {11}Direction: Input {1}IsNullable: FalseParameterName: "@original_FiresolveJobNo"Size: 0SourceColumn: ""SourceColumnNullMapping: FalseSourceVersion: Current {512}Value: NothingIs there a property I can set to generate a suitable Null check clause for the Update statement?Many Thanks,Keith.
View 3 Replies
View Related
Apr 18, 2007
I have an event:
Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted
Dim command As SqlClient.SqlCommand
command = e.Command
If command.Parameters("@nReturnCode").Value <> 0 Then
DROPDEAD()
End If
That fires from:
<DeleteParameters>
<asp:Parameter Name="nDeletebyId" Type="Int64" />
<asp:Parameter Name="nOtherId" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
</DeleteParameters>
End Sub
When I:
GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex)
But nReturnCode is ALWAYS NULL... I even did a stored procedure that just:
ALTER PROCEDURE [dbo].[sp_nDeletebyId]
@nReturnCode bigint output,
@nReturnId bigint output AS
SET @nReturnCode = 0
SET @nReturnId = 0
And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server...
and, BTW, the row is deleted!
Chip Kigar
View 2 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
View 3 Replies
View Related
Oct 1, 2007
Hi All,
I am having problems getting values out of an sqldatasource.
I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.
the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.
The insert is fine, i just don't know how to get the values out of the first 'select source'
Any pointers or suggestions most appreciated.
Cheers
View 2 Replies
View Related
Dec 22, 2005
I have added a sqldatasource to my form. I want to programmatically get field values directly from this control without adding a databound control such as gridview and then get values from the gridview.
Is this perphaps not the best way to use sqldatasource? Maybe it is meant to be used together with a databound control?
In that case what and how should I code it? Example please
/regards J
View 1 Replies
View Related
Apr 1, 2008
i have an sqldatasource that has a 1 row dataset
how can i read these values to labels?
thanks
View 1 Replies
View Related
Sep 21, 2007
Hello all,
I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
I have a SqlDataSource"
<asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
And my SP:/* 09-19-07 Used to update affiliate profile */
CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS
SET NOCOUNT ON
-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)
SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END
ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster
SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments
WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId
SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END
ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster
WHERE
affiliate_id = @AffiliateId
SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO
My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
Thanks!
View 3 Replies
View Related
Jan 1, 2006
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup?
I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control
Many thanks,
View 1 Replies
View Related
Oct 10, 2006
Im ripping my hair out here.I need to access the field in a datasource control of use in non presentation layer code based actions.I know the I can use a code base connection and query but I dont see why i need to make two trips the the DB when the info is already available.The datasource is attached to a details view control and the details view control is nested in a loginview controlI've tried defining but all I can get in the header name of the field but not the dataitem, the dataitem causes an error help please jim
View 4 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Mar 3, 2008
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution.
A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure.
I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP)
SELECT @MessageCode AS MessageCode, @MessageText AS MessageText
I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset.
In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened.
Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
View 10 Replies
View Related
May 19, 2008
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
View 6 Replies
View Related
Apr 10, 2006
I have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything. I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>. In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me). If a field is entered, then it is included in the search. Otherwise it should be ignored. In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1 SET @tagnum=NULL SET @ownername = '%'+@ownername+'%' SET @mailaddress = '%'+@mailaddress+'%' SET @address2='%'+@address2+'%' SET @city = '%'+@city+'%' SET @telephone='%'+@telephone+'%' SET @dogcolour='%'+@dogcolour+'%' SET @dogbreed='%'+@dogbreed+'%' SET @dogage='%'+@dogage+'%' SET @doggender='%'+@doggender+'%' SET @dogname='%'+@dogname+'%' SET @applicationdate='%'+@applicationdate+'%'
SELECT DISTINCT * FROM DogRegistry WHERE ( TagNum = @tagnum OR OwnerName LIKE @ownername OR MailAddress LIKE @mailaddress OR Address2 LIKE @address2 OR City LIKE @city OR Telephone LIKE @telephone OR DogGender LIKE @doggender OR DogBreed LIKE @dogbreed OR DogAge LIKE @dogage OR DogColour LIKE @dogcolour OR DogName LIKE @dogname OR ApplicationDate LIKE @applicationdate ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them. TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered). Debugging has shown that when nothing is entered it passes "".
Any ideas?
View 9 Replies
View Related
Jun 29, 2000
I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?
View 3 Replies
View Related
Nov 2, 2007
Would this take care of null values in either a.asset or b.asset?
SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets
What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.
View 8 Replies
View Related
Jan 16, 2006
Hi,
How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?
The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.
And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors
So far I have tried to use something like
ISNULL([Column 34])
Or
SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]
Or
SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34
but none seems to work [Column 34] being the offending column.
What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors
Any ideas?
View 2 Replies
View Related
Apr 20, 2007
I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.
Before I get to involved with SQL, is it a bad practice to use nulls?
If it is what do you enter in place of the null value,
which will lead to more code, right?
Davids Learning SQL
View 13 Replies
View Related
Oct 25, 2006
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
View 2 Replies
View Related
Jun 12, 2006
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):
<asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False"
ReadOnly="True" SortExpression="ProductID" />
<asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" />
<asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" />
<asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" />
<asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True"
SortExpression="loadid" />
<asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" />
<asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" />
<asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" />
</Columns>
</asp:GridView>
and the sqldatasource's info:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" >
<UpdateParameters>
<asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter>
<asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter>
<asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter>
<asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter>
<asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter>
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
TIA,
John
View 1 Replies
View Related
Jul 31, 2006
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies
View Related