As you know we can get the last identity by using these functions:@@IDENTITY, IDENT_CURRENT( '[Table_Name]' ) And More.But if I set my primary key datatype to uniqueidentifier, I Can't get the last GUID inserted by SQL.Anyone have any idea?Reach me at my blog: http://ramezanpour.spaces.live.com
View 6 RepliesHello there!
it was a while since i studied SQL and that brings us to my problem...
I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().
later in the SP i need that uniqueidentifier value? how do I get it?
I tried this:
CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier = newid,
@strHeader nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400),
@strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt
Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho
rEmail,@strKeywords)
declare @uidCategoryId uniqueidentifier
EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews
@strHeader = 'Detta är den andra nyheten',
@strAbstract = 'dn första insatt med sp:n',
@strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@dtDate = '2003-01-01',
@dtDateStart = '2003-01-01',
@dtDateStop = '2004-01-01',
@strAuthor = 'David N',
@strAuthorEmail = 'david@davi.com',
@strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem?
Can I use something similar to the @@IDENTITY?
I will be greatful for any ideas...
thanks
/David, Sweden
Hello there!
it was a while since i studied SQL and that brings us to my problem...
I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().
later in the SP i need that uniqueidentifier value? how do I get it?
I tried this:
CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier = newid,
@strHeader nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400),
@strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho rEmail,@strKeywords)
declare @uidCategoryId uniqueidentifier
EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews
@strHeader = 'Detta är den andra nyheten',
@strAbstract = 'dn första insatt med sp:n',
@strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@dtDate = '2003-01-01',
@dtDateStart = '2003-01-01',
@dtDateStop = '2004-01-01',
@strAuthor = 'David N',
@strAuthorEmail = 'david@davi.com',
@strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem?
Can I use something similar to the @@IDENTITY?
I will be greatful for any ideas...
thanks
/David, Sweden
Hi all,
I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
I have the following database structure:
The table aspnet_users, which we all know
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this:
string assigned is null, i am passing this as a method parameter
...............
string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
...................
System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter();
dbParam_au_pr.ParameterName = "@assigned";
if (assigned == null)
{
dbParam_au_pr.Value = null;
}
else
{
dbParam_au_pr.Value = assigned;
}
dbParam_au_pr.DbType = System.Data.DbType.String ;
dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all.
I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned
nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned,
which was not supplied.
Please help with this as soon as possible.Thanks
OK, I use asp.net membership and have created a new table that links together related users by use of the uniqueidentifier field as a foreign key. However, it is possible to link to a not-yet existent user (by their email) so that when that user creates an account and gets a guid, it will be updated into my table of related users and finalise the link. Trouble is, I'm trying to stop people adding the same "pending user" multiple times, so in my BLL logic I look for the entered email address for the current user and I want to check on the foreign key (the GUID field) so that I can tell the user that they've already added this user, but they haven't signed up yet (if they haven't got a foreign user key with a guid in it, they haven't signed up yet).
However, when I check the foreign key guid value (shows as null in sql enterprise manager) for null values, it never goes down the "true" logic channel... what I mean is that I've debugged it, hovered over the item I've read (it says it is system.dbnull, which it is) and yet when I do "isDBNull(myGUID.Value)" it never equates to true!
Why not?
I am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
View 5 Replies View RelatedI need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work
int Key = (int)command.ExecuteScalar();
where command is SqlCommand object.
It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
View 1 Replies View Relatedis there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies View RelatedI am really struggling. I am trying to query a sql database table using a uniqueidentifier.
Public Class SalesDataClass
Public Function getAccountNumber(ByVal ID As String) As String
Dim accountnumber As String = "0"
'Try
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("InterhealthCRM_MSCRMConnectionString").ConnectionString)
Using command As New SqlCommand("getAccountNumber", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameterdat1 As New SqlParameter("@accountid", SqlDbType.UniqueIdentifier)
parameterdat1.Value = ID
command.Parameters.Add(parameterdat1)
Dim parameterdat2 As New SqlParameter("@accountnum", SqlDbType.NVarChar, 20, ParameterDirection.Output)
command.Parameters.Add(parameterdat2)
connection.Open()
command.ExecuteNonQuery()
accountnumber = parameterdat2.Value
Return accountnumber
End Using
End Using
'Catch ex As SqlException
' Catch ex As InvalidOperationException
' Catch ex As Exception
' You might want to pass these errors
' back out to the caller.
' End Try
End Function
End Class
Can someone help me correct my code.
Sproc:
ALTER PROCEDURE [dbo].[getAccountNumber]
-- Add the parameters for the stored procedure here
@accountId uniqueidentifier,
@accountnum nvarchar(20) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT accountNumber from accountbase where accountid = @accountid
return @accountnum
END
Hi,
I am using UNIQUEIDENTIFIER column for my table. I do not insert data for it. I left it on database by setting to default NEWID(). Now in my application I need the value of UNIQUEIDENTIFIER column I just inserted. Is there any function or query to get this value like in case of IDENTITY column we can get the latest inserted value from select @@IDENTITY.
Thanks and regards,
Uday
Hey I was curious about the Uniqueidentifier is that better then using the @IDENTITY, apparently the Unique uses your computers mac address as a base???
View 4 Replies View Relatedas above, how can i use this datatype to generate a running number for my userID ? i tried with newid() but it returns a unique 32bit.
View 10 Replies View Related
does SQLCE supports the use of uniqueidentifier datatype.and how can i use it?
i have heard that SQLCE supports only integer type as identity column.
so what datatype i should use for identification(primary key).
I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Code:
CREATE TABLE DaOldTable(
pkCHAR(10)NOT NULL,
a_columnCHAR(10)
)
CREATE TABLE DaNewTable(
idINTNOT NULL IDENTITY,
[Code] ....
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code:
INSERT INTO DaNewTable(a_column)
--OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
SELECT a_column
FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable
DROP TABLE DaNewTable
DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
I have a table with id(uniqueidentifier) as the primary key, and another table with id(int) as the foreign key.
When I try to INNER JOIN them on id=id in a view, I get an error that uniqueidentifier and int are incompatible.
I'm new to SQL SERVER and I consider uniqueidentifier as equal to AutoNumber in MSAccess. Isn't it so? If not, how do I make this JOIN work?
Hello all,
I would like to put another line into my combo box using this SQL statement but this part "(select newid() as QuestionID, 'Select a Question' as QuestionText)" is not working. (select newid() as QuestionID, 'Select a Question as QuestionText) union all (SELECT * FROM (SELECT TOP 100 * FROM [dbo.aspnet_Questions]) as tbl)
RETURN
It gives me an error: Invalid object name 'dbo.aspnet_Questions'. Can anybody please help me with this error?
Thank you, Vic.
I just want to ask if there is a passible explaination for why this code thosen't generate the proper RETURN VALUE, My coal is when a user uses the asp:CreateUserWizard i retrive GUID from the new account.With this I will check if the relation tabel has that value, so I would like to run this.
Stored Proc.
CREATE PROCEDURE proc_CustomerCheckExist@UserId uniqueidentifierASIF EXISTS (SELECT COUNT(*) FROM dbo.aspnet_LD_Customers WHERE UserId = @UserId) Return 1ELSE Return 0GO
Problem is if I take 2 different GUID, I still get the same result "Return 1" as true even if I dont have the GUID in my tabel row
Thanks!
I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...
thx in adv
Hi,
I am writing a C# application that uses a SQL server database to hold its data. I need to create a stored procedure that returns a particular row's primary key value. This is no problem if the primary key is an INT. But my primary key is a unique identifier, and the stored procedure doesn't want to let me return any values that aren't INTs. Can someone please tell me how to get around this?
Thanks in advance.
Scott
I am trying to use a stored procedure and it seems to be giving me a error - I think it doesn't like the uniqueidentifier. I have included the error, stored procedure, and code behind that calls the stored procedure.Thanks for any help on this! This is the error I get:System.ArgumentException: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type. at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen) at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly() at System.Data.SqlClient.SqlParameter.Validate(Int32 index) at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at view_WardSec_PatientLogDetail2.UpdateRecord_buttonClick(Object sender, EventArgs e) in C:Documents and SettingsKBuchanan.LMHDesktopWebSitesNewest_ERViewsview_WardSec_PatientLogDetail.aspx.vb:line 358 Here is the stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[UpdateTblVisit_WardSecPatientLog] ( @VID decimal, @lbl_ChiefComplaint nvarchar(50), @TriageDtTmTextBox datetime, @PtInRmDtTmTextBox datetime, @RnInRmDtTmTextBox datetime, @PhyInRmDtTmTextBox datetime, @EDHoldDisposDtTmTextBox datetime, @DispDschDtTmTextBox datetime, @ddlTriageNurse uniqueidentifier, @ddlPriNurse uniqueidentifier, @ddlEDPhy uniqueidentifier, @ddlPriRefPhy decimal, @ddlSecRefPhy decimal, @ddlDschRN uniqueidentifier, @ddlDschPhy uniqueidentifier, @ddl_MnsArriv decimal, @lblDschDiag nvarchar(50), @chkbox_LogComplete bit )AS
SET NOCOUNT ON
Update tblVisit SET vChiefComplaint = @lbl_ChiefComplaint, vTriageDtTm = @TriageDtTmTextBox, vPtInRmDtTm = @PtInRmDtTmTextBox, vRnInRmDtTm = @RnInRmDtTmTextBox, vPhyInRmDtTm = @PhyInRmDtTmTextBox, vEDHoldDisposDtTm = @EDHoldDisposDtTmTextBox, vDispDschDtTm = @DispDschDtTmTextBox, vTriageRnID = @ddlTriageNurse, vRnID = @ddlPriNurse, vEdPhyProvID = @ddlEDPhy, vPriRefPhyProvID = @ddlPriRefPhy, vSecRefPhyProvID = @ddlSecRefPhy, vDschRnID = @ddlDschRN, vDschPhyID = @ddlDschPhy, vMnsArrvID = @ddl_MnsArriv, vDschDiag = @lblDschDiag, vLogCompletedInd = @chkbox_LogComplete, vLogLastEdit = getDate() WHERE VID = @VID SET NOCOUNT OFF RETURN
-----------------------------------------------------------------------------Following is the codebehind for a button oncommand event. This codeshould execute the stored procedure.-----------------------------------------------------------------------------
Public Sub UpdateRecord_buttonClick(ByVal sender As Object, ByVal e As System.EventArgs) 'Use this to update the CareGiverID into the tbl_Visit table. Dim sbSql As New System.Text.StringBuilder sbSql.Append("EXEC UpdateTblVisit_WardSecPatientLog ") sbSql.Append("@VID, ") sbSql.Append("@lbl_ChiefComplaint, ") sbSql.Append("@TriageDtTmTextBox, ") sbSql.Append("@PtInRmDtTmTextBox, ") sbSql.Append("@RnInRmDtTmTextBox, ") sbSql.Append("@PhyInRmDtTmTextBox, ") sbSql.Append("@EDHoldDisposDtTmTextBox, ") sbSql.Append("@DispDschDtTmTextBox, ") sbSql.Append("@ddlTriageNurse, ") sbSql.Append("@ddlPriNurse, ") sbSql.Append("@ddlEDPhy, ") sbSql.Append("@ddlPriRefPhy, ") sbSql.Append("@ddlSecRefPhy, ") sbSql.Append("@ddlDschRN, ") sbSql.Append("@ddlDschPhy, ") sbSql.Append("@ddl_MnsArriv, ") sbSql.Append("@lblDschDiag, ") sbSql.Append("@chkbox_LogComplete, ") sbSql.Append("@UserID ")
'Response.Write(sbSql.ToString) 'Response.End()
'Create variables for each of the edited values Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ERTrekker_ProdConnectionString1").ConnectionString) Dim cmd As New SqlCommand(sbSql.ToString(), con)
FindControls(DetailsView, "lbl_ChiefComplaint") Dim ilbl_ChiefComplaint As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "TriageDtTmTextBox") Dim iTriageDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "PtInRmDtTmTextBox") Dim iPtInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "RnInRmDtTmTextBox") Dim iRnInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "PhyInRmDtTmTextBox") Dim iPhyInRmDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "EDHoldDisposDtTmTextBox") Dim iEDHoldDisposDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "DispDschDtTmTextBox") Dim iDispDschDtTmTextBox As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "ddlTriageNurse") Dim iddlTriageNurse As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlPriNurse") Dim iddlPriNurse As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlEDPhy") Dim iddlEDPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlPriRefPhy") Dim iddlPriRefPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlDschRN") Dim iddlDschRN As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddlDschPhy") Dim iddlDschPhy As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "ddl_MnsArriv") Dim iddl_MnsArriv As DropDownList = CType(MyControl, DropDownList) FindControls(DetailsView, "lblDschDiag") Dim ilblDschDiag As TextBox = CType(MyControl, TextBox) FindControls(DetailsView, "chkbox_LogComplete") Dim ichkbox_LogComplete As CheckBox = CType(MyControl, CheckBox)
'Add all of the parameters to the command With cmd.Parameters .AddWithValue("@VID", Request("vID")) .AddWithValue("@lbl_ChiefComplaint", ilbl_ChiefComplaint.Text.ToString) .AddWithValue("@TriageDtTmTextBox", iTriageDtTmTextBox.Text.ToString) .AddWithValue("@PtInRmDtTmTextBox", iPtInRmDtTmTextBox) .AddWithValue("@RnInRmDtTmTextBox", iRnInRmDtTmTextBox.Text.ToString) .AddWithValue("@PhyInRmDtTmTextBox", iPhyInRmDtTmTextBox.Text.ToString) .AddWithValue("@EDHoldDisposDtTmTextBox", iEDHoldDisposDtTmTextBox.Text.ToString) .AddWithValue("@DispDschDtTmTextBox", iDispDschDtTmTextBox.Text.ToString) .AddWithValue("@ddlTriageNurse", iddlTriageNurse.SelectedValue.ToString) .AddWithValue("@ddlPriNurse", iddlPriNurse.SelectedValue.ToString) .AddWithValue("@ddlEDPhy", iddlEDPhy.SelectedValue.ToString) .AddWithValue("@ddlPriRefPhy", iddlPriRefPhy.SelectedValue.ToString) .AddWithValue("@ddlDschRN", iddlDschRN.SelectedValue.ToString) .AddWithValue("@ddlDschPhy", iddlDschPhy.SelectedValue.ToString) .AddWithValue("@ddl_MnsArriv", iddl_MnsArriv.SelectedValue.ToString) .AddWithValue("@lblDschDiag", ilblDschDiag.Text.ToString) .AddWithValue("@chkbox_LogComplete", ichkbox_LogComplete.Checked.ToString) .AddWithValue("@UserID", System.Web.HttpContext.Current.Session("UserID"))
End With
' Open the connection and execute the command Try con.Open() If cmd.ExecuteNonQuery() < 1 Then Throw New System.Exception("The record was not updated") End If Catch ex As Exception System.Web.HttpContext.Current.Response.Write(ex.ToString()) Finally If Not con Is Nothing AndAlso con.State = System.Data.ConnectionState.Open Then con.Close() End If End Try End Sub
Hello everyone.
I have 2 tables in MSSQL Server 2000, Products(idProduct, idProductType, name, quatity) and ProductsType(idProductType, name).
idXXXX are the primery keys and the data type is uniqueidentifier.
In table Products, the collum idProductType is a foreign key refering table ProductsType.
I can insert as many product types as i want without any problems.
Insert INTO ProductsType(name) VALUES('milk')
When inserting products i use the idProductType that was automatacly generated in last insert.
INSERT INTO Products (uuidProductType,name,quantity,)
VALUES ('AD9388A3-CA86-482D-B57F-6FA068E7D405','President Milk 50cl',500)
but i got the following error in SQL Query Analyzer:
Server: Msg 8169, Level 16, State 2, Line 1
Syntax error converting from a character string to uniqueidentifier.
I have searched the forum and i cant find any solution. i need to use uniqueidentifer data types because replication with PDA devices.
Any tips?
Thank you in advance.
Helder
Hey can you use the Uniqueidentifier as a primary key or no??
View 4 Replies View RelatedHi,
I use this procedure to add a record to the db (SQL 2005)
i constantly get this error:
Conversion failed when converting from a character string to uniqueidentifier.
This is the sp:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[AddBootloader]
@BootName nvarchar(50),
@Version nvarchar(50),
@CSD nvarchar(50),
@CreatorID uniqueidentifier,
@FilePath nvarchar(150),
@FileSize nvarchar(150)
AS
INSERT INTO dbo.EB_Bootloaders
([Bootname]
,[Version]
,[CSD]
,[FilePath]
,[FileSize]
,[CreatorID])
VALUES
(
@BootName,
@Version,
@CSD,
@CreatorID,
@FilePath,
@FileSize)
And this is the table:
USE [Ebdata]
GO
/****** Object: Table [dbo].[EB_Bootloaders] Script Date: 07/12/2006 10:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EB_Bootloaders](
[ID] [bigint] IDENTITY(1,3) NOT NULL,
[BootName] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Version] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CSD] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[FilePath] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,
[FileSize] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[CreateDate] [datetime] NULL CONSTRAINT [DF_EB_Bootloaders_CreateDate] DEFAULT (getdate()),
[CreatorID] [uniqueidentifier] NOT NULL,
[UpdateDate] [datetime] NULL,
[UpdateUser] [uniqueidentifier] NULL,
CONSTRAINT [PK_EB_Bootloaders_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Ebdata]
GO
ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users] FOREIGN KEY([CreatorID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[EB_Bootloaders] WITH NOCHECK ADD CONSTRAINT [FK_EB_Bootloaders_aspnet_Users1] FOREIGN KEY([UpdateUser])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION
The funny thing is that i copied the sp code from another which runs perfect,
I insert a Guid from an asp.net page.
Hope someone can help me here because im am stucked!
Cheers Wimmo
Hi:
I am using the built in securty database from vs2005
I am trying to write an insert trigger that will add a role each time a user is added but I seem to be having difficulty I believe it's with the uniqueidentifier datatype. when I run this trigger via an insert statement I get the following error.
Can anyone set me straight?
========error==============
Cannot insert the value NULL into column 'RoleId', table 'R:AAAPROJECTSASPVS2005MERCERBUCKSAPP_DATAASPNETDB.MDF.dbo.aspnet_UsersInRoles'; column does not allow nulls. INSERT fails.
==================insert statement==========
Declare @NewUserID as uniqueIdentifier
set @NewUserID=newID()
print Cast(@NewUserID as varchar(50))
insert into dbo.aspnet_Users (ApplicationID,UserID,UserName,LoweredUserName,LastActivityDate)
Values('f23f01f0-7ad7-463b-87e2-f3e9141e6426',@NewUserID,'lrchase','lrchase','7/20/2005')
======trigger============================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [InsertRole]
ON [dbo].[aspnet_Users]
AFTER INSERT
AS
DECLARE @RoleID uniqueidentifier
DECLARE @UserID uniqueidentifier
Select @UserID=Userid from inserted
Print @UserID
SELECT @RoleID = RoleID
FROM dbo.aspnet_Roles where dbo.aspnet_Roles.LoweredRoleName='user'
Print @RoleID
SET NOCOUNT ON;
Insert Into dbo.aspnet_UsersInRoles
Values(@UserID,@RoleID)
Thanks Len
hi. im new in using rowguid, i want to know how long can this datatype can hold. and is there a chance that value can be duplicate or the sql is checking it already before it create a new one.
and what will happen if i select yes in isrowguid.
thnx
Hi all,I'm trying to run a select where auniqueidentifier/GUID equals a hex, but I don't seem to be gettingmatches.For example, this query returns the expected record:select * from items where itemGUID ='{11111111-2222-3333-4444-555555555555}'But this one does not:select * from items where itemGUID = 0x11111111222233334444555555555555Any tips?thanks, -Scott
View 5 Replies View RelatedSo, what's the common preference out there for primary and/or surrogate keys? Uniqueidentifier or identity?
(No replication is involved.)
SQL2K SP4
Greetings. I just inherited doscovered a 3gb DB that is chewing up lots of CPU time on a pretty beefy server. This DB uses 4 main tables. It has many more, but most of the Inserts/ Updates/ Deletes/ and Joins are done mainly on 4 tables. Anyways, I figured out which sprocs use the most CPU between the hours of 7 and 7 every day, off to a good start. The first one is hardly every run. While it is a pig in terms of CPU usage, the amount of times it's run is somewhat minimal compared to others. So the next one is is a sproc that does auto refreshes, and is run every minute by thousands. I start to analyze it, particularly for missing indexes, but all is well. So I'm beating my head up against the wall and I realize, THE CLUSTERED PRIMARY KEY (PK) FOR THESE TABLES USE A GUID DATA TYPE....
So I have some questions about this data type, and also inserting into a clustered PK in general.
Are joins on this data type as bad as joining on varchar columns in terms of speed?
I don't see that any of my insert sprocs are taking a long time. So if an insert occurs, and a page split needs to happen (which is quite potential with this data type as a clustered PK), does it -- A) Begin the insert, complete the insert, then do a page split? Or does it -- B) Begin the insert, do the page split, then complete the insert?
If A, it would make sense that I would see no lag for my insert sprocs, but also see a high CPU usage (provided I have a high number of page splits, which is yet to be determined. If B, I would expect to see a lot of long running insert sprocs, which I don't.
convert(uniqueidentifier, 00000000-0000-0000-0000-000000000000)
not working.