Click here to Skip to main content
15,922,007 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
i have set a column it table as identity specification. now my question is do i need to provide that column value from C# to store procedure ? do i need to write that column in insert query??
Posted

If you have set Identity true for that column and provided increment and seed, then no need to insert that. It will automatically increment.
You need to insert values for other columns only.

Refer-

1. SQL AUTO INCREMENT Field[^]
2. Auto increment primary key in SQL Server Management Studio 2012[^]
 
Share this answer
 
Comments
Muhamad Faizan Khan 28-Dec-13 4:05am    
i am not providing its value then show me the error.
if i provide then show me error
Muhamad Faizan Khan 28-Dec-13 4:09am    
public int SaveSQ()
{
SqlParameter[] param = new SqlParameter[3];
param[0]= new SqlParameter("@Mode", "SaveSQ");
param[1] = new SqlParameter("@SearchedQuery", _SearchedQuery);
param[2] = new SqlParameter("@DateNTime", DateTime.Now);
// param[3] = new SqlParameter("@QueryId",);
DataTable dt = new DataTable();
return objDBBridge.ExecuteNonQuery(SPName, param);
}

//sp is
BEGIN
INSERT INTO [dbo].[SearchedQueries]
([SearchedQuery] ,[DateNTime])
VALUES
(@SearchedQuery
,@DateNTime)
END

while QUERY_ID i am not giving not written also in sp but the erro showing provide query ID expected
Have you set QUERY_ID as Primary key and Identity field as suggested in those links I have given?
Muhamad Faizan Khan 28-Dec-13 4:15am    
yes off course
Then it should work. Have you tried executing the query directly in SQL Server Management Studio itself?
If yes, then try once.
If you set it as identity, you need to set SET IDENTITY_INSERT in order to be able to set the values.

I just saw your reply. The error means what it says. did you stop to read it ?

Error converting data type nvarchar to datetime2.

You are passing in a string and trying to convert it to a datetime2, but it's not compatible. Make your proc take the parameter types that exist in your table, so that they are strongly typed and you're not relying on SQL Server conversions.
 
Share this answer
 
Comments
Muhamad Faizan Khan 28-Dec-13 5:00am    
now i change my query
execute SPSearchQueryDML @Moade='SaveSQ' ,
@SearchedQuery =N'Select [Hadith_Text] ,[Hadith_Urdu],[Hadith_English],[Chapter_English_Name],[Chapter_Urdu_Name],[Baab_English_Name],[Baab_Urdu_Name] ,[Baab_Id] ,[Book_Arabic_Name] From HadithSearched Where Baab_Id =2 order by ID',
@DateNTime = CURRENT_TIMESTAMP

hence got error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CURRENT_TIMESTAMP'.
Christian Graus 28-Dec-13 5:23am    
The current time and date in SQL Server is retrieved with getdate(). http://technet.microsoft.com/en-us/library/ms188751.aspx is how to use current_timestamp.

Honestly, I have no idea what you're trying to do, but it's clear you have no idea how to do it. Why are you building a query as a string ? That is almost always a terrible idea.
Muhamad Faizan Khan 28-Dec-13 5:26am    
i want to save a query into table ...well this it is the different story. you just told me how to save current timedate in a table's column i also used GetDate() function but it is showing me this error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

while the query is
execute SPSearchQueryDML @Mode='SaveSQ',
@SearchedQuery =N'Select [Hadith_Text] ,[Hadith_Urdu],[Hadith_English],[Chapter_English_Name],[Chapter_Urdu_Name],[Baab_English_Name],[Baab_Urdu_Name] ,[Baab_Id] ,[Book_Arabic_Name] From HadithSearched Where Baab_Id =2 order by ID',
@DateNTime = GetDate()
Christian Graus 28-Dec-13 5:30am    
You're still only telling us half the problem. I have no idea what the proc is you're calling, why you're passing in a SQL statement, what your proc code is, what your tables are, etc. Type select getdate() and run it. You'll see it's EXACTLY how you get the current date. The more I look at your code, the worse it is. Honestly, I can't help you unless you edit your post and ask a REAL question, with all the details someone would need, to try to help you. What's the SQL of the proc ? What's the schema of the tables it uses ? What are you trying to do ? The question you asked, was based on your assumption of what the problem was, but your error messages showed that this was not the issue. We need to work out your REAL question, then we can answer it.
Christian Graus 28-Dec-13 5:40am    
I've stayed up to try to help, I am going to bed soon. One thing I see:

INSERT INTO [dbo].[SearchedQueries]
([SearchedQuery] ,[DateNTime])
VALUES
(@SearchedQuery
,@DateNTime)
END

Don't pass in @DateNTime, if it's always going to be getdate(). Just use getdate() in the insert statement instead of the variable. It looks to me like you're storing SQL you have run, which seems crazy to me, it implies you're string mashing SQL some where. But, if this is your proc, it does not seem to take a @Mode parameter. Either way, the @mode param is stupid. Have one proc per 'mode', or make your modes ints, and define them to descriptions in a table, and in an enum in your code.

Good night.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900