How To Write To Database From DAtaSets

[Guest]

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)

 

[W.G. Ryan eMVP]

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.

 

[Guest]

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

 

[CJ Taylor]

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

 

[Cor Ligthert]

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)