March 17, 2014 at 9:36 am
This is the last form. I can't still insert.It's not giving error. There are only warnings.
Did you look at my last post? The logic in your proc can very easily do nothing unless 1 of 2 very precise values are passed to @p2.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 9:39 am
'Warning 1 The currently targeted framework ".NETFramework,Version=v4.0,Profile=Client" does not include "System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" which the referenced assembly "CrystalDecisions.CrystalReports.Engine, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304, processorArchitecture=MSIL" depends on. This caused the referenced assembly to not resolve. To fix this, either (1) change the targeted framework for this project, or (2) remove the referenced assembly from the project. WindowsFormsApplication2
This is most likely because you have a reference in your project to a crystal reports assembly that is using an older version of .net. You need to figure out which version of the framework that assembly is targeted for and adjust your project accordingly, or make a reference to a more current assembly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 9:39 am
And when you run the proc from SQL as I suggested?
Those are compile-time warnings, not interested in compile-time messages, interested in the run-time errors (if any) from SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2014 at 9:45 am
Sean Lange (3/17/2014)
altana (3/17/2014)
It still works same.How do you know that the users are entering the right values in your textbox?
Your C# code is passing the value from a textbox as p2.
cmd.Parameters.Add(new SqlParameter("@p2", f1.textBox1.Text));
If the user enters any value besides "User Name" or "Serial Number" your proc isn't going to do anything.
if @p2='User Name'
...
else if @p2='Serial Number'
...
For debugging you might consider adding "select @p2" to your code. Then consider changing the UI component from a textbox to a combobox so that YOU control the values going in.
I assigned the item,that I randomly choose from listbox, to the textbox. I want to call this value (text in the textbox) from Db and insert values into this column. So I don't need a combobox.
I have added 'select @p2' to the code. But it doesn't help to work.
March 17, 2014 at 9:45 am
You could greatly simplify this proc and remove about 40% of the lines which don't really provide anything.
create proc [SP$insert]
(
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output
)
as
set nocount on
select @p2 --What value is being passed?
if @p2='User Name'
INSERT INTO env.dbo.Base(user_name) VALUES (@p1)
if @p2='Serial Number'
INSERT INTO env.dbo.Base(ser_num) VALUES (@p1)
SELECT @id = SCOPE_IDENTITY() --If nothing is inserted this will be NULL
set nocount off
You also really need look at this article. It explains the difference between SCOPE_IDENTIY and @@IDENTITY. The difference is subtle but extremely important to understand if you are using either of them.
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 9:48 am
altana (3/17/2014)
Sean Lange (3/17/2014)
altana (3/17/2014)
It still works same.How do you know that the users are entering the right values in your textbox?
Your C# code is passing the value from a textbox as p2.
cmd.Parameters.Add(new SqlParameter("@p2", f1.textBox1.Text));
If the user enters any value besides "User Name" or "Serial Number" your proc isn't going to do anything.
if @p2='User Name'
...
else if @p2='Serial Number'
...
For debugging you might consider adding "select @p2" to your code. Then consider changing the UI component from a textbox to a combobox so that YOU control the values going in.
I assigned the item,that I randomly choose from listbox, to the textbox. I want to call this value (text in the textbox) from Db and insert values into this column. So I don't need a combobox.
I have added 'select @p2' to the code. But it doesn't help to work.
I think you might be confused about what I am saying. Adding it to the proc isn't going to magically make your proc work. If you are calling this from your front end you might as well remove it because it isn't going to help you.
The point I was trying to make is that you let the user decide what value to pass for @p2 and unless it is one of 2 precise values your proc does exactly what you are reporting...nothing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2014 at 9:57 am
May also be worth taking a look at using the OUTPUT clause in place of SCOPE_IDENTIY and @@Identity as well
March 18, 2014 at 1:36 am
Sean Lange (3/17/2014)
You could greatly simplify this proc and remove about 40% of the lines which don't really provide anything.
create proc [SP$insert]
(
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output
)
as
set nocount on
select @p2 --What value is being passed?
if @p2='User Name'
INSERT INTO env.dbo.Base(user_name) VALUES (@p1)
if @p2='Serial Number'
INSERT INTO env.dbo.Base(ser_num) VALUES (@p1)
SELECT @id = SCOPE_IDENTITY() --If nothing is inserted this will be NULL
set nocount off
You also really need look at this article. It explains the difference between SCOPE_IDENTIY and @@IDENTITY. The difference is subtle but extremely important to understand if you are using either of them.
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]
How can I use "select @p2" in SP? I have already identified as 'f1.textBox1.Text' in C# code.
March 18, 2014 at 1:53 am
GilaMonster (3/17/2014)
And when you run the proc from SQL as I suggested?Those are compile-time warnings, not interested in compile-time messages, interested in the run-time errors (if any) from SQL Server.
That's the problem.It's not getting error despite discarding try-catch block.So I can't see the failure.
March 18, 2014 at 4:07 am
use env
go
create proc [SP$insert2](
@p1 nvarchar(100),
@p2 nvarchar(100),
@id int output
)
as
begin
set nocount on
INSERT INTO env.dbo.Base(user_name) VALUES (@p1)
SELECT @id = SCOPE_IDENTITY()
SELECT @p2
WHERE @p2 = 'User Name'
INSERT INTO env.dbo.Base(ser_num) VALUES (@p1)
SELECT @id= SCOPE_IDENTITY()
SELECT @p2
WHERE @p2 = 'Serial Number'
set nocount off
end
go
Now I can insert values but into the both columns. How can where-condition work?
March 18, 2014 at 4:12 am
What are you trying to do?
Of course that's going to insert the value twice, there's no conditions around the insert. The WHERE is just on a select of the parameter, kinda pointless to do to be honest.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2014 at 4:27 am
Wenn I click the item 'User Name' from the listbox(assigned to the Textbox), I want to insert a value into the 'user_name' column in the DB. That's all. I have totally 25 Columns.
March 18, 2014 at 4:35 am
Insert a new row?
So, if the user selects all 25 columns one by one, then you want to have 25 separate rows in the database table, each with a single column populated?
Did you ever run the original procedure from SQL Server (not from whatever client you're writing)? Did you look at the output there and try to debug what was happening.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2014 at 5:51 am
GilaMonster (3/18/2014)
Insert a new row?So, if the user selects all 25 columns one by one, then you want to have 25 separate rows in the database table, each with a single column populated?
Did you ever run the original procedure from SQL Server (not from whatever client you're writing)? Did you look at the output there and try to debug what was happening.
Actually first I have to control the product serial number.If it exists, I add the value in the related row. If not, then first I insert the serial number into the DB. But I start with insert-action, I am planning to query serial number after this. I think I'm wrong.
Yes, it runs successful without mistake. Output is: 'Auto-attach to process '[1196] [SQL] 10.34.16.219' on machine '10.34.16.219' succeeded.
The thread '10.34.16.219 [55]' (0x9c0) has exited with code 0 (0x0).'
March 18, 2014 at 6:46 am
I think you maybe need to go back to the drawing board, get the requirements clear and then sit down with someone there who knows SQL and go through how insert and update statements work.
The stated requirements have been changing through the thread and the code you initially posted is no where close to what you just stated need to be done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply