sql.client Insert newbie question

  • Thread starter Thread starter David
  • Start date Start date
D

David

I've managed to work with disonnected sets enough to retrieve and navigate
datasets. But, How you use use the command builder to dynamically insert,
update and delete rows? I'd like to use getinsertcommand, getupdatecommand,
getdeletecommand methods, but I can't figure out how to call a method to
execute them and pass the databound information to the insert, update
commands. Here's my sample code. Thank you for any help.

daver



Dim SqlConn As SqlConnection
Dim SqlCMD As SqlCommand
Dim SqlDA As SqlDataAdapter
Dim SqlCmdBldr As SqlCommandBuilder


SqlConn = New SqlConnection("Server=test;database=Dave;Integrated
Security=SSPI")
SqlCMD = New SqlCommand("Select name from namelist", SqlConn)
SqlDA = New SqlDataAdapter(SqlCMD)
SqlCmdBldr = New SqlCommandBuilder(SqlDA)

Dim sqlString As String
Dim ds As New DataSet
Dim dt As New DataTable

SqlDA.Fill(ds, "namelist")
dt = ds.Tables(0)

txtName.DataBindings.Add("text", dt, "Name")

Try
sqlString = "Insert into Namelist ( name ) values (" & txtName.Text & ")"
SqlCMD = New SqlCommand(sqlString, SqlConn)
SqlConn.Open()
SqlCMD.CommandType = CommandType.Text
SqlCMD.ExecuteNonQuery()
SqlConn.Close()


Catch exp As SqlException
MsgBox(exp.Message, MsgBoxStyle.Critical)
End Try
 
Looks like my last post didn't make it.

I think the confusion is between connected and
disconnected mode. Using the DataAdapter to update your
db is 'disconnected' whereas using the command object
directly is using connected mode. Once you have a
commandbuilder, it has built your logic for you, so all
you need to do is call the update method.

The problem with connected methodology per se is that
(problem probably isn't the best word), is that in your
second snippet, if it was behind an update button and the
user hit it 1000 times, it would do 1000 inserts (unless
there was something like a Primary Key or similar to
prevent it). on the other hand, the DataSet knows what's
changed in it, so if you call 1000 updates you'd only have
one submitted if the data only changed once.

It's also worth noting about Simple vs. Complex binding.
Textboxes and controls that only have one value at a time
are simple bound. ComboBoxes, listboxes etc that show one
value but have many are complex. They behave a little
differently.

You will need to use a BindingContext or BindingManager to
do any serious databinding.....For what it's worth, you
may want to pick up David Sceppa's ADO.NET core reference,
it's first class in every regard.

Anwyay, here's a code snippet, I removed some irrelevant
code for brevity. I wrote this in haste a while back, but
it should give you the idea.

At module level, declare BMB as BindingManagerBase.

Then, when the grid row changes, or the ListBOx(it's an
Infragisitics control so I don't want to confuse you with
it), I fire updates on each of my dataadapters. This code
assumes you've filled to the DataSet before calling it.

Let me know if you have any problems.

Good Luck,

Bill

Sub InitializeBinding()
Try

bmb = Me.BindingContext(Ds1, "Employees")
bmb.Position = bmb.Count
bmb.Position = 0

tbFirstName.DataBindings.Add("Text",
Ds1, "Employees.FirstName")
tbTotalLeave.DataBindings.Add("Text",
Ds1, "Employees.Leave_Accrued")
tbLastName.DataBindings.Add("Text",
Ds1, "Employees.LastName")
tbTitle.DataBindings.Add("Text",
Ds1, "Employees.Title") tbVision.DataBindings.Add
("Text", Ds1, "Employees.Vision")
tbOnSite.DataBindings.Add("Text",
Ds1, "Employees.OnSite_Phone")
tbDepartureDate.DataBindings.Add("Text",
Ds1, "Employees.Termination_Date")
tbHomePhone.DataBindings.Add("Text",
Ds1, "Employees.HomePhone")
tbCellPhone.DataBindings.Add("Text",
Ds1, "Employees.Cell_Phone")
tbEmergencyNum.DataBindings.Add("Text",
Ds1, "Employees.Emergency_Contact_Num")
tbEmergencyContact.DataBindings.Add("Text",
Ds1, "Employees.Emergency_Contact")

dgLeave.DataSource = Ds1
dgLeave.DataMember = "Employees.EmpLeave"
dgNotes.DataSource = Ds1
dgNotes.DataMember = "Employees.EmpNotes"
dgReview.DataSource = Ds1
dgReview.DataMember = "Employees.EmpReview"
dgPayroll.DataSource = Ds1
dgPayroll.DataMember = "Employees.Payroll"

Catch ex As System.Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
-----Original Message-----
You have not yet set
Click to expand...
the "InsertCommand", "DeleteCommand", and
 
You have not yet set the "InsertCommand", "DeleteCommand", and
"UpdateCommand" of your DataAdapter.

I changed the names of your command object so they would all be different...

cmdSelect = New SqlCommand("Select name from namelist", SqlConn)
SqlDA = New SqlDataAdapter(cmdSelect)
SqlCmdBldr = New SqlCommandBuilder(SqlDA)

SqlDA.UpdateCommand = SqlCmdBldr.GetUpdateCommand();
SqlDA.InsertCommand = SqlCmdBldr.GetInsertCommand();
SqlDA.DeleteCommand = SqlCmdBldr.GetDeleteCommand();

Or if you are creating the command without the builder (as you started to
do)...

sqlString = "Insert into Namelist ( name ) values (@Name)";
cmdInsert = New SqlCommand(sqlString, SqlConn)
prmName = cmdInsert.Parameters.Add("@Name");
prmName.Value = txtName.Text;
sqlDA.InsertCommand = cmdInsert;

I recommend you make the command's manually instead of using CommandBuilder.
I've rarely seen the CommandBuilder generate the command you would want to
use in production, so why bother ever use it? I see it more as a teaching
tool.

Try my database oriented open source projects below.
 
I thought we were sooo close. My application is in VB.NET Your sample code
uses prmName. However, when I use the cmdinsert.parameters.add, it returns
an integer. prmName must be declared as such and cannot be assigned a
value. Am I missing something simple? Thanx again.

daver
 
Did you explicitly set the data type of the parameter?

For example:
=======================
Private Function CreateDataAdapterUpdateCommand() As OleDbCommand
Dim strSQL As String
strSQL = "UPDATE [Order Details] " & _
" SET OrderID = ?, ProductID = ?, " & _
" Quantity = ?, UnitPrice = ? " & _
" WHERE OrderID = ? AND ProductID = ? AND " & _
" Quantity = ? AND UnitPrice = ?"
Dim cmd As New OleDbCommand(strSQL, cn)

Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")
pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID")
pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", OleDbType.Currency, 0, "UnitPrice")

Dim param As OleDbParameter
param = pc.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID_Orig", OleDbType.Integer, 0, _
"ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0, _
"Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0, _
"UnitPrice")
param.SourceVersion = DataRowVersion.Original

Return cmd
End Function
========================


Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "(e-mail address removed)" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: Re: sql.client Insert newbie question
| Date: Fri, 22 Aug 2003 15:22:36 -0500
| Lines: 104
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: 209.40.129.101
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:59084
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I thought we were sooo close. My application is in VB.NET Your sample
code
| uses prmName. However, when I use the cmdinsert.parameters.add, it
returns
| an integer. prmName must be declared as such and cannot be assigned a
| value. Am I missing something simple? Thanx again.
|
| daver
|
|
| | > You have not yet set the "InsertCommand", "DeleteCommand", and
| > "UpdateCommand" of your DataAdapter.
| >
| > I changed the names of your command object so they would all be
| different...
| >
| > cmdSelect = New SqlCommand("Select name from namelist", SqlConn)
| > SqlDA = New SqlDataAdapter(cmdSelect)
| > SqlCmdBldr = New SqlCommandBuilder(SqlDA)
| >
| > SqlDA.UpdateCommand = SqlCmdBldr.GetUpdateCommand();
| > SqlDA.InsertCommand = SqlCmdBldr.GetInsertCommand();
| > SqlDA.DeleteCommand = SqlCmdBldr.GetDeleteCommand();
| >
| > Or if you are creating the command without the builder (as you started
to
| > do)...
| >
| > sqlString = "Insert into Namelist ( name ) values (@name)";
| > cmdInsert = New SqlCommand(sqlString, SqlConn)
| > prmName = cmdInsert.Parameters.Add("@name");
| > prmName.Value = txtName.Text;
| > sqlDA.InsertCommand = cmdInsert;
| >
| > I recommend you make the command's manually instead of using
| CommandBuilder.
| > I've rarely seen the CommandBuilder generate the command you would want
to
| > use in production, so why bother ever use it? I see it more as a
teaching
| > tool.
| >
| > Try my database oriented open source projects below.
| >
| > --
| > Michael Lang, MCSD
| > See my .NET open source projects
| > http://sourceforge.net/projects/dbobjecter (code generator)
| > http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
| >
| > | > > I've managed to work with disonnected sets enough to retrieve and
| navigate
| > > datasets. But, How you use use the command builder to dynamically
| insert,
| > > update and delete rows? I'd like to use getinsertcommand,
| > getupdatecommand,
| > > getdeletecommand methods, but I can't figure out how to call a method
to
| > > execute them and pass the databound information to the insert, update
| > > commands. Here's my sample code. Thank you for any help.
| > >
| > > daver
| > >
| > >
| > >
| > > Dim SqlConn As SqlConnection
| > > Dim SqlCMD As SqlCommand
| > > Dim SqlDA As SqlDataAdapter
| > > Dim SqlCmdBldr As SqlCommandBuilder
| > >
| > >
| > > SqlConn = New SqlConnection("Server=test;database=Dave;Integrated
| > > Security=SSPI")
| > > SqlCMD = New SqlCommand("Select name from namelist", SqlConn)
| > > SqlDA = New SqlDataAdapter(SqlCMD)
| > > SqlCmdBldr = New SqlCommandBuilder(SqlDA)
| > >
| > > Dim sqlString As String
| > > Dim ds As New DataSet
| > > Dim dt As New DataTable
| > >
| > > SqlDA.Fill(ds, "namelist")
| > > dt = ds.Tables(0)
| > >
| > > txtName.DataBindings.Add("text", dt, "Name")
| > >
| > > Try
| > > sqlString = "Insert into Namelist ( name ) values (" & txtName.Text &
| ")"
| > > SqlCMD = New SqlCommand(sqlString, SqlConn)
| > > SqlConn.Open()
| > > SqlCMD.CommandType = CommandType.Text
| > > SqlCMD.ExecuteNonQuery()
| > > SqlConn.Close()
| > >
| > >
| > > Catch exp As SqlException
| > > MsgBox(exp.Message, MsgBoxStyle.Critical)
| > > End Try
| > >
| > >
| > >
| >
| >
|
|
|
 
Back
Top