[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

How to use a store procedure when I do an insert to return an id from an upload?

I am currently inserting info into a MS SQL database when I do an upload, I need to somehow create a Stored Procedure to return an id.  Not sure how to do this.
0
jettman26
Asked:
jettman26
  • 7
  • 6
  • 2
  • +2
1 Solution
 
lijunguoCommented:
could you tell me which insert you want to make change?
0
 
jettman26Author Commented:
Probably the insert when I save the infomation, not when uploading.  That is where I would get the most benefit isn't it?

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        'If the info_id parameter was passed from Default.aspx
        If Not (Request.QueryString("info_id") Is Nothing) Then
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', info_date = '" & calendar.SelectedDate & "', notes = '" & Left(Replace(notesText.Text, "'", ""), 7000) & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"
            'If the info_id parameter was not passed from Default.aspx
        Else
            myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
        End If

        myConn.Open()
        Try
            If Not (Request.QueryString("info_id") Is Nothing) Then
                myCommand.ExecuteNonQuery()
                lblMessage.Text = "Record Successfully Added"
                myConn.Close()
                Response.Redirect("Default.aspx")
            Else
                GetInfoID()
                myCommand.ExecuteNonQuery()
                lblMessage.Text = "Record Successfully Added"
                myConn.Close()
                Response.Redirect("Default.aspx")
            End If
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub
0
 
sachiekCommented:
object obj = myCommand.ExecuteScalar() - This will also return a value to you.



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
b1xml2Commented:
suppose the sql table is like this:
tbl_user
UserId int identity (1,1)
Name varchar(100)


suppose the sql stored procedure is like so:

create proc InsertData
@name varchar(100)
as
insert tbl_user (name) values(@name)
select @@IDENTITY

[VB.NET]
Dim connection As New SqlConnection(...)
Dim command As connection.CreateCommand()
command.CommandText = "InsertData"
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@RETURN_VALUE",SqlDbType.Int,4)
command.Parameters(0).Direction = ParameterDirection.ReturnValue
command.Parameters.Add("@name",SqlDbType.VarChar,100).Value = "yoohoo"
connection.Open()
Dim id As Integer = DirectCast(command.ExecuteScalar(),Integer)
connection.Close()
command.Dispose()

[C#]
SqlConnection connection = new SqlConnection (...);
SqlCommand command = connection.CreateCommand();
command.CommandText = "InsertData";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@RETURN_VALUE",SqlDbType.Int,4);
command.Parameters[0].Direction = ParameterDirection.ReturnValue
command.Parameters.Add("@name",SqlDbType.VarChar,100).Value = "yoohoo";
connection.Open();
int id = (int)command.ExecuteScalar();
connection.Close();
command.Dispose();

0
 
sachiekCommented:
To get back value you can try this statment in your code.

object obj = myCommand.ExecuteNonQuery()

int x;
  x = (int) obj;

Sachi
0
 
lijunguoCommented:

--use 3 input parameter, 1 output parameter.
--run the following code from SQL query analyzer to create store procedure sp_info
-- *********start
CREATE PROCEDURE sp_info
(

@class_name char(30),
@info_date datetime,
@notes char(7000),
@rtn_id int output
)

as

insert into info(class_name,info_date,notes) values (@class_name,@info_date,@notes)
SELECT @rtn_id = SCOPE_IDENTITY() -- make sure return id is from this transaction
GO


-- ************end


replace btnSave_Click method with this one, you may rewrite insert_parameter with an array make it look nice and neat

    Public Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "server=(local);database=classes; UID=lguo;PWD=lguo"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        If Not (Request.QueryString("info_id") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', info_date = '" & calendar.SelectedDate & "', notes = '" & Left(Replace(notesText.Text, "'", ""), 7000) & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"

        Else

            'myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
            'Imports System.Data.SqlClient first
            Dim insert_parameter As SqlParameter
            myCommand.CommandText = "sp_info"
            myCommand.CommandType = CommandType.StoredProcedure

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@class_name"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.Char
            insert_parameter.Size = 30
            insert_parameter.Value = classDownList.SelectedValue.ToString.Trim
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@info_date"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.DateTime
            insert_parameter.Value = calendar.SelectedDate
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@notes"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.Char
            insert_parameter.Size = 7000
            insert_parameter.Value = Left(Replace(notesText.Text, "'", ""), 7000)
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@rtn_id"
            insert_parameter.Direction = ParameterDirection.Output
            insert_parameter.SqlDbType = SqlDbType.Int
            myCommand.Parameters.Add(insert_parameter)


        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()

            lblInfoID.Text = CType(myCommand.Parameters("@rtn_id").Value, String)

            lblMessage.Text = "Record Successfully Added"
            myConn.Close()
            'Response.Redirect("Default.aspx")
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub


    go to this method
    Public Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick

 also replace this line
 myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (IDENT_CURRENT('INFO'), '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
 with
 myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (" & Integer.Parse(lblInfoID.Text) & ",'" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"


you may store return value in an integer, not a label.
0
 
jettman26Author Commented:
lijunguo,
VS.NET says "Type 'SqlParameter' is not defined" for this line in the  btnSave_Click method.
Dim insert_parameter As SqlParameter
0
 
dharmesh_amityCommented:
CREATE PROCEDURE sp_info
(

@class_name char(30),
@info_date datetime,
@notes char(7000)
)

as
DECLARE @rtn_id INT

insert into info(class_name,info_date,notes) values (@class_name,@info_date,@notes)
SELECT @rtn_id = SCOPE_IDENTITY() -- make sure return id is from this transaction

RETURN @rtn_id
GO
0
 
lijunguoCommented:
need to add this line at the beginning.
Imports System.Data.SqlClient

 'myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
            'Imports System.Data.SqlClient first

Actually, I've already mentioned in the comment
0
 
jettman26Author Commented:
lijunguo,
what did you mean by
"you may store return value in an integer, not a label."?

Also, where do I use the Return value?  I don't see where I was supposed to use it for an output in the code.
0
 
lijunguoCommented:
Did you see the post which I made yesterday?

 go to this method
    Public Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick

 also replace this line
 myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (IDENT_CURRENT('INFO'), '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
 with
 myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (" & Integer.Parse(lblInfoID.Text) & ",'" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
^^^^^^^^^^^^^^^^^^
0
 
lijunguoCommented:
Did you see this line within try block I posted yesterday?

lblInfoID.Text = CType(myCommand.Parameters("@rtn_id").Value, String)
0
 
jettman26Author Commented:
sorry lijunguo.  I apparently didn't get it all copied.

I am getting the error:
"ERROR: EXECUTE permission denied on object 'sp_info', database 'master', owner 'dbo'. "

I wonder why it says master.  shouldn't it say Classes?
0
 
lijunguoCommented:
where did you create 'sp_info'? It looks like you create 'sp_info' in the 'master' db, not 'classes' db.

So you use 'Enterprise Manager', go to 'master'->'stored procedures', find 'sp_info' and delete it.
Then recreate it from 'Classes' db, and set appriate permission.
0
 
jettman26Author Commented:
lijunguo,
Thanks, it worked.
Are there any other places in my code that you think I would benefit from using a stored procedure?
0
 
lijunguoCommented:
You're welcome.

I prefer to use stored procedures. There are many advantages. Such as security, performance.
Here is a link which cover more details.
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx

Since it's working, I recommend you change some of your DB structure.

such as notes char(7000) to varchar(7000)

You may post a question on SQL server, how to choose a data type for your DB structure. Because I have a small project to  deliver this weekend just from my boss. Cann't help you more this week. Sorry about it.

regards,
Lijunguo
0
 
jettman26Author Commented:
Thanks a lot.  I have learned much from you.
Maybe next week we can
 "Use drag & drop to generate sqldataadapter, dataset, and generate select, update, delete,insert is easy to do at the beginning, but it's quite hard to debug later. If you want to change it, the whole structure will change. If you want to do it, let me know."
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now