473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to push data from datatable to database

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 btnConfirmBooking_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirmBooking.Clic

Dim cmdInsRenter As New SqlCommand("InsRenterAndSelIdentity", cn
cmdInsRenter.CommandType = CommandType.StoredProcedur

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

'now set the value
cmdInsRenter.Parameters("@Lname").Value = txtLname.Tex
cmdInsRenter.Parameters("@Fname").Value = txtFname.Tex
cmdInsRenter.Parameters("@Addr").Value = txtAddr.Tex
cmdInsRenter.Parameters("@RenterPostcode").Value = txtRenterPcode.Tex
cmdInsRenter.Parameters("@RenterPhoneNo").Value = txtRenterPhoneNo.Tex

Tr
cn.Open(
daRenter.Update(Mydataset.Tables("MyDataTable")
cmdInsRenter.ExecuteNonQuery(

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

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

Jul 21 '05 #1
5 7933
Cor
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
Jul 21 '05 #2
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?
Jul 21 '05 #3
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?


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("InsRenterAndSelIdentity", cn)
cmdInsRenter.CommandType = CommandType.StoredProcedure

'declare the parameters
cmdInsRenter.Parameters.Add("@Lname", SqlDbType.Char, 50)
cmdInsRenter.Parameters.Add("@Fname", SqlDbType.Char, 50)
cmdInsRenter.Parameters.Add("@Addr", SqlDbType.Char, 300)
cmdInsRenter.Parameters.Add("@RenterPostcode", SqlDbType.Char, 50)
cmdInsRenter.Parameters.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 SQLcommandbuilder(daRenter)

Try
--------------------
cn.Open()
You can do it, but the datadapter does it also for you
--------------------
daRenter.Update(Mydataset.Tables("MyDataTable"))
-----------------------------------------
cmdInsRenter.ExecuteNonQuery()
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.Message)
Finally
cn.Close()
End Try
End Sub

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

Cor
Jul 21 '05 #4
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.InsRentAndSelIdentit

@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 btnConfirmBooking_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirmBooking.Clic

Dim cmdInsRenter As New SqlCommand("InsRenterAndSelIdentity", cn
cmdInsRenter.CommandType = CommandType.StoredProcedur

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

'now set the value
cmdInsRenter.Parameters("@Lname").Value = txtLname.Tex
cmdInsRenter.Parameters("@Fname").Value = txtFname.Tex
cmdInsRenter.Parameters("@Addr").Value = txtAddr.Tex
cmdInsRenter.Parameters("@RenterPostcode").Value = txtRenterPcode.Tex
cmdInsRenter.Parameters("@RenterPhoneNo").Value = txtRenterPhoneNo.Tex

daRenter = new SQLdataadapter(cmdInsRenter

Tr
cn.Open(
daRenter.Update(Mydataset.Tables("MyDataTable")

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


Jul 21 '05 #5
Cor
Hi Bhavna,

Can you show me with a link to MSDN where you got this code?
,


'now set the values
cmdInsRenter.Parameters("@Lname").Value = txtLname.Text
cmdInsRenter.Parameters("@Fname").Value = txtFname.Text
cmdInsRenter.Parameters("@Addr").Value = txtAddr.Text
cmdInsRenter.Parameters("@RenterPostcode").Value = txtRenterPcode.Text cmdInsRenter.Parameters("@RenterPhoneNo").Value =

txtRenterPhoneNo.Text

To update a dataset?

I never saw it

Cor
Jul 21 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: VMI | last post by:
We have this huge application that's based on storing tons of data on a dataTable. The only problem we're having is that storing LOTS of data (1 million records) into a datatable will slow down the...
6
by: Robert Schuldenfrei | last post by:
Dear NG, I have done all I can do with Crystal Reports (CR) using the "pull" mode. I now need to generate data in my application, load a DataSet with that data, and "push" this on to a CR...
13
by: Leszek Taratuta | last post by:
Hello, I have several drop-down lists on my ASP.NET page. I need to keep data sources of these lists in Session State. What would be the most effective method to serialize this kind of data...
5
by: Bhavna | last post by:
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...
4
by: JIM.H. | last post by:
Hello, I am trying to write the data I got from a web service to my table in SQL Server I need to append the dataset wsDS to the dataset ds and do update. PVS.myWS.Loader load = new...
9
by: Anil Gupte | last post by:
After reading a tutorial and fiddling, I finally got this to work. I can now put two tables created with a DataTable class into a DataRelation. Phew! And it works! Dim tblSliceInfo As New...
6
Cintury
by: Cintury | last post by:
Hi all, I've developed a mobile application for windows mobile 5.0 that has been in use for a while (1 year and a couple of months). It was developed in visual studios 2005 with a back-end sql...
2
by: chike_oji | last post by:
Please can someone help me. I am writing a web application, that allows for the upload of an excel sheet into the database. I have an upload button and a save button. The upload button allows...
2
by: ashish1985s | last post by:
using System.IO; using System.Data; using System.Collections; using System.Configuration; using System.Xml; using System.Data.SqlClient; using System; using log4net; using log4net.Config;
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.