Insert Row

Question

Insert Row

In vs 2003 I had a form that was a mixture of textboxes and comboboxes.  The comboboxes had datasources, etc. I was able to verify that the contents of all the controls were valid.  If so, I created a dataadapter, then a dataset, and added a row, all in code.  It was cumbersome, but worked.

I want to reproduce the behavior in vs 2005.  The same code obviously won't work.  I searched through the msdn 2005 documentation, and came across some hints, but no examples.

It involved a datatable with queries orther than select queries:

Insert Results   Creates new rows by copying existing rows from one table into another, or into the same table as new rows. This type of query creates an SQL INSERT INTO...SELECT statement.

Insert Values   Creates a new row and inserts values into specified columns. This type of query creates an SQL INSERT INTO...VALUES statement.

Is there any examples of this in detail, or a walkthrough?

dennist

Answer 1

Hi,


AFAIK, there were no major changes in ADO .net... So possibly your vs2003 code might work...
Could you post your vs2003 code? Maybe I could help converting it...
If all you want to do is append a row then here are the steps...

1. Create a DataAdapter
         OleDbDataAdapter myAdapter = new OleDbDataAdapter("<your select statement>")

2. specify a Insert Command
         myAdapter.InsertCommand = new OleDbCommand("your insert statement")

Everytime you append a row to your table and call update in your adapter, your insert sql statement would then be executed in your database...



cheers,


Paul June A. Domag
         

Answer 2

OK Paul, it's a little messy, because I was experimenting and never cleaned up the code, because I soon started to work with 2005 beta.  Anyway, I wanted to try to do it in a function, and not in the form, just to experiment with what I could do compared to vb6.

It worked everytime

Public Function NewTopic(ByVal frm As frmTopicFromStart) As Boolean

NewTopic = False

'Dim cn As New OleDbConnection(gstrConn)

Try

cn.Open()

Catch er As Exception

MessageBox.Show("Type = " & er.GetType.ToString & vbCr & "Message = " & er.Message)

End Try

 

 

'Dim da As OleDbDataAdapter = New OleDbDataAdapter

'Dim ds As New DataSet

'ds.DataSetName = "ds1"

'cn.Open()

'da.FillSchema(ds, SchemaType.Source, "Topics")

'''cn.Close()

'ds.WriteXmlSchema("H:\HasbaraNET\ado.net tests\H:\HasbaraNET\ado.net tests\Kevin corrections\TFSNet2\TFSNet2\ds.xsd")

 

Dim cmd As New OleDbCommand("SELECT * FROM Topics", cn)

Dim da As OleDbDataAdapter = New OleDbDataAdapter

da.SelectCommand = cmd

' Fill the DataSet

Dim ds As New DataSet

ds.DataSetName = "ds1"

Try

da.FillSchema(ds, SchemaType.Source, "Topics")

Catch er As Exception

MessageBox.Show("Type = " & er.GetType.ToString & vbCr & "Message = " & er.Message)

End Try

da.Fill(ds, "Topics")

ds.WriteXmlSchema("C:\hasbaraprograms\HasbaraPrograms\TFSNet2\ds.xsd")

 

 

 

 

 

 

 

 

Dim dsA As New ds1

da.Fill(dsA, "Topics")

Dim tblTopics As ds1.TopicsDataTable = dsA.Tables(0)

Dim rowTopics As ds1.TopicsRow

''Dim dr As DataRow

'Dim iRow As Integer

'iRow = dsA.Tables(0).Rows.Count - 1

'rowTopics = dsA.Tables(0).Rows(iRow)

'MsgBox(rowTopics.Issuer.ToString)

'isn't working because no rows

Try

rowTopics = tblTopics.NewTopicsRow

 

 

 

 

 

rowTopics.ParentID = CInt(frm.txtParentID.Text)

rowTopics.Title = frm.txtTopicTitle.Text

rowTopics.TopicText = frm.txtTextGeneral.Text

rowTopics.Issuer = frm.cboTopicIssuers.SelectedValue

rowTopics.BeginText = 0

rowTopics.Length = frm.txtTextGeneral.TextLength

rowTopics.TopicDate = frm.dtp1.Value

rowTopics.DateType = frm.cboDateType.SelectedValue

rowTopics.PointOfView = frm.cboPOV.SelectedValue

rowTopics.ContentRights = CInt(frm.txtContentRights.Text)

rowTopics.Publisher = frm.cboPublications.SelectedValue

'rowTopics.CreateDate = Now

'rowTopics.ChangeDate = Now

rowTopics.Active = True

'rowTopics.PermissionID = 6

 

 

 

 

 

 

tblTopics.AddTopicsRow(rowTopics)

'da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO Topics(ID,Topics,CreateDate,ChangeDate,Active) values (?,?,?,?,?)", cn)

da.InsertCommand = New OleDb.OleDbCommand("INSERT INTO Topics(ParentID, Title, TopicText, Issuer, BeginText, Length, TopicDate, DateType, PointOfView, ContentRights, Publisher, Active) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", cn)

'INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice) VALUES (?, ?, ?, ?)

'Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn)

AddHandler da.RowUpdated, AddressOf OnRowUpDated

 

'Try

' da.InsertCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID")

'Catch er As Exception

' MessageBox.Show("Type = " & er.GetType.ToString & vbCr & "Message = " & er.Message)

'End Try

da.InsertCommand.Parameters.Add("@ParentID", OleDb.OleDbType.Integer, 0, "ParentID")

da.InsertCommand.Parameters.Add("@Title", OleDb.OleDbType.VarWChar, 16, "Title")

da.InsertCommand.Parameters.Add("@TopicText", OleDb.OleDbType.LongVarWChar, 64, "TopicText")

da.InsertCommand.Parameters.Add("@Issuer", OleDb.OleDbType.Integer, 4, "Issuer")

da.InsertCommand.Parameters.Add("@BeginText", OleDb.OleDbType.Integer, 4, "BeginText")

da.InsertCommand.Parameters.Add("@Length", OleDb.OleDbType.Integer, 4, "Length")

da.InsertCommand.Parameters.Add("@TopicDate", OleDb.OleDbType.Date, 8, "TopicDate")

da.InsertCommand.Parameters.Add("@DateType", OleDb.OleDbType.Integer, 4, "DateType")

da.InsertCommand.Parameters.Add("@PointOfView", OleDb.OleDbType.Integer, 4, "PointOfView")

da.InsertCommand.Parameters.Add("@ContentRights", OleDb.OleDbType.Integer, 4, "ContentRights")

da.InsertCommand.Parameters.Add("@Publisher", OleDb.OleDbType.Integer, 4, "Publisher")

'da.InsertCommand.Parameters.Add("@CreateDate", OleDb.OleDbType.Date, 8, "CreateDate")

'da.InsertCommand.Parameters.Add("@ChangeDate", OleDb.OleDbType.Date, 8, "ChangeDate")

da.InsertCommand.Parameters.Add("@Active", OleDb.OleDbType.Boolean, 2, "Active")

Catch ex As Exception

MessageBox.Show(ex.Message, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Try

da.Update(dsA, "Topics")

Catch e As System.Exception

MessageBox.Show(e.Message, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)

Finally

cn.Close()

End Try

 

 

rowTopics = tblTopics.NewTopicsRow

Dim dr As DataRow

Dim iRow As Integer

iRow = dsA.Tables(0).Rows.Count - 1

rowTopics = dsA.Tables(0).Rows(iRow)

'MsgBox(rowTopics.Title)

'MsgBox(rowTopics.TopicText)

'isn't working because no rows

 

 

'You now have access to all fields in the last row through the DataRow object (dr)

cn.Close()

 

End Function

Answer 3

Hi,

Bummer! I have a problem downloading VB .net Express (Must be my internet connection). But I quite understand on what you are trying to do here... I guess all you want is to be able to add a row in your table...

Ok, here is a more detailed step on how to achieve that. I made it in vc++/cli, converted it manually in vb .net code .Since I dont have a compiler, this might not compile properly but i guess you'll get the logic...

Note: Error Catching has been removed... Also Disposing

Public Function NewTopic(frm as frmTopicFromStart) as boolean

' Establish Connection to Data Source

dim cn as new OleDbConnection(myConstr)

cn.open()

' Create An Adapter

Dim da as new OleDbDataAdapter("SELECT * FROM Topics", cn)

' Specify Insert Routine...

da.InsertCommand = new OleDbCommand("INSERT INTO Topics(ParentID, Title) VALUES(@ParentID, @Title)")

' Build Parameters

da.InsertCommand.Parameters.Add ("@ParentID", OleDb.OleDbType.Integer, 0, "ParentID")

da.InsertCommand.Parameters.Add ("@Title", OleDb.OleDbType.VarWChar, 16, "Title")

' Create Dataset and populate it...

Dim ds as new DataSet("ds1")

da.Fill(ds, "Topics")

' Reference the underlying table...

dim dt as DataTable

dt = ds.Tables(0)

' Not sure here...

' Create a row and populate values

Dim newRow as DataRow = dt.NewRow()

newRow("ParentID") = CInt(frm.txtParentID.Text)

newRow("Title") = frm.txtTopicTitle.Text

' Add the newly created row in the RowsCollection

dt.Rows.Add(newRow)

' Update using data adapter...

da.Update(ds, "Topics")

cn.close()

End Function

BTW, Sorry for the bad formatting... Got a problem with this editor...

Cheers,

 

Paul June A. Domag