|
I am currently working Asp.net with c#, inserting data from webform into sqlserver 2000 datatable.
table id column contain identity increment.It will increment id for every new recored.
I inserting 10 recoreds id 1 to 10 into table then deleted last two records (9&10) then inserting a new record
these record id having 11 insted of 9 in id column.
Plz help me .......
Thanks in adv
|
|
|
|
|
This is normal. The absolute simplest thing to do is to code your application so it does not expect or require contiguous identifiers.
The database records the next identifier for a table with an IDENTITY column in the table's metadata. It uses and increments that value when inserting a new row. You can only reset the value by truncating the table, removing all existing rows, or using the DBCC CHECKIDENT command. You have to set a special option to insert a value into the identity column.
If you really can't stand to have gaps, you'll need to manage the identifiers yourself.
|
|
|
|
|
Hello to every One,
i'm using database in my web form for login and user purpose.. due to some reasons i have to format my pc.
after that when i again re-install sql server 2000 and visual studio7 i'm unable to connecto to the database in the following line:
Protected sqlconnection1 As SqlConnection = New SqlConnection("data source=.;initial catalog=daccrop;persist security info=False;user id=dac1605;pwd=dac1605;workstation id=self;packet size=4096")
Actually i have changed the server and computer name in my new installation i.e from "DAC_147_347" to "garg" and again i don't remember how the authentication was done i.e windows or by sql server login. Following is the modified coding for connecting to database..
Protected sqlconnection1 As SqlConnection = New SqlConnection("data source=GARG;initial catalog=dac1605;integrated security=SSPI;persist security info=False;workstation id=GARG;packet size=4096")
This is still not working..
suggest me how should i know the setting done in sql server and after knowing how should i implement it.
The Error shown to me is:
"SQL server does not exist or access is denied"
Thank You..
|
|
|
|
|
Use Enterprise manager to view the Server properties and see how security is set up. If it is set up for windows authentication, then change your connection to use that. If Sql server or mixed, then use EM to view the logins, and find the login you wish to use. Reset the password, then update your connection string.
|
|
|
|
|
Hi Guys,
The database is remotely located in other place and I have no more time to go back there. How can I backup the database remotely to my local machine?
I am using MsSQL2005 for my remote web development, and I just wanted to have a copy of the database into my local machine.
I hope you can help me with this.
hifiger2004
|
|
|
|
|
If you can, back the database up to a shared drive (or ftp directory that you have access to) on the remote, then copy (ftp) the backup file to your machine.
|
|
|
|
|
Hi Rob,
That's what I am thinking before. But if there are some other ways to do it aside from ftp then i will go for it.
Anyway, since there's a lot of time wasted for finding some other ways, I rather go for ftp.
Thanks for your support Rob. 
hifiger2004
|
|
|
|
|
hi,
i am using MSSQL2000.
i wrote a query with sum() of aggregate function.
like
Select sum(sal)from table1 where empid=1 and date>'1/23/2006'
in this if thers is no record it rteurn 'NULL'
is it possible to return it as 0
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
i got its answer
SELECT isnull(sum(Sal),0) FROM table1 WHERE...
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
of cause,
the code like this:
Select case when sum(sal) is null then 0 esle sum(sal) end
from table1 where empid=1 and date>'1/23/2006'
|
|
|
|
|
thank u
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Hai everybody,
Can any one give me a hint how to throw an exception explicitly in SQL stored Procedure inside the try catch block.
and how can i quit form the procedure in mid way
Thanks in advance.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
The command is called raiserror. If you do a seach on it you will find plently of examples. If you want to quit a stored procedure and you don't want to use raiserror with a high enough severity then you the Return key word to exit the procedure.
Hope that helps.
Ben
|
|
|
|
|
Thank you Ben.
It is working fine.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
I make a DataBase and create the table and procedure all in the the default schema and primery file group
Can any one tell me how to replace the tables and procedures in a new file group and schema
merwa
|
|
|
|
|
I am developing a solution in C# on top of ADO.NET. I am using MS Access as my data store. My requirement is that i have to insert a Unicode string containing Arabic characters into the database. So following is the INSERT command
INSERT INTO ClauseTranslations (Translation) VALUES('اردو کی ایک مثال')
So thats it. Now this query executes very well in the MS Access environment but simply fails when executed from the C# application using the ADO.NET classes. The error message is
"Syntax Error in INSERT Statement"
Can anyone guide me whats wrong and where is it wrong?
Regards,
Mohsin
Polite Programmer
More Object Oriented then C#
|
|
|
|
|
It is possible that your field name (Translations) is a keyword for the Jet Engine SQL implementation. Try enclosing it in square brackets [Translation] to avoid interpretation as a keyword.
If that fails, implement the insert using a parameterized insert stored procedure (querydef in Access Lingo) and use an SQLCommand to execute it from C#. This almost always works, as SQL syntax is interpreted at the database, and data values are cleanly encapsulated, avoiding any parsing problems.
Using stored procedures and commands also makes your application far less vulnerable to sql injection attacks.
|
|
|
|
|
Hey guys,
I am trying to write a C# program to import data from an Excel spreadsheet to a Sql table using a stored procedure.
So, I've created the connection objects:
OleDbConnection XLcon = new OleDbConnection(
@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = D:\Desktop\OA.xls;"
+ @"Extended Properties = Excel 8.0;");
SqlConnection SQLcon = new SqlConnection(
@"Data Source = (local); Integrated Security = SSPI; Initial Catalog = WebData");
And the Command Objects:
SqlCommand SQLcmd3 = new SqlCommand();
SQLcmd3.CommandText = "ImportOA";
SQLcmd3.CommandType = CommandType.StoredProcedure;
SQLcmd3.Connection = SQLcon;
OleDbCommand XLcmd = new OleDbCommand("SELECT [Date], [Order Application], "
+ "[Demand Orders], [Demand Units], [Demand Net], [Demand Gross], "
+ "[Demand Freight], [Demand Tax], [Demand Amount], [Demand Product Promo], "
+ "[Demand Ship Promo], [Fulfillment Units], [Fulfillment Net], "
+ "[Fulfillment Freight], [Fulfillment Tax], [Fulfillment Amount], "
+ "[Fulfillment Product Promo], [Fulfillment Ship Promo], [Return Orders], "
+ "[Return Units], [Return Net], [Month], [Quarter], [Year], [Week #], "
+ "[Return Amount] FROM [Excel$]", XLcon);
Set up the parameters:
SQLcmd3.Parameters.Add("@Date", SqlDbType.SmallDateTime);
SQLcmd3.Parameters.Add("@OrderApplications", SqlDbType.VarChar,10);
.
.
.
SQLcmd3.Parameters.Add("@ReturnAmount", SqlDbType.Money);
And then tried to import the data:
XLcon.Open();
OleDbDataReader XLread = XLcmd.ExecuteReader();
while (XLread.Read())
{
SQLcon.Open();
SQLcmd3.Parameters["@Date"].Value = XLread[0];
SQLcmd3.Parameters["@OrderApplications"].Value = XLread[1];
.
.
.
SQLcmd3.Parameters["@ReturnAmount"].Value = XLread[25];
SQLcon.Close();
}
XLcon.Close();
XLread.Close();
The code runs fine but it does not import anything into the table. The spreadsheet has data in it and I have verified that the Store procedure works so why can't I get the values in the spreadsheet into the table?
Thanks,
Bryan
|
|
|
|
|
I'm not sure what you are trying to do here, but it doesn't look correct.
you have created a command query containing a select statement that specifies a list of fields to be selected from the worksheet named Excel$. You read the results of that query into a command object that is linked to a stored proc in SQL server somewhere. You open a connection, set command parameters to row values read from excel and then close the connection (do you execute the command first?).
First, I would open and close the connection outside of the reader while loop, and just execute SQLcmd3 at the bottom of the loop. Close and dispose of the connection (and command) after the while loop.
Set a breakpoint at one of the lines that sets a parameter value and verify that you are really returning data from the sheet.
Good luck
|
|
|
|
|
Hi, I have to show a filesystem on a page. The information is stored in a table where I have ID(PK), Name, Parent, Order
001, Node1, NULL, 1
002, Node2, NULL, 2
003, Node3, 001, 1
004, Node4, 001, 2
005, Node5, 002, 2
I need to display the results as it should be seen:
+Node1
Node3
Node4
+Node2
Node5
The query I have is like this:
SELECT A.*
FROM TableFS A
LEFT OUTER JOIN TableFS B
ON A.ParentID = B.ItemID
ORDER BY A.ParentID, B.Order
The result is similar to what I should get, but similar is not enough... at least it's what they tell me... 
If anyone can help... 
Thanks...!
|
|
|
|
|
SELECT A.*
FROM TableFS A
INNER JOIN TableFS B ON A.ItemID = B.ParentID
ORDER BY A.ParentID, B.Order
Regards
KP
|
|
|
|
|
Sorry, but this is not even close. Items without parents have value in NULL, so your querry dosen't take those nodes in charge, and gives me duplicated values anyway.
Thanks for your time 
|
|
|
|
|
I have a database column that is variable character 1500, If I try to read a record with a value whose length is over 200 characters I get an empty string and the debugger returns the following error:
This expression causes side effects and will not be evaluated
|
|
|
|
|
Hi,
I've been using SQL Server 2005 for almost 2 years now. I want to know if there is anything to look at for the new SQL Server?
Regards
ma se
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
