Addnew in VB.NET ?

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

In VB6 to add a new record in a recorset, you can use the Addnew method,
then set each column's value, then call the Update method.
How can I do this in VB.NET ?
Thank you.

VB6:
Private m_rs As new ADODB.Recordset

m_rs.Open "SELECT * FROM myTable", adoCon, adOpenKeyset, adLockOptimistic
with m_rs
.AddNew
.Fields("ip") = AccountIP & " " ' x.Address
.Fields("account") = Account & " " 'Left$(x.Key, 5)
.Fields("DATEtime") = Now & " "
.Fields("name") = userver & " "
.Update
end with
 
fniles said:
In VB6 to add a new record in a recorset, you can use the Addnew
method, then set each column's value, then call the Update method.
How can I do this in VB.NET ?
Thank you.

VB6:
Private m_rs As new ADODB.Recordset

m_rs.Open "SELECT * FROM myTable", adoCon, adOpenKeyset,
adLockOptimistic with m_rs
.AddNew
.Fields("ip") = AccountIP & " " ' x.Address
.Fields("account") = Account & " " 'Left$(x.Key, 5)
.Fields("DATEtime") = Now & " "
.Fields("name") = userver & " "
.Update
end with
Click to expand...

http://msdn2.microsoft.com/en-us/library/e80y5yhx.aspx



Armin
 
Thank you.
When I tried it, when trying to update the database, it gave me the error
"Update requires a valid InsertCommand when passed DataRow collection with
new rows."
This is what I do:
Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_cmdSQL As SqlClient.SqlCommand
Dim m_dsSQL As DataSet
Dim m_row As System.Data.DataRow

m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = "select * from myTable"
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = m_cmdSQL
m_daSQL.Fill(m_dsSQL)
m_row = m_dsSQL.Tables(0).NewRow
m_row("colA") = sValue
m_dsSQL.Tables(0).Rows.Add(m_row)
m_daSQL.Update(m_dsSQL) --> error "Update requires a valid InsertCommand
when passed DataRow collection with new rows."

Do I have to create an Insert Command in the SQLDataAdapter like so :
m_daSQL.InsertCommand = New SqlClient.SqlCommand("INSERT INTO myTable (ColA)
VALUES(?)", adoCon)
myParm = m_daSQL.InsertCommand.Parameters.Add("@colA", SqlDbType.VarChar,
15, "ColA")
?

Thanks.
 
Thank you, that works.
This is what I do:
Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_cmdSQL As SqlClient.SqlCommand
Dim m_dsSQL As DataSet
Dim m_row As System.Data.DataRow
Dim m_cmdBuilderSQL As SqlClient.SqlCommandBuilder

m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = "select * from myTable"
End With
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = m_cmdSQL
m_cmdBuilderSQL = New SqlClient.SqlCommandBuilder(m_daSQL)
m_daSQL.Fill(m_dsSQL)
m_row = m_dsSQL.Tables(0).NewRow
m_row("colA") = sValue
m_dsSQL.Tables(0).Rows.Add(m_row)
m_cmdBuilderSQL.GetInsertCommand()
m_daSQL.Update(m_dsSQL)

I am wondering which one is faster, using the code above (with
sqlcommandbuilder) or use ExecuteNonQuery method of the SQLCommand like the
following:
Dim m_cmdSQL As SqlClient.SqlCommand

sSQL = "insert into myTable (colA) values (sValue)"
m_cmdSQL = New SqlClient.SqlCommand
With m_cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
m_cmdSQL.ExecuteNonQuery()
 
I am wondering which one is faster, using the code above (with
sqlcommandbuilder) or use ExecuteNonQuery method of the SQLCommand like
the following:
Click to expand...

This will in my idea probebly only be showable when you do this 10000 times
and than only as a part of a millisecond.

In dataprocessing there are however mostly better options to take a look at
speed which can than better first be investigated. (As there is a need for
it, keep in mind the 20:80 rule for that)

Mostly something as 20% of your code is doing 80% of the job.

Cor
 
But, with the sqlcommandbuilder, you need to select the table first, add the
row then update it, instead of just executing an "INSERT INTO" command using
ExecuteNonQuery.
You don't think the ExecuteNonQuery is faster ?

Thanks
 
But, with the sqlcommandbuilder, you need to select the table first, add
the row then update it, instead of just executing an "INSERT INTO" command
using ExecuteNonQuery.
You don't think the ExecuteNonQuery is faster ?
Click to expand...
Picoseconds, in my eyes not important enough to take any investigation or
whatever too.

This part of the job in the updating takes on the client versus the netwerk
aand server let's say 1:100 of the time, if you want speed, than look at the
hardware how you can improve it.

Just my opinion,

Cor
 
Back
Top