Obter ID da "tabela mestre" para os demais inserts sem SP

Question

 

Gostaria de implementar outras instruções insert com o ID obtido na Table Pessoa (PE_ID) por meio do SCOPE_IDENTITY para que as demais inserts tenham como ID este valor obtido qdo insere na tabela Pessoa.

 

Terei mais inserts depois que precisam do mesmo PE_ID gerado no insert da tabela Pessoa.

 

Não sei se posso passar a transacao como parametro na ExecuteScalar, peço auxilio para ajustar/acertar este código:

 

 

'INSER€AO DOS DADOS EM PROCESSO DE MIGRA€ÇO PARA BASE SQL SERVER

 

Code Snippet

Dim SQL As String = "INSERT into Pessoa (PE_Nome, PE_Sobrenome, PE_Sexo, PE_Nascimento, PE_LocalNascimento, PE_EC_ID, PE_DtEstadoCivil, PE_Antigo) values (@Nome, @sobreNome, @Sexo, @Nascimento, @LocalNascimento, @EstCivID, @DtEstadoCivil, @Antigo);" + _

 

"INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (SCOPE_IDENTITY(), @PEN_PTE_ID, @EnderecoResid, @BairroResid, @CidadeResid, @UF_Resid, @CEP_Resid, @FoneResid, @FoneResid2, @E_mail1, @E_mail2);"

Dim cmd As New SqlCommand(Sql, connSQL)

 

If drAccess(16).HasValue() Then

 

 

Dim ID_Pessoa = Convert.ToInt32(cmd.ExecuteScalar(sqlTrans, SQL, cmd.Parameters))

 

 

Dim cmd_EnderecoComercial As New SqlCommand("INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (VALOR DO SCOPE_IDENTITY() DA PRIMEIRA TABELA PESSOA, @PEN_PTE_ID_COMERC, @EnderecoComerc, @BairroComerc, @CidadeComerc, @UF_Comerc, @CEP_Comerc, @FoneComerc, @FoneComerc2, @E_mail1_Comerc, @E_mail2_Comerc);", connSQL)

 

Dim PEN_PTE_ID_Comerc As Integer = 2

cmd_EnderecoComercial.Parameters.AddWithValue("@PEN_PTE_ID_COMERC", PEN_PTE_ID_Comerc)

 

cmd_EnderecoComercial.Parameters.AddWithValue("@EnderecoComerc", drAccess(16))

 

 

 

Aguardo ajuda.

Answer 1

Olá Magno,

 

Não sei se entendi bem sua dúvida, mas logo após inserir o registro, você pode obter o SCOPE_IDENTITY, armazenar em uma variável qualquer no .NET, e informá-lo nos próximos INSERTs de tabelas "filhas". Algo como:

 

Code Snippet

 

 'Faz o INSERT na tabela "PAI"

 '...

 

 'Cria um Command para obter o ultimo ID criado para a tabela pai

 Dim cmd As New SqlCommand("SELECT scope_identity()", connSQL)

 

 'Obtém o valor do ID

 Dim ID_Pessoa = Convert.ToInt32(cmd.ExecuteScalar())

 

 '... Continua inserindo o restante dos registros nas tabelas "filhas"

 

 

Abraços,
Caio Proiete

---

Caio Proiete
http://www.caioproiete.com

Answer 2

Olá, Boa Tarde !

 

O conteúdo da variável ID_Pessoa que testei por trace debug e me retorna 0. Recordo q se nao for logo após o insert, retornaria null, entao tentei colocar logo após o insert e antes dos parametros, mas dae retorna exception Must declare the scalar variable "@Nome". (primeiro parametro da insert da tabela PAI - Pessoa)

 

Parametro com o valor 0, não pode ser assim ...

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

Antes, tentativa anterior, coloquei logo depois deste código:

 

Code Snippet

connSQL.Open()

 

'INSER€AO DOS DADOS EM PROCESSO DE MIGRA€ÇO PARA BASE SQL SERVER

 

'Faz o INSERT na tabela "PAI"

Dim cmd As New SqlCommand("INSERT into Pessoa (PE_Nome, PE_Sobrenome, PE_Sexo, PE_Nascimento, PE_LocalNascimento, PE_EC_ID, PE_DtEstadoCivil, PE_Antigo) values (@Nome, @sobreNome, @Sexo, @Nascimento, @LocalNascimento, @EstCivID, @DtEstadoCivil, @Antigo);", connSQL)

 

 

dae retornou exception Must declare the scalar variable "@Nome". (primeiro parametro da insert da tabela PAI - Pessoa)

 

 

 

Então implementei depois do Insert da tabela PAI assim:

 

Code Snippet

< consistencia de parametros >

>

>

< Final consistencia >

 

'Insert no SQL Server da Tabela PAI - Pessoa

Dim daSQL As New SqlDataAdapter(cmd)

'preenche o dataset

daSQL.Fill(dsSetSQL)

 

'________________________________________ CAPTURA PE_ID DA TABELA PESSOA(tbl.PAI) _________________________________________

 

'Cria um Command para obter o ultimo ID criado para a tabela pai

Dim cmd_PE_ID As New SqlCommand("SELECT scope_identity()", connSQL)

 

'Obt‚m o valor do ID

Dim ID_Pessoa = Convert.ToInt32(cmd.ExecuteScalar())

 

 

 

Dim daSQL_PE_ID As New SqlDataAdapter(cmd_PE_ID)

 

'preenche o dataset

daSQL_PE_ID.Fill(dsSetSQL_PE_ID)

 

 

'... Continua inserindo o restante dos registros nas tabelas "filhas"

' SQL ENDERECO RESIDENCIAL

Dim cmd_EndercoResidencial As New SqlCommand("INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (@ID_Pessoa, @PEN_PTE_ID, @EnderecoResid, @BairroResid, @CidadeResid, @UF_Resid, @CEP_Resid, @FoneResid, @FoneResid2, @E_mail1, @E_mail2);", connSQL)

'Parƒmetros de Inser‡Æo PESSOAEND * RESIDENCIAL *******************************************************************

 

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

 

 

 

 

e agora me retorna essa exception:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PessoaEnd_Pessoa". The conflict occurred in database "DBCepa", table "dbo.Pessoa", column 'PE_ID'. The statement has been terminated.

 

 

Acho que não está pegando o valor corretamente. Gostaria de saber o por quê... me ajudem!

 

Aguardo ajuda.

 

 

 

Answer 3

 

Olá, Bom Dia !

 

Parametro @ID_Pessoa com o valor 0, não pode ser assim ... está pegando valor 0 mas preciso do valor da tabela "PAI" -> Pessoa.

 

Que ajuste preciso especificar para ele pegar o valor correto de acordo com a tab. Pessoa?

 

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

 

Implementei depois do Insert da tabela PAI assim:

 

Code Snippet

 

connSQL.Open()

 

'INSER€AO DOS DADOS EM PROCESSO DE MIGRA€ÇO PARA BASE SQL SERVER

 

'Faz o INSERT na tabela "PAI"

Dim cmd As New SqlCommand("INSERT into Pessoa (PE_Nome, PE_Sobrenome, PE_Sexo, PE_Nascimento, PE_LocalNascimento, PE_EC_ID, PE_DtEstadoCivil, PE_Antigo) values (@Nome, @sobreNome, @Sexo, @Nascimento, @LocalNascimento, @EstCivID, @DtEstadoCivil, @Antigo);", connSQL)

 

< consistencia de parametros >

>

>

< Final consistencia >

 

'Insert no SQL Server da Tabela PAI - Pessoa

Dim daSQL As New SqlDataAdapter(cmd)

'preenche o dataset

daSQL.Fill(dsSetSQL)

 

'________________________________________ CAPTURA PE_ID DA TABELA PESSOA(tbl.PAI) _________________________________________

 

'Cria um Command para obter o ultimo ID criado para a tabela pai

Dim cmd_PE_ID As New SqlCommand("SELECT scope_identity()", connSQL)

 

'Obt‚m o valor do ID

Dim ID_Pessoa = Convert.ToInt32(cmd.ExecuteScalar())

 

 

 

Dim daSQL_PE_ID As New SqlDataAdapter(cmd_PE_ID)

 

'preenche o dataset

daSQL_PE_ID.Fill(dsSetSQL_PE_ID)

 

 

'... Continua inserindo o restante dos registros nas tabelas "filhas"

' SQL ENDERECO RESIDENCIAL

Dim cmd_EndercoResidencial As New SqlCommand("INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (@ID_Pessoa, @PEN_PTE_ID, @EnderecoResid, @BairroResid, @CidadeResid, @UF_Resid, @CEP_Resid, @FoneResid, @FoneResid2, @E_mail1, @E_mail2);", connSQL)

'Parƒmetros de Inser‡Æo PESSOAEND * RESIDENCIAL *******************************************************************

 

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

 

 

 

 

Retorna essa exception:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PessoaEnd_Pessoa". The conflict occurred in database "DBCepa", table "dbo.Pessoa", column 'PE_ID'. The statement has been terminated.

 

 

Não está pegando o valor corretamente. Gostaria de saber o por quê da minha implementação nao está pegando esse valor da tabela Pessoa(tab. PAI)... me ajudem!

 

Aguardo ajuda.

Answer 4

 

Estou implementando isso dentro de uma transação do SQL Server no vb.net, acho que isso não interfere no uso de SCOPE_IDENTITY, me corrijam se estiver errado.

 

Aguardo ajuda.

Answer 5

Olá Magno,

 

Você precisa obter o scope_identity imediatamente após fazer o INSERT. No seu código acima, após fazer um INSERT, você está chamando o método Fill de um DataAdapter. Esse método Fill, por sua vez, faz um SELECT na base de dados, e faz com que você perca o valor do scope_identity.

 

Abraços,
Caio Proiete

---

Caio Proiete
http://www.caioproiete.com

Answer 6

Magno Machado wrote:

Estou implementando isso dentro de uma transação do SQL Server no vb.net, acho que isso não interfere no uso de SCOPE_IDENTITY, me corrijam se estiver errado.   Aguardo ajuda.

 

Olá Magno,

 

Desde que o seu SELECT para obter o scope_identity utilize a mesma conexão e a mesma transação utilizada no INSERT, não há qualquer problema.

 

Abraços,
Caio Proiete

---

Caio Proiete
http://www.caioproiete.com

Answer 7

 

Conforme sugestão, inseri o trecho destacado em amarelo antes do Fill. Está ocorrendo o seguinte: Objeto não pode ser convertido de DBNull em outros tipos. (Está vindo null na consulta do scope_identity).

 

Gostaria de saber qual é a melhor disposição(lugares corretos) para especificar esse código, pois não estou conseguindo obter o valor do PE_ID (table Pessoa)  que é a Tab. PAI e as filhas dependem do id gerado para que possam ser inseridas também, logo em sequência...

 

Que lugar seria o mais adequado de declarar o Fill do dataAdapter? No final de tudo? Depois que acaba o insert? Estou confuso, me ajudem.

 

Aguardo Ajuda...

 

Code Snippet

 

connSQL.Open()

 

'INSER€AO DOS DADOS EM PROCESSO DE MIGRA€ÇO PARA BASE SQL SERVER

 

'Faz o INSERT na tabela "PAI"

Dim cmd As New SqlCommand("INSERT into Pessoa (PE_Nome, PE_Sobrenome, PE_Sexo, PE_Nascimento, PE_LocalNascimento, PE_EC_ID, PE_DtEstadoCivil, PE_Antigo) values (@Nome, @sobreNome, @Sexo, @Nascimento, @LocalNascimento, @EstCivID, @DtEstadoCivil, @Antigo);", connSQL)

'************************************ TABLE PESSOA **********************************************

If nome Is System.DBNull.Value OrElse String.IsNullOrEmpty(nome) Then

'Se entrou no IF, o Nome nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@Nome", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@Nome", nome.ToString)

End If

 

If sobrenome Is System.DBNull.Value OrElse String.IsNullOrEmpty(sobrenome) Then

'Se entrou no IF, o sobrenome nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@sobreNome", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@sobreNome", sobrenome.ToString)

End If

If drAccess(1) Is System.DBNull.Value OrElse String.IsNullOrEmpty(drAccess(1)) Then

'Se entrou no IF, o Sexo nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@Sexo", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@Sexo", drAccess(1).ToString())

End If

 

 

Dim dt As DateTime

Dim dataValida As Boolean = False

'Menor data poss¡vel no SQL Server

Dim menorDataSql As New DateTime(1753, 1, 1)

 

If Not IsDBNull(drAccess(2)) Then

dataValida = DateTime.TryParse(drAccess(2), dt)

End If

If Not dataValida OrElse dt < menorDataSql Then

cmd.Parameters.Add("@Nascimento", SqlDbType.DateTime).Value = DBNull.Value

ElseIf drAccess(2) IsNot DBNull.Value Then

cmd.Parameters.Add("@Nascimento", SqlDbType.DateTime).Value = dt

End If

 

If LocalNasc Is System.DBNull.Value OrElse String.IsNullOrEmpty(LocalNasc) Then

'Se entrou no IF, o LocalNasc nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@LocalNascimento", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@LocalNascimento", LocalNasc.ToString)

End If

 

If IsDBNull(EstadoCivil) OrElse (EstadoCivil = Nothing) Then

'Se entrou no IF, o EstadoCivil nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@EstCivID", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@EstCivID", EstadoCivil)

End If

 

If (DtEstadoCivil.HasValue) Then

'foi informado o valor do campo DtEstadoCivil

cmd.Parameters.Add("@DtEstadoCivil", SqlDbType.DateTime).Value = DtEstadoCivil

Else

'NÇO foi informado nenhum valor para o campo DtEstadoCivil

'cmd.Parameters.AddWithValue("@DtEstadoCivil", DBNull.Value)

cmd.Parameters.Add("@DtEstadoCivil", SqlDbType.DateTime).Value = DBNull.Value

End If

 

If IsDBNull(drAccess(6)) OrElse (drAccess(6) Is Nothing) Then

'Se entrou no IF, o C¢dPF¡sica nÆo ‚ v lido.

cmd.Parameters.AddWithValue("@Antigo", DBNull.Value)

Else

cmd.Parameters.AddWithValue("@Antigo", drAccess(6))

End If

 

'________________________________________ CAPTURA PE_ID DA TABELA PESSOA(tbl.PAI) _________________________________________

'Cria um Command para obter o ultimo ID criado para a tabela pai

Dim cmd_PE_ID As New SqlCommand("SELECT scope_identity()", connSQL)

 

'Obt‚m o valor do ID

Dim ID_Pessoa = Convert.ToInt32(cmd_PE_ID.ExecuteScalar())

 

'... Continua inserindo o restante dos registros nas tabelas "filhas"

 

' SELECT QUE CAPTURA O PE_ID da Tabela Pessoa (TABLE PAI)

Dim daSQL_PE_ID As New SqlDataAdapter(cmd_PE_ID)

'preenche o dataset

daSQL_PE_ID.Fill(dsSetSQL_PE_ID)

 

'Insert no SQL Server da Tabela PAI - Pessoa

Dim daSQL As New SqlDataAdapter(cmd)

'preenche o dataset

daSQL.Fill(dsSetSQL)

 

' SQL ENDERECO RESIDENCIAL

Dim cmd_EndercoResidencial As New SqlCommand("INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (@ID_Pessoa, @PEN_PTE_ID, @EnderecoResid, @BairroResid, @CidadeResid, @UF_Resid, @CEP_Resid, @FoneResid, @FoneResid2, @E_mail1, @E_mail2);", connSQL)

 

 

'Parƒmetros de Inser‡Æo PESSOAEND * RESIDENCIAL *******************************************************************

 

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

Dim PEN_PTE_ID_Resid As Integer = 1

cmd_EndercoResidencial.Parameters.AddWithValue("@PEN_PTE_ID", PEN_PTE_ID_Resid)

 

 

 

Answer 8

Olá Magno,

 

Está vindo NULL, porque você não está inserindo o registro na tabela Pessoa... Você cria um SqlCommand (cmd), mas nunca chega a executá-lo! (ExecuteNonQuery). Veja, mais ou menos, como deveria estar:

 

Code Snippet

 

 'Instancia o Command

 Dim cmd As New SqlCommand("INSERT into Pessoa ...", connSQL)

 'Adiciona os parâmetros

 cmd.Parameters.AddWithValue("@Nome", nome.ToString)

 '...

 

 'Finalmente, insere o registro

 cmd.ExecuteNonQuery()

 

 '---------------

 

 'Só agora, que o registro foi inserido, é que podemos obter

 'o valor que foi atribuido ao seu campo identity:

 Dim cmd_PE_ID As New SqlCommand("SELECT scope_identity()", connSQL)

 

 'Obtém o valor do ID

 Dim ID_Pessoa = Convert.ToInt32(cmd_PE_ID.ExecuteScalar())

 

 

Abraços,

Caio Proiete

---

Caio Proiete
http://www.caioproiete.com

Answer 9

 

Ajustei dessa maneira e ainda ocorre a exception: Objeto não pode ser convertido de DBNull em outros tipos.

 

Acho que deve haver algo fora do lugar mas ainda não encontrei, peço ajuda.

 

Code Snippet

 

'Instancia o Command

 Dim cmd As New SqlCommand("INSERT into Pessoa ...", connSQL)

 'Adiciona os parâmetros

 cmd.Parameters.AddWithValue("@Nome", nome.ToString)

 '...

     ' E OS DEMAIS PARAMETROS

 

'Finalmente, insere o registro

cmd.ExecuteNonQuery()

 

'________________________________________ CAPTURA PE_ID DA TABELA PESSOA(tbl.PAI) _________________________________________

'Cria um Command para obter o ultimo ID criado para a tabela pai

Dim cmd_PE_ID As New SqlCommand("SELECT scope_identity()", connSQL)

'Obt‚m o valor do ID

Dim ID_Pessoa = Convert.ToInt32(cmd_PE_ID.ExecuteScalar())

 

'... Continua inserindo o restante dos registros nas tabelas "filhas"

 

' SELECT QUE CAPTURA O PE_ID da Tabela Pessoa (TABLE PAI)

Dim daSQL_PE_ID As New SqlDataAdapter(cmd_PE_ID)

'preenche o dataset

daSQL_PE_ID.Fill(dsSetSQL_PE_ID)

 

'Insert no SQL Server da Tabela PAI - Pessoa

Dim daSQL As New SqlDataAdapter(cmd)

'preenche o dataset

daSQL.Fill(dsSetSQL)

 

' SQL ENDERECO RESIDENCIAL

Dim cmd_EndercoResidencial As New SqlCommand("INSERT into PessoaEnd (PEN_PE_ID, PEN_PTE_ID, PEN_Endereco, PEN_Bairro, PEN_Cidade, PEN_UF, PEN_CEP, PEN_Fone, PEN_Fone2, PEN_E_Mail, PEN_E_Mail2) values (@ID_Pessoa, @PEN_PTE_ID, @EnderecoResid, @BairroResid, @CidadeResid, @UF_Resid, @CEP_Resid, @FoneResid, @FoneResid2, @E_mail1, @E_mail2);", connSQL)

'Parƒmetros de Inser‡Æo PESSOAEND * RESIDENCIAL *******************************************************************

 

'guarda o PE_ID gerado pela tabela Pessoa(Tabela PAI)

cmd_EndercoResidencial.Parameters.AddWithValue("@ID_Pessoa", ID_Pessoa)

 

Dim PEN_PTE_ID_Resid As Integer = 1

cmd_EndercoResidencial.Parameters.AddWithValue("@PEN_PTE_ID", PEN_PTE_ID_Resid)

 

 

 

Aguardo ajuda,

 

a variável ID_Pessoa deve ter o valor do Id da tabela Pessao (PAI) e não NULL como ainda continua. Help! Deve haver lugar certo, mas não sei o que ainda falta para funcionar...

 

 

Answer 10

 

 

Nesse link está a solução para esta thread (mesmo assunto) em forums diferentes, consegui:

 

http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=4152166&SiteID=21&mode=1

 

Abraços