473,741 Members | 5,161 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Full dataset into an empty SQL table

EMW
Hi,

I managed to create a SQL server database and a table in it. The table is
empty and that brings me to my next chalenge:

How can I get the info in the table in the dataset to go in an empty SQL
table?

Is there a short way like the FILL method to get data into the dataset or do
I have to read each datarow in the table and write it one at the time to the
database?

rg,
Eric
Nov 20 '05 #1
22 4231
Cor
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder ", otherwise you have a lot of
work to do).

I hope this helps?

Cor

I managed to create a SQL server database and a table in it. The table is
empty and that brings me to my next chalenge:
How can I get the info in the table in the dataset to go in an empty SQL
table?
Is there a short way like the FILL method to get data into the dataset or do I have to read each datarow in the table and write it one at the time to the database?

Nov 20 '05 #2
EMW
I'm currently trying things with update, but it seems to me I'm updating the
dataset (thus losing the info).

"Cor" <no*@non.com> schreef in bericht
news:O0******** ******@TK2MSFTN GP10.phx.gbl...
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder ", otherwise you have a lot of
work to do).

I hope this helps?

Cor

I managed to create a SQL server database and a table in it. The table is empty and that brings me to my next chalenge:
How can I get the info in the table in the dataset to go in an empty SQL
table?
Is there a short way like the FILL method to get data into the dataset
or do
I have to read each datarow in the table and write it one at the time to

the
database?


Nov 20 '05 #3

The update command will do you no good at this point. You
must use the Add function first to add data to your form
before you can update it.

Below is a snippet of code that shows you must have a
valid SQL Select statement first, open your recordset and
then add to it. Do not forget to declare your recordset
or you will have problems.

This is the code for an add button event to add a new
user to a table.

SQL = "SELECT * FROM tusers WHERE UserID = '" & Trim
(txtFName.Text) & Trim(txtLName.T ext).Substring( 0, 1)
& "'"
Rs.Open(SQL, Your_Connection .RETURN_CONNECT ION,
ADODB.CursorTyp eEnum.adOpenDyn amic,
ADODB.LockTypeE num.adLockBatch Optimistic)
If Rs.EOF Then
Rs.AddNew()
Call SetDataUser()
Rs.UpdateBatch( )
MsgBox("Record has been added.")
Else
MsgBox("UserID has already been taken. Please
create a new one by changing the first name of the user.
This will help create a unique ID for that person.")
Rs.Close()
Exit Sub
End If
Rs.Close()
Always close your recordset after using it to make sure
all updates occur and you do not accidentally crash your
program.

The Call SetDataUser() calls a subroutine that assigns
each field to it's corresponding one in the table like
below:
Private Sub SetDataUser()

Dim myString As String = txtLName.Text
Dim myChar As Char
myChar = myString.Chars( 0)

Rs.Fields(0).Va lue = txtFName.Text & myChar
Rs.Fields(1).Va lue = txtLName.Text
Rs.Fields(2).Va lue = txtFName.Text
Rs.Fields(4).Va lue = cmboBoxDept.Sel ectedItem
If cmboboxbackup.S electedItem = "" Then
Rs.Fields(5).Va lue = "No back-up"
Else
Rs.Fields(5).Va lue =
cmboboxbackup.S electedItem
End If

End Sub

Hope this helps
-----Original Message-----
I'm currently trying things with update, but it seems to me I'm updating thedataset (thus losing the info).

"Cor" <no*@non.com> schreef in bericht
news:O0******* *******@TK2MSFT NGP10.phx.gbl.. .
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder ", otherwise you have a lot of
work to do).

I hope this helps?

Cor
>
> I managed to create a SQL server database and a table in it. The table
is > empty and that brings me to my next chalenge:
> How can I get the info in the table in the dataset
to go in an empty SQL > table?
> Is there a short way like the FILL method to get
data into the datasetor
do
> I have to read each datarow in the table and write

it one at the time to the
> database?


.

Nov 20 '05 #4
Cor
Hi EMW,

I think know maybe what you mean. But I make a scenario.

You want to put the structure from one database table in your dataset, put
data in that dataset and write it in your database.

That is Fill, even if it is completly empty because in that way you get your
structure
with "Select * from database table" (Maybe a lazy methode, but for me it
works).

You want to add rows.

Most simple is
dataset.tables( 0).rows.add(dat aset.tables(0). newrow)

Or
dr as newrow = dataset.tables( 0).newrow
dr("item1") = "something"
dr("item2") = "somethinge lse"
dataset.tables( 0).rows.add(dr)

To update that dataset use the Update with the same select.
This is real code but see it as pseudo because it is not the nicest

\\\
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand(sqlS tr, Conn)
da.SelectComman d = cmd
Dim cb As SqlCommandBuild er = New SqlCommandBuild er(da)
da.Update(datas et)
////

This are of course samples, to bring you on the route, you have to check all
things yourself for the details.

If hope this brings you more on the route?

Cor




Nov 20 '05 #5
Cor
Two typos I see direct
with "Select * from database table" Select * from databasetable"
dr as newrow = dataset.tables( 0).newrow

dr as datarow = dataset.tables( 0).newrow
Nov 20 '05 #6
EMW
Hi Cor,

I got this before I read your reply, but it still doesn't work quite well.

Here is my code:

Dim con As New SqlConnection
Dim sqlDa As SqlDataAdapter
Dim dr As DataRow
Dim mycmd As SqlCommand
Dim sqlNewstr As String
con.ConnectionS tring = "Server=(local) ;Database=Sitel ist;Integrated
Security=SSPI;"
sqlDa = New SqlDataAdapter
sqlDa.SelectCom mand = New SqlCommand("SEL ECT * FROM sites", con)
Dim cb As SqlCommandBuild er = New SqlCommandBuild er(sqlDa)
con.Open()
Dim dstoo As New DataSet
sqlDa.Fill(dsto o, "Sites") 'connect the dataset to the empty table
Dim dsT As DataTable
dsT = dstoo.Tables(0) 'handle to the empty table
Dim ab
For ab = 1 To ds.Tables(0).Ro ws.Count - 1
dr = dsT.NewRow()
dr(1) = ds.Tables(0).Ro ws(ab)(1)
dr(2) = ds.Tables(0).Ro ws(ab)(2)
dr(3) = ds.Tables(0).Ro ws(ab)(3)
dr(4) = ds.Tables(0).Ro ws(ab)(4)
dr(5) = ds.Tables(0).Ro ws(ab)(5)
dr(6) = ds.Tables(0).Ro ws(ab)(6)
dr(7) = ds.Tables(0).Ro ws(ab)(7)
dr(8) = ds.Tables(0).Ro ws(ab)(8)
dr(9) = ds.Tables(0).Ro ws(ab)(9)
dr(0) = ds.Tables(0).Ro ws(ab)(0)
dsT.Rows.Add(dr ) 'add the new rows to the table
Next
dstoo.Tables(0) .AcceptChanges( ) 'save the changes to the table in the
dataset
Try
sqlDa.Update(ds too, "sites") 'update the database with the contents of
the dataset
Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try
con.Close()
No exceptions or error, but the table in the database remains empty, so
there must be something wrong...

ds is the dataset holding the info and what I do here is copy each row into
the new dataset which is linked to the database.

I hope you can help me with this.

thanks in advance,
Eric

"Cor" <no*@non.com> schreef in bericht
news:uW******** ******@tk2msftn gp13.phx.gbl...
Hi EMW,

I think know maybe what you mean. But I make a scenario.

You want to put the structure from one database table in your dataset, put
data in that dataset and write it in your database.

That is Fill, even if it is completly empty because in that way you get your structure
with "Select * from database table" (Maybe a lazy methode, but for me it
works).

You want to add rows.

Most simple is
dataset.tables( 0).rows.add(dat aset.tables(0). newrow)

Or
dr as newrow = dataset.tables( 0).newrow
dr("item1") = "something"
dr("item2") = "somethinge lse"
dataset.tables( 0).rows.add(dr)

To update that dataset use the Update with the same select.
This is real code but see it as pseudo because it is not the nicest

\\\
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand(sqlS tr, Conn)
da.SelectComman d = cmd
Dim cb As SqlCommandBuild er = New SqlCommandBuild er(da)
da.Update(datas et)
////

This are of course samples, to bring you on the route, you have to check all things yourself for the details.

If hope this brings you more on the route?

Cor



Nov 20 '05 #7
Cor
Hi EMW,

I did not want to answer anymore tonight, but you see I did, it is almost
ready I think.
Dim dsT As DataTable
dsT = dstoo.Tables(0) 'handle to the empty table

With that I thought you are doing nothing

if sqlDa.haschange s then
sqlDa.Update(ds too.getchanges, "sites")
end if
dstoo.Tables(0) .AcceptChanges( )

'save the changes to the table in the dataset, so you can go on with the
data in the dataset and when you go on, the next time the updates are only
the changes after this accept changes :-))

Quick typed again
Nov 20 '05 #8
Cor
> if sqlDa.haschange s then
if dstoo.haschange s
Nov 20 '05 #9
EMW
Thanks!!

I won't keep you awake anymore.......t onight.. ;)

rg,
Eric
"Cor" <no*@non.com> schreef in bericht
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..
if sqlDa.haschange s then

if dstoo.haschange s

Nov 20 '05 #10

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

Similar topics

3
4607
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. I'm getting some strange results depending the input XML file I use. I was wondering if somebody could help me understand what is going on or point me to a good reference. The code for my program looks like this:
1
2001
by: John | last post by:
Hi All, When I delete the last record from my dataset and then WriteXML() to the file; the Table itself, in the xml file, is removed. I need to prevent the table structure from being deleted if there are no more records to be written to the XML file from the Dataset. Any ideas here, John.
6
19054
by: Mike P | last post by:
I have written a simple web service that basically takes a value input by a user and returns a dataset with all related data found in a database. In my client app use this code to get the data from the web service : Dataset dsData = ws.wsmethod(parameter); And whether any data is returned or not, I don't get an error, so I need to test if the dataset is empty or not to see if any data has been
3
1736
by: martin | last post by:
Hi, I am having trouble determining if my dataset is empty. I have function that populates a dataset from a database and then retuens it. If the database contains no data then the dataset will be completely empty, and then when I go to databind() it to a combo box and error will be throw. What I whould like to do is check the dataset from code to see if it is empty.
4
4210
by: dtblankenship | last post by:
Hello everyone, I know this question has been asked many times in the forums, and after spending a few days reading, I am still confused as to the answer. I have a ListBox (lstBox), SqlConnection (sqlConnection), SqlDataAdapter (daLookupData), SqlDataAdapter (daData), DataSet (dsLookupData), and DataSet (dsData), all created via the IDE during design-time. Here is the design of my simple tables: (An example since I can't post my...
1
986
by: Mirna | last post by:
Hello, My problem is the following: If one of the tables in the Dataset is empty, the command "Page.Databind" is giving me this error : "INDEX 0 IS NOT NON-NEGATIVE AND BELOW TOTAL ROWS COUNT" for example: if i have a textbox bounded to "bnd_field" in the table "tbl_child"
5
7426
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data objects to do all this. My only problem now is navigating through the data. I can get the data into a datagrid without any problems, but I want the data to show up in textboxes and use some sort of move next, move previous, move last, etc (like in VB6) command to navigate the data (using...
5
2113
by: John | last post by:
Hi all, I'm sorry I'm reposting this but the original was urgent and I do need closure on this. I'm calling a stored proc which does 4 "selects" and then I populate a dataset looping through the dr using a dr.NextResult() but the stored proc may not return any results for one or more Select stetments. When this happens, a table is not returned so the dataset.Fill() does not populate conatin 4 tables which then each bind to a grid -...
2
2048
by: Shum | last post by:
Hi! i have a question.. I'm filling a dataset from a table in which some rows are empty, i dont want those empty records to be filled in the dataset.. Does any one know how to restrict it to only non empty records.. ??
0
8794
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9342
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9272
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9211
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8218
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6756
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.