sql stored procedure problem

Question

0.00/5 (No votes)

See more:

hello all
i have one small assignment in windows apps with sql database.
I have created two tables one with ID,FN, LN and second one with LocationID, city, state, ID.
I have also created relationship between this two tables on ID
and assignment is all about stored procedure so we have to use sp to insert data
this is my sp

ALTER PROCEDURE dbo.NewInsertCommand
(
	@FirstName nvarchar(25),
	@LastName nvarchar(25),
	@City nvarchar(50),
	@State nvarchar(50)
)
AS
	SET NOCOUNT OFF;
	DECLARE @UserID int;
	
INSERT INTO [UserData] ([FirstName], [LastName]) VALUES (@FirstName, @LastName);
	
SELECT @UserID=@@IDENTITY;
INSERT INTO [Location] (City,State,UserID)
VALUES (@City, @State, @UserID);

and bellow this is my vb for button click event

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        SqlCommand1 = New SqlCommand("NewInsertCommand", SqlConnection1)
        SqlCommand1.CommandType = CommandType.StoredProcedure
        SqlCommand1.CommandText = "NewInsertCommand"
        SqlCommand1.Parameters.AddWithValue("FirstName", FirstName.Text)
        SqlCommand1.Parameters.AddWithValue("LastName", LastName.Text)
        SqlCommand1.Parameters.AddWithValue("City", ComboBox1.SelectedItem.ToString)
        SqlCommand1.Parameters.AddWithValue("State", ComboBox2.SelectedItem.ToString)

        SqlConnection1.Open()
        SqlCommand1.ExecuteNonQuery()
        SqlConnection1.Close()
        MsgBox("Item saved")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

so now problem is when ever i try to insert data in to database it dosen't store in to it but if i right click in stored procedure file (dbo.NewInsertCommand) and select step into and input all the parameters in run sp dialog box then it's saves data into database
any help please

thanks again

Posted 4-Jul-10 0:31am

bapu2889

Updated 4-Jul-10 5:26am

Not Active

v2

Add a Solution

Comments

Sandeep Mewara 4-Jul-10 6:51am

Have you used DEBUGGER and checked the code part is running fine and no error is there?

bapu2889 4-Jul-10 7:09am

hello thanks for your rep.
yes but to be very honest i dont know what to check.
but it's taking all the strings from textbox and then run ExecuteNonQuery and it displays item saved message box so could you please tell me what should i check
waiting for your kind help
thanks

3 solutions

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)

Sandeep Mewara · Answer 1 · 2010-07-04T07:49:00

Ok.

Looks like the way you have added the parameters to the SP are not correct. Parameter names are not properly passed. Stored Procedures won't stop if there is an error.

C#

SqlCommand1.Parameters.AddWithValue("FirstName", FirstName.Text)

is not the right way to add the parameters.

Try:

C#

SqlParameter objParam1 = new SqlParameter();
objParam1.ParameterName = "@FirstName";
objParam1.Value = "Sandeep";
SqlCommand1.Parameters.Add(objParam1);

Your SP is fine. You need to make changes in your code.
If needed, for more details have a look here:
SqlCommand.Parameters [^]

Not Active · Answer 2 · 2010-07-04T05:37:00

Solution 1

Use SQL Profiler to see what calls are being made and what values are being sent to your stored proc or add some trace type statements to help understand what is happening while not debugging.

Two other things, unrelated to your problem

SET NOCOUNT ON rather than SET NOCOUNT OFF

Use SCOPE_IDENTITY rather than @@IDENTITY

Posted 4-Jul-10 5:37am

Not Active

Comments

bapu2889 4-Jul-10 12:31pm

hello mark
thanks for your rep.
could you please tell me how to use scope_identity
waiting for your kind rep.

thanks

bapu2889 4-Jul-10 12:42pm

hello again
i have also tried this

ALTER PROCEDURE dbo.NewInsertCommand
(
@FirstName nvarchar(25),
@LastName nvarchar(25),
@City nvarchar(50),
@State nvarchar(50)

)
AS
SET NOCOUNT ON;
DECLARE @UserID int;

INSERT INTO [UserData] ([FirstName], [LastName]) VALUES (@FirstName, @LastName);

SET @UserID = SCOPE_IDENTITY();

INSERT INTO [Location] (City,State,UserID)
VALUES (@City, @State, @UserID);

but still no luck

bapu2889 · Answer 3 · 2010-07-06T00:05:00

Hello all
thanks every one who has posted for my question.
this is what i have done and now it's working fine without any problem all 4 operation insert, delete, update, and search with stored procedure.
but only one thing i dont understand when i started this assignment i just drag and drop sqlcommand and sqlconnection but it was not working and then i have removed those two and add this

VB

Private Sub InsertData()

<pre>    Try
        Dim Con As New SqlConnection
        Con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & MyString & "\TrialDataBase.mdf;Integrated Security=True;User Instance=True"
        Dim cmd As New SqlCommand("NewInsertCommand", Con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstName.Text
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastName.Text
        cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = ComboBox1.SelectedItem.ToString
        cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = ComboBox2.SelectedItem.ToString

        Con.Open()
        cmd.ExecuteNonQuery()
        MsgBox("Data Saved Successfully !", MsgBoxStyle.Information)
        Con.Close()
        FirstName.Clear()
        LastName.Clear()
    Catch ex As SqlException
        MsgBox(ex, MsgBoxStyle.Critical)
    End Try

End Sub

but anyway it's done and dusted but i still want to know why drag and drop dosen't work
thanks again
have a nice day

sql stored procedure problem

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0