I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim null As New Nullable(Of Integer)
Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter
I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration. Thank you in advnace for any help on this! INSERTrequest ( emp_id, request_submit_date, request_start_date, request_end_date, request_duration, request_notes, time_off_id ) Select@emp_id, GETDATE(), @request_start_date, @request_end_date, 1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date), @request_notes, @time_off_id
I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side. Thanks! in advance.
once upon a time someone told me how to do this, but i forget, and it was for classic asp.. so what im trying to do is create a new record (a workstation) and get back the auto generated ID (its not actually a GUID, its an int which is set to the identity) and then once i have that create other records (for external devices, different table) that will have a link to which machine they are attached to (that Id im looking to get returned) I know this can be done in a two step process, create the record, query it for its id, and then slap that in my next insert statement for the external devices... but... i know there was a way to do it before and i assume there is a way to do it now... anyone know how? Thanks Justin
Im still learning my way around SQL and queries and i was wondering : How do you get a SQL Table to autoincrement a Guid? (is it "Is Identity?" or "RowGuid"....) How would i create a new row with a new Guid, and insert into the values i want without specifying the Guid?
The primary key of my database (SQL server 2005) table is a uniqueidentifier.
I am using the following code to insert a row into my table:
myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command myCommand.Connection = this.dbConnection; //add the database connection to the command myCommand.ExecuteNonQuery(); //execute the insert query
I need to retrieve the GUID that is automatically generated when the insert command is executed.
Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using
string _id = (string)myCommand.ExecuteScalar();
and I am still stuck. I will really appreciate it if someone can refer me to some code sample.
I have created a database in the SQL Server 2005 and the same database in sql mobile. I have a field, which has a GUID and uniqueidentifier, when i tried to insert the same data which is there in sql server 2005 database into sql mobile database it is giving me error as below
Major Error 0x80040E14, Minor Error 25501
> INSERT INTO TypeValue (TypeValue_ID,DisplayValue,TypeName,OrderIndex,IsActive)
The code below returns the members who have accounts that are inactive, and works correctly. It returns the memberid, but returns null values for the barcode values. I am explicitly looking for unmatched rows, i.e. the LEFT OUTER JOIN will return joined member-address rows which do not have a matching account, according to the ON conditions, and these are the ones I want because of that WHERE condition. Therefore account.barcode will always be NULL too.
Is there to perform the same query and return account details? I have tried running a query to return just the memberid values and then perform a second query to match the account details, but this is very ineffecient and slow.
select member.memberid, account.barcode FROM member INNER JOIN address ON address.MemberID = member.Memberid LEFT OUTER JOIN account ON account.Memberid = member.MemberID AND account.enddate >= current_date AND account.closed = 0 WHERE account.Memberid is null AND member.isDeleted = 0
I've got the following query in SQL 2000:select a.SSN, a.MonthName, a.IMClinicDay,b.IMClinicDay as SecondDayfrom tblResidentRotations ainner join view7 bon a.SSN = b.SSNwhere a.AcademicYear = '2004-2005' and a.SSN = '999999999' anddatename(month, a.IMClinicDateFirst) = b.MonthNameThis query returns a resultset like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 January Tuesday PM Friday PM999999999 April Friday PM Monday PM....and so onFor some of the months, there is a null value for "b.IMClinicDay". Forexample, it's null for August, December, and February. I want myresultset to look like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 August Tuesday PM null999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 December Tuesday PM null999999999 January Tuesday PM Friday PM999999999 February Friday PM null999999999 April Friday PM Monday PM....and so onHow can I return a null for these days? Thanks for any help oradvice.
i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null. SP Code ALTER PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable] @assignmentPrimaryKey int, @rowCount int OUTPUT AS BEGIN SET NOCOUNT ON; SELECT employeePrimaryKey FROM assignmentTable WHERE primaryKey = @assignmentPrimaryKey; SET @rowCount = @@RowCount; END c# code SqlConnection conn = GetOpenSqlConnection(); if (conn == null) return true; SqlDataReader reader = null; SqlParameter p1 = new SqlParameter(); SqlParameter p2 = new SqlParameter(); try { SqlCommand command = new SqlCommand(); command.CommandText = "RetrieveEmployeeKeyFromAssignmentTable"; command.CommandType = CommandType.StoredProcedure; command.Connection = conn; p1.ParameterName = "@assignmentPrimaryKey"; p1.Value = assignmentPrimaryKey; p2.ParameterName = "@rowCount"; p2.Direction = ParameterDirection.Output; p2.Value = 0; command.Parameters.Add(p1); command.Parameters.Add(p2); reader = command.ExecuteReader(); if (p2.Value == null) //always true any suggestions would be appreciated. thanks. matt
I have a simple table on my webpage which shows data from my database. It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value. I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work. If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
I have a table tblnetwork on which identity property is defined on column networkid.
When I issue the following command new record is getting inserted into tblnetwork and identity column is getting incremented properly. But, I am not able to get the @@identity value It is returned as NULL.
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.
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.
I'm still having issues with this despite my attempts to resolve. I even have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution, but still the encrypted data returns nulls when I exec as a user other than DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117 r$$#1946kcj$n44ncjhdlj' GO
CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES ENCRYPTION BY CERTIFICATE HumanResources037; GO
USE [AdventureWorks]; GO
-- Create a column in which to store the encrypted data ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO
-- Open the symmetric key with which to encrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber. UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO
-- Verify the encryption. -- First, open the symmetric key with which to decrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO
-- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match.
alter procedure getDecryptedIDNumber with exec as owner as SELECT NationalIDNumber, EncryptedNationalIDNumber AS "Encrypted ID Number", CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS "Decrypted ID Number" FROM HumanResources.Employee; GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master] GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
USE [AdventureWorks] GO
CREATE USER [test] FOR LOGIN [test] GO
use [AdventureWorks] GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test] GO
GRANT IMPERSONATE ON USER:: dbo TO test; GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/ exec as user = 'dbo' exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hello again! I'm having yet another problem with my code.
CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' + CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' + CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END
The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?
I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible. Example: Source file.xls Name Grade OtherGrade John 30 30.23 In the DataViewer Name Grade OtherGrade John 30 NULL
anyone knows if there's an easy way to insert new GUIDs into uniqueidentifier cells when editing data in table on Management Studio Express? or Visual Studio 2005 Server Explorer? I can't find a shortcut, and copying from external tools is pain..
I suppose it's possible to build a VB macro in Visual Studio, would it work when editing table data?
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.
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where Date1='8/9/2006 11:17:41 AM' 2)select * from X where Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance
i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected. there are 3 parameters, date range, receipt, po # if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account. My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this: ******************************************* name = contact_id data type = integer prompt = [blanked out] hidden = checked internal = checked multi-value = unchecked allow null value = checked allow blank value = unchecked
available values: from query: dataset = account value field = contact_id
label field = contact_id
default values: from query: dataset = account value field = contact_id **********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
I am using SQL Server Data Tools for Visual Studio 2012. I have a very simple SSIS package with a Data Flow task that exports from an OLE DB Source to a tab-delimited unicode Flat File Destination and a Bulk Insert task that loads from the file. Both the Flat File Destination and Bulk Import are using the same code page. The Bulk Insert task is using the wide char format to read from the file. The process works fine with nvarchar and int columns, but when I add a unique identifier column it fails with "type mismatch or invalid character for the specified code page".
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help! private void PopulateControls() { DataTable table = CartAccess.getCart(); } public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param); DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; } public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
I'd like to return the following result set: CompanyModules.CompanyID | Modules.Name | Present 1 | A | True 1 | B | True 1 | C | True 2 | A | True 2 | B | False 2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present 1 | A | 1 1 | B | 1 1 | C | 1 2 | A | 1
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
