DataSource Controls :: Retrieving Identity Value After Using DataAdapter.Update?
Jul 29, 2010I am not sure how to do this. I would like to merge the identity back into the dataset.
View 2 RepliesI am not sure how to do this. I would like to merge the identity back into the dataset.
View 2 RepliesI am getting a 0 in the PKey field. Any ideas?
[Code]....
i am having a datatable with 10 rows.
Out of this 10 rows 3 Rows have their RowState as Added, 2 rows as Modified, and 4 as Deleted.
Now i want to use Datadapter's InsertCommand, UpdateCommand and DeleteCommand
Is it possible to update datatable's changes to Database with Single DataAdpater.Update(DataTable) Call.
Means i will call DataAdapter.Update(DataTable) method only once and with this all new added rows should be inserted in database, modified rows should be updated and deleted rows should be deleted from Database.
1. As per my knowledge we use dataadapters/dataset when we want to deal with the huge amount of column/data. Is there anyway that if I want to update one or two columns in a table without using dataadapter/dataset?
2. There is a way that without clicking a button we can get the data using objectdatasource, I mean to say that there is no vb.net data in the code behind then how to debug if there is not data in vb.net in code behind.
How could I add parameters on dataadapter? I tried
[Code]....
But parameters is unknown for dataadapter.
public static DataSet GetSubCategories(int category_id)
View 4 Repliesi'm tring to read data from a sql DB, where is email == mystring - then display its name here is the code:
string mystring;
mystring = "email@email.com";//"Welcome " + System.Convert.ToString(Session["user"]);
generalTableTableAdapters.kurtula_generalTableAdapter generals = new
generalTableTableAdapters.kurtula_generalTableAdapter();
generalTable.kurtula_generalDataTable genaraltab = generals.Getgeneral();
foreach (DataRow row in genaraltab.Rows)
{
if (Convert.ToString(row["email"])== user)
{
Label1.Text = System.Convert.ToString(row["name"]);
}
}
as you can see I'm a table adapter but is giving me the following error: i realy dont know what this error means - though i did a simular action using access DB (and it did work), where i had to user the following: using System.Data.OleDb; now because im using SQL DB im using: using System.Data.SqlClient; //sql server
Im trying to do the batchinsert (bulk) in sqldataadapter using dataset.but somehow its not inserting (even no error as well), i dunno where i went wrong.my coding..
Dim _cmd As New SqlCommand
My code is not updating the sql database. I am definately passing the correct information in to this function, and it runs without errors. If I exec the procedure with the passed in data, it works great. Can someone tell me what is missing?
public DataTable UpdateEmailList(int listId, string connectionString, DataTable EmailList)
{
//DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(null, conn);
SqlCommand cmd = null;
//add
cmd = new SqlCommand("dbo.fp_ServiceAwards_AddEmailToList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@emailList_id", SqlDbType.Int)).Value = listId;
cmd.Parameters.Add(new SqlParameter("@email_address", SqlDbType.NVarChar, 255, "email_address"));
cmd.Connection = conn;
da.InsertCommand = cmd;
//delete
cmd = new SqlCommand("dbo.fp_ServiceAwards_DeleteEmailFromList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@emailList_id", SqlDbType.Int)).Value = listId;
cmd.Parameters.Add(new SqlParameter("@email_address", SqlDbType.NVarChar, 255, "email_address"));
cmd.Connection = conn;
da.DeleteCommand = cmd;
try
{
da.Update(EmailList);
}
catch (Exception ex)
{
string msg = ex.Message;
}
conn.Close();
conn.Dispose();
return EmailList;
}
Title says it all. What is the difference between a DataSource and DataAdapter? I'm trying to decide if I should descend from IDataSource or IDataAdapter for code to be shared between Winforms and WebForms and I can't seem to figure out what the difference is other than slightly different ways to use them.
View 1 RepliesI want to issue an INSERT command for an SQL Server table using DataAdapter without first issuing a SELECT command. Could anybody send me lines of code to handle this? Also how do i manage INSERT into selective table columns (I have 10 columns but i only want to update 2 of them)?
View 2 RepliesI've been self teaching SQL and asp.net and i'm a little stuck. I have a stored proc for the insert and then in my Insert inside my class it's not returning the ID after the insert as I am receiving an error message that NULL value has cannot be inserted. I want to add default values to a seperate table after the insert. I will provide my code below, if anyone can help me why the value is not going across?
[Code]....
Resetting IDENTITY column to a new valueDuring application development we always entry some test data also our tester test with test data and as a result if the table contains IDENTITY columns( specially ID column) which incremented to the number of data already entered.
However, when the application goes running with real data to the client and the client want his ID values from starting 1, then we need to RESEED the value of the IDENTITY column of the table after clearing all data. This can be done as bellows:
DBCC CHECKIDENT (TableName, RESEED, 0)
However, can be reseed with any values instead of zero(0) in the above statement.
I have an identity column that increment by 1 on every insert. I have deleted all the rows using delete query but the identity coulumn is still not reseting to zero, how do i reset it, as it was when the table was first created.
View 4 RepliesI have created small database prods. I have created one table Products. I have given columns like productid, productname, price. after creating table. I got an idea to add identity to productid ie., auto increment. how to add identity to existing column.
View 5 RepliesI have been searching on here for many hours and read many posts but for some reason I am being slow and cannot figure this out. I am writing a record and need to get the identity field back. I have tried many combinations based on what I have read but the below code is where I am sitting at now. it has appdatasource.ExecuteScalar() underlined and the error says 'ExecuteScalar' is not a member of 'System.Web.UI.WebControls.SQLDataSource'. I would be very grateful if someone could point out what I am missing to get this to work.
Protected Sub SubmitApp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitApp.Click
Dim appdatasource As New SqlDataSource
appdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToString
appdatasource.InsertCommandType = SqlDataSourceCommandType.Text
appdatasource.InsertCommand = "INSERT INTO APPLICATION (FirstName,MiddleName,LastName,AcceptTerms,IPv4Address,DateInserted) VALUES (@FirstName,@MiddleName,@LastName,@IPv4Address,@DateInserted);select @@SCOPE_IDENTITY"
appdatasource.InsertParameters.Add("FirstName", fname.Text)
appdatasource.InsertParameters.Add("MiddleName", minit.Text)
appdatasource.InsertParameters.Add("LastName", lname.Text)
appdatasource.InsertParameters.Add("DateInserted", DateTime.Now())
appdatasource.InsertParameters.Add("IPv4Address", Request.UserHostAddress.ToString)
Dim appid As Integer = appdatasource.ExecuteScalar()
End Sub
I'm trying to insert data into two tables that are related within a store procedure. After the first insert I want to get the id of the inserted row to use as a parameter during the insertion of data in the other table. I'm sure this is a common task, and I have looked into things like scope_identity(), @@identity and ident_current. However I'm not sure exactly how to use the id as a parameter.My attempt now looks like:
DECLARE @RETURN_VALUE
INSERT INTO Table1(test_name) VALUES ('asdf')
@RETURN_VALUE = SCOPE_IDENTITY()
INSERT INTO Table2(test_name) VALUES(@RETURN_VALUE)
I was wondering how I could use user.identity in a where statement in sqldatasource to return results for a user profile. I already have a table that stores the username. Just need to return it's results.
View 5 RepliesI have one table it's contain multiple columns and one of them is
[Call_Ref_No] [numeric](18, 0) IDENTITY(1,1) NOT NULL
In this column I am facing issue is some time its missing the identity value... For example
1
2
3
5
6
8
9
Here in this example 4 and 7 is missing...I could not able to find these two reference in my table so what I can do? How I can stop this occurrence.
The scenario is that I wanna insert a record to the database via INSERT query and I need to user the identity number of this record in the INSERT query.
But I don't know how to achieve this.
Here is my table structure and code for your better understanding.
There are three columns in the Table: categoryID, categoryName, categoryPath. I need the path based on the categoryID which is an identity int column.
Now I used this code to do INSERT:
[Code]....
I am trying to get the identity column on which the record is inserted but i couldn't using stored procedure
see
[Code]....
My Code Using ASP.NET C# i am trying to display the identity in textbox
[Code]....
http://forums.asp.net/p/1625331/4177299.aspx#4177299?Is+Identity%3f+in+table+definition+not+available
I tried all that was suggested in the above link..but I just cannot see the Identity data type option in the drop down. How can I set one field as a Identity data type if I dont see it?
The wierd thing is this was working fine and then it just started throwing this error,
"Cannot insert explicit value for identity column in table 'Reports' when IDENTITY_INSERT is set to OFF."
Here is the code:
[Code]....
I have a detailsView that I use for Insert. Once a record is Inserted, I need to get the new idenetity value.
Is there some function that grabs the inserted identity value? Or do I need to query it somehow?
By the way, the reason I'm doing this is the user is inserting a record into a 'general' list that is being applied to a 'specific' list - example, an employee can select a vendor from a list, but the vendor isn't listed so the employee adds the vendor in the vedor list and while the new vendor is inserted, the vendor info is also applied to the new order record.
I have an SQL Server table named "Fields" with an autoincrement identity column "FieldID" as the primary key, an nchar column "Phrase" that textbox entries are to be stored in from a web page, and another column that is defaulted to null. The following is the recommended example on how to use Scope_Identity() to get a newly inserted table row identity value:
//C#
string query = "Insert Into Categories (CategoryName) Values (@CategoryName);" +
"Select Scope_Identity()";
int ID;
string connect = @"Server=.SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" +
"Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CategoryName", Category.Text);
conn.Open();
ID = (int)cmd.ExecuteScalar();
}
}
Obviously, this example uses a Categories Table in a Northwind database, which I do not have. So I am confused by how to adapt this example for my application. The following is my data context:
DataClassesDataContext db = new DataClassesDataContext();
Could you explain how I can adapt the example for my "Fields" table in my "EFMDB" database?