Re: Retrieving and Saving Data in Databases

[killer7k]

Hi John ,

as in title it's saving to database

so my question if is there a way to save an xml file to database
i mean we have a valid xml let's say coming from dataset writexml
& i want to import that file to be used (saved) on another database

Thanks m8 your help much appreciated

[jmcilhinney]

Hi John ,

as in title it's saving to database

so my question if is there a way to save an xml file to database
i mean we have a valid xml let's say coming from dataset writexml
& i want to import that file to be used (saved) on another database

Thanks m8 your help much appreciated

Call DataSet.ReadXml, loop through every row of every table and call SetAdded, then save the data as shown above.

[killer7k]

Call DataSet.ReadXml, loop through every row of every table and call SetAdded, then save the data as shown above.

Hi John ,

i never saw an example using SetAdded
do you have anyone ?

Thanks

[jmcilhinney]

Hi John ,

i never saw an example using SetAdded
do you have anyone ?

Thanks

Look first, ask questions later.

http://social.msdn.microsoft.com/Sea...=setadded&ac=8

[killer7k]

Hi John ,

i saw those link but nothing really helpful
here where i am but not working

ds.ReadXml(uploadfile)
 DataGridView1.DataSource = ds.Tables(txtname.Text)

 ds.Tables(txtname.Text).AcceptChanges()


        Dim dtrow As DataRow
        For Each dtrow In ds.Tables(txtname.Text).Rows
            dtrow.SetAdded()
        Next
        da.Update(ds.Tables(txtname.Text))

Thanks

[jmcilhinney]

Hi John ,

i saw those link but nothing really helpful
here where i am but not working

ds.ReadXml(uploadfile)
 DataGridView1.DataSource = ds.Tables(txtname.Text)

 ds.Tables(txtname.Text).AcceptChanges()


        Dim dtrow As DataRow
        For Each dtrow In ds.Tables(txtname.Text).Rows
            dtrow.SetAdded()
        Next
        da.Update(ds.Tables(txtname.Text))

Thanks

What SetAdded does is set the RowState of a DataRow to Added, which it needs to be if that row is to be acted upon by the InsertCommand of a DataAdapter. Does your DataAdapter have an InsertCommand? How did you create it, explicitly or with a CommandBuilder?

[killer7k]

What SetAdded does is set the RowState of a DataRow to Added, which it needs to be if that row is to be acted upon by the InsertCommand of a DataAdapter. Does your DataAdapter have an InsertCommand? How did you create it, explicitly or with a CommandBuilder?

i didnt use DataAdapter to insert command but just to update the dataset
i have only an xml file which has data
so i want this data to be copied to dataset
so after i can query
i have a DatagridView filled by the xml file using readxml & i have a textbox which i can put the query what i want
so when i start the query there is an exception ...
yes because dataset is empty
when i asked you , you told me to use setAdded to save data
that what i have done but nothing

hope you understand what i mean

Thanks

[jmcilhinney]

i didnt use DataAdapter to insert command but just to update the dataset
i have only an xml file which has data
so i want this data to be copied to dataset
so after i can query
i have a DatagridView filled by the xml file using readxml & i have a textbox which i can put the query what i want
so when i start the query there is an exception ...
yes because dataset is empty
when i asked you , you told me to use setAdded to save data
that what i have done but nothing

hope you understand what i mean

Thanks

Yes, I understand but you still didn't answer my question.

Does your DataAdapter have an InsertCommand? How did you create it, explicitly or with a CommandBuilder?

Also, you said that the code is "not working" but that could mean anything. If an exception is thrown then give me the error message.

[killer7k]

i didnt use the DataAdapater
i just tought it may help
i clearly the situation

 Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 uploadfile = openDlg.FileName
            ds.ReadXml(uploadfile)
           

            DataGridView1.DataSource = ds.Tables(txtname.Text)
  ds.Tables(txtname.Text).AcceptChanges()


        Dim dtrow As DataRow
        For Each dtrow In ds.Tables(txtname.Text).Rows
            dtrow.SetAdded()
        Next

End Sub

   Private Sub btnrunquery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrunquery.Click

cmd = con.CreateCommand
        cmd.CommandText = txtquery.Text
        da.SelectCommand = cmd
        da.Fill(ds, txtname.Text)

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = txtname.Text
End Sub

[jmcilhinney]

Create a DataAdapter, create a Command, assign that to the adapter's InsertCommand property, call Update. That's all you have to do. How can you expect to insert data without an INSERT statement to do it?

[killer7k]

Create a DataAdapter, create a Command, assign that to the adapter's InsertCommand property, call Update. That's all you have to do. How can you expect to insert data without an INSERT statement to do it?

yes & how i will insert data ?
the data coming only from the xml file ..

[jmcilhinney]

yes & how i will insert data ?
the data coming only from the xml file ..

Read post #3. I created this thread in the first place so people could refer to existing examples and the same things wouldn't have to be repeated over and over.

[killer7k]

Read post #3. I created this thread in the first place so people could refer to existing examples and the same things wouldn't have to be repeated over and over.

John you get me wrong ...
it's not the problem that i dont know how to insert update or othere & you know me
the problem is how i can insert data from xml to dataset
it's not like wriitng in textbox & then insert those values...

i think you misunderstood me John

[jmcilhinney]

i think you misunderstood me John

I think so too. I thought you were already populating the DataSet by calling ReadXml. I'm afraid I don't really know what you're asking for. Sorry. Can you start at the beginning?

[killer7k]

of course John i will start from the beginning

Here you the situation in theory :
-I have data coming from an XML File ( From our parteners)
-so when i receive this File i have to do some query on it
-let's say an example :
-- I recieve the File which has : Customers(Customers_ID,Customers_Name,Customers_Email,Customers_DateComing)

-so once i recieve the File i can show his content on DataGridView using DataSet with ReadXML Method ' Until here all is ok & working

so next part which i have problem on
i have a textbox which save the query that i will use on the DataGridView content
basically here the problem :
so in normal time when i got data from database
i can query like this :

 Private Sub btnrunquery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrunquery.Click
ds.clear()
cmd = con.CreateCommand
        cmd.CommandText = txtquery.Text
'let's say i want to show customer that are new in this month 
        da.SelectCommand = cmd
        da.Fill(ds, "customers")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = customers
End Sub

this example is what i want really but dont work because i'am having data coming from that Xml file

-so how i will do that ith XML File ?

you said to me using setAdded ; i dont really know if it help in this situation or not
but i posted that code on last post but not working

hope you understand me now , if there is a part to explain more just tell me

Thanks

[jmcilhinney]

of course John i will start from the beginning

Here you the situation in theory :
-I have data coming from an XML File ( From our parteners)
-so when i receive this File i have to do some query on it
-let's say an example :
-- I recieve the File which has : Customers(Customers_ID,Customers_Name,Customers_Email,Customers_DateComing)

-so once i recieve the File i can show his content on DataGridView using DataSet with ReadXML Method ' Until here all is ok & working

so next part which i have problem on
i have a textbox which save the query that i will use on the DataGridView content
basically here the problem :
so in normal time when i got data from database
i can query like this :

 Private Sub btnrunquery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrunquery.Click
ds.clear()
cmd = con.CreateCommand
        cmd.CommandText = txtquery.Text
'let's say i want to show customer that are new in this month 
        da.SelectCommand = cmd
        da.Fill(ds, "customers")

        DataGridView1.DataSource = ds
        DataGridView1.DataMember = customers
End Sub

this example is what i want really but dont work because i'am having data coming from that Xml file

-so how i will do that ith XML File ?

you said to me using setAdded ; i dont really know if it help in this situation or not
but i posted that code on last post but not working

hope you understand me now , if there is a part to explain more just tell me

Thanks

I'm still not sure what you mean. Are you saying that you just want to filter the data you have in the DataSet so the user only sees a subset of it? If so then it's no wonder I was confused because that's really got nothing to do with the subject of this thread. In that case you bind your DataTable to a BindingSource and set its Filter property. Any controls bound to that BindingSource will only display the subset of the data that matches the specified criteria.

If that's what you want then it's not a data access issue; it's a data display issue and the question belongs in it's own thread, not this one that is dedicated to retrieving and saving data in databases.

[killer7k]

i dont know if wrong thread or not
but i just samply want to do query like select or update or delete to this data that are on the DataGridViex (the one coming from the xml file)

is that so hard to explain John ?

i just want to do query on DataGridView Data

i know it's simple but i dont know why you didnt undertand me

i post pics ?

Thanks

[jmcilhinney]

i know it's simple but i dont know why you didnt undertand me

Because the fact that you posted to a thread entitled "Retrieving and Saving Data in Databases" led me to believe that you wanted to retrieve or save data in a database. Silly me. You don't, so you shouldn't have posted to this thread in the first place. You should have created a new thread. I'm going to ask a moderator if they can split this conversation off into its own thread because:

A. that's where it belongs; and
B. it won't get in the way of people who are specifically interested in saving and retrieving data in databases.

[killer7k]

ok i'am sorry John

[si_the_geek]

Posts moved from this CodeBank thread to their own thread.

[jmcilhinney]

OK, I want to try to establish exactly what it is you want to do because it's still not clear to me. Your previous posts seem to be contradictory.

You have an XML file that you want to load into a DataSet by calling ReadXml. You then want to edit that data and save it. Is that all correct? If so:

1. Where di the file come from? Was it created by calling WriteXml or some other way?
2. What sort of editing do you want to do? Add records? Delete records? Change existing records?
3. Do you want to be able to show subsets of this data to the user? If so, how will the parameters be specified?
4. Where do you want to save the data? The same XML file? A database? Somewhere else?

[killer7k]

OK, I want to try to establish exactly what it is you want to do because it's still not clear to me. Your previous posts seem to be contradictory.

You have an XML file that you want to load into a DataSet by calling ReadXml. You then want to edit that data and save it. Is that all correct? If so:

1. Where di the file come from? Was it created by calling WriteXml or some other way?
2. What sort of editing do you want to do? Add records? Delete records? Change existing records?
3. Do you want to be able to show subsets of this data to the user? If so, how will the parameters be specified?
4. Where do you want to save the data? The same XML file? A database? Somewhere else?

Hi John

first i really appreciate your great help

1-yes coming from WriteXML

2-First here data coming from the xml file are displayed on a DataGridView using ReadXMl method of Dataset

i want to do a select only at the moment once it work i can get idea for others

but this query i will wrote it in a textbox manually something normal like this

select something from tablename where something=something

& once i run that query the DataGridView is updated with new result of this query

3-no need

4-save new result to a new XML File at the moment

Thanks

[jmcilhinney]

You can filter data in a DataTable by setting the RowFilter property of its DefaultView. You'd assign the "something = something" part to do that. There's no easy way to change the number of columns unless you use LINQ.

[killer7k]

You can filter data in a DataTable by setting the RowFilter property of its DefaultView. You'd assign the "something = something" part to do that. There's no easy way to change the number of columns unless you use LINQ.

hmm i see but there is no LINQ in 2005
something = something will show all columns...

what about to copy this Data That is coming from the xml to a Dataset
& then run query to that dataset ?

Thanks

[jmcilhinney]

You cannot query a DataSet. That's what LINQ does. If you don't have LINQ then you can't do it. You're just going to have to write your own code to create a new DataTable and add the columns yourself, then call Select on the original DataTable to get the matching rows and then copy the data across. It's a bit laborious but it's not difficult.

[killer7k]

i see the situation very well but i cant find a solution

John what can you suggest me to do ? of course only with 2005
anything just tell me some suggestion

Thanks

[jmcilhinney]

As I said, write the code yourself to transfer the data you want. Create a new DataTable, add the columns you want, call Select on the original DataTable, loop through the results and transfer only the column values that exist in the new table.