Adding Records To SQL CE With Standard VS2008 Without Getting Identity Value
Apr 28, 2008
I'm using VS2008 (as shipped with SQL CE 3.5 as shipped).
I want to write a database app and started with a quite simple example:
One table "Master" has 2 columns: int MasterID, int Value (where the MasterID is the primary key and is an identity column.
For the GUI I used the typed dataset components (based on the namespace: System.Data.SqlServerCe) which were automatically generated by VS2008. I just dropped the table from the datasources view to a form and got a DataGridview, a Navigator and some DB components, quite fine so far.
When i run the app i can add new rows and the identity column is filled with -1, -2 ... as expected. When I hit the save button, the rows are saved to DB, but the values of the identity columns are not updated (the still have -1, -2..)
When I use the exact same example with SQL Express evrything works fine: the identity Column's values are updated automatically and have +1, +2, ...
Sure there is a statement "SELECT @@IDENTITY"... but I'm just using automated code so far, and i expect that code to work (automatically). Next problem: if i want to insert that "Select @@identity" stuff (I don't want to do that, but perhaps I have to?) where can I do this, I did not found a OnInsertedRow-Event?
This problem is part of a bigger one: I want to use Master/Detail, but inserting the Details fails because the inserting of the master rows didn't return the correct Indentity values, so the detail rows cannot be inserted (the foreign keys -1, -2... will raise an error).
I am having a problem when trying to add the Client Components to an existing installation of SQL 2005 Standard Edition SP1 running on Windows 2003 Standard. The server was originally built with SQL 2005 Standard Edition and had SP1 installed at a later date. I am using the CD that SQL was originally installed with which is not a service packed copy of SQL. The reason given when clicking the details button is as follows:
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
how can i change a column attribute using alter table in sql server 2005. i want to add an identity property to my column userid , which deosn't have an identity property defined when created.
i tried this.
alter table userlookup alter column userid int identity(1,1) not null
I have a table that I need to add an identity field to. I created a field in the table as an INT and added values to all of the existing records. When I try to change it to an IDENTITY field I continually get an error saying 'Invalid cursor state' . The help function tells me that this is caused by not having enough space in the transaction log but I don't understand this b/c the trans log is configured to expand as needed. Anyone know how I can do this?
Hi Friends, I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
While executing the following code,it is showing some warnings.
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET ANSI_WARNINGS ON GO Drop procedure [DBO].[Usp_Extract_OLAP_Data_1] GO
Create Procedure [DBO].[Usp_Extract_OLAP_Data_1] as
begin
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID('[DBO].[processed_olap_data_1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) DROP TABLE [DBO].[processed_olap_data_1] select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1 EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')
select * from processed_olap_data_1 order by generatedid
END
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SET ANSI_WARNINGS OFF GO
Out Put is as follows ----------------------
(141 row(s) affected)
(141 row(s) affected)
Warning: The table 'processed_olap_data_1' has been created but its maximum row size (3873071) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
(141 row(s) affected)
Please guide me on this issue that why iam getting this warning even i set the ansi null and ansi warnings off. one more thing is that is there any other way to add an identity column with out using alter statement.and is there any problem with front end guys if it shows like this warnings..."
Hi,In my stored procedure I'm doing a SELECT onINFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id onthis table, so I was wondering if it was possible to add it dynamicallyin my SELECT, so that I would assign a unique id to each recordreturned by my SELECT?Thanks for your support.
I've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer
I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?
I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can't all NULLs. I have heard that this not possible if the table already exists.
I have downloaded the demo's of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
After SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?
Below the log showing the flag being added to the error log:
I created a new database called "TestReportServer" as mentioned in the installation instruction but I didn't see (or could select) the option "Create the report server database in SharePoint integrated mode". How can I select this option? Do I need to remove the reporing services and reinstall it again? Any suggestions?
After creating the database I get the error 'Some or all identity references could not be translated'.
The user I selected is a local administrator and has permission to all groups starting with wss.
I guess the database is not created as a sharepoint integration mode as I can start Server Management Studio and see the database. Is that a correct assumption?
I hope somebody out there can help as I am strating to bang my head towards my desk right now :-)
I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:
The command contained one or more errors. [,,,,,]
But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?
Here's my dilema. I'm using VB.Net 2003 and SQL DB
I'm having a problen adding a record to a table in my SQL DB
My previous experience has been working with Access databases.
When I try to create an SQLDataAdapter using the wizard from the toolbox the wizard will only create the Select statement. it won't create the UPDATE, DELETE or INSERT statements. I'm not sure why, I even tried using the form wizard and it still wouldn't work...
My tables all have primary keys defined.
So I decided that I would try to create everything programatically.
What I need to know is how to create the INSERT statement and which statements to use to add the record to the table.
I know that when using the OLEDataAdapter with the Acces DB I would do something like the following code.
Hi, I was wondering if you can help. I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table. What I need to do is after I have created these new records update a field in each of them. Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them? Ideally I would like to use @@identity and datareaders with vb.net. Cheers Mark :)
I trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.
I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:
use [c:developesqlsqldatapsw.listener]
append from 'c:developesqldatalistener.txt' type sdf
go
and then I pressed F5. I get an error that says:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'. No entry found with that name. Make sure that the name is entered correctly.
I checked in the directory c:developesqlsqldata and the file psw.mdf is there and when I look in the psw database, the table listener is also there.
Could someone tell me what is going wrong? Also, how do I append the whole dbf table onto an SQL table. there's about 6000 records.
I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB INTO #TMP1C from Vantage.dbo.SBB_BASE (NOLOCK) where PRIN_SBB in (6000,7500)
[Code] .....
Not entirely sure what I am doing wrong, or why the numbers are coming back so high.
I am trying to add a website address to all the records in a database that match a certain criteria. The statement that I am using is shown below, but surprise surprise, it's not working! I'm new to SQL so any help would me much appreciated! Thanks.
declare @ComNum int set @ComNum = (select max(communication_number)+1 from communications)
insert into "communications" (contact_number, device, ex_directory, dialling_code, std_code, number, extension, notes, amended_by, amended_on, cli_number, communication_number) values (NULL, 'WW', 'N', NULL, 'WW', 'W', NULL, 'www.abc.co.uk', 'Jon', 2007-11-29, NULL, @ComNum); select name from organisations where name ='Abc Limited'
So, I have not included a VALUE for CONTACT_NUMBER as I wish to update all records with the website details as per the INSERT statement where the NAME column in the ORGANISATIONS table is 'Abc Limited'. I know something is wrong but I can't quite work out what!
Hey guys. I need a little help once again, here's the problem:
We are using an attendance system software that adds records in an Access 97 table whenever someone logs in or out of the room (Yes, it's old and I hate it, but it came with the hardware).
So we plan to create a program that lets admins check those login/logout data in an SQL Server 2005 database using a Visual Basic as the frontend.
Our only problem is, how do we transfer data from the Access 97 tables to our SQL tables *everytime* it gets updated? We don't need every detail the Access97 table has (it has IDNo, CardNo, Time, Location, plus other useless stuff). We just need to extract the IDNo(int) and Location(varchar) field(we'll just use getdate() for the Time since the update is done in realtime anyway).
So in a way, we are trying to find out a way to technically sync both tables, just like Database Mirroring style.
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.
I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:
use [c:developesqlsqldatapsw.listener] append from 'c:developesqldatalistener.txt' type sdf goand then I pressed F5. I get an error that says:
Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'c:developesqlsqldatapsw.listener'.No entry found with that name. Make sure that the name isentered correctly. I checked in the directory c:developesqlsqldata and the filepsw.mdf is there and when I look in the psw database, the tablelistener is also there. Could someone tell me what is going wrong? Also, how do Iappend the whole dbf table onto an SQL table. there's about6000 records.
When adding records containing a date field in SQL Server 2005 Express I get an error Input string was not in a correct format. Do datefields have to be converted when adding or editing into strings?
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
I guess this is a fairly common topic but couldn't find the right words to find anything in a search.
What I'm getting at, is there any tsql functions or combination of commands for the following.
You have identity columns in your tables, if you set the a seed and autoincrement, I enter in rows 1 -10 and then I delete 4, 6, 7, 8.
My next new record uses 11. Is there any logic that allows you to check and reuse 4, 6, 7 & 8 described above? Not looking for something that consists of having to create an extra ID table for each table and handle configuring what the next available number is everytime an Insert or delete is called.
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
