Guid Vs Identity

Jul 20, 2005

Hi,

Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?

Has anyone tested what's faster, working with Guid or Autonumber ?

View 24 Replies


ADVERTISEMENT

Major Problem With @@IDENTITY, Treeview And GUID

Nov 14, 2004

Hi,

I'm trying to insert data into locally stored database (SQL Server).
The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset.
The treeview contains checkboxes allowing a user to select what to install in the locally stored database.

To sum up:


1. Get data from a webservice' not my problem
2. Present data in a Treview control' not my problem
3. Allow to user to select which data to install' not my problem
4. Insert data that the user has selected into my db' MY PROBLEM!!!!


The Treeview looks like this.

- Group1
| | ---- Rule1.1
| | ---- Rule1.2
|
- Group2
| | ---- Rule2.1
| | ---- Rule2.2
| | ---- Rule2.3

.....


The Treeview is generated with DataRelations between Group and Rule.

My locally stored database is designed by a third party provider and therefore the database must not be altered.
The table I want to store data in is called "Groups" and it looks like this:


GroupID uniqueidentifier ' (newid())
GroupName nvarchar(50)
ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value.
GroupType tinyint ' 0 = subgroup, 1 = "top"group

Data in the table "Groups" would look like this:

GroupID GroupNameParentGroupIDGroupType
---------------------------------------------------------
{000001...}Group1<NULL>1
{000011...}Rule1.1{000001...}0
{000012...}Rule1.2{000001...}0
{000002...}Group2<NULL>1
{000021...}Rule2.1{000002...}0
{000022...}Rule2.2{000002...}0
{000023...}Rule2.3{000002...}0



The third party also created a stored procedure called pr_AddGroup taking the following parameters:

@GroupName ' can be both the RuleName and the GroupName
@GroupType ' can be 0 for subgroup or 1 for "top"group
@ParentGroup ' GUID


The problem with this stored procedure is that it does not have return value, which is here my problem actually lies.
If it returned @@IDENTITY I could use this as the parameter for @ParentGroup.
Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @@IDENTITY to get the newly created record).

My SQL Commands look like this

Dim cmd As SqlCommand
Dim Conn As SqlConnection = New SqlConnection
Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI"
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = Conn
cmd.CommandText = "pr_AddGroup"

cmd.Parameters.Add(New SqlParameter("@GroupName", SqlDbType.NVarChar, 50, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@GroupType", SqlDbType.TinyInt, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@ParentGroup", SqlDbType.UniqueIdentifier, ParameterDirection.Input))

dim cmd2 as SqlCommand
cmd2 = new SqlCommand
cmd2.commandtype = commandtype.Text
cmd2.commandtext = "SELECT @@IDENTITY as ID FROM Groups"
cmd2.connection = Conn

dim ParentGroupGUID as system.guid


To get the data inserted in the Groups table I would something like the following, but the code is very ugly
(and it doesn't work either);

For Each Group In TreeView1.Nodes ' Loop through Groups
If Group.Checked Then
cmd.Parameters("@GroupName").Value = Group.Text.ToString
cmd.Parameters("@GroupType").Value = 1

cmd.ExecuteNonQuery()
ParentGroupGUID = cmd2.executescalar()

End If

For Each Rule In Group.Nodes ' Loop through Rules.
If Rule.Checked Then
cmd.Parameters("@GroupName").Value = Group.Text.ToString
cmd.Parameters("@GroupType").Value = 1
cmd.Parameters("@ParentGroup").value = ParentGroupGUID
cmd.ExecuteNonquery()
End If
Next
Next


I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)

View 3 Replies View Related

How To Retrieve The GUID Value Of A SQL NewID() Identity Column After An Insert ?

Jan 10, 2006

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

View 1 Replies View Related

GUID As Primay Column And Identity Column

Jan 9, 2007

Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK  or it will bring some problems?
Thank you in advance for your help
 

View 3 Replies View Related

Populate GUID Column In Table B With Values From GUID Column In Table A

Mar 4, 2008


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

View 4 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?



TIA,



Barkingdog

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?



TIA,



barkingdog



P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View 12 Replies View Related

GUID&#39;s

Feb 1, 2001

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?

View 1 Replies View Related

GUID

May 26, 2006

Hello,

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]

Thanks,

Aviansh



View 13 Replies View Related

Profile GUID

Apr 13, 2007

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.

View 3 Replies View Related

How To Specify A Guid In A WHERE Clause

Sep 25, 2007

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!

View 2 Replies View Related

GUID Issues

May 5, 2008

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

View 3 Replies View Related

GUID Troubles.

Apr 17, 2004

Hi,

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

View 5 Replies View Related

GUID Vs Integer

Apr 17, 2006

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)

View 6 Replies View Related

A GUID Question

Sep 26, 2007

does SQL Server have the equivalent of the Identity or Scope_Identity functions that can be used to return the last GUID PK?

View 5 Replies View Related

GUID Performance

Apr 11, 2008

I have a database with tables with GUID's als primary keys.

Because of the uniqueness GUID's are a performance hit on INSERT records.

So I wanted to use NEWSQUENTIALID() to make INSERTS faster.

Does NEWSQUENTIALID() increase performance as well with SELECT statements? Or is the benefit between NEWID() and NEWSQUENTIALID() only when INSERTing records?



Henri
~~~~
There's no place like 127.0.0.1

View 3 Replies View Related

Loving GUID's

Apr 29, 2008

I'm reluctant to admit I'm in love with uniqueidentifiers AKA guid's.

However, this love comes with a cost because of the size and thus SQL Cache and thus IO reads (and fragmented index pages and so on)

My love has to do with generic programming and keeping multiple database updateable (adding core functionality with the flexibility to let users add and change data in tables that have core data in it as well). Also merging databases is easy or relate different entities by storing a metadata link table.

So, what I'm trying to do is making the disadvantages as small as possible.

So I'm using NEWSEQUENTIAL Id's and retrieve new made records with OUTPUT as TABLE.

So I changed the default CLUSTERED INDEX as NON-CLUSTERED.

I'm not fond on usings numerics for some primary keys and uniqueidentifiers for others.

What else can I do to keep using GUID's as primary keys with as less disadvantages as possible?

Henri
~~~~
There's no place like 127.0.0.1

View 13 Replies View Related

GUID As Primary Key

Mar 13, 2008

I have a table profiles and a table resumes.
The id's are currently int indentity fields.
I was thinking of using a GUID as ID.

Why would i do this ?

In my webapplication I have i.e. this page
DeleteProfile.aspx?profileId=1

I don't want to give the user the ability to change this to f.e.
DeleteProfile.aspx?profileId=3

A user will almost possibly never guess a certain GUID, right ?

Is this a good idea, or not ?

View 6 Replies View Related

Is A GUID Always Unique?

Jul 20, 2005

Greetings All, I have read many upon many articles here regarding GUIDdata types and uniqueness. There have been many opinions regardingthe effectiveness of GUID's and when they should/should not be used.However, every article strongly implies, if it does not state itoutright, that GUID's are always unique. My question is this, whathappens if you have a database that uses GUID's and the NIC is changedout on the box? From what I understand the MAC address of the NIC isused as part of the algorithm to generate a GUID. If you change outthe NIC after generating 1 billion GUID's do you run the chance ofgenerating a duplicate GUID?I look forward to your insightfulness on this issue.Regards, Louis.

View 4 Replies View Related

How To Get The SQLEXPRESS GUID ?

Feb 26, 2007

Hi, please can someone be so kind and tell me the fastet way to get the GUID of the running SQLExpress instance ?

Sincerely,gonzo883.

View 5 Replies View Related

Component GUID

Apr 28, 2008

Exists any easy way to renew component GUID without having to recreate the component?

View 6 Replies View Related

GUID In Mobile SQL

Mar 12, 2006

I have been trying to setup the GUID new GUID when I add a row into a Pocket PC WM 5.0 Mobile SQL (*.sdf) utilizing C# through Visual Studio 2005 - targetting Pocket PC Smart Device

Any help would be most welcome...

Thanks

View 7 Replies View Related

GUID Question

Sep 17, 2006

I am reading a book and it talks a little bit about using GUIDs as primary keys. I am very confused about this because if this is a generated value per table, how could you use it as a foreign key in another table as it appears to be uniquely generated per table. Here is the code in the book which I don't understand. Can someone explain this in simple terms?

I know the identity column would be a problem if I had a multi-terabyte database with huge tables, so what do I do in this situation?

create table dbo.product(

productid uniqueidentifier not null

rowguidcol default (newid())

primary key nonclustered

etc

thx,

Kat

View 5 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

Return GUID On Insert

Oct 24, 2007

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

View 8 Replies View Related

Getting GUID Value From StoredProcedure.ExecuteScalar()

Jun 12, 2008

I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure:   getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString()            If getGroupID.ExecuteScalar() = Nothing Then                'Find Group by IP address if input Data Table doesn't have group                getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString()                If getGroupIDByIP.ExecuteScalar() = Nothing Then                    insertGroup.Parameters("@GroupID").Value = Guid.NewGuid                    insertGroup.Parameters("@Group").Value = dr.Item("Group ID")                    insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar()                    insertGroup.ExecuteNonQuery()                    command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()                Else                    command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar()                End If            Else                command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar()            End If Thank you 

View 2 Replies View Related

How Can I Verify A GUID Number ?

May 13, 2004

Dear SQL,

I need to SELECT something from a database that has a UNIQUEIDENTIFIER (GUID) field,

If the number is wrong (has some other than A-Z 0-9) than the ASP page just freaks out and gets "error converting from a character string to uniqueidentifier"

How can I check that the GUID is OK before I SELECT ?
this is the number format:
{7A9B5F81-4936-4A31-B4E2-9168AAB75A0}

I tried to cast this "error" number with no successs:
"WHERE Deceased_ID = cast('"& "---------4936-4A31-B4E2-9168AAB75A0" &"' as uniqueidentifier)"


Thanks in advance, Yovav.

View 5 Replies View Related

Inserting Into A Table With Guid As Key

May 23, 2004

I am having a terrible time doing an insert.... dispite what the error message says, I think it has something to do with trying to insert a GUID (my primary key).... but I am probably wrong :-)

Any ideas


The error message


Server Error in '/LightQuote' Application.
--------------------------------------------------------------------------------

Prepared statement '(@User_ID uniqueidentifier,@User_Type int,@Status int,@UserName ' expects parameter @UserName, which was not supplied.
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: Prepared statement '(@User_ID uniqueidentifier,@User_Type int,@Status int,@UserName ' expects parameter @UserName, which was not supplied.

Source Error:


Line 195: dbConnection.Open()
Line 196: Try
Line 197: rowsAffected = dbCommand.ExecuteNonQuery
Line 198: Finally
Line 199: dbConnection.Close()



The Insert Function

Public Function InsertOneUser( _
ByVal user_ID As System.Guid, _
ByVal user_Type As Integer, _
ByVal status As Integer, _
ByVal userName As String, _
ByVal password As String, _
ByVal secret_Question As Integer, _
ByVal secret_Answer As String, _
ByVal company As String, _
ByVal company_Type As Integer, _
ByVal first_Name As String, _
ByVal last_Name As String, _
ByVal address1 As String, _
ByVal address2 As String, _
ByVal city As String, _
ByVal state As String, _
ByVal zip As String, _
ByVal country As String, _
ByVal day_Phone As String, _
ByVal night_Phone As String, _
ByVal fax As String, _
ByVal email As String, _
ByVal web_Site As String) As Integer
Dim myConnection As New Connection
Dim connectionString As String = myConnection.ConnString
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [tblUser] ([User_ID], [User_Type], [Status], [UserName], [Password], " & _
"[Secret_Question], [Secret_Answer], [Company], [Company_Type], [First_Name], [L" & _
"ast_Name], [Address1], [Address2], [City], [State], [Zip], [Country], [Day_Phone" & _
"], [Night_Phone], [Fax], [Email], [Web_Site]) VALUES (@User_ID, @User_Type, @Sta" & _
"tus, @UserName, @Password, @Secret_Question, @Secret_Answer, @Company, @Company" & _
"_Type, @First_Name, @Last_Name, @Address1, @Address2, @City, @State, @Zip, @Coun" & _
"try, @Day_Phone, @Night_Phone, @Fax, @Email, @Web_Site)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_user_ID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_user_ID.ParameterName = "@User_ID"
dbParam_user_ID.Value = user_ID
dbParam_user_ID.DbType = System.Data.DbType.Guid
dbCommand.Parameters.Add(dbParam_user_ID)
Dim dbParam_user_Type As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_user_Type.ParameterName = "@User_Type"
dbParam_user_Type.Value = user_Type
dbParam_user_Type.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_user_Type)
Dim dbParam_status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_status.ParameterName = "@Status"
dbParam_status.Value = status
dbParam_status.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_status)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_password As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_password.ParameterName = "@Password"
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_password)
Dim dbParam_secret_Question As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_secret_Question.ParameterName = "@Secret_Question"
dbParam_secret_Question.Value = secret_Question
dbParam_secret_Question.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_secret_Question)
Dim dbParam_secret_Answer As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_secret_Answer.ParameterName = "@Secret_Answer"
dbParam_secret_Answer.Value = secret_Answer
dbParam_secret_Answer.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_secret_Answer)
Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_company.ParameterName = "@Company"
dbParam_company.Value = company
dbParam_company.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_company)
Dim dbParam_company_Type As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_company_Type.ParameterName = "@Company_Type"
dbParam_company_Type.Value = company_Type
dbParam_company_Type.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_company_Type)
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)
Dim dbParam_last_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_last_Name.ParameterName = "@Last_Name"
dbParam_last_Name.Value = last_Name
dbParam_last_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_last_Name)
Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address1.ParameterName = "@Address1"
dbParam_address1.Value = address1
dbParam_address1.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_address1)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address2.ParameterName = "@Address2"
dbParam_address2.Value = address2
dbParam_address2.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_city.ParameterName = "@City"
dbParam_city.Value = city
dbParam_city.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_city)
Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_state.ParameterName = "@State"
dbParam_state.Value = state
dbParam_state.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_state)
Dim dbParam_zip As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_zip.ParameterName = "@Zip"
dbParam_zip.Value = zip
dbParam_zip.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_zip)
Dim dbParam_country As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_country.ParameterName = "@Country"
dbParam_country.Value = country
dbParam_country.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_country)
Dim dbParam_day_Phone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_day_Phone.ParameterName = "@Day_Phone"
dbParam_day_Phone.Value = day_Phone
dbParam_day_Phone.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_day_Phone)
Dim dbParam_night_Phone As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_night_Phone.ParameterName = "@Night_Phone"
dbParam_night_Phone.Value = night_Phone
dbParam_night_Phone.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_night_Phone)
Dim dbParam_fax As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_fax.ParameterName = "@Fax"
dbParam_fax.Value = fax
dbParam_fax.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_fax)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_email)
Dim dbParam_web_Site As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_web_Site.ParameterName = "@Web_Site"
dbParam_web_Site.Value = web_Site
dbParam_web_Site.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_web_Site)

Dim rowsAffected As Integer = 0
dbConnection.Open()
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close()
End Try

Return rowsAffected
End Function


The Sub that calls the Insert function



Public Sub Add_New_User()
m_User_ID = New Guid 'm_User_ID is the private field for the public User_ID Property
myData.InsertOneUser( _
User_ID, _
User_Type, _
Status, _
UserName, _
Password, _
Secret_Question, _
Secret_Answer, _
Company, _
Company_Type, _
First_Name, _
Last_Name, _
Address1, _
Address2, _
City, _
State, _
Zip, _
Country, _
Day_Phone, _
Night_Phone, _
Fax, _
Email, _
Web_Site)

End Sub

View 2 Replies View Related

Clustered Index On GUID

Dec 5, 2005

I have a 3rd party app which creates tables for developers. It uses a guid as the unique key which is indexed. However none of these indexes are clustered. I have hear both good and bad about clustering on a GUID. i can't change this design other than to make these indexes clustered. Any opinions?

View 2 Replies View Related

Inserting GUID Data

Jun 7, 2002

Hi everybody
I have a table named GrpMembership which has columns ParticipantId and InterestGroupId that have datatypes uniqueidentifier.When I try to run the following insert stmt:
insert into grpmembership
(guparticipantid,guInterestGroupId,bIsAdmin,dtCrea ted)
values
({EE0BC83E-01DD-4B0D-BFAA-34B508C28CF3}
{D149BD88-55B6-4EB9-AA81-1E1B5141EFA6},
0,6/7/2002)

it gives error:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Please Help

Regards
Jignesh

View 1 Replies View Related

GUID&#39;s Req&#39;d For Transactional Replication??

Aug 22, 2002

Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?

I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?

thx -
~BT

View 1 Replies View Related

GUID And Curly Braces

Jun 19, 2001

If I return a GUID as an output parameter in an ASP page, it has curly braces around it. When I query the table in QA, there are no braces around it. Why is that.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved