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.
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?
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
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 Dim GiftOcc_ID As Guid da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1) Catch ex As Exception
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)
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?
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".
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
The query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade?
--PROPERTYITEM INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId]) SELECT Property.propertyId, ITEM.ItemID FROM ITEM RIGHT OUTER JOIN miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN Property ON property.adprintid = miamiherald.adprintid WHERE validAD=1
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work... Thanks! Dim objConn3 As SqlConnectionDim mySettings3 As New NameValueCollectionmySettings3 = AppSettingsDim strConn3 As StringstrConn3 = mySettings3("connString")objConn3 = New SqlConnection(strConn3)Dim strInsertPatient As StringDim cmdInsert As SqlCommandDim strddlSex As StringDim strddlPatientState As StringDim rowsAffected As Integer strddlSex = ddlSex.SelectedItem.TextstrddlPatientState = ddlPatientState.SelectedItem.TextstrInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF" cmdInsert = New SqlCommand(strInsertPatient, objConn3) cmdInsert.Parameters.Add("@UserID", "Joe For Now")cmdInsert.Parameters.Add("@Accession", Accession.Text)cmdInsert.Parameters.Add("@LastName", LastName.Text)cmdInsert.Parameters.Add("@MI", MI.Text)cmdInsert.Parameters.Add("@FirstName", FirstName.Text)cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)cmdInsert.Parameters.Add("@ddlSex", strddlSex)cmdInsert.Parameters.Add("@DOB", DOB.Text)cmdInsert.Parameters.Add("@Address1", Address1.Text)cmdInsert.Parameters.Add("@Address2", Address2.Text)cmdInsert.Parameters.Add("@City", City.Text)cmdInsert.Parameters.Add("@Suite", Suite.Text)cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)cmdInsert.Parameters.Add("@ZIP", zip.Text)cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text) objConn3.Open()cmdInsert.ExecuteNonQuery()objConn3.Close()
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
Hi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value. I was going down the road of something like: addnew as sqldatasource = new sqldatasource addnew.insertcommand = "Insert into....; def @NewID as scope_identity" addnew.insert()
The problem is I don't know how to add a output parameter using VB or how to retrieve it. Any help would be much appreciated, this is doing my head in.... Doug.
HI y'all, I have a problem in my query I hope you dudes can help. Ya see I have this query:SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer And this is the resultsCustId CustName CustAddress CreditLimit dateStart DateUpdated-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------CS0201 Reena Mayfield Str. 300000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000 CS0202 Bryant Bringfield 202 50000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000CS0203 Jack Marjory 22 3000000.000 2006-08-01 00:00:00.000 2007-05-18 17:30:23.000CS0204 Alan Orchard 25 4500000.000 2006-10-02 00:00:00.000 2006-08-01 00:00:00.000Then I add Where clause to the query, like this SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer WHERE DateUpdated = '08/01/2006'And the result is just as I expected:CustId CustName CustAddress CreditLimit dateStart DateUpdated -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------CS0201 Reena Mayfield Str. 300000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000 CS0202 Bryant Bringfield 202 50000.000 2006-08-01 00:00:00.000 2006-08-01 00:00:00.000CS0204 Alan Orchard 25 4500000.000 2006-10-02 00:00:00.000 2006-08-01 00:00:00.000But when I changed the date parameter to 05/18/2007 for example like this SELECT CustId,CustName,CustAddress,CreditLimit,dateStart,DateUpdated From AR_Customer WHERE DateUpdated = '05/18/2007'It doesn't show any results at all, it just showing the header CustId CustName CustAddress CreditLimit dateStart DateUpdated -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------What I want to ask is why is that happen, is it because the date contains hours--according to the database is the date contain hours like this 2007-05-18 17:30:23.000. I already use this operator <=,>= but it just showing the wrong result. Can you guys tell me the correct query, please. I appreciate any help. Thanks. FYI: I use SQL Server 2000Best Regards.
Hi, first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff. I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly. Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label? Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!
I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to.
As such, when I try to query the syslogins table I only get the NT group.
Is there any way of retrieving the users that belong to this NT group.
Any help or suggestions on this would be greatly appreciated.
I get 100 rows of result, i want to write a sql query similar to the one available in MySql database where in i can specify the starting row and number of rows of records i want,
something like,
select * from emp LIMIT 10,20
I want all the records from the 10th row to the 20th row.
This would be helpful for me to do paging in the front end , where is i can navigate to the next previous buttons and fetch the corresponding records.
I want to do something like in google, previous next screens.
So I am trying to limit the number of rows fetched from a query.
somethin like,
select * from emp where startRowNum=10 and NoOfRecords = 20
( select min(cast(f.valuestr as float)) from bvfindings f where f.ObjectName = 'Hematocrit' and f.sessionid = s.sessionid and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)), f.valuetime from bvfindings f where f.ObjectName = 'Hb'
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
we are currently using id's as primary key and replication is not part of our project. will this be a problem if we decide to do replication? will microsoft generate an identifier then. what is the advantages of using GUID now or doing it latter?
I'm working on a smart client app that has an offline sql express store and needs to work with several types of central databases (support for multiple products - ms sql, DB2 etc)
While trying to put together some offline functionality that needs the user to create records on the offline sql express data store, we've run into the need of being able to uniquely identify records so replicating the data in the offline store back into the primary database should not be a problem.
The data created offline spans many tables and involves several tables with relation ships - FK etc...Clearly not a simple case of store and forward.
We dont want to get into the mess of performing key replacement during a synch job with the server. Thats way too much trouble.
GUID seems like a good choice, but as always we have several stake holders having different opinions. And with databases other than MS SQL we will have to store them as strings.
To cut to the chase - can we not hash a GUID to get an integer while retaining atleast the same likelyhood of producing unique ids ? [no drop]
I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled) Can anyone tell me whether it is possible and How? I am currently using the session state to store it in and then using the session=... to get the value into the parameter. Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource? Thanks in advance.
Hi Everyone, I'm trying to create a SQL Delete statement using a string builder and the WHERE clause uses a Guid. Here is the code:stb.Append("DELETE FROM UserRights WHERE UserIDPtr = "); stb.Append(TargetUserID);The resulting string is: "DELETE FROM UserRights WHERE UserIDPtr = e01549fb-edf5-4668-de8b-b13dd5661a6e" When I try to do an ExecuteNonQuery() using the string as the CommandText, I get an error. Invalid column name 'e01549fb',Invalid column name 'edf5',Invalid column name 'de8b',Invalid column name 'b13dd5661a6e' It is also strange that '4668' did not show up as an invalid column name, but I don't think that is relavent to this issue. Can someone show me (or point me to an article) about using Guid's in a text string as a SQL command? Thanks in advance!
My database is using the membership store for all the user information. I added a tabel "Skills" with 3 fields "SkillID (GUID)" "SeekerID (GUID, This is the UserId)" and "SkillName ( Varchar(MAX))" on the page i have a ListView setup to display the SkillName fields based on the SeekerID. The original code was Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ' Get a reference to the currently logged on user Dim CurrentUser As MembershipUser = Membership.GetUser ' Determine the currently logged on user's UserId value Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) End Subthat kept returning string to GUID conversion errors, so i had to change Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) to Dim SeekerID As Object = CType(CurrentUser.ProviderUserKey.ToString(), Object) that appears to be working now. On the same page however, I want to insert records into the table, I tried 2 options both of which have a different problem. Option 1: Use a textbox (ID = NewSkill) and a button with the following code on it: Protected Sub SetNewSkill_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As MembershipUser = Membership.GetUser Dim SeekerGUID As Object = CType(CurrentUser.ProviderUserKey, Object)
Dim NewSkill As TextBox = CType(FindControl("NewSkill"), TextBox) Dim connectionString As String = ConfigurationManager.ConnectionStrings("QJSdatabase").ConnectionString() Dim insertSql As String = "INSERT INTO Skills(SeekerID, SkillName)VALUES(@SeekerGUID, @NewSkill)" Using myConnection As New SqlConnection(connectionString) myConnection.Open() Dim myCommand As New SqlCommand(insertSql, myConnection) myCommand.Parameters.AddWithValue("@SeekerGUID", SeekerGUID) myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) myCommand.ExecuteNonQuery() myConnection.Close() End Using End Sub
Problem: myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) gets outlined and returns the error: Object reference not set to an instance of an object
Option 2: Use a DetailsView linked to a SQLDataSource on the page <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="210px" AutoGenerateRows="False" DataSourceID="SqlDataSource2" DefaultMode="Insert"> <Fields> <asp:BoundField DataField="SkillName" HeaderText="Add Skill:" SortExpression="SkillName" /> <asp:CommandField ShowInsertButton="True" /> <asp:TemplateField InsertVisible="False"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server"></asp:Label> </ItemTemplate> </asp:TemplateField> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:database %>" DeleteCommand="DELETE FROM [Skills] WHERE [SkillID] = @SkillID" InsertCommand="INSERT INTO [Skills] ([SeekerID], [SkillName]) VALUES (@SeekerGUID, @SkillName)" SelectCommand="SELECT SkillName FROM [Skills]" UpdateCommand="UPDATE [Skills] SET [SkillName] = @SkillName WHERE [SkillID] = @SkillID"> <DeleteParameters> <asp:Parameter Name="SkillID" Type="Object" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="SeekerGUID" Type="Object" /> <asp:Parameter Name="SkillName" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="SkillName" Type="String" /> <asp:Parameter Name="SkillID" Type="Object" /> </UpdateParameters> </asp:SqlDataSource>
http://junk.icore-studios.com/junk/Codeissues/postresumeerror.html The @SeekerGUID is being generated by the same pageload code as the first chunk i gave (I have 2 variables SeekerID is the GUID converted to string to work with the ListView filter and SeekerGUID is the GUID)
Ultimatly getting either option to work would be fine. Though I think the second would be preferable because I think it'd be easier to replicate later on. Thanks in advance for your time and any help
I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.
CREATE PROCEDURE spScanUpdate @id varchar (100), @name varchar (75) AS DECLARE @GUID_ID as uniqueidentifier SELECT@GUID_ID = CAST ( @id as uniqueidentifier )
UPDATE tScan SET name = @name WHEREid = @GUID_ID GO
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)
