insert without primary key

[Anonymous]

Is it possible to insert rows with ado.net in a table that lacks primary key
columns?

Using oracle 9.2/odp.

 

[Carlos J. Quintero [.NET MVP]]

Yes, why not? At the very least, using a Command with a direct SQL INSERT
statement will work.

--

Carlos J. Quintero

MZ-Tools 4.0: Productivity add-ins for Visual Studio .NET
You can code, design and document much faster.
http://www.mztools.com

 

[Anonymous]

The code below throws the following error:
System.InvalidOperationException: Dynamic SQL generation failed. No key
information found
at Oracle.DataAccess.Client.OracleCommandBuilder.CheckPrimaryKey()
at Oracle.DataAccess.Client.OracleCommandBuilder.GetUpdateCommand()


----------

Dim cnOracle As Oracle.DataAccess.Client.OracleConnection
Dim daOracle As Oracle.DataAccess.Client.OracleDataAdapter
Dim oCmd As Oracle.DataAccess.Client.OracleCommand
Dim ocb As Oracle.DataAccess.Client.OracleCommandBuilder
Dim sql As String
Dim dt As DataTable
Dim dr As DataRow


' Open DB
cnOracle = New Oracle.DataAccess.Client.OracleConnection( _
"Data Source=" & TextBox1.Text & ";User Id=" & TextBox2.Text &
";Password=" & TextBox3.Text & ";")
daOracle = New Oracle.DataAccess.Client.OracleDataAdapter
cnOracle.Open()


' Drop table
sql = "drop table mytable"
oCmd = New Oracle.DataAccess.Client.OracleCommand(sql, cnOracle)
Try
oCmd.ExecuteNonQuery()
Catch ex As Oracle.DataAccess.Client.OracleException
End Try

' Create table without any primary key
sql = "create table mytable ( id number(10), value varchar2(20) )"
oCmd = New Oracle.DataAccess.Client.OracleCommand(sql, cnOracle)
oCmd.ExecuteNonQuery()


' Load empty table from db
dt = New DataTable
sql = "select * from mytable"
daOracle.SelectCommand = New Oracle.DataAccess.Client.OracleCommand(sql,
cnOracle)
daOracle.Fill(dt)

' Add row
dr = dt.NewRow
dr("id") = 123
dr("value") = "asd"
dt.Rows.Add(dr)


' Save table to db
ocb = New Oracle.DataAccess.Client.OracleCommandBuilder
ocb.DataAdapter = daOracle
daOracle.UpdateCommand = ocb.GetUpdateCommand

daOracle.Update(dt)


' Close DB
cnOracle.Close()

 

[Guest]

Not with the autogenerated UPDATE command. You will have to write your own
queries for INSERT, UPDATE and DELETE and attach them to the data adapter.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Nick Malik [Microsoft]]

You are going to have to create the INSERT, and SELECT statements yourself
and put them into your data adapter.


--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.