Inserting into a Database Error

[Ivan Weiss]

Another question for the whizzes out there. I have the following code
in a database class:

Public Sub insertData(ByVal argInsertSql As String, ByVal argTable As
String)
Dim myConnection As New OleDbConnection(dbConnString)
Dim strSelectSql As String = "SELECT * FROM " & argTable
Dim myDataAdapter As New OleDbDataAdapter(strSelectSql,
myConnection)
Dim myDataSet As New DataSet()
Dim myDataTable As DataTable()
Dim myDataRow As DataRow()

Try
myConnection.Open()
myDataAdapter.Fill(myDataSet, argTable)
myDataTable = myDataSet.Tables(argTable)

myDataRow = myDataTable.newrow
Catch
DisplayErrorMessage("clsDatabase:insertData")
End Try

End Sub

The myDataTable = myDataSet.Tables(argTable) AND
the myDataRow = myDataTable.newrow

are both generating errors. Saying cant convert data table to
1-dimensional array of type datatable. I am trying to learn how to
update a database via ADO.Net directly from an online book I have and
there example isn't working.

What am I doing wrong?

-Ivan

 

[Bernie Yaeger]

Hi Ivan,
Try to follow the steps below. I create the connection; I load a
dataadapter; I use it to load the dataset; then I add a new row; finally I
add a commandbuilder (you must have this or more involved insert commands to
update the back end). I'd recommend you get a good text of databases using
ado .net - ADO .Net by David Sceppa (MS Press) is about the best.

HTH,

Bernie Yaeger

Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")

Dim ocmd As New SqlCommand("select * from histd_", oconn)

Dim oda As New SqlDataAdapter(ocmd)

Dim ods As New DataSet("History Details")

oconn.Open()

oda.Fill(ods, "History Details")

Dim r As DataRow

r = ods.Tables(0).NewRow()

r("bipad") = "98705 "

r("imcacct") = "81378-001456"

r("issuecode") = "200212"

r("posstatus") = "u"

r("draw") = 17

r("rreturn") = 2

r("net") = r("draw") - r("rreturn")

' now, to update the back end, this won't work without a commandbuilder that
references the dataadapter

Dim mcommandbuilder As SqlCommandBuilder = New SqlCommandBuilder(oda)

ods.Tables(0).Rows.Add(r)

Try

oda.Update(ods, "history details")

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

oconn.Close()

 

[Cor]

Hi Ivan,

Have a look at the datatable "copy" method.

I hope this helps a little bit

Cor

 

[Ivan Weiss]

I was reading a little bit and the text I have said to avoid using
command builders as they impact performance negatively and require a lot
of overhead/resources.

Is this true or are they adequate and effective?

Also, In general I am new to OOP. Is it preferred practice to create a
database class rather than typing the database code individually into
each sub I need it in throughout my program?

-Ivan

 

[Bernie Yaeger]

Hi Ivan,

Yes, it says that everywhere. Nevertheless, if you have a single table to
update, they work just fine. Overhead? Yes, it might take 5000 times as
long - but .0000000001 of a second isn't hurt very much when it becomes
..0002 of a second, unless your updating 500,000 rows at once. Yes, there
are times when you have to 'roll your own', but it isn't terribly difficult
to do, just tedious.

Re tying the database creation code into each sub - you could do this in a
form's init code, above the load event, and load it in the load event - then
it's available throughout the form. Only problem is it is in memory
throughout, so you have to decide which approach you prefer.

Again, I strongly recommend the Sceppa book or the Vaughn book (Apress, ADO
and ADO .Net Best Practices).

HTH,

Bernie

 

[Ivan Weiss]

I am going to take your recommendation on getting a book for sure but in
the meantime I miss the old days of VB database classes.

Is there a way to simply update the database using SQL statements
(INSERT) etc....

All I want to is add one row, and it seems to me like Visual Studio .Net
has made this unneccessarily hard to do and memory intensive. I do not
want to fill a dataset and use up RAM, I just want to quickly update my
database and thats it.

-Ivan

 

[Bernie Yaeger]

Hi Ivan,

You're essentially correct; it's a lot more involved than it used to be.
yes, it's more flexible, but it seems unnecessarily difficult at times,
although I've gotten used to it and basically comfortable these days (but it
still takes a lot longer than it used to).

Yes, you can surely add a row using a stored procedure. Here's an example:
Dim oconn As New SqlConnection("data source=d5z0071;initial
catalog=imc;integrated security=sspi;")

Dim addcmd As New SqlCommand

addcmd = New SqlCommand("sp_histd_add2", oconn)

addcmd.CommandType = CommandType.StoredProcedure

Dim param1 As SqlParameter

param1 = addcmd.Parameters.Add("@ibipad", SqlDbType.Char, 6)

param1.Direction = ParameterDirection.Input

param1.Value = "00893 "

Try

addcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

oconn.Close()

HTH,

Bernie