How do I use DataBinding and SQLDataAdapter to Insert a record?

[Ranny]

Question: What is the process to Insert a record using Dataset and SQLDataAdapter?

Explanation: Thanks to Cor I am able to get a record to load, modify it, and save it
to the database. My current problem is one of my fields is NOT NULL and a primary
key. I want to be able to open the form with the fields blank (new record).
My current thinking is:

'Create the SQL Connection, DataAdapter, and DataSet
mySQLConnection = New SqlConnection("server=myserver;database=Northwind;Uid=userid;Pwd=userpwd;")
mySLQDataAdapter = New SqlDataAdapter("select * from Employees", mySQLConnection)
mySLQDataAdapter.TableMappings.Add("Employees", "Employees")
dim myDataSet as new DataSet
myDataSet.ReadXMLSchema("NorthwindEmployees.xsd")

dim sqlCBuilder as SqlCommandBuilder = New SqlCommandBuilder(mySLQDataAdapter)
mySLQDataAdapter.DeleteCommand = sqlCBuilder.GetDeleteCommand()
mySLQDataAdapter.InsertCommand = sqlCBuilder.GetInsertCommand()
mySLQDataAdapter.UpdateCommand = sqlCBuilder.GetUpdateCommand()


At this point, I believe I have connected to the SQL Server, created a DataAdapter
with the sequel "select * from Employees", created an instance of a DataSet, and
loaded the structure of the DataSet with a XML Schema file. I have not Filled the
DataSet from the Adapter yet. If I try to Databind to the window forms controls
I will get an error. (One of the controls is a label with the EmployeeID for display.

txtEmployeeID.DataBindings.Add(New Binding("Text", myDataSet, "Employees.EmployeeID"))
txtLastName.DataBindings.Add(New Binding("Text", myDataSet, "Employees.LastName"))
txtFirstName.DataBindings.Add(New Binding("Text", myDataSet, "Employees.FirstName"))
dtDOB.DataBindings.Add(New Binding("Value", myDataSet, "Employees.BirthDate"))
txtTitle.DataBindings.Add(New Binding("Text", myDataSet, "Employees.TitleOfCourtesy"))


I would like to have the form come up empty. The user would fill in the form. I
would then call

mySLQDataAdapter.Update(myDataSet,"Employees")

and the record would be added. I could then do something (?) to clear the
form and they could add another record.

Would someone set me straight on the correct sequence of events.

Thanks
Ranny



User submitted from AEWNET (http://www.aewnet.com/)

 

[Ken Tucker [MVP]]

Hi,

http://msdn.microsoft.com/library/d...ndowsformscurrencymanagerclassaddnewtopic.asp

Ken
---------------------
Question: What is the process to Insert a record using Dataset and
SQLDataAdapter?

Explanation: Thanks to Cor I am able to get a record to load, modify it,
and save it
to the database. My current problem is one of my fields is NOT NULL and a
primary
key. I want to be able to open the form with the fields blank (new record).
My current thinking is:

'Create the SQL Connection, DataAdapter, and DataSet
mySQLConnection = New
SqlConnection("server=myserver;database=Northwind;Uid=userid;Pwd=userpwd;")
mySLQDataAdapter = New SqlDataAdapter("select * from Employees",
mySQLConnection)
mySLQDataAdapter.TableMappings.Add("Employees", "Employees")
dim myDataSet as new DataSet
myDataSet.ReadXMLSchema("NorthwindEmployees.xsd")

dim sqlCBuilder as SqlCommandBuilder = New
SqlCommandBuilder(mySLQDataAdapter)
mySLQDataAdapter.DeleteCommand = sqlCBuilder.GetDeleteCommand()
mySLQDataAdapter.InsertCommand = sqlCBuilder.GetInsertCommand()
mySLQDataAdapter.UpdateCommand = sqlCBuilder.GetUpdateCommand()


At this point, I believe I have connected to the SQL Server, created a
DataAdapter
with the sequel "select * from Employees", created an instance of a DataSet,
and
loaded the structure of the DataSet with a XML Schema file. I have not
Filled the
DataSet from the Adapter yet. If I try to Databind to the window forms
controls
I will get an error. (One of the controls is a label with the EmployeeID
for display.

txtEmployeeID.DataBindings.Add(New Binding("Text", myDataSet,
"Employees.EmployeeID"))
txtLastName.DataBindings.Add(New Binding("Text", myDataSet,
"Employees.LastName"))
txtFirstName.DataBindings.Add(New Binding("Text", myDataSet,
"Employees.FirstName"))
dtDOB.DataBindings.Add(New Binding("Value", myDataSet,
"Employees.BirthDate"))
txtTitle.DataBindings.Add(New Binding("Text", myDataSet,
"Employees.TitleOfCourtesy"))


I would like to have the form come up empty. The user would fill in the
form. I
would then call

mySLQDataAdapter.Update(myDataSet,"Employees")

and the record would be added. I could then do something (?) to clear the
form and they could add another record.

Would someone set me straight on the correct sequence of events.

Thanks
Ranny



User submitted from AEWNET (http://www.aewnet.com/)