Updating Null Values
Jun 23, 2008how to use update statement to update fields in a table that has Null values
update sheet1 set [Middle Initial]='' where isnull([Middle Initial],'')=''
this statement some times works sometimes not.
ADVERTISEMENT
SQL Parameter Update Not Updating Changed Null/blank Values
Oct 24, 2006I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
SqlConnection1.Open()
strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "
cmdCategoriesUpdate.CommandText = strSQLu
With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()
Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform
Jan 25, 2008I 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
NULL Values Returned When Reading Values From A Text File Using Data Reader.
Feb 23, 2007I 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 RelatedIntegration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values
Dec 9, 2013I 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]
Null Values For Datetime Values Converted To '0001-01-01'
Mar 29, 2006Hi
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.
Use Order By But Want NULL Values As High Values
Nov 9, 2000Hi,
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
Converting Empty String To Null When Inserting/updating
Mar 10, 2006I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
View 1 Replies View RelatedProblem Updating Null Text Field To 256 Characters
Aug 9, 1999If I create a row with a nullable text column whose initial value is null and then update the column with a value that is exactly 256 characters long, the value remains null. Once I update the column to any other value (including null), it works as expected. I have not yet seen a way around this.
I am working in 6.5 SP3.
Integration Services :: SSIS Package - Replacing Null Values In One Column With Values From Another Column
Sep 3, 2015I 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.Â
Need Help For Updating Values
May 14, 2008Hi!
I am trying to retrieve and update data. I am retrieving the data into the respective controls such as textboxes. The codes are follow:
Retrieve data - working
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=" & "@ID", sqlConn)
Dim paramID As New SqlParameter("@ID", SqlDbType.Int)
paramID.Direction = ParameterDirection.Input
com.Parameters.Add(paramID)
paramID.Value = e.Value
sqlConn.Open()
Dim reader As SqlDataReader = com.ExecuteReader()
While reader.Read()
StartTimeTextBox.Text = reader("StartTime")
EndTimeTextBox.Text = reader("EndTime")
CompanyNameTextBox.Text = reader("CompanyName")
PurposeTextBox.Text = reader("Purpose")
AccountManagerTextBox.Text = reader("AccountManager")
PresenterTextBox.Text = reader("Presenter")
ColorDDL.SelectedValue = reader("ColorCode")
StatusRadioButtonList.SelectedValue = reader("Status")
CommentTextBox.Text = reader("Comments")
End While
update the data - NOT WORKING
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim cmd As SqlCommand = New SqlCommand("UPDATE Demo_Theatre_DB SET StartTime = @StartTime, EndTime = @EndTime, CompanyName = @CompanyName, Purpose = @Purpose, AccountManager = @AccountManager, Presenter = @Presenter, ColorCode = @ColorCode, Status = @Status, Comments = @Comments WHERE ID=@ID")
cmd.Parameters.AddWithValue("StartTime", StartTimeTextBox.Text)
cmd.Parameters.AddWithValue("EndTime", EndTimeTextBox.Text)
cmd.Parameters.AddWithValue("CompanyName", CompanyNameTextBox.Text)
cmd.Parameters.AddWithValue("Purpose", PurposeTextBox.Text)
cmd.Parameters.AddWithValue("AccountManager", AccountManagerTextBox.Text)
cmd.Parameters.AddWithValue("Presenter", PresenterTextBox.Text)
cmd.Parameters.AddWithValue("ColorCode", ColorDDL.SelectedValue)
cmd.Parameters.AddWithValue("Status", StatusRadioButtonList.SelectedValue)
cmd.Parameters.AddWithValue("Comments", CommentTextBox.Text)
Try
sqlConn.Open()
cmd.ExecuteScalar()
Catch ex As Exception
InsertMsgLabel.Text = ex.ToString
End Try
End Sub
I am getting the following error whenever I click update button.
"ExecuteScalar: Connection property has not been initialized"
Your help will be appriciated!
Thanks!
What Is The Difference Between Updating Null Value Vs Empty String To Varchar/char Field?
Aug 29, 2007Hi,
What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why?
Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable
Table 2 :tState - Name char(2) nullable
Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state
So,when the state is not present for the country ,i have two options may be - null,''
tCountryDetails.State = '' or tCountryDetails.State = null?
Updating With Aggregate Values
Mar 9, 2004I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below
update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id
I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.
Is there another way to do this with a subselect and update?
Updating Values In A Table
Nov 7, 2007I have a table named 'Personal' that has a field that contains values that represent colors (ex: BLACK, BROWN, BLUE, etc.). How do I run an update statement to update the values to defined set. For example (BLACK = BLK, BLUE = BLU, etc.)?
Thanks
Updating Several Values At The Same Time
Nov 16, 2007Hi
I'm new to SQL and have a problem with the following script:-
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
Thanks for your help.
Jon
Updating Duplicated Values
Feb 14, 2008
I have a table like that:
ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
Updating The Values While Selecting
May 2, 2008 SELECT
#followups.suspectid,
#followups.planid,
cond4,
cond5,
cond6,
cond7,
cond8,
tbpdmmembers.firstname tbpdmmembers_firstname,
dbo.GetMemID_PartC_D(#followups.hic,#followups.IsPart_C) tbpdmmembers_MemberID,
--tbpdmmembers.Plan1 tbpdmmembers_MemberID,
tbaddress.address1 tbaddress_address1,
tbaddress.address2 tbaddress_address2,
tbaddress.city tbaddress_city,
tbaddress.state tbaddress_state,
tbaddress.zip tbaddress_zip
from #followups
I want to check if the tbaddress.address1 tbaddress_address1 if it is =null or empty then make it = tbaddress.address2 tbaddress_address2, at the same time I make the tbaddress.address2 tbaddress_address2, null. Otherwise leave it as it is.
Actually, I am working with the report and the tbaddress.address1 carrying the apt number. if there is no aprt # then the line print blanks. so that brings the empty line between the street address and the citystate line.
That is what i am tryig to cover. There could be a way to do this from the crystal report but I am thinking of doing this way cause I can't get the solution to do it from the crystal report.
Updating A Table With Only Certain Values
May 16, 2008Hello, I have been SSIS for a bit now, but really only it its most straight forward simple tasks I have the following issue which for the life of me I can't seem to sort out
1)Excel Source - - -> all data pushed into a initial SQL table
2) Data in initial SQL table gets sorted, certain fields get made UPPER , right trim that sort of thing
3) now I select a set of distinct values of a certain column in the SQL table and get a list of maybe 60 results
these results I want to enter into a table in a different database on same server, the table in the destination db has a identity column and a value column
adding / appending all the distinct value from the import data works fine,
but I want to add only the values which are not already in the destination table what is the best way to update the destination table, should I be using stored proc or is there a easier way in SSIS to do this
Thanks
JDBC Driver NullPointerException When Updating Timestamp Column Value To Null: Version 1.1.1501.101
Jun 21, 2007When updating a timestamp column value to null from within a java program, I get the exception below. The env is MS SQL server 2005 SP2, and MS jdbc driver version: 1.1.1501.101. Please advise of any workarounds or solutions.
Update <table_name> set <colname_oftype_timestamp> to null
java.lang.NullPointerException
at com.microsoft.sqlserver.jdbc.AppDTVImpl$SetValueOp.executeDefault(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.executeOp(Unknown Source)
at com.microsoft.sqlserver.jdbc.AppDTVImpl.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(Unknown Source)
Thanks,
sm.
Updating Values During SELECT Using CTE And Row_Number()
Sep 19, 2007Hi,
I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement.
I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'."
The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int)
AS
BEGIN
WITH SearchResults AS(
UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
)
SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
Thanks in advance for any help.
Updating By Default/specific Values
Apr 5, 2007In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
E.G.
AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
Updating Values In One Row From Values In Another Row
Oct 13, 2007Hi
I have a table containing:
ProductCat Product Qty NewQty
TypeA ProductA -10
TypeA ProductB 5
TypeB ProductX 8
TypeB ProductY -5
I need to get rid of negative values in the same ProductCat by adding surplus values to the negative values in the same ProductCat ie. This is what I ant the table to look like.
ProductCat Product Qty NewQty
TypeA ProductA -10 -5 (Add ProductB to ProductA in the same ProductCat to reduce the deficit)
TypeA ProductB 5 0 (Due to the fact that I added 5 to ProductA NewQty must be reduced by 5)
TypeB ProductX 8 3
TypeB ProductY -5 0 (Deficit only has to be reduced to 0 where possible therefor the balance
of 3 for ProductX)
Note the values in Qty can stay as the same. I am interested in the New values.
Could someone please help me with the correct method of doing this?
Thank you.
The Database Is Not Updating Any Values, I There A Problem With My Code?
Oct 6, 2006Im trying to update the values int the database and the query is executed correctly but database is not updated and the returned message is one, I really dont know whats causing this here is my code.1 private void UpdatetheDatabase(string connection)
2 {
3 SqlConnection connect = new SqlConnection(connection);
4 SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " +
5 " SET [Name]=@Name,[City]=@City, " +
6 " WHERE [ID]=@ID", connect);
7 update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text;
8 update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text;
9 update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID;
10 connect.Open();
11 int x = update_customer_details.ExecuteNonQuery();
12 connect.Close();
13 StatusLabel.Text = x.ToString();
14 }
your help will be highly appreciated.
Updating/getting Values From Datagrid In C# With Variable Parameters
Oct 24, 2006HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT
View 1 Replies View RelatedUpdating Column Values In Multipe Tables
May 26, 2000I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.
I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.
I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.
2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.
Anyone got a better Idea?
Updating Values In A Table With Foreign Key Violations
Aug 25, 2004Hello!
I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date.
What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?
Any help??
Many thanks!
Auto Incrementing-&>Updating Values In Two Related Tables :Help!!!
Nov 11, 2005hi there,
i am new to sql server database.i am doing small projects right
now using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted in
table 2 . so for eg if i have three records 1 ,2 and 3 ....then i
delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table
2....now i want 3 to become 2 in both table 1 and table 2...so that i
dont have empty space between two student id's 1 and 3. so this is my
problem....if any one can help me out with suggestions please
do.
thank you all........
ahmed_ind
SQL2005 Prb1 -Row Cannot Be Located For Updating. Some Values May Have Been Changed
Mar 3, 2006hi ,
I recently moved from SQL 2000 to SQL 2005.
The client side is vb6 and using Dcom dll's hosted on the db server.
I have a table which has oninsert trigger
When the recordset is updated in the com class it throws the foll error :
"Row cannot be located for updating. Some values may have been changed since it was last read."
The same code was working with SQL 2000 !!!
Any clues.
implicit tran are off on the sql 2005 server.
Thanks in advance.
Custom Task Not Updating Property Values Or Saving.
May 2, 2008Hi Folks,
I have a task I wrote which does not always update the property value (as seen in the properties pane)
Basically, change something on the form, then update the task host property with:
this.taskHostValue.Properties["Duration"].SetValue(this.taskHostValue, Convert.ToInt32(spnDuration.Value));
Stepping through this, it does exactly what it is supposed to. Having a look at the property value, it confirms it has changed.
Reopening the UI and resetting all the controls returns the expected results.
The package however does not realise it has changed. There is no * next to the package name in the top tabs.
As long as the package thinks it is unchanged, SaveXML does not get called either so the tasks do not persist.
Changing the value on the properties pane works fine though.
The frustrating thing is this is slightly random. Slight in the sense that sometimes it works but most of the time it does not.
The sample code I used was the MS download IncrementTask (Which works BTW) so I can't see it as being a VS / SSIS bug but rather something I am / am not doing. 3 tasks I have written all behave the same. I have to "nudge" them before savign the package.
Any ideas what the problem might be?
TIA
Cheers,
Crispin
Updating Trigger Problem : Trying To Replace The Values From The Upadate Statement
Apr 25, 2007I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.
Here's my situation
tst_update
ID int
Col1 varchar(10
I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.
Can someone help.
Thanks,
Ashish
Using NOT IN With Null Values
May 19, 2008Hi, 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 RelatedSQL Null Values In VB
Apr 10, 2006I 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?
Null Values
Jun 29, 2000I 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