Update database from datatable when using INNER JOIN in SelectCommand

 Hi EveryBody,

 I've searched the net and this forum for my particular problem but can't find my exact problem.

I want to update the database from a datatable. The datatable is the result of a join on the database.

 
I can't seem to use the dataadapter.update method because it gives me an error that a certain column doesn't exist.

 
This is because when I use the INNER JOIN, the datatable consists of the 2 joined tables from the query

When I execute the query in SQLServer it just gives me one table.

My question is how do I get rid of the second, joined, table so i can use the update command.

I'm already using it for one table and that goes fine. it's just the joined table that gives me errors 

 

Here is my Code:

 

1    		strSQLBoekingen = "SELECT * FROM Boekingen INNER JOIN Huizen on Huizen.HuisID = Boekingen.HuisID where TypenNr = '"typenr"
2

3 Dim SqlConnection As New SqlConnection("
connection")
4
5 Dim daBoekingen As New SqlDataAdapter(strSQLBoekingen, SqlConnection)
6 Dim daTypePeriode As New SqlDataAdapter(strSql, SqlConnection)

7 daTypePeriode.Fill(dsTypePeriode, "
TypePeriode")
8 daBoekingen.Fill(dtBoekingen)
9 dsTypePeriode.Tables.Add(dtBoekingen)
10
11 dsTypePeriode = updateBoeking(dsTypePeriode, distRow("
maxxResId"), dsBeschikbaar) ' updates the dataset
12
13 Dim cbSQL As New SqlCommandBuilder(daTypePeriode)
14 SqlConnection.Open()
15 daTypePeriode.Update(dsTypePeriode.Tables(0))
16 dtBoekingen = dsTypePeriode.Tables(1)

17 Dim command As New SqlCommand("
UPDATE Boekingen SET StatusCode = @StatusCode, StatusCodeChange = @StatusCodeChange WHERE ReserveringsNr = @ReserveringsNr", SqlConnection)
18 command.Parameters.Add("
@ReserveringsNr", SqlDbType.Int, 20, "ReserveringsNr")
19 command.Parameters.Add("
@StatusCode", SqlDbType.NVarChar, 1, "StatusCode")
20 command.Parameters.Add("
@StatusCodeChange", SqlDbType.DateTime, 10, "StatusCodeChange")

21 daBoekingen.UpdateCommand = command
22 command.ExecuteNonQuery()
23 SqlConnection.Close()
24 dsTypePeriode.Tables.Remove("
Typeperiode")
25 dsTypePeriode.Tables.Remove(dtBoekingen)
 
 
0
Bolletrie
11/9/2007 10:18:52 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

2 Replies
493 Views

Similar Articles

[PageSpeed] 21

that's because you're using a sqlcommandbuilder that can't support query with joins.

 You can always assign the insertcommand/updatecommand/deletecommand of your adapter with the query you want and not use sqlcommandbuilder

0
rumbafum
11/9/2007 12:40:57 PM

I used the explanation in this post http://forums.asp.net/t/926260.aspx that way i could still use the update method.

So far it works

However I still have a question.  

Do you always get both tables in the resulting datatable when doing a join in SQL???

0
Bolletrie
11/9/2007 12:56:18 PM
Reply:

Similar Artilces:

updating database from datatable using sqlbulkcopy
Hi, I have a scenario whereby I've created a datatable which is bound to a gridview. This datatable is then populated with data entered by a user via input controls in the footer of the the gridview. Once the user has finished populating the datatable I want to upload this information to an existing table in my database (PricesTable). Needless to say it's not working and i'm struggling with the code to enable the upload to my database table. Any assitance with this would be greatly appreciated. Thanks in advance. Here are my methods:protected void BindGridView() { //De...

Updating database using ADO.NET
Hi,  I'm trying to update a gridview using ADO.NET  on  GridView1_SelectedIndexChanged event With the code below, I'm able to update all the fields on Column "eval_completed", but I want only one cell ( checkbox ) , the one on the Selected row to be updated ( checked ).  protected void GridView1_SelectedIndexChanged(object sender, EventArgs e) {          SqlConnection con = new SqlConnection("Data Source=W2BZS1VV01;Initial Catalog=Alpha;Integrated Security=True"); SqlCommand cmd = new SqlComma...

How to update Database (Datasource) using DataAdapter ? ?
Hi, I have a datagrid in a WEB FORM which is populated from Dataset & DataAdapter. The grid has a "Edit" & "Cancel/Delete/Update" buttons. My question is, upon user editing of a row from the grid he may choose "Update or Delete" button. upon each changes done by user, I don't want to update the database (datasource)instanlty and bind the data with datagrid. I want to update the datasource upon user clicking of another button (save all the changes) which is available on WEB FORM. In windows forms, I normally apply "DataAdapter.Update"...

Record is not updated in the Database when i am using datatable
hi all, Can u pls guide how to insert the record to the database using DataTable (Oracle DataAdapter). It will be very useful for me if any answer. DataTable dt = new DataTable(); OracleCommand cmd = new OracleCommand("Select COUNTRY_CODE,COUNTRY_NAME from T_ID_RAC_COUNTRY_MASTER",((OracleConnection)Session["con"]),tx); OracleDataAdapter myAdap = new OracleDataAdapter(cmd); myAdap.Fill(dt); DataRow myRow = dt.NewRow(); myRow[0] = "MX"; myRow[1] = "Mexico"; dt.Rows.Add(myRow); dt.AcceptCh...

Updating DataTable in DataBase using SqlDataAdapter
Hi GuysI am facing a problem while updating a DataTable to database using sqldataadapter.In my application I am fetching a dataTable from Database and filling values into textboxes on the UI.User has given facility to change or add new texboxes (new row) on the fly .(Textboxes on the UI are like in a row(tr) having two textboxes in each row.)I am again then converting a new (empty) datatable from scrap and filling its rows with the value of textboxes on submit button event.The datatable which I have created has the same schema as the database table.Now what I want here is that changed value ...

Updating SQL Database
I am quite new to ASP.net 2.0. I have had plenty of experience using ADO.net in standard windows applications. In my app I am opening a connection to an SQL database and I am creating a DataTable without a DataSet: Shared m_cnADONetConnection As New System.Data.SqlClient.SqlConnection Shared m_daDataAdapter As System.Data.SqlClient.SqlDataAdapter Shared m_rowPosition As Integer Shared m_dtContacts As New System.Data.DataTable I am initializing everything and filling my DataTable when the Page first Loads if it isnt a postback.Protected Sub Form1_Load(ByVal sender As Object, ByVal ...

Using joins in SelectCommand and Updates/Deletes
I'm reading through the tutorials and while it mentions that the auto-generated Update and Delete commands basically won't work if you joins in your select command, I don't want to write a SQL statement with sub selects because of the inefficiency of such queries. When writing the select query with a join, I had to manually add the update and delete queries, but it looks like I also have to add a overloaded update and delete functions that accept datarows as parameters. Any ideas on how I can do this, or are there any thoughts on other approaches? Hi, i am in the same situation and am cur...

Help with Gridview update using object datasource
I have a grid view that is using an object datasource. They code for both is posted below.When i try to do an update I get the following error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@Description any idea what this means and what I need to do to fix it?Here is the objectDatasource code:  1 <asp:ObjectDataSource ID="IncidentDataSource" runat="server" 2 OldValuesParameterFormatString="original_{0}" SelectMethod="GetIncidents" 3 UpdateMethod="Upda...

Inner join between two DataTable using C#
 I have 2 datatables like below and I want to create the third table using LINQ. The key of both is ID. I want to join the two datatables so that I have all the records of the first table (left join).Can someone help me figure this out.   FirstDataTable - Table 1 ID      Type            Col1            Col2            ----      ---------        ...

Generic database updates using ADO.NET
I am relatively new to ASP.NET and am looking for some advice re using .NET to update a database In classic ASP I could use ADO 2.5 to create a generic database update routine. I used it to save forms data to a database table- it works roughly like this Form data is submitted to a Database Update routine which 1) Opens a recordset based on the table to update 2) invokes the ADO .Addnew method for the rs 3) loops through all the fields on the form using request.forms.fields.count and matches each form entry with a database field name    (the database field names must match the ...

Trouble Updating Database Using Command Object
************* Edited by moderator Adec *************** Inserted missing < code></ code> tags. Always include such tags when including code in your postings. Don't force the moderators to do your work. Many readers disregard postings without the code tags. ************************************************** Hello, I'm trying to use a collection of textboxes to add a new entry to an access database using OleDB and C#, but I keep getting the following error: -------------------------------------- The field is too small to accept the amount of data you att...

Updating with Formview Cntrols using Object DataSource
Hi AllI am trying to update a database from a Formview control in ASP.NET using an ObjectDataSource control as my business Object. The select method works fine but when I attempt to perform an update operation, I get the following error message.<===================================================================================ObjectDataSource 'MovieDetails' could not find a non-generic method 'UpdateMovies' that has parameters: movieName, ReleaseDate, synopsis, rating, language, filmColor, ratingID, locationCity, locationCountry, categoryID, filmCompany, movieID, editorRe...

Any examples about using Object Database with ADO.NET(c#)
Can I have any examples source codes about using Object Database with ADO.NET(c#) Is this a particular db or Object DB vs Relation DB? If the latter here is one link I found <a href = "http://www.informit.com/content/index.asp?product_id=%7BA2B9405F-6B83-4474-B034-BC613B68EB1B%7D">http://www.informit.com/content/index.asp?product_id=%7BA2B9405F-6B83-4474-B034-BC613B68EB1B%7D</a>. You might want to also check the threads on the Architecture forum on O/R mappers like Entity Broker, Norpheme etc. HTHEric LandesMADNUG President http://www.madnug.netCrystal Alliance Edit...

Displaying status of database update in a form using objects.
Hello Gurus,I'm new to .Net and i've been working on a website, I'm trying to use the best practices where possible (at least what I believe to be the best ) and so I've been using Objects throughout rather than manipulating low level data rows and the like. I'm hoping that this will make it easier for me to expand the application in the future. In the application, I've created a simple form for creating a new Contact object which is then added to the database. It functions correctly, but upon clicking the submit button there's no feedback to give an indication to...

Web resources about - Update database from datatable when using INNER JOIN in SelectCommand - asp.net.object-datasource

myAdapter.InsertCommand = InsertCmd(); myAdapter.InsertCommand.UpdatedRowSource - Pastebin.com
myAdapter.InsertCommand = InsertCmd();myAdapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.None;myAdapter.DeleteCommand = ...

Welcome to Rollette.COM
Rov, Submarine, PVC, camera, underwater, lens, builge pump, Remote operated vehicle, Sea, ROV, video

Resources last updated: 12/26/2015 9:56:20 AM