By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,579 Members | 1,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,579 IT Pros & Developers. It's quick & easy.

How To Write To Database From DAtaSets

P: n/a
Hello All,

Iam trying to ADD NEW RECORDS to my address book. but iam with an error,
when i call the FILL method of the SqlDataAdaptor. Looking 4 assistance.

The code is as follows

TIA.
-------------------------------
Private Sub Btn_Add_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Btn_Add.Click

With mDaAddresses
.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = Cn
.CommandText = "Insert into
addresses(contact_id,contact_name,"
.CommandText += " address01, address02, email, group_id)
values(@cid,@cname,@add1,@add2,@email,@gid)"
With .Parameters
.Add("@cid", SqlDbType.VarChar, 10, "Contact_Id")
.Add("@cname", SqlDbType.VarChar, 40, "Contact_Name")
.Add("@add1", SqlDbType.VarChar, 20, "Address01")
.Add("@add1", SqlDbType.VarChar, 20, "Address02")
.Add("@email", SqlDbType.VarChar, 20, "email")
.Add("@gid", SqlDbType.VarChar, 10, "Group_Id")
End With
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "Addressess")
.MissingSchemaAction = MissingSchemaAction.AddWithKey
End With
mDaAddresses.Fill(mDsInsert)

Dim mDataRow As DataRow
mDataRow = mDsInsert.Tables("Addresses").NewRow()
mDataRow("contact_id") = Trim(Lbl_Contact.Text)
mDataRow("contact_name") = Trim(UCase(Txt_CName.Text))
mDataRow("address01") = Trim(Txt_Add01.Text)
mDataRow("address02") = Trim(Txt_Add02.Text)
mDataRow("email") = Trim(Txt_Email.Text)
mDataRow("group_id") = Trim(Cmb_Grps.SelectedIndex)

mDsInsert.Tables("addresses").Rows.Add(mDataRow)
mDaAddresses.Update(mDsInsert)
Nov 21 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You're calling .Fill and haven't specified a SelectCommand unless it's
somewhere else that I can't see. You need a SelectCommand to call .Fill.

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Sincere" <Si*****@discussions.microsoft.com> wrote in message
news:82**********************************@microsof t.com...
Hello All,

Iam trying to ADD NEW RECORDS to my address book. but iam with an error,
when i call the FILL method of the SqlDataAdaptor. Looking 4 assistance.

The code is as follows

TIA.
-------------------------------
Private Sub Btn_Add_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Btn_Add.Click

With mDaAddresses
.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = Cn
.CommandText = "Insert into
addresses(contact_id,contact_name,"
.CommandText += " address01, address02, email, group_id)
values(@cid,@cname,@add1,@add2,@email,@gid)"
With .Parameters
.Add("@cid", SqlDbType.VarChar, 10, "Contact_Id")
.Add("@cname", SqlDbType.VarChar, 40, "Contact_Name")
.Add("@add1", SqlDbType.VarChar, 20, "Address01")
.Add("@add1", SqlDbType.VarChar, 20, "Address02")
.Add("@email", SqlDbType.VarChar, 20, "email")
.Add("@gid", SqlDbType.VarChar, 10, "Group_Id")
End With
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "Addressess")
.MissingSchemaAction = MissingSchemaAction.AddWithKey
End With
mDaAddresses.Fill(mDsInsert)

Dim mDataRow As DataRow
mDataRow = mDsInsert.Tables("Addresses").NewRow()
mDataRow("contact_id") = Trim(Lbl_Contact.Text)
mDataRow("contact_name") = Trim(UCase(Txt_CName.Text))
mDataRow("address01") = Trim(Txt_Add01.Text)
mDataRow("address02") = Trim(Txt_Add02.Text)
mDataRow("email") = Trim(Txt_Email.Text)
mDataRow("group_id") = Trim(Cmb_Grps.SelectedIndex)

mDsInsert.Tables("addresses").Rows.Add(mDataRow)
mDaAddresses.Update(mDsInsert)

Nov 21 '05 #2

P: n/a
hello Ryan

As per your suggestion I've used the Select command, but to no avail, can u
plz provide some code and a bit of explanation too.

the code is as follows
TIA
------
Private Sub Btn_Add_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Btn_Add.Click

With mDaAddresses
.SelectCommand() = New SqlCommand
With .SelectCommand
.CommandText = "select * from addresses"
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "Addressess")
.MissingSchemaAction = MissingSchemaAction.AddWithKey

.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = Cn
.CommandText = "Insert into
addresses(contact_id,contact_name,"
.CommandText += " address01, address02, email, group_id)
values(@cid,@cname,@add1,@add2,@email,@gid)"
With .Parameters
.Add("@cid", SqlDbType.VarChar, 10, "Contact_Id")
.Add("@cname", SqlDbType.VarChar, 40, "Contact_Name")
.Add("@add1", SqlDbType.VarChar, 20, "Address01")
.Add("@add2", SqlDbType.VarChar, 20, "Address02")
.Add("@email", SqlDbType.VarChar, 20, "email")
.Add("@gid", SqlDbType.VarChar, 10, "Group_Id")
End With
End With
End With
mDaAddresses.Fill(mDsInsert)

Dim mDataRow As DataRow
mDataRow = mDsInsert.Tables("Addresses").NewRow()
mDataRow("contact_id") = Trim(Lbl_Contact.Text)
mDataRow("contact_name") = Trim(UCase(Txt_CName.Text))
mDataRow("address01") = Trim(Txt_Add01.Text)
mDataRow("address02") = Trim(Txt_Add02.Text)
mDataRow("email") = Trim(Txt_Email.Text)
mDataRow("group_id") = Trim(Cmb_Grps.SelectedIndex)

mDsInsert.Tables("addresses").Rows.Add(mDataRow)
mDaAddresses.Update(mDsInsert)
End Sub
Nov 21 '05 #3

P: n/a
Fill is used to fill a dataset with information from a database

Update is the method to write dataset data to the database using an
implementation of IDataAdapter (i.e. OLEDataAdapter, SQLDataAdapter, etc..)

You have to set up your UpdateCommand and InsertCommands to add data to the
database, as it checks the RowState property of a row in a dataset to
determine which command to use.

HTH,
CJ

"Sincere" <Si*****@discussions.microsoft.com> wrote in message
news:F3**********************************@microsof t.com...
hello Ryan

As per your suggestion I've used the Select command, but to no avail, can u plz provide some code and a bit of explanation too.

the code is as follows
TIA
------
Private Sub Btn_Add_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Btn_Add.Click

With mDaAddresses
.SelectCommand() = New SqlCommand
With .SelectCommand
.CommandText = "select * from addresses"
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "Addressess")
.MissingSchemaAction = MissingSchemaAction.AddWithKey

.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = Cn
.CommandText = "Insert into
addresses(contact_id,contact_name,"
.CommandText += " address01, address02, email, group_id)
values(@cid,@cname,@add1,@add2,@email,@gid)"
With .Parameters
.Add("@cid", SqlDbType.VarChar, 10, "Contact_Id")
.Add("@cname", SqlDbType.VarChar, 40, "Contact_Name")
.Add("@add1", SqlDbType.VarChar, 20, "Address01")
.Add("@add2", SqlDbType.VarChar, 20, "Address02")
.Add("@email", SqlDbType.VarChar, 20, "email")
.Add("@gid", SqlDbType.VarChar, 10, "Group_Id")
End With
End With
End With
mDaAddresses.Fill(mDsInsert)

Dim mDataRow As DataRow
mDataRow = mDsInsert.Tables("Addresses").NewRow()
mDataRow("contact_id") = Trim(Lbl_Contact.Text)
mDataRow("contact_name") = Trim(UCase(Txt_CName.Text))
mDataRow("address01") = Trim(Txt_Add01.Text)
mDataRow("address02") = Trim(Txt_Add02.Text)
mDataRow("email") = Trim(Txt_Email.Text)
mDataRow("group_id") = Trim(Cmb_Grps.SelectedIndex)

mDsInsert.Tables("addresses").Rows.Add(mDataRow)
mDaAddresses.Update(mDsInsert)
End Sub

Nov 21 '05 #4

P: n/a
Sincere,

Bill does not like this one, however with your code I see no problem at all.

I changed it inline, that goes wrong with joins and with more than 100
datafields or more complex things.

I hope this helps?

Copr
Private Sub Btn_Add_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Btn_Add.Click

With mDaAddresses
.SelectCommand() = New SqlCommand
With .SelectCommand
.CommandText = "select * from addresses"
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "Addressess")
.MissingSchemaAction = MissingSchemaAction.AddWithKey

mDaAddresses.Fill(mDsInsert)
dim cmb as new SQLCommandbuilder(mDaAddresses)
Dim mDataRow As DataRow
mDataRow = mDsInsert.Tables("Addresses").NewRow()
mDataRow("contact_id") = Trim(Lbl_Contact.Text)
mDataRow("contact_name") = Trim(UCase(Txt_CName.Text))
mDataRow("address01") = Trim(Txt_Add01.Text)
mDataRow("address02") = Trim(Txt_Add02.Text)
mDataRow("email") = Trim(Txt_Email.Text)
mDataRow("group_id") = Trim(Cmb_Grps.SelectedIndex)

mDsInsert.Tables("addresses").Rows.Add(mDataRow)
mDaAddresses.Update(mDsInsert)
End Sub

Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.