Resolved Getting primary binary key?

[Vorort]

Hello there,
I've to get a primary key from ms sql database table. Unfortunate th keys are declared as binary length 16. My query recognizes the field as system.binary and I don't know how to use this field further to insert data or modify another table which is linked using this key. Does anyone know?
Thanx

 

[jmcilhinney]

My query recognizes the field as system.binary

I've never heard of such a thing and I just searched the .NET documentation and got no matches. A varbinary column maps to a Byte array in VB so I would expect a binary column to do the same. Is that actually what's happening?

Regardless, why do you have to do anything? If there is a foreign key in another table related to that column then presumably it is the same data type so why can't you just use the value you get out of the parent table as it is, just as you would do for any other data type? Either you're trying to solve a problem that doesn;t exist or you haven't explained the problem adequately.

 

[Vorort]

I've never heard of such a thing and I just searched the .NET documentation and got no matches. A varbinary column maps to a Byte array in VB so I would expect a binary column to do the same. Is that actually what's happening?

Regardless, why do you have to do anything? If there is a foreign key in another table related to that column then presumably it is the same data type so why can't you just use the value you get out of the parent table as it is, just as you would do for any other data type? Either you're trying to solve a problem that doesn;t exist or you haven't explained the problem adequately.

Sorry i wasn't clear enough. The field I'm trying to read is a guid. When I read the field tape in vb (like idx = reader.GetDataTypeName(0)) it returns binary. Then when I read the contents of that field like (retval = reader.GetSqlBinary(0).ToSqlGuid) the returned value diffiers from the value in the table I'm querying. e.g. in SQL the value is '0xF811D3C1BD804D60A4304E1CD341FE1A' the returned value is 'c1d311f8-80bd-604d-a430-4e1cd341fe1a'

 

[jmcilhinney]

If you are using the uniqueidentifier data type in SQL server then that will map to the Guid data type in VB. If you call GetGuid on the data reader then it will return a Guid value. You can then assign that wherever you need to when saving it to a new record. If you need to specify a SqlDbType value, e.g. when creating a SqlParameter, use UniqueIdentifier.

 

[Vorort]

If you are using the uniqueidentifier data type in SQL server then that will map to the Guid data type in VB. If you call GetGuid on the data reader then it will return a Guid value. You can then assign that wherever you need to when saving it to a new record. If you need to specify a SqlDbType value, e.g. when creating a SqlParameter, use UniqueIdentifier.

Thank you for your reply. I tried to do so but when I want to update a record with the returned guid it will fail because the returned guid is not existing in the table

 

[jmcilhinney]

Thank you for your reply. I tried to do so but when I want to update a record with the returned guid it will fail because the returned guid is not existing in the table

I must have missed where you explained that and showed us what you actually did. A FULL and CLEAR explanation of the problem includes ALL the relevant information.

 

[Vorort]

I must have missed where you explained that and showed us what you actually did. A FULL and CLEAR explanation of the problem includes ALL the relevant information.

Sorry, I'm a newbie

 

[jmcilhinney]

Whatever you did, you obviously did it wrong. You need to show us what you did so that we can see what's wrong with it. I just tested for myself and it worked exactly as I explained, so obviously didn't do as I explained. We shouldn't have to guess what you did.

 

[jmcilhinney]

I just created a new SQL Server database named Test. I created tables like this:

CREATE TABLE [dbo].[Parent](
    [ParentId] [uniqueidentifier] NOT NULL,
    [ParentName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
    [ParentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

and this:

CREATE TABLE [dbo].[Child](
    [ChildId] [uniqueidentifier] NOT NULL,
    [ParentId] [uniqueidentifier] NOT NULL,
    [ChildName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
    [ChildId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Parent] ([ParentId])
GO

ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]
GO

I parent data like this:

insert into Parent values (NEWID(), 'Parent1')
insert into Parent values (NEWID(), 'Parent2')

I then executed this code:

Using connection As New SqlConnection("Data Source=(local);Initial Catalog=Test;Integrated Security=True"),
      parentCommand As New SqlCommand("SELECT * FROM Parent", connection),
      childAdapter As New SqlDataAdapter("SELECT * FROM Child", connection)
    connection.Open()

    Dim childTable As New DataTable

    childAdapter.FillSchema(childTable, SchemaType.Source)

    Using parentReader = parentCommand.ExecuteReader()
        Dim childNumber = 1

        While parentReader.Read()
            childTable.Rows.Add(Guid.NewGuid(),
                                parentReader.GetGuid(parentReader.GetOrdinal("ParentId")),
                                $"Child{childNumber}")
            childNumber += 1
        End While
    End Using

    Dim childBuilder As New SqlCommandBuilder(childAdapter)

    childAdapter.Update(childTable)
End Using

and it worked exactly as I said it would.

 

[Vorort]

I'll try to explain what I happens: in the table on the server is a primary key declared gguid binary(16) From this table I need to get some information and then alter another table linked to this table using the primary key from the first. When I select one row using a keyword and reading the primary key (which I need to have to alter another table) I get this key: 'c1d311f8-80bd-604d-a430-4e1cd341fe1a' ! On the sqlserver executing the same query I get as PK: '0xF811D3C1BD804D60A4304E1CD341FE1A' Because my prog only knows this key: 'c1d311f8-80bd-604d-a430-4e1cd341fe1a' I'm not able to alter the other table because this key is unknown on the sql server. See my problem?

 

[Vorort]

I resolved the problem by: SELECT CONVERT(VARCHAR(max), GGUID ) FROM dbo.xxxxx WHERE KEYWORD LIKE 'Avast'
Now I get the right guid .
Thanx for reading and / or replieing
Cheers