ExecuteScalar Returns 0 (null) But INSERT Is Successful.
Sep 25, 2007
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
View 7 Replies
ADVERTISEMENT
Oct 25, 2006
I am using the following C# code and T-SQL to get result object from aSQL Server database. When my application runs, the ExecuteScalarreturns "10/24/2006 2:00:00 PM" if inserting a duplicated record. Itreturns null for all other conditions. Does anyone know why? Doesanyone know how to get the output value? Thanks.------ C# -----aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};object oRtnObject = null;StoredProcCommandWrapper =myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);------ T-SQL ---ALTER PROCEDURE [dbo].[procmyCalendarInsert]@pBegin datetime,@pEnd datetime,@pUserId int,@pOutput varchar(200) outputASBEGINSET NOCOUNT ON;select * from myCalendarwhere beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserIdif @@rowcount <0beginprint 'Path 1'set @pOutput = 'Duplicated reservation'select @pOutput as 'Result'return -1endelsebeginprint 'Path 2'-- check if upperlimit (2) is reachedselect rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))),count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))))from myCalendargroup by rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))having count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))) =2and (rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))) =rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd asvarchar(20))))-- If the @@rowcount is not equal to 0 then-- at the time between @pBegin and @pEnd the maximum count of 2 isreachedif @@rowcount <0beginprint 'Path 3'set @pOutput = '2 reservations are already taken for the hours'select @pOutput as 'Result'return -1endelsebeginprint 'Path 4'--safe to insertinsert dbo.myCalendar(beginTime, endTime,userId)values (@pBegin, @pEnd, @pUserId)if @@error = 0beginprint 'Path 4:1 @@error=' + cast(@@error as varchar(1))print 'Path 4:1 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Reservation succeeded'select @pOutput as 'Result'return 0endelsebeginprint 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Failed to make reservation'select @pOutput as 'Result'return -1endendendEND
View 1 Replies
View Related
Oct 15, 2004
The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
Any ideas on what I might be doing wrong... ?
View 1 Replies
View Related
May 3, 2006
Hello.
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3))
RETURNS nvarchar(1000)
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
And it works as expected, except when I use NULL:
DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
View 3 Replies
View Related
Jan 14, 2008
I'm running a pretty standard insert command on a button (myCommand.ExecuteNonQuery();)
I have a few other methods that run, but I only want them to proceed if the above was inserted successfully, how would i check that?
View 8 Replies
View Related
Feb 10, 2006
hello,
I'm new to store procedure in slq 2000.
I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier",
now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful.
So how to create this store procedure ?
much thanks
View 6 Replies
View Related
Oct 9, 2014
I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.
SELECT db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID,
db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode,
db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport,
db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term,
[code]...
It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS. How to implement to LkupMedical table....
I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..
View 12 Replies
View Related
Jan 21, 2004
hi,
i have the following query to sum the total due balance for a customer:
select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?
thanks
View 7 Replies
View Related
Jul 23, 2005
I am trying to script out the creation of database scripts. I am tryingto use @@servername in the statement. I found out the select@@servername returns NULL. I used sp_dropserver to drop any servernamesfirst, restarted SQL, ran sp_addserver 'servername' to add theservername, restarted SQL. select @@servername still returns NULL...Any ideas why this may be happening?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 18, 2007
I have the following code in my custom source component's AcquireConnection method -
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;
if (cmado == null)
throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");
// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
if (oledbConnection == null)
throw new Exception("The ConnectionManager is not an ADO.NET connection.");
isConnected = true;
}
The value of oledbConnection is null.
I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?
private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";
ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");
PubTRCM.Name = "PubTR";
PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);
#region Create OLEDB Source Component
//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";
Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;
//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();
//Initialize the component
SourceDesignTime.ProvideComponentProperties();
if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}
SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");
//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();
#endregion
View 3 Replies
View Related
May 15, 2006
Hi,
we are facing some issue to get the underlying OledbConnection from the runtime ConnectionManager.
below is the code sample that we are using
IDtsConnectionService conService = (IDtsConnectionService)this.serviceProvider.GetService(typeof(IDtsConnectionService));
if (conService == null)
return;
ArrayList conCollection = conService.GetConnectionsOfType("OLEDB");
for (int count = 0; count < conCollection.Count; count++)
{
string conName = ((ConnectionManager)conCollection[count]).Name;
if (conName == conMgrname)
{
conMgr = DtsConvert.ToConnectionManager90((ConnectionManager)conCollection[count]);
ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);
Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;
OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;
}
}
In the sample above the conn is null.
To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.
Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.
The connection type for the ADo>NET OLE-DB connection is -
ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.
View 7 Replies
View Related
Apr 26, 2007
I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:
Server1 has Database1.
Database1 has Table1 with two columns encyrpted -- Card Number and SS Number
Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too.
Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value
Here's exactly what I did to create the encyprtion and decryption keys on the restored database:
-- Create the master key encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'
-- Create a symetric key
CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES
ENCRYPTION BY Password='myPassword';
Go
-- Create Card Certificate
CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server';
GO
-- Change symmetric key
OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';
-- I then verified if the key is opened
SELECT * FROM sys.openkeys
If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!
Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.
Thanks in advance!
faiga16
3 Posts
View 9 Replies
View Related
Mar 9, 2006
I have a SQL 2005 clustered server which is returning a Null value for @@servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @@servername which returns a null value. So I am struck in a loop.
Any advice is appreciated.
thanks
View 2 Replies
View Related
Mar 7, 2008
Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()
View 14 Replies
View Related
Jan 4, 2007
Hi All,
I have a built an application to manage the report-subscription on my ReportServer.
I'm having some troubles with retrieving the subscription properties.
My problem:
I can retrieve all my propertie except the schedule definition, when I deserialize it, the Sceduledefenition.Item always returns null.
I use the next code to deserialize it:
//////////////////////////////////////////////////////////////////////////////////////////////////////
private XmlAttributeOverrides GetSchedule()
{
XmlAttributeOverrides xmlAttrOverride = new XmlAttributeOverrides();
XmlAttributes XmlAttr = new XmlAttributes();
XmlAttr.Xmlns = false;
xmlAttrOverride.Add(typeof(ScheduleDefinition), XmlAttr);
xmlAttrOverride.Add(typeof(MinuteRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(DailyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(WeeklyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(MonthlyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(MonthlyDOWRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(DaysOfWeekSelector), XmlAttr);
xmlAttrOverride.Add(typeof(MonthsOfYearSelector), XmlAttr);
return xmlAttrOverride;
}
private ScheduleDefinition DeserializeScheduleDefenition(string MatchData)
{
XmlAttributeOverrides xmlAttrOverride = GetSchedule();
XmlSerializer ser = new XmlSerializer(typeof(ScheduleDefinition),xmlAttrOverride);
Stream stream = new MemoryStream(System.Text.Encoding.Default.GetBytes(MatchData));
stream.Position = 0;
return (ScheduleDefinition)ser.Deserialize(stream);
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I'have tried almost everything, but could not find the solution.
Please, this is very important for me, so every tip or shared thought could be very helpfull.
Thnx in advance
View 2 Replies
View Related
Nov 16, 2007
How do you prevent SQL from returning a null value for a computed column?
The following formula returns blank if any of the values are blank ... pftitle allows nulls, the other two are required on Insert.
(((([pflastname]+', ')+[pffirstname])+' - ')+[pftitle])
Where can I find the documentation of computed field operators and expressions?
View 3 Replies
View Related
Oct 22, 2006
Im having some unexpected results from combining ISNULL with logical and '&&' and conditions '?:' in derived columns.
This works and returns A or B.
(ISNULL(Col1) && Col2 == "X") ? "A" : "B"
This fails returning Null when Col1 is null
(Col2 == "X" && ISNULL(Col1)) ? "A" : "B"
This fails returning null when Col1 is null
(ISNULL(Col1) && Col2 == "X") ? "A" : (ISNULL(Col1) && Col2 == "Y") ? "B" : "C"
I've applied service pack one and still have the issue. I've also tried using ISNULL in a condion like (ISNULL(Col1) ? TRUE : FALSE)
Anyone else seen this behavior?
View 6 Replies
View Related
Jul 28, 2006
I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!
View 7 Replies
View Related
Mar 10, 2004
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();
SqlDataReader myReader = cmd.ExecuteReader();
if (myReader.Read()) {
thanks
Rod
View 2 Replies
View Related
Jan 21, 2008
The DecryptByKey function occasionally returns null even though the EncryptByKey function retuned a non-null value. The problem only occurs for a subset of rows returned by a single select and every time the script is executed, a different set of rows is affected by the problem. Occasionally all fields get encrypted/decrypted successfully, but this is rare.
It seems that the EncryptByKey function occasionally returns a value that can not be decrypted at a later point in time.
I am running on Windows XP Professional SP 2 with SQL Server 9.0.3042.
I have included a sample of the code below.
Thank you,
Mike
CREATE FUNCTION [dbo].[encrypt_text]
(
@input_text varchar(255)
)
RETURNS varbinary(8000)
AS
BEGIN
RETURN EncryptByKey(Key_GUID('eia_key'), @input_text)
END
CREATE FUNCTION decrypt_text
(
@input_text varbinary(8000)
)
RETURNS varchar(255)
AS
BEGIN
return convert(varchar(255),DecryptByKey(@input_text))
END
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N'eia_key')
DROP SYMMETRIC KEY eia_key
CREATE SYMMETRIC KEY eia_key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = '???'
OPEN SYMMETRIC KEY eia_key DECRYPTION BY PASSWORD = '???'
execute util_print 'Deleting data'
execute ld_delete_lips_data
execute util_print 'Loading data'
set nocount on
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (1, 'TERM', 0, 0)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (2, '0 - 2', 0, 2)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (3, '2 - 5', 2, 5)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (4, '5 - 10', 5, 10)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (5, '10+', 10, null)
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (1, dbo.encrypt_text('3 Month'), dbo.encrypt_text('Blended'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (2, dbo.encrypt_text('1 Year'), dbo.encrypt_text('Fundamental'))
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
values (3, dbo.encrypt_text('Technical'), dbo.encrypt_text('Technical'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (1, dbo.encrypt_text('Low'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (2, dbo.encrypt_text('Median'))
insert into forecast_levels (forecast_level_id, forecast_level_name)
values (3, dbo.encrypt_text('High'))
execute util_reseed_ident 'asset_classes', 0
execute util_execute_sql 'insert into asset_classes default values', 11
insert into sectors (sector_id, sector_name)
values (1, dbo.encrypt_text('Sovereign'))
insert into sectors (sector_id, sector_name)
values (2, dbo.encrypt_text('Inflation Linked'))
insert into sectors (sector_id, sector_name)
values (3, dbo.encrypt_text('Quasi & Foreign Government'))
insert into sectors (sector_id, sector_name)
values (4, dbo.encrypt_text('Securitized/Collateralized'))
insert into sectors (sector_id, sector_name)
values (5, dbo.encrypt_text('Corporate'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (6, dbo.encrypt_text('AAA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (7, dbo.encrypt_text('AA'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (8, dbo.encrypt_text('A'))
insert into credit_ratings (credit_rating_id, credit_rating_name)
values (9, dbo.encrypt_text('BBB'))
insert into sectors (sector_id, sector_name)
values (10, dbo.encrypt_text('High Yield'))
insert into sectors (sector_id, sector_name)
values (11, dbo.encrypt_text('Emerging Debt'))
set nocount off
insert into currencies (currency_id, currency_name, currency_code)
select CurrencyID, dbo.encrypt_text(CurrencyName), dbo.encrypt_text(CurrencyCode)
from lips_import..Currencies
View 3 Replies
View Related
Apr 11, 2008
Is there a way to check for System.BDNull when I use the column name instead of column index? The column Photographer (in the excample below) sometimes contains the value null, and then it throws an error. Or do I have to go back to counting columns (column index of the returned data) again? try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { albumItem = new Album((int)reader["Id"]); if (reader["Photographer"] != null) albumItem.Photographer = (string)reader["Photographer"]; albumItem.Public = (bool)reader["IsPublic"]; } } } Regards, Sigurd
View 2 Replies
View Related
Jan 6, 2006
I have a stored procedure like "select * from ThisTable"
I'm doing a dataread like:
Dim val as String = dateRead("column_from_ThisTable")
If the value in the column is not null everything works great, but if the value is null, instead of getting a value of "" which I expect, I get the column name??? In this case "column_from_ThisTable"
How do I make sure I get "" returned when the value in the column is db.null?
View 3 Replies
View Related
Feb 22, 2006
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies
View Related
Apr 20, 2007
I'm using SSEE 9.0.3042 and started loosing sleep over trying to figure out why the following piece of SQL
DECLARE @x xml
DECLARE @a nvarchar(400);
DECLARE @b nvarchar(400);
DECLARE @c nvarchar(400);
SET @x=N'<SuchKriterienDataSet xmlns="http://tempuri.org/SuchKriterienDataSet.xsd">
<SuchKriterien><Index>0</Index><Name>a</Name><Wert>Test1</Wert><Bedingung>0</Bedingung></SuchKriterien>
<SuchKriterien><Index>1</Index><Name>b</Name><Wert>Test2</Wert><Bedingung>0</Bedingung></SuchKriterien>
<SuchKriterien><Index>2</Index><Name>c</Name><Wert>Test3</Wert><Bedingung>0</Bedingung></SuchKriterien>
</SuchKriterienDataSet>';
WITH XMLNAMESPACES ('http://tempuri.org/SuchKriterienDataSet.xsd' AS ds)
SELECT
@a=@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "a"])[1]','nvarchar(400)'),
@b=@x.value('(/dsuchkriterienDataSet/dsuchkriterien/ds:Wert[../ds:Name = "b"])[1]','nvarchar(400)'),
@c=@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "c"])[1]','nvarchar(400)');
WITH XMLNAMESPACES ('http://tempuri.org/SuchKriterienDataSet.xsd' AS ds)
SELECT
@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "a"])[1]','nvarchar(400)'),
@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "b"])[1]','nvarchar(400)'),
@x.value('(/dsuchKriterienDataSet/dsuchKriterien/ds:Wert[../ds:Name = "c"])[1]','nvarchar(400)')
UNION ALL
SELECT @a,@b,@c
returns
Test1 Test2 Test3
Test1 NULL Test3
instead of
Test1 Test2 Test3
Test1 Test2 Test3
This is the shortest repro I could assemble. Once you increase the number of variables, a random pattern of dropped values emerges.
I could repro on fully patched Windows XP German w/ SSEE ENU and Windows Server 2003 64-bit w/ SSEE GER.
Any workaround highly appreciated.
Thx,
Henry
View 5 Replies
View Related
Mar 3, 2008
Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?
Code Snippet
SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.
View 10 Replies
View Related
Jan 6, 2005
Hi
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
Cheers - Andy
View 4 Replies
View Related
Jul 15, 2015
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.
View 7 Replies
View Related
Apr 2, 2007
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
View 8 Replies
View Related
Sep 7, 2000
I'm fairly new to SQL, so this might be simple question:
I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query.
Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?...
Any help or suggestions would be appreciated.
View 2 Replies
View Related
Jan 8, 2008
Hi all,I have an old ASP .NET 1.1 application that I haven't had time to rebuild with .NET 2.0. No changes have been made to the application or the SQL Server the application uses. I have a web form where users can type multiple lines of text, and it is entered into an SQL database (SQL 2000 Enterprise) into a column with a datatype of "text". Recently (it seems out of nowhere), If my users enter a carriage return into the webform, it becomes a question mark in the sql database. It's really a bit funny, but also annoying, lol. Does anyone have any idea why this might be happening? Thanks so much!
View 3 Replies
View Related
Feb 3, 2004
Hi !
I have trying to do an insert with a subroutine that calls a stored procedure, but it doesn’ t run. The page loads properly but nothing is inserted in the table of the database, no errors appears after submit the form.
<asp:Button id="SubmitButton" OnClick="Send_data" Text="Submit" runat="server"/>
Here is the code:
Sub Send_data(Sender As Object, E As EventArgs)
Dim CmdInsert As New SqlCommand("new_user1", strConnection)
CmdInsert.CommandType = CommandType.StoredProcedure
Dim InsertForm As New SqlDataAdapter()
InsertForm.InsertCommand = CmdInsert
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "User_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters.Add(New SqlParameter("@e_mail", SqlDbType.varchar, 50, "e_mail"))
CmdInsert.Parameters("@e_mail").Value = mail.Text()
End Sub
What it lacks?
View 3 Replies
View Related
Mar 23, 2007
private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;
SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}
i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.
and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
what is the problem?
View 6 Replies
View Related
Jan 30, 2007
Good evening,
I have created a table like this:
create table Teacher(tid integer,
fname varchar(20),
lname varchar(30),
primary key(tid));
Well i insert many Teachers uning : insert into values ... etc.
These inserts are being placed in an sql file named insert.sql.
All the insertions are fine.
Now I'm trying to insert another teacher using another file e.g. my_insert.sql and it says cannot insert NULL into teacher tid etc.
Why does this happen,in my my_insert.sql file all the filds of the tid are filled with non null values?
What's going wrong?
Thanks, in advance!
View 12 Replies
View Related
