I have a result set stored in a temporary table variable. The fields need to have heirarchy when they finaly get displayed to the user. I need to replace the repeating fields per group with NULL and leave out only one set of unique records per group.
I want to do something like this- make the fields in red NULL while leaving out the ones in green.
ValB ValB2 ValB3 ValB40 ValA50 { Row. Group B
ValB ValB2 ValB3 ValB41 ValA51 }
I was able to achieve this by using @@ROWCOUNT with in a while loop by performing one UPDATE at a time (SET ROWCOUNT = 1). But i have to run Count(*) each time I loop through the update query to figure out the remaining number of rows I have per group as I need to leave out one untouched. But the Count function is taking too much time and I'm having performace issues.
Any idea how I might be able to do this in my stored proc. efficiently?
hi, I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
Is there a setting in SQL Server that ensures a column is not allowed to have the same value more than once? Or must this be set up in the insert statment itself? Or how about a business rule?
When setting up an ODBC connection on a non-sql Server X and choosing from the drop down menu which SQL Server to connect to, I see two identical names for my SQL Server (Y). No matter which one I choose, Im not able to go to the screen after being asked how I want to log in (SQL server authentication)
If I build a ODBC connection from any other desktop, I only see one SQL Server Y, and am able to establish a connection to SQL Server Y, with the same user account and password as when Im on Server X.
Server X has two network cards and is teamed...could that be a possible problem ?
I have a table that contains some duplicate records. The entire record is not duplicated, but there are two fields which if both are duplicated, result in what I call a duplicate record. I can see that I have these duplicates by doing the following:
SELECT DISTINCT ReferenceNumber, ShipToCompanyID FROM tabMedia WHERE (MediaTypeID = '1') AND (FinishDate > '2011-01-01') GROUP BY ReferenceNumber, ShipToCompanyID HAVING (COUNT(ReferenceNumber) > 1) AND (COUNT(ShipToCompanyID) > 1) ORDER BY ReferenceNumber
but of course, this only returns two fields. I need the entire record returned where the duplicate exists...
Using Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?
I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345 BRAND NAME = AA 2. ITEM SERIAL NO = 12345 BRAND NAME = BB.
I am relativley new to SQL and have a question about identity fields.
I am creating a script to run everynight to insert records into a support table in a database. one of the fields is a identity field that is updated everytime a record is added locally or over the web.
Some records that are added into the database locally by users do not get added into theis support table, but so those new people entered in can use the website a entry must be added to this support table.
I am working on a script to take the records that where added by users and automatically put them in every night using a basic schedueled job.
The identy number is updated everynight in a table that collects all the important identiy numbers. I would like to use this table to alter the seed value and then increment by one every time a new record is added. This is my only sticking point so far.
Hi, I need to add to a database table.I have 5 variables that need to be added. But, i need to add 3 of them in one procedure and then the other2 in another procedure...so i'm using dboperation add to add the first 3 and dboperation edit to add the other 2 vars. However, when i add the first 3 vars, i need to set the other 2 to null...so in my datasetop proc i'm using Optional Byval var as string="".....but this doesnt work...it says "conversion from dbnull to string is not valid".......ive also tried db.null, "N/A" and nothing...but it still gives me the error?
Hi I set up some routes incorrectly on one of my servers and included a mirror address when i shouldnt have. now i cant get rid of the mirror address - when i try
alter route remote_route with mirror_address = null
i get an error saying
incorrect syntax near null
Does anybody know how i can clear out the mirror address for a route?
(If this is not the right forum, please advise and I'll repost).I am using Visual Web Developer 2005 Express (ASP .NET 2,0), with a SQL 2005 database. I have programmed a Data Access Layer using table adaptors: this is my standard way of using the database. I had defined a table, Voyages: -CREATE TABLE [dbo].[Voyages]( [VOYid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Voyages_VOYid] DEFAULT (newid()), [VOYUser] [varchar](50) NOT NULL, [VOYShipName] [varchar](255) NOT NULL, [VOYDepartPort] [uniqueidentifier] NOT NULL, [VOYDepartDate] [datetime] NULL, [VOYArrivePort] [uniqueidentifier] NOT NULL, [VOYArriveDate] [datetime] NULL, [VOYCaptain] [varchar](50) NULL, [VOYComment] [varchar](1024) NULL, [VOYUpdateid] [uniqueidentifier] NULL, CONSTRAINT [PK_RESVoyages] PRIMARY KEY CLUSTERED I defined a table adaptor based on the query SELECT Voyages.*FROM Voyagesand defined variables in my VB code: - Dim tavoyages As New ShippingTableAdapters.VoyagesTableAdapter Dim tbvoyages As Shipping.VoyagesDataTable Dim trvoyages As Shipping.VoyagesRowand read data into these variables with:- tbvoyages = tavoyages.GetDataByVoyid(Voyageid) If tbvoyages.Count > 0 Then trvoyages = tbvoyages(0) ....All of this works perfectly, and trvoyages contains all the correct data.I wanted to set VOYUpdateID to NULL, so I coded trvoyages.VOYUpdateid = Nothing tavoyages.update(trvoyages)However this set the database field to "00000000-0000-0000-0000-000000000000", not to NULL. However this code, in which we use another of the Update overloads naming all variables and "Nothing" in the VOYUpdateid position, works perfectly: -tavoyages.Update(trvoyages.VOYid, trvoyages.VOYUser, trvoyages.VOYShipName, trvoyages.VOYDepartPort, trvoyages.VOYDepartDate, trvoyages.VOYArrivePort, trvoyages.VOYArriveDate, trvoyages.VOYCaptain, trvoyages.VOYComment, Nothing, trvoyages.VOYid) Is there any way in which trvoyates.VOYUpdateid can be set to Null. I tried dbnull, but just got an error message.Thank you,Robert Barnes.
I am using a Ms-Access DS which is accessed by a website's server-side scripts.
What I would like to do is set an existing record's date/time field to null. I have tried to simply alter its value by not including any data within the sharps (##), however that did not work.
After adding a Unique constraint to a database I cannot add more than one record with a null value for the constrained field. I've tried both adding the constraint to an empty table as well as a table with multiple null values already in the subject field; both efforts have failed.
According to BOL SQL-7 allows Unique Constraints on fields with Null values. Am I missing a step? I do need to allow nulls in the field yet ensure that when there is a non-null value it is unique.
The SQL statement I've used is: ALTER TABLE tbl_MasterUIC ADD CONSTRAINT uniquesamplenbr UNIQUE NONCLUSTERED (samplenbr)
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select * from VendTable1 a join ( select firstname, lastname from VendTable1 group by firstname, lastname having count(*) > 1 ) b on a.firstname = b.firstname and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
I am working on Sql Server 7.0 with a group of Visual Basic programmers that have made the following request:
1.They want all decimals/integer fields in the database to default to 0(zero). Is there an way to do this for all fields instead of on an individual basis? 2.They don't want a null returned on any character string field.
What is the best way to take care of these 2 requests? Thanks in advance. Karen Suenram
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...
The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?
I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...
But I am wondering if there is a way to do the select in one shot?
Im trying to use MS SQL server mananagement studio to greate a view to recive information from my database. The problem i have is that i include a tabel where a post have "Null" then that post will not be shown i my qustion/View.
How can i make soure that i get all the post even if they have "null" in a field where it do not mather(its not a Key)
Greatful for very fast, help. Need this to be solved tomorrow :-(
1) Cannot have results in which both m.homephone & d.homephone are both null, or both m.workphone & d.workphone are both null in the result set
2) We also do not want to see any combos where one homephone is null and the other has only the char 0....same for workphone...no null & 0 combinations.
e.g.
m.homephone d.homephone
null 0
0 null
The phone fields are varchar
I know this is hidiously wrong but is my first sloppy attempt at best: select m.number, m.homephone as master_homephone, d.homephone as debtor_homephone, m.workphone as master_workphone, d.workphone as debtor_workphone
FROM master m
INNER JOIN debtors d ON d.Number = m.number
where (d.homephone <> m.homephone OR d.workphone <> m.workphone) AND (d.homephone IS NOT NULL AND m.homephone IS NOT NULL) AND (d.workphone IS NOT NULL AND m.workphone IS NOT NULL) AND NOT ((d.homephone IS NULL AND m.homephone = '0') OR (d.homephone = '0' AND m.homephone IS NULL)) AND NOT ((d.workphone IS NULL AND m.workphone = '0') OR (d.workphone = '0' AND m.workphone IS NULL))
Hi, I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields. I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is: SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept) so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful. Cheers, Tom
Hi there, I have a fairly generic question regarding database design. I am working on my first database using SQL Express. I am testing the database by inserting records into the pre defined tables. Sometimes I forget to input text into a non null field. When this happens it displays an error and then skips a few rows which makes my row numbering messy. For example say I have Row 1, Row 2 Row 3 all nicely formatted but on Row 4 I make a mistake this row then disapears and their is gap between 3 and 5 where 4 was. Although I am just testing, I was wondering what happens in a real scenario if an inexperienced data input person makes a few mistakes it makes the database numbering systems look bad. In this case is it common to make most fields NULL to allow for admin errors??
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null. Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
I'm using SQL Server Express and am trying to import a CVS file. The CVS file contains a string field (named DAS) that represents a Date. This field can be null.
I've tried using the DTS Wizard to import this CVS file and convert the DAS field to a Date, which works great until it hits a record with a NULL DAS field. It then throws a convertion error.
Still using the DTS Wizard, I've changed the DataType of the DAS field in the Source file to [DT_DATE], it works fine but all the null dates are converted to 12/30/1899.
Is there a way (DTS Wizard or something else) that will allow me to import these CVS files with null Date fields and keep them as null in SQL Server table.
This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.
Here is my current SELECT statement:
SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name, m.JobTitle, m.Company, m.Department, m.Address, m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ, m.WorkPhone, m.FAX, m.Email, c.Chapter, m.Active, s.Sector, i.Industry FROM tblMembers m LEFT OUTER JOIN tblChapters c ON m.ChapterID = c.ChapterID LEFT OUTER JOIN tblSectors s ON m.SectorID = s.SectorID LEFT OUTER JOIN tblIndustries i ON m.IndustryID = i.IndustryID WHERE m.DRGInclude = 1
My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.
The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.
Can someone tell me what I am missing? Is there a function that I can use for this?
I using Visual Web Designer Express (with Visual Basic), with a SQL Server 2000 database. I have a prototype application running satisfactorily using code that builds queries, but I'm now trying to rebuild the application "properly" using three-tier architecture. I have been following the principles of Scott Mitchell's tutorials. I have created an database .xsd with a table adaptor, and replaced the rather grotty query-building code in the business layer with better code referencing the table adaptor. Thus where the first version had code: - Dim queryString As String = "SELECT * FROM NZGDB_User WHERE USRid = '" & Userid & "'" Dim message As String = "" Dim Found As Boolean = False Try Using connection As New SqlConnection(GDB_AppSettings.Connection) Dim command As New SqlCommand(queryString, connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() If reader.Read() Then Found = True _USRid = reader("USRid") _USRPassword = reader("USRPassword") _USREmail = reader("USREmail") _USRTitle = reader("USRTitle") _USRName = reader("USRName") _USRRole = reader("USRRole") If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End If End If reader.Close() End Using Catch ex As Exception If Left(Err.Description, 68) = "An error has occurred while establishing a connection to the server." Then Return "Cannot open database to logon" Else Return Err.Description End If End Try the new version is much more elegant: - Dim taUser As New NZGDBTableAdapters.NZGDB_UserTableAdapter() Dim tbUser As NZGDB.NZGDB_UserDataTable = taUser.GetUserByUserid(userid) If tbUser.Count <> 1 Then ' Anything other than 0 or 1 should be impossible Return "User not found" End If Dim trUser As NZGDB.NZGDB_UserRow = tbUser(0) _USRid = userid _USRPassword = password _USREmail = trUser.USREmail _USRTitle = trUser.USRTitle _USRName = trUser.USRName _USRRole = trUser.USRRole _USRAgreedConditions = trUser.USRAgreedConditions However, there is a problem. The database field USRAgreedConditions is a Datetime field that can be null. The new version works perfectly when it is NOT null, but throws an exception: - System.Data.StrongTypingException was unhandled by user code Message="The value for column 'USRAgreedConditions' in table 'NZGDB_User' is DBNull." Source="App_Code.wt2vzoc1" .... There is no point in writing: - If Not IsDBNull(trUser.USRAgreedConditions) Then _USRAgreedConditions = trUser.USRAgreedConditions End Ifbecause the exception occurs within the automatically-created code in the data access layer. I tried changing the Nullvalue property of the field USRAgreedConditions in the table adaptor, but the only valid option is (Throw Exception) unless the field is a String. Of course USRAgreedConditions is a Datetime field, so I can't change the property. It seems that my only options are: -1. To stick with the old query-building code. But this doesn't give me the advantages of a proper 3-tier architecture2. To change the generated code in wt2vzoc. This MUST be a bad idea - surely I should leave this code untouched. Besides, what if the table adaptor has to be regenerated when I change the table design?3. Code a Try block within the business layer: - Try _USRAgreedConditions = trUser.USRAgreedConditions Catch ex As Exception _USRAgreedConditions = Nothing End Try This seems to work OK, but seems less elegant than the original code in the old version: - If IsDBNull(reader("USRAgreedConditions")) = False Then _USRAgreedConditions = reader("USRAgreedConditions") End IfIs there a better way?
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code: SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open(); The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field. Appreciate if someone could tell me what i am doing wrong. Thanks
On my aspx page I have a basic sqldatasource and gridview - both were set up using the wizards. The SQL Server 2000 database table that the sqldatasource is querying has some fields in it that are set to not allow nulls, however, the gridview control is not displaying all of the fields in the table - including some of the non-null fields. My problem is, when I run an insert on the table from my aspx page, I get an error that says: "Cannot insert the value NULL into column 'ColumnName', table 'TableName'; column does not allow nulls. INSERT fails. The statement has been terminated." The 'ColumnName', as you may have guessed, is one of the aforementioned columns that doesn't allow nulls, but isn't in the GridView. How can I do an insert on this table without messing with the non-null fields. Those fields are relevant to the purposes of the gridview, so I don't want to display them, let alone allow editing of them. Any suggestions on my options would be greatly appreciated! Thanks Capella07
If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked. The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.
// This code is called 10 times with a select * from where item="xxx" P21Conn.Open();
