I have two columns A (which allows nulls) and B( which does not allow nulls).
How can I add the contents of columns A and B SO THAT I DO NOT GET A NULL RESULT WHEN A IS NULL.
The result of A+B concatanation will be stored in a column, C.
Appreciate your help
Ziggy
In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.
View 1 Replies View RelatedIf i have a table with Col1,Col2,Col4, and Col5, how can I create and add a Col3 with null values? The format would be varchar.
View 13 Replies View RelatedHey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
Hi,
I want to perform the following select statement
select (sum(ColA) + sum(ColB) + sum(ColC)) / 3
from tableA
Where ....
I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).
How do I get around this. I need to add the values of the columns?
Any help would be appreciated.
Thanks,
Bill
Hi All,
I've one table named tableAB. in that i've added one new column with not null option in the enterprise manager. then i've generated the script, and run the script in client database. because already data is there, it is not accepting to put null value in the new column. so the is missing. anyway backup is there with me.
what is the solution for this....
thanks in advance
Hi,
I'm merge replicating a SQL Server 2005 database (publisher) to SQL Compact databases (subscribers) on mobile devices. I understood that I could add a "not null" column to a replicated table on the server as long as I specified a default value, but it seems this is not possible. I ran the following script on the server database:
ALTER TABLE Activity ADD ActivityRequiresProject bit not null default(0)
which executed OK. When I went to synchronize the db on the mobile device I got the following error:
Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values.
The SQL statement failed to execute. If this occurred while using merge replication, this is an internal error. If this occurred while using RDA, then the SQL statement is invalid either on the PULL statement or on the SubmitSQL statement. [ SQL statement = alter table "Activity" add "ActivityRequiresProject" bit not NULL constraint "DF__Activity__Activi__4A47DDAE" default ( ( 0 ) ) ]
Does anyone know if this is a valid error? Is is possible to add a not null column with default, and if not how do I update the schema on a replicated database?
Regards,
Greg
I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this? Here's my code for adding information from the table to the database: '--------------------------------------------- ' name: Button_Click() '--------------------------------------------- Sub Button_Click( s As Object, e As EventArgs ) Dim strConnect As String Dim objConnect As SQLConnection Dim strInsert As String Dim cmdInsert As SqlCommand
'Get connection string from Web.Config strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New SqlConnection(strConnect) strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @ClientID, @DomainName, @Registrar, @ExpirationDate )" cmdInsert = New SqlCommand( strInsert, objConnect) cmdInsert.Parameters.Add( "@ClientID", dropClient.SelectedItem.Value ) cmdInsert.Parameters.Add( "@DomainName", txtDomainName.Text ) cmdInsert.Parameters.Add( "@Registrar", txtRegistrar.Text ) cmdInsert.Parameters.Add( "@ExpirationDate", txtExpirationDate.Text )
objConnect.Open() cmdINsert.ExecuteNonQuery() objConnect.Close() 'Display the results "page" DisplayResults()
End Sub Here's the code for the form: <form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()"> <div align="center"> <table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099"> <tr> <td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td> <td> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Client's Name:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <asp:dropdownlist id="dropClient" runat="server" /> </p> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Domain Name:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" /> </p> </td> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Registrar:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" /> </p> </td> </tr> <tr> <td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr> Expiration Date:</nobr></strong></font></td> <td colspan="2" valign="top" bgcolor="#e8e8e8"> <p> <ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" /> </p> </td> </tr>
<TR> <TD> </TD> <TD align="center"> <asp:Button Text="Submit" OnClick="Button_Click" Runat="Server" /> </TD> </TR> </table> </form> </div>
I'm new to replication and am trying to determine the best approach to add a column (NOT NULL with no DEFAULT) to a replicated table. The only success I have had is if I do the following:
Delete entire Subscription
Delete entire Publication
Add column to table
Create new Publication
Create new Subscription
Run SnapShot
The problem with this approach is that each step affects the entire database and not just the modified table. I think it is inefficient to redo replication for a simple object change. What am I missing? Is there a way to only replicate the changes made to the one table without having to run a SnapShot for the entire publication?
Keep in mind the column must be defined as NOT NULL and cannot have a Default.
Thanks, Dave
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
Is it possible through SSRS designer?
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
ColumnA has existing data in it some of the data are Nulls
what would be the best way to have new as well as existing
data change to 0, using a constraint?
hi, I have a problem with a flat file and a table
I have a flat file with 5 columns and in the table I have 30 columns and they all are (not null)
I cannot leave other columns in target for which it throws mistake, have to insert something, since as I insert information in the demas columns???
Since I can solve this problem???
helps please
I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.
The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.
Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Key cannot be null. Parameter name: key
I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:
m_sqlCmd.ExecuteScalar();
The m_sqlCmd has been fed an SQLParameter with direction set to output.
When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok.
When the row doesn't exist, in my watch I have:
m_sqlParam.SqlValue with value {Null}
I can't seem to work out how to test this value out.
I've tried several things but none seem to work.
This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??
if (m_sqlParam.SqlValue != null)....
{
// I'm here!! I thought the watch says this is null???
}
Sorry if this is obvious, but I can't work this one out!!
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
If I had a basic table with 3 VARCHAR fields; let's say A, B, C
How could I write a query that returns the count of the number of NULL columns for every record in a table?
Ideally, it would be something like:
SELECT
CAST (A IS NULL) AS INTEGER
+ CAST (B IS NULL) AS INTEGER
+ CAST (C IS NULL) AS INTEGER
FROM MyTable
That doesn't work at all. I can't seem to do "IS NULL" in the SELECT area. Should I write a T-SQL user-defined function that takes all three columns as parameters? Would that be performance friendly for large data sets?
Content table
deleteddate revokeddate
1/1/2001 null
null 2/2/2003
4/5/2004 null
I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update
deleteddate revokeddate
1/1/2001 null
null null
4/5/2004 null
I started off as below, But I am getting incorrect values.
Declare @deleterevoke bit
Declare @DelRevId int
Declare @DelRevnumrows int
Declare @tempDeletedDate datetime
Declare @tempRevokedDate datetime
[code]....
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
View 2 Replies View RelatedThis procedure doesnt increment the "Views" column like it should when the value is NULL (not zero)
CREATE procedure [dbo].[sp_theDb_image_addView]
@imageId int
AS
SELECT [views],
CASE WHEN [views] is null then 1
END
FROM theDb_image WHERE imageId=@imageI
UPDATE theDb_image SET [views] = [views] + 1 WHERE imageId=@imageI
The CASE statement apparently isn't working.... any advice?
Thanks.
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
View 4 Replies View RelatedDoes anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean
View 5 Replies View RelatedHi,
This question comes without the wish for any philosofical debates concerning NULL
Normally SQL Server 2005 uses 13 bytes to store a NUMERIC(22,7) value. Does it use the full 13 bytes when the column contains NULL as well? And what about DATETIME and (BIG)INT? Most of the info on storage of NULL values is about varchar/char/nvarchar/varchar...
Thnx, Jeroen.
I want to run a query that selects rows from the table where a datetime column has null values;
select * from Orders where IsNull(dClosedDate,'Null') = 'Null'
However i get this error:
Conversion failed when converting datetime from character string.
Any help appreciated
Ive created a Data Access Layer (xsd file) and some of the tables have DateTime columns. I need to allow NULL values to be saved, but when I try to do that I get an error
In my code (c#) ive used Nullable DateTime, but when I try to assign a nullable Datetime to a DateTime column, I get this error
cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'
I understand the reasons why, so how do I save null DateTime values to the underlying database through a schema ?
In the schema file, the DateTime columns have <DBNULL> as the default value, AllowBDNull is true, but I am only allowed 'Throw exception' on the NULL value property
I could probably do this another way (by not using a schema and writing the code myself) but Id prefer to keep the strongly typed code.
I dont want to use MAx or MIn values for the default datetime values, so how can this be done ?
How to find out that there is a null value in a column rather than a valid integer, DateTime or bool value, for strings I use 'as' operator to cast the column value and it returns null when column value is null, but for value types using 'as' operator causes compile error and using simple casting causes runtime error, for example:
int count = (int)row["Count"];
and
int count = row["Count"] as int;
the first one throws an exception when Count is null and the second doesn't compile at all since 'as' applies to reference types, so what is the way other than exception handling to determine null value in a column?
Hello,
Is then any type of statement I could run to drop all the columns of a table that have a NULL value thoughout or must this be done manually?
Thanks,
Bryan
I am trying to divide these two columns...
parsename('$'+ Convert(varchar,Convert(money,Q.[Product Cost]-R.[Shared Cost]),1),2) as [Pre Cost],
parsename('$'+ Convert(varchar,Convert(money,R.[New Product Cost]-R.[Shared Cost]),1),2) as [Post Cost]
Formula:
[Post Cost] / [Pre Cost]
Expected:
If [Post Cost] is null then I would need to assign 1 and divide by [Pre Cost] and similarly if [Pre Cost] is null then I would need to assign 1 so that I can divide [Post Cost] / [Pre Cost]
Table DDL below:The tables I have contain Timesheet information. Each row in thetblTSCollected table contains an entry for an employee into thetimesheet system, specifically by scanning the barcode on their badge.A whole bunch of business logic periodically attempts to "pair" theseinto logically matched scans. For example, some employees will scan inand out of a single place of work. For these there will be a rowwritten to the tblTSRuleApplied table which contains, inter alia andsome redundant data, the fldCollectedID for the two rows. The earlierwill be put into the fldStartTimeCollectedID, and the later into thefldEndTimeCollectedID. Some employees will clock on at their base,then perform sub-duties at different locations during the day, andclock off at their home base at the end of their shift. For these, thesystem would identify the outer records as a matching pair, and thenpair up inner records by location.However, if the employee fails to enter a valid "clocking in and out"pair (for example, if they clock in at the wrong location) the systemneeds to generate a "dummy" "clocking in and out" record for thepayroll department. Ideally, this would have NULL values in thefldStartTimeCollectedID and fldEndTimeCollectedID columns. This wouldalert a user in a different part of the system, where missingtimesheets were being arbitrated, that an employee appeared to havefailed to clock in for that day. Of course, the user could seeon-screen that they had clocked in, but at an incorrect location.Unfortunately, the database designer is not here for the moment (he wasknocked off his bicycle recently), but he put a unique index on thetblTSRuleApplied table that prevents the same value being entered intothe fldStartTimeCollectedID and fldEndTimeCollectedID columns. This isgenerally A Good Thing, since we don't want the same timesheet scan toform both a "clocking on" event and a "clocking off" event.So, is there any way of retaining the requirement that thefldStartTimeCollectedID and the fldEndTimeCollectedID columns may notcontain the same value in a single row, UNLESS that value is NULL inwhich case all is hunky dory. I should add that the clients don't muchcare for Triggers (and neither do I for that matter).Many thanks if you are able to help.Edwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollectedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINTFK_tblTSRuleApplied_tblTSCollected1GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINTFK_tblTSArbAccept_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINTFK_tblTSCollected_tblTSRuleAppliedGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSCollected]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblTSRuleApplied]GOCREATE TABLE [dbo].[tblTSCollected] ([fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,[fldEmployeeID] [int] NULL ,[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[fldTimeStamp] [datetime] NULL ,[fldRuleAppliedID] [int] NULL ,[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fldProcessed] [smallint] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblTSRuleApplied] ([fldEmpRuleID] [int] NOT NULL ,[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,[fldStartTime] [datetime] NULL ,[fldEndTime] [datetime] NULL ,[fldStartTimeCollectedID] [int] NULL ,[fldEndTimeCollectedID] [int] NULL ,[fldStartArbStatus] [smallint] NULL ,[fldEndArbStatus] [smallint] NULL ,[fldDurationArbStatus] [smallint] NULL ,[fldPrimary] [smallint] NOT NULL ,[fldDateEntered] [datetime] NULL ,[fldEnteredBy] [int] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADDCONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR[fldProcessed],CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED([fldCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADDCONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR[fldPrimary],CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED([fldRuleAppliedID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED([fldStartTimeCollectedID],[fldEndTimeCollectedID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblTSCollected] ADDCONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY([fldEmployeeID]) REFERENCES [dbo].[tblEmployee] ([fldEmployeeID]),CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY([fldLocationCode]) REFERENCES [dbo].[tblLocation] ([fldLocationCode]),CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY([fldRuleAppliedID]) REFERENCES [dbo].[tblTSRuleApplied] ([fldRuleAppliedID])GOALTER TABLE [dbo].[tblTSRuleApplied] ADDCONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY([fldStartTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY([fldEndTimeCollectedID]) REFERENCES [dbo].[tblTSCollected] ([fldCollectedID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY([fldDurationArbStatus]) REFERENCES [dbo].[tblTSDurationStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY([fldEmpRuleID]) REFERENCES [dbo].[tblTSEmpRules] ([fldEmpRuleID]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY([fldStartArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus]),CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY([fldEndArbStatus]) REFERENCES [dbo].[tblTSTimeStatus] ([fldStatus])GO
View 7 Replies View RelatedHi,
I would like to ask how can I get count of null values in each column together?
I know I can use COUNT(*) - COUNT(<col>), but I need to explicitly name every column in the query.... is it possible without it?
Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:
/* SQL Server & Windows NT login name, security access */
USE master
go
SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME
FROM syslogins
WHERE status != 0
AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))
I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!
Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42