How to push data from datatable to database

[Bhavna]

Hello

I want to push the data in my datatable into my database. How must i do this? I have been told to create a insert query. I have done this and have a select statement below it to retrieve the Identity value
What i want to ask is how i add my insertCommand to the dataadapter

I keep getting a error when i click my 'Submit' button..
Update requires a valid InsertCommand when passed DataRow collection with new rows

Im Sure my coding is wrong cos i was trying to play around to see if i could fluke it :o

This is my coding..

Private Sub btnConfirmBooki ng_Click(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnConfirmBooki ng.Clic

Dim cmdInsRenter As New SqlCommand("Ins RenterAndSelIde ntity", cn
cmdInsRenter.Co mmandType = CommandType.Sto redProcedur

'declare the parameter
cmdInsRenter.Pa rameters.Add("@ Lname", SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ Fname", SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ Addr", SqlDbType.Char, 300
cmdInsRenter.Pa rameters.Add("@ RenterPostcode" , SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ RenterPhoneNo", SqlDbType.Char, 25

'now set the value
cmdInsRenter.Pa rameters("@Lnam e").Value = txtLname.Tex
cmdInsRenter.Pa rameters("@Fnam e").Value = txtFname.Tex
cmdInsRenter.Pa rameters("@Addr ").Value = txtAddr.Tex
cmdInsRenter.Pa rameters("@Rent erPostcode").Va lue = txtRenterPcode. Tex
cmdInsRenter.Pa rameters("@Rent erPhoneNo").Val ue = txtRenterPhoneN o.Tex

Tr
cn.Open(
daRenter.Update (Mydataset.Tabl es("MyDataTable ")
cmdInsRenter.Ex ecuteNonQuery(

Catch x As Exceptio
MsgBox(x.Messag e
Finall
cn.Close(
End Tr
End Su

plz can someone help to modify my coding
Thank u in advance

[Cor]

Re: How to push data from datatable to database

Hi Bhavna,

The dataAdapter update needs
a Select command
a Update command
a Delete command
a Insert command

Did you use the IDE to make your dataadapter or did you do it by hand?

It is difficult to answer you now.

Cor

[Bhavna]

Re: How to push data from datatable to database

Hello Cor,

I created the dataadapter by hand. I did not use the IDE.
The only thing i wrote for it was its declaration.

Private daRenter As SqlDataAdapter

Is there more i need to do?

[Cor]

Re: How to push data from datatable to database

> I created the dataadapter by hand. I did not use the IDE.[color=blue]
> The only thing i wrote for it was its declaration.
>
> Private daRenter As SqlDataAdapter
>
> Is there more i need to do?[/color]

A lot or maybe not.

First you have to decide if you use a dataadpater or a single insert.
But let supose you choise for the datadapter.

I tell you only some higlights, you have to do some search for yourself I, I
never did those things with a stored procedure, I asume that your stored
procedure is a select.


Dim cmdInsRenter As New SqlCommand("Ins RenterAndSelIde ntity", cn)
cmdInsRenter.Co mmandType = CommandType.Sto redProcedure

'declare the parameters
cmdInsRenter.Pa rameters.Add("@ Lname", SqlDbType.Char, 50)
cmdInsRenter.Pa rameters.Add("@ Fname", SqlDbType.Char, 50)
cmdInsRenter.Pa rameters.Add("@ Addr", SqlDbType.Char, 300)
cmdInsRenter.Pa rameters.Add("@ RenterPostcode" , SqlDbType.Char, 50)
cmdInsRenter.Pa rameters.Add("@ RenterPhoneNo", SqlDbType.Char, 25)

You do not have to add the values to the dataadapter but to the datatable if
you use a dataadapter and you can (if you do not want to write them by hand)
use the commanbuilder to make the insert, delete and update from to your
dataadapter.

But first add the command to the dataadapter, I think you did not do it
seeing your code below.
daRenter = new SQLdataadapter( cmdInsRenter)

I do not know if it will go with a stored procedure but you can try,
otherwise I would first skip the stored procedure and use a normal select
and than change the program back when it works to with a stored procedures

cmd = new SQLcommandbuild er(daRenter)



Try
--------------------
cn.Open()
You can do it, but the datadapter does it also for you
--------------------
daRenter.Update (Mydataset.Tabl es("MyDataTable "))
-----------------------------------------
cmdInsRenter.Ex ecuteNonQuery()
this one above you do not need with the dataadapter, you have connected the
command to it as I did above.
---------------------------------------

Catch x As Exception
MsgBox(x.Messag e)
Finally
cn.Close()
End Try
End Sub

I hope this brings you a little bit on the route?

Cor

[Bhavna,]

Re: How to push data from datatable to database

When u say that u assume my SPROC is a select, do u mean that it is a Select statment
My SPROC i created is a Insert query that i use to push the added rows in my datatable to my database

CREATE PROCEDURE dbo.InsRentAndS elIdentit

@Lname char(50)
@Fname char(50)
@Address varchar(300)
@Postcode varchar(50)
@PhoneNo varchar(25

A
SET NOCOUNT ON

INSERT INTO Rent(Lname, Fname, Addr, RentPostcode, RentPhoneNo) VALUES (@Lname, @Fname, @Address, @Postcode, @PhoneNo)
SELECT Lname, Fname, Addr, RentPostcode, RentPhoneNo, RentID FROM Rent WHERE (RentID = @@IDENTITY)

RETURN

Is the the easiest way to do this

I also do not know what u meant by the following statemnet..
"You do not have to add the values to the dataadapter but to the datatable if
you use a dataadapter and you can (if you do not want to write them by hand
use the commanbuilder to make the insert, delete and update from to you
dataadapter.

What do i add to the datatable instead if the Dataadapter

And are u saying that if the stored procedure does not work i should use the ide to create all the commands via the commandBuilder
Is there no way to do it manually by code

I added the one line u suggested to my code but it still states that i need to create a valid insert command!

This is the code i tried

Private Sub btnConfirmBooki ng_Click(ByVal sender As System.Object, ByVal e As System.EventArg s) Handles btnConfirmBooki ng.Clic

Dim cmdInsRenter As New SqlCommand("Ins RenterAndSelIde ntity", cn
cmdInsRenter.Co mmandType = CommandType.Sto redProcedur

'declare the parameter
cmdInsRenter.Pa rameters.Add("@ Lname", SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ Fname", SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ Addr", SqlDbType.Char, 300
cmdInsRenter.Pa rameters.Add("@ RenterPostcode" , SqlDbType.Char, 50
cmdInsRenter.Pa rameters.Add("@ RenterPhoneNo", SqlDbType.Char, 25

'now set the value
cmdInsRenter.Pa rameters("@Lnam e").Value = txtLname.Tex
cmdInsRenter.Pa rameters("@Fnam e").Value = txtFname.Tex
cmdInsRenter.Pa rameters("@Addr ").Value = txtAddr.Tex
cmdInsRenter.Pa rameters("@Rent erPostcode").Va lue = txtRenterPcode. Tex
cmdInsRenter.Pa rameters("@Rent erPhoneNo").Val ue = txtRenterPhoneN o.Tex

daRenter = new SQLdataadapter( cmdInsRenter

Tr
cn.Open(
daRenter.Update (Mydataset.Tabl es("MyDataTable ")

Catch x As Exceptio
MsgBox(x.Messag e
Finall
cn.Close(
End Tr
End Su

[Cor]

Re: How to push data from datatable to database

Hi Bhavna,

Can you show me with a link to MSDN where you got this code?
,[color=blue]
>
>
> 'now set the values
> cmdInsRenter.Pa rameters("@Lnam e").Value = txtLname.Text
> cmdInsRenter.Pa rameters("@Fnam e").Value = txtFname.Text
> cmdInsRenter.Pa rameters("@Addr ").Value = txtAddr.Text
> cmdInsRenter.Pa rameters("@Rent erPostcode").Va lue =[/color]
txtRenterPcode. Text[color=blue]
> cmdInsRenter.Pa rameters("@Rent erPhoneNo").Val ue =[/color]
txtRenterPhoneN o.Text

To update a dataset?

I never saw it

Cor