identity specification value need to provide in SP?

Question

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 27-Dec-13 21:23pm

Muhamad Faizan Khan

Add a Solution

2 solutions

Solution 2

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.

Posted 27-Dec-13 22:45pm

Christian Graus

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2013-12-27T21:51:00

Solution 1

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[^]

Posted 27-Dec-13 21:51pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 28-Dec-13 4:13am

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 28-Dec-13 4:18am

Then it should work. Have you tried executing the query directly in SQL Server Management Studio itself?
If yes, then try once.

Muhamad Faizan Khan 28-Dec-13 4:32am

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 = datetime2

and error comes in
Msg 8114, Level 16, State 5, Procedure SPSearchQueryDML, Line 0
Error converting data type nvarchar to datetime2.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 28-Dec-13 7:54am

Try to resolve this. You are sending nvarchar instead of datetime2.