If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion
Is it possible to use insert, update and delete commands all in the same code? How could this be done?
Printable View
If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion
Is it possible to use insert, update and delete commands all in the same code? How could this be done?
Did you even bother to read my posts in this thread?Quote:
Originally Posted by AC1982
I have read the posts and I tried implementing the code but I came up with an error. It was due to a mistake in my Update sql query which I have now fixed. Thanks for creating this thread and writing about ado.net parameters on your blog the information has proven very helpful.Quote:
Originally Posted by jmcilhinney
For future reference, if you are having a particular problem with a piece of code, it would make sense to ask a question about it in the main forum, rather than in the CodeBank, which is intended for code samples. By all means reference a thread like this to say that you are using this code, but posting it in the main forum means that everyone who visits the forum are more likely to see it, and you will get more responses. In addition, if you are getting an error/exception in your code, remember to tell people about that in your post, including the stack trace. This is how people will be able to help you.Quote:
Originally Posted by AC1982
Gary
You can also just put brackets "[]" around your columns to avoid this. I pretty much do this all the time now.Quote:
Originally Posted by venuspcs
I am trying to use this for what I assumed was a relatively simple insert into query, taking the values from a DataTable, but I am encountering issues. Any advise if you have time would be gratefully received - I assume by now you are sick of the questions and pleas for help :)
My Post
I have based my code off of the following by jmci which is on page 1 of this thread:
Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:vb.net Code:
Here is my code:
vb.net Code:
I currently have a pair of text boxes and a Go/Cancel button on my form. Ultimately it will be a combo box and a "access code" box where parents select their name and type in a code. Once the person is authenticated, they will then be able to check in/check out their child and/or simply unlock the door. I am having a problem with my VB code talking to the MS Access database. I keep getting the error on the line "Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()" that it "Could not find file 'C:\Documents and Settings\Joey.JPNET.000\Local Settings\Application Data\Temporary Projects\WindowsApplication1\bin\Debug\Daycare.mdb'." This is because I am using the newer .aacdb format database and not the older .mdb format database. How do I format it such that it connects to the correct database which is actually located in that folder?
Thank you for your help!
~Joey
Very very nice data you've provided.
Hi John. I don't understand thisCode:Using connection As New SqlConnection("connection string here")
Using adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
connection)
Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
connection)
insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
adapter.InsertCommand = insert
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim table As New DataTable
'Retrieve the schema.
adapter.FillSchema(table, SchemaType.Source)
'Add the new rows to the DataTable, e.g.
Dim row As DataRow = table.NewRow()
row("Name") = someName
row("Quantity") = someQuantity
row("Unit") = someUnit
table.Rows.Add(row)
'Save the changes.
adapter.Update(table)
End Using
End Using
. Isn't there any way, in which i don't use this line of code and insert multiple values? Please guide me.Code:adapter.missingschema........
The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema. You can build the DataTable schema yourself and not call FillSchema and, either way, you can set the PrimaryKey of the DataTable yourself. If you're going to call Fill or FillSchema though, you may as well let it set the PrimaryKey for you to, which it will do if you set MissingSchemaAction to AddWithKey.Quote:
Originally Posted by ADQUSIT
Do you simply mean that this property automatically adds PK to the DataTable? Am I right?:confused:Quote:
The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema
And What is Schema John?
Do you need to use a connection string if you link the DB with the server explorer?
If MissingSchemaAction is set to Add then, when calling Fill, any columns that exist in the result set of the query will be created in the DataTable if they don't already exist, but the PrimaryKey property of the DataTable will not be set. Using AddWithKey will also set the PrimaryKey property to the same column(s) as the database if the query involves only one table and includes that table's PK column(s).Quote:
Originally Posted by ADQUSIT
Schema is basically the structure of the database, in this case the number of columns, their names and data types, etc.
The only way to connect to a database is with a connection string. If you use the Server/Database Explorer then that's where the connection string comes from. Select a data connection in the Server/Database Explore and open the Properties window and you'll see the connection string for that connection.Quote:
Originally Posted by awweather
How would one save a data from a bound data table to SQL Server CE, using ADO.NET?
I havent worked with Sql Server Compact much at all, but I noticed there there is no equivalent to SQLDbType, so I am having trouble defining parameters.
Using SQL Server CE is pretty much exactly as I've shown above, as it is for pretty much any data source when using ADO.NET. You need to add the SQL Server CE ADO.NET provider, which you do by referencing the System.Data.SqlServerCe.dll assembly. The relevant classes can then be found in the System.Data.SqlServerCe namespace. When working with parameters, you use the exact same System.Data.SqlDbType enumeration to specify the data type as you do for SQL Server.Quote:
Originally Posted by AC1982
Dear JMC,
I am fairly new to updating, inserting and deleting data from a datagridview.
After reading this topic I have build the following code:
I call the savedata() and getData() from 2 different buttons.Code:Dim sql As String = String.Format("SELECT R.Datum,R.Chauffeur_ID,Wagen_ID,R.Beginstand,R.Eindstand,R.Liter,R.Dieselkoeling,R.Stops,R.StopsDachser,R.BrutoUren,R.Maut,R.Eurovignet " & _
"FROM tblRIT AS R ")
Private connection As New OleDbConnection(_Connectionstring)
Private adapter As New OleDbDataAdapter(sql, connection)
Private table As New DataTable
Private Sub InitialiseDataAdapter()
' tblRIT has a combined key [Datum, Chauffeur_ID, Wagen_ID]
Dim delete As New OleDbCommand("DELETE FROM tblRIT WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
Dim insert As New OleDbCommand("INSERT INTO tblRIT (Datum, Chauffeur_ID, Wagen_ID) VALUES (@Datum, @Chauffeur_ID, @Wagen_ID)", Me.connection)
Dim update As New OleDbCommand("UPDATE tblRIT SET Datum = @Datum, Chauffeur_ID = @Chauffeur_ID, Wagen_ID = @Wagen_ID WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
delete.Parameters.Add("@Datum", OleDbType.Date, "Datum")
delete.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
delete.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
insert.Parameters.Add("@Datum", OleDbType.Date, "Datum")
insert.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
insert.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
update.Parameters.Add("@Datum", OleDbType.Date, "Datum")
update.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
update.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
Me.adapter.DeleteCommand = delete
Me.adapter.InsertCommand = insert
Me.adapter.UpdateCommand = update
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub GetData()
'Retrieve the data.
Me.adapter.Fill(Me.table)
dgvRitInfo.DataSource = Me.table
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
End Sub
Private Sub SaveData()
'Save the changes.
Me.adapter.Update(Me.table)
End Sub
When I press the button getData the datagridview gets filled with the correct data.
But when I update data and I click the saveData button I get the error message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
But when I insert data and I click the saveData button I get the error message: "Update requires a valid InsertCommand when passed DataRow collection with modified rows."
But when I delete data and I click the saveData button I get the error message: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."
How should I solve this problem?
I am a little bit lost.
Those errors mean that the InsertCommand, UpdateCommand and DeleteCommand of your data adapter haven't been set, which means that you must not be calling your InitialiseDataAdapter method.Quote:
Originally Posted by bodylojohn
Should I call the InitialiseDataAdapter method on each butten click event?
You should call the method when you need to to do what it does. What does it do? It initialises the data adapter. When do you need to initialise the data adapter? That's when you should call it.Quote:
Originally Posted by bodylojohn
jmcilhinney,
I've read entire article many times and I'm still facing troubles with doing inserts. What I have is a stored procedure in Oracle and doing exactly what you proposed doesn't insert a record. I don't know If I'm declaring parameters wrong or something else, and my error is quite bad - "ORA-00900", which means wrong SQL statement and you can't figue out what is wrong. My debugger shows me nothing so I really can't even tell If my values are passed or not. I only know that procedure executes with no errors from Oracle directly - and It inserts record too.
this is my ORACLE procedure:
And this is my VB.NET code, starting from your example:Code:CREATE OR REPLACE PROCEDURE MYSCHEMA.UPDATE_TABLE (
CHOOSE_SELECT_IN IN NUMBER,
ID_INSTALLATIONS_IN IN MYSCHEMA.INSTALLATIONS.ID_INSTALLATIONS%TYPE,
ID_TABLE1_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE1_FK%TYPE,
ID_TABLE2_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE2_FK%TYPE,
DESCRIPTION_IN IN MYSCHEMA.INSTALLATIONS.DESCRIPTION%TYPE)
IS
BEGIN
CASE CHOOSE_SELECT_IN
WHEN 1 THEN
INSERT INTO MYSCHEMA.INSTALLATIONS (ID_INSTALLATIONS,
ID_TABLE1_FK,
ID_TABLE2_FK,
DESCRIPTION)
VALUES (ID_INSTALLATIONS_IN,
ID_TABLE1_FK_IN,
ID_TABLE2_FK_IN,
DESCRIPTION_IN);
END CASE;
END UPDATE_TABLE;
/
I initialize Datadapter when It get's filled with data. And when I try to insert using SaveData I get error I mentioned. What is wrong ? Looking at Parameters.Add method you have columns like this (parameterName,OracleDBType, Size, srcColumnName). I don't know what size is for, I tried to set It same as my field size are in DB, but no effect. What I would probably need is to send corresponding values of fields from Datatable to stored procedure parameters, but with exception of CHOOSE_SELECT_IN = 1 which tells procedure what Query needs to be executed. Please help If you know how to solve this.Code:Private Sub InicialiseDataAdapter()
Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)
insert.CommandType = CommandType.StoredProcedure
insert.Parameters.Add(New OracleParameter("CHOOSE_SELECT_IN", OracleDbType.Decimal, 1, ParameterDirection.Input))
insert.Parameters.Add(New OracleParameter("ID_INSTALLATIONS_IN", OracleDbType.Decimal, 4, "ID_INSTALLATIONS"))
insert.Parameters.Add(New OracleParameter("ID_TABLE1_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE1_FK"))
insert.Parameters.Add(New OracleParameter("ID_TABLE2_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE2_FK"))
insert.Parameters.Add(New OracleParameter("DESCRIPTION_IN", OracleDbType.NVarchar2, 5, "DESCRIPTION"))
Me.adapter.InsertCommand = insert
Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
End Sub
Private Sub SaveData()
'Save the changes.
Me.adapter.Update(table)
End Sub
I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.Quote:
Originally Posted by LuckyLuke82
I'm sorry, I forgot to add that in my post, I edited It now. I have that allready, but It doesn't work either. I'm not sure what I do wrong. Only thing that comes in my mind is that I fill DataAdapter from different stored procedure. My steps regarding this are (in Load):Quote:
I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.
EDIT:Code:Using cmd As New OracleCommand("MY_ANOTHER_STORED_PROCEDURE", Myconn)
cmd.CommandType = CommandType.StoredProcedure
'Adding parameters for stored procedure here
...
'Assigning adapter Select command and display data in Datagridview
adapter.SelectCommand = cmd
adapter.Fill(table)
InicialiseDataAdapter()
My_DGV.DataSource = table
'I accept changes because rows get manually changed by me during binding - I set different values for 1 row
table.AcceptChanges()
End Using
Looks like I fixed It. It works now, but I'm not sure for how long. What I did is that I end edit in Datagridview first (My_DGV.EndEdit) and now It saves into DB. However I have another problem in one different Datagridview- I need to save changes only for rows where certain cell has value and ignore rows other even If It's added row, or modified - how can I do that ? Loop through Datagridview rows or datatable rows ?
This thread is not intended to be a place that anyone can ask any question related to ADO.NET. You should start a new thread dedicated to this new specific topic.Quote:
Originally Posted by LuckyLuke82
Ok, no problem.