I'm trying to import a few hundreds of data to a SQL server database table from Excell Spreedsheet. In the taable, I created Id as key that is identity. How can I solve identity when I import excel data to the database table.
View 3 RepliesHow to create sql server table with unique random identity between 45365 - 5782155129452 ?
View 5 RepliesI have 3 tables. Blog and Tag have a many to many relationship. BlogTag is a junction table with a quantity column.
**Blog**
BlogID
Title
**Tag**
TagID
Name
**BlogTag**
BlogID
TagID
Quantity
I'm not sure how I handle the quantity column. I'd like it to store how many Blogs have a certain Tag NameHow do I deal with the quantity column when adding a new blog that has tags?
I am developing a asp.net application and i am using SQL Server 2008. I took a IDENTITY column as Record_ID for detail table where i will have trillions of records per year. So just want to ask whats the largest number record id (identity) column can hold and in ASP.NET which data type i should use to handle record id as i am using this id as a reference to update the table data. I don't want to end up being trapped some day.
View 1 RepliesI am working on a web app for an online photo album. It is the last project in Scott Mitchell's book: "ASP.Net 2.0 in 24 hours".
I am creating a page where users can enter new photo images. The functionality for uploading an image will come later. I am working on just inserting a new row into the Pictures table with an optional category, a required title and a required description.
I am getting an error when I try to insert a new row.
Here is a screenshot of the page with a shot of the dropdown list.
The Categories are user-specific. I am getting the correct values retrieved.
[URL]
There are two pretty simple database tables involved here.
NOTE: The CategoryID and PIctureID are both autoincrement integer columns.
[URL]
The page uses a DetailsView that uses a SqlDataSource that uses the Pictures table. (The DetailsView's Default Mode property is set to "Insert". The "Enable Inserting" checkbox is also checked.)
The dropdown list uses a dropdown control that uses a SqlDataSource that uses the Categories table.
When the "Insert" button is clicked the CategoryID value associated with selected (Category) Name on the dropdown list will be used along with the Title and Description values to insert a row in to the Pictures table. (If no Category value is selected, then a null value will be used for the CategoryID. This is OK because the CategoryID column in the Pictures table allows nulls.)
My problem is that I am getting this error:
Cannot insert explicit value for identity column in table 'Pictures' when IDENTITY_INSERT is set to OFF.
(FYI: I have the full version of both Visual Studio and SQL Server.)
Here is my source code for the page:
[Code]....
I am inserting data into Employee table using stored procedure.In employee table EmpNo is identity column.Once i insert data into table stored procedure should EmpNo i.e identitycolumn value.
correct my stored procedure to returm EMpNo Identity column after inserting row.
alter procedure SAR_Sp_AddEmployee(@EmpName varchar(10),@CampaignID int,@startDate datetime) as insert into EmpMaster values(@EmpName,@CampaignID,@startDate)
Following the MVC1 vers of Nerddinner, but using 2010 and MVC2. Never seen this error. Looked in controller, model, view. Where does MVC pass the identity value, and how to stop it? I have never had to set IDENTITY_INSERT in any app before.
View 2 RepliesI 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?
i have created a web form, where i have 3 textboxes into a table and one submit button.
i would like to store values from the textboxes into my database (sql server) when i click the submit button.
but how to insert the value that is not in textbox into database which is primary key for table without identity column.
I am using LINQ to store and retrieve data from tables with a clustered index key. However, I also need to know how to obtain an inserted table row identity with an auto-increment Primary key. The "Go To Definition" feature for my data context of dataClassesDataContext provides the following information:
[System.Data.Linq.Mapping.DatabaseAttribute(Name="EFMDB")]
public partial class DataClassesDataContext : System.Data.Linq.DataContext
I am asking for the C# code that is needed in a button click event handler that will insert a row of data in a table named "Fields". It only consists of an auto-incrementing primary key column (FieldID), a string column (Phrase) and a null defaulted variable column.
I am trying to get the @@Identity from a table by using a stored procedure called by my asp.net website. I am using a datareader to do this but get an error as it tells me it cannot convert the returned value to getint. Here is my code. What should I be using instead of getint?
Dim myReader = cmd.ExecuteReader()
While (myReader.Read())
intID = myReader.GetInt32(0)
End While
myReader.Close()
I have a database table and the column that has identity specification set to true is not displaying in numerical order. Whether I use Server Explorer and use Show Table Data or I create an application to view the contents of the table using a DataGridView I see that column displayed with the numbers often though not always in a non-numerical order.
Does it perhaps have to do with the way the rows for that column were added or would it be something else. I was looking at the properties for that column but don't see what I would change to make the rows display in numeric order.
I have error:
Cannot insert explicit value for identity column in table 'UserDetails' when IDENTITY_INSERT is set to OFF.
I'm trying to insert a record through a BLL (Business Logic Layer) class with an 'Insert' button attached to the FooterTemplate of a GridView2. My code:
[Code]....
[Code]....
BLL layer code:
[Code]....
The field 'idUser' is autoincremented. When I use SqlDataSource with a DetailsView, add option it adds a record and autoincrements the 'isUser'.
I need to retrieve the identity value after the record has been created for another datasource. I.E. When the record is created in SqlDataSource7 the identity value (4700) would be assigned to Label1.text and used in SqlDataSource8 to insert a new record.
How do I do this? In the CodeBehind retrieve the identity value using SqlDataSource7 Scope_Identity() and putting that value into the SqlDataSource8 ApplicantID column.
[code]...
I just not created a new database with 2 tables in it. In a table, I have set a column as a primary key and trying to change its properties i.e. setting is Identity to YES, but am not able to do so.
View 2 RepliesSo far it seems like I get something figured out then run into another issue... Anyways, I was finally able to grab an ID from a table and enter the value into a Label using this code:
[Code]....
Now i want to make things trickier by grabbing the last value enterd. Now there will possibly be multiple people using the application simultaneously, i think it is still possible to do this. Here is the statement that enters VALUES into the table that generates the CaseID that i want to grab from:
[Code]....
From what i have researched, I may need to do a WHERE some where to grab the @@IDENTITY. I am having trouble figuring out where the @@IDENTITY goes, and possibly the correct syntax as well.
I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.
View 29 RepliesI want to konw how to start my identity column from 001?
[Code]....
this does not work as it still takes it froom cause 001 and 1 is the same
I want to know mail difference between primary key and identity key with example.
View 3 RepliesI have a Stored Proceedure which DID return the Scope_Identity when this was executed in SQLServer 2000.
Now I am porting this over to SQLServer 2008, and this always returns 0, and not the Scope_Identity which it should.
Here is the Stored Proceedure which will pass null for the parameter @pProjId for an insert:
[Code]....
Here is the script which creates the hip_Project Table:
[Code]....
What do I need to change for SQLServer 2008?
Again, this executed fine in SQLServer 2000 and always returned the Scope_Identity as expected. This problem has come up with other stored proceedures as well for which I need the Scope_Identity returned.
I have table with around 100 record. Now I want to set identity to primary Key column
View 3 RepliesHow to handle this error
"An explicit value for the identity column in table 'UserActivation' can only be specified when a column list is used and IDENTITY_INSERT is ON."
how can i create IDENTITY ID for primary Key column like this
AAAAA
AAAAB
AAAAC
AAAAD
AAAAE
.
'
'
'
AAAA1
AAAA2
AAAA3
AAAA4
.
.
.
.
AAABA
AAABB
AAABC
I have stored procedure where i am creating table and setting primary key how to set auto increment ?
set @sql =@sql +
'CONSTRAINT [PK_'+@Table+']
PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]'
I need to pass a table type parameter to a user-defined table valued function in SQL Server 2005.How would I do this?
My function name is udf_t_GetSales ( @financialYearMonthsData as table)
The table @financialYearMonthsData has 3 columns ( MonthId int, DisplayText nvarchar(500), CalendarYear int)