|
Yes I have tried the procedure in query analyser and it inserts data successfully. I have added 4 records in tabale using this SP but and everytime I start application, it starts from ID = 5 (ID is IDENTITY column).
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Just a lil doubt, are you explicitly opening transactions?
If yes, you'll have to commit your transactions by using the COMMIT keyword everytime you insert a row. By default AUTO COMMIT is ON in SQL Server. Check your connection properties to see if its turned off somewhere.
SG
Cause is effect concealed. Effect is cause revealed.
modified on Monday, March 24, 2008 5:39 AM
|
|
|
|
|
No I am not using this statment and I have also checked SqlCommand object's propertys using it like this com.Transaction.Commit(); function but it is not working. 
I think it is the problem of SQLEXPRESS that may does not allow inserting or updating by default. If anyone give any idea about it, I shall be thankful.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Check your connection string/properties. Are you connecting to SQL Express as 'User Instance = True' ?
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Yes this property is set to true as 'User Instance = True'.
I think it might be some limitation of SQL EXpress but uptil now I haven't come to know about any such limitation.
Same problem also posted here by someone.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2555908&SiteID=17[^]
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Make sure your connections string says something like:
Database='Path&FileName';...;User Instance = False
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Well everything is fine with connection string and application works fine enough as far as it is in RAM but acutal changes are not commited.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Oh dear. This old chestnut again. I assume that you are running SQL Server Express as part of your application and that you have a local database. Here's a hint - when the application runs, the database is copied into the bin directory (everytime it's run from the Visual Studio menu). This means that the work you've inserted effectively disappears because it has been replaced by a new copy of the database. In order to see the data, you need to look in the Debug (or Release depending on your build mechanism) directory where you will be able to see the database. You can open it up and take a look at it in there.
|
|
|
|
|
Thank u very mcuh Henlon.
That was AWESOME. [ ]
That really works. When I opened the database from bin folder it contained all the data that was inserted earlier.
I don't know how should I say thank u to u. Thank u so much. [;)]
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
No problem. I'm glad I could help.
|
|
|
|
|
Hi All,
I've just downloaded and installed this ready to integrate into a desktop app i'm making. I've just going through an article on SQLServerCentral to get a quick introduction to it. The weird thing is that when adding the reference to System.Data.SqlServerCe.dll it wasn't in my list on the .NET tab ... I checked the GAC and the file is in there though!
Also "Microsoft SQL Server Compact 3.5 " isn't appearing in my list of available DataSources inside Visual Studio 2008.
I'm just downloading SQL Server Management Studio Express to see what light that can shed on this. Any pointers or info would be great.
Cheers,
Jammer
Going where everyone here has gone before!
My Blog
|
|
|
|
|
Hi all,
First let me discuss the scenario. I have 2 tables,a party master table where in i store all details of party(a user) which has a PK partycode and a price_relation_master table where i associate that party with multiple items ie an itemcode.The items come from a table called item_master with pk itemcode where all the details of an item are stored.
Now i wan`t to develop a copy facility such that all the items assigned to a party are copied to other party. Copying is not a prob, but i before copying i have to check whether that item was previously assigned to dest party or not. If the item was assigned to that party that item shouldn`t be copied. Does any body have any info about such cases!!!! Any help would be appreciable....
When you fail to plan, you are planning to fail.
|
|
|
|
|
How to pass the parameters of @dbName, @LogicalName, @ToServer
to job dynamically using stored procedure?
|
|
|
|
|
I am having trouble figuring how to structure the tables to solve this problem. Here's where i'm at:
Existing Tables:
Tool(
ToolID Int Id PK...,
...,
Cavities Int Not Null
)
Product(
ProdID Int Id PK...,
...
)
ProdLine(
PLine ID Int Id PK...,
...
)
Problem:
A Production 'Cycle' consists on making a combination of 1 to n Products with a specific Tool where n <= Cavities for that Tool.
For example, a tool can make up to n peices of 1 product per cycle, or 1 peice of 4 products per cycle, or a combination thereof.
A common combination would be for two products to be made with a given tool on a given ProdLine in a given amount of time.
I want to create table(s) to reflect the relationships between this additional data(CycleTime, and Product Combinations for a Tool) and the existing tables.
hope this is decipherable, it is spinning my head.
Is have modelled several solutions that are all very ugly and I wonder if anyone has an elegant suggestion (...my wife has given me many (non-elegant) suggestions mostly regarding why i am trying to learn this concept on Easter Friday night!  )
|
|
|
|
|
Hi,
How to Debug stored porcedure SQL server 2000 ?
Thank& Regards
Vijay s
|
|
|
|
|
Hi Vijay,
If you are looking to debug a stored procedure within SQL Server 2000 then I would like to mention that it consits several steps which is hard to explain here. Thefore, I would suggest you go through the following article which explains debugging process (within SQL Server 2000) step by step.
http://www.15seconds.com/issue/050106.htm[^]
I hope this helps.
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
if you want to debug the stored procedure, open a stored procedure and right click and select the option 'step into stored procedure', you can debug stored procedure without debugging your application.
|
|
|
|
|
I would like to create two procedures that do the following:
1. Retrieving the next value that an identity column would get if a new row is inserted.
2. Change the value of the next value that will be created when the next row is inserted.
Can I do any of the above with C# and ADO.NET?
(Using SQL Server 2005)
_____________________________
...and justice for all
APe
|
|
|
|
|
d00_ape wrote: Retrieving the next value that an identity column would get if a new row is inserted.
IDENT_CURRENT + 1[^] gives the next identity value for specified table.
d00_ape wrote: Change the value of the next value that will be created when the next row is inserted.
Set IDENTITY_INSERT[^] to "on" for inserting into an identity column.
|
|
|
|
|
1. OK - IDNTITY_CURRENT + 1 works perfect for me!
2. I don't know if I missunderstand but can really helpe me to modify what the next identity number will be? I dont want to manually do INSERTs with the identity column value, only step the counter forward to a specific value.
----------------------------------------------------------------
My goal is to make users of a program (that uses the db and the table) to set a new start value each year.
Year 2008: next new row id = 200800001
Year 2008: next new row id = 200900001
I use the id to represent an Invoice-number and my clients like to see on the number from which year it is created.
_____________________________
...and justice for all
APe
|
|
|
|
|
d00_ape wrote: modify what the next identity number will be
Ok, I misunderstood you. If you need to change the next identity value, reseed it. Try DBCC CHECKIDENT (table, reseed, value)
|
|
|
|
|
Perfect!! That's what I needed!
_____________________________
...and justice for all
APe
|
|
|
|
|
you can do this using two methods.
1) using sql server column identity property:
in this method, which column you want to give identity, open the table definition and right click on the field (which you want to be indent), set the identity specification - IsIdentity to 'yes' & Identity Increment and Identity seed to 1.
2) using c# in your asp.net page:
using asp.net page, first get the maximum record of the table using max(column name) using data reader & increment that value to 1 and assign that value to new inserted row.
for ex:
sqlconnection cn = new sqlconnection();
cn.open();
string str = "select max(userid) from user";
sqlcommand cmd = new sqlcommmand(str,cn);
idatareader rdr = cmd.executereader();
int id = 0;
if(rdr.read()==true)
{
id = rdr[0].tostring();
}
id = id+1;
now the id you will get is incremented id for inserting new query or row to table.
use this id and insert value of this id as the field of userid to insert new row.
|
|
|
|
|
Hi,
I need help with an sql query please.
I wish to insert a new record into a table, and instantly retreive the new records id. Is it possible to do this in one sql statement rather than an INSERT followed by the SELECT statement?
My INSERT looks like this:
<code>string conn = System.Configuration.ConfigurationManager.ConnectionStrings["artisanConnectionString"].ConnectionString;
SqlConnection uniqueCon = new SqlConnection(conn);
//set SQL statement
SqlCommand cmd = new SqlCommand("INSERT INTO [tbl_Additional] (add_Item, add_Price) VALUES ('" + TextBox10.Text + "', '" + TextBox11.Text + "') ", uniqueCon);
cmd.Connection.Open();
try
{//insert/ add new record
//commit to database
cmd.ExecuteNonQuery();
//clear form add/edit
TextBox10.Text = null;
TextBox11.Text = null;
//confirm to user
ShowMessageBox("New unique Item Added!");
//update dataset
GridView2.DataBind();
}
catch
{
ShowMessageBox("Unable to add record!!! Please try again later.");
}
cmd.Connection.Close();</code>
Any help would be greatly appreciated.
Thanks & Happy Easter!
|
|
|
|
|
You should use a stored procedure, then you can populate an OUTPUT parameter with SCOPE_IDENTITY() value, example: here[^].
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
