VB.Net - Request ID, On Save, display auto ID

Question

0.00/5 (No votes)

See more:

William,

Your answer did work and I will respond accordingly and rate....

How do I get the ID to display in both the browser and in a label.

I am currently doing one or the other but not both.
In the example, both are uncommented but I can only use one or the other or the page tries to process twice.

<pre lang="vb">If employee_id <> -1 Then
               lblRecordNumber.Text = employee_id.ToString()
               Response.Redirect("thelinktopage.aspx?id=" & employee_id.ToString())
           End If

Trying to implement a click event that will call the update stored procedure for an exiting employee

Call the insert stored procedure new employee.

I need to also display the ID in a label or textbox.

Here's my beginning:

VB

Protected Sub Button_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate2.Click
        _blnUpdated = False
        Dim sqlConn As New SqlClient.SqlConnection
        Dim lblRecordNum As String
        Dim intEmployeeID As Integer
        Dim perEmployee_ID As New SqlParameter("@employee_id", SqlDbType.Int)

        sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
        sqlConn.Open()
        Dim sqlCmd As New SqlCommand

        If _blnUpdated = True Then
            sqlCmd.CommandText = "IUpdateEmployee" ' Stored Procedure to Call
            'intEmployeeID = Integer.Parse(Request.QueryString("id"))
        Else
            sqlCmd.CommandText = "InsertNewEmployee"
        End If
        With sqlCmd
            .Parameters.AddWithValue("@employee_id", intEmployeeID)

[Modified: removed extra pre to format code correctly tag]

Posted 7-Apr-10 3:11am

technette

Updated 9-Apr-10 15:20pm

v4

Add a Solution

1 solution

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)

Gregory Gadow · Answer 1 · 2010-04-07T05:25:00

It sounds like you either have an existing employee with an existing employee ID, or you are creating a new employee and need the stored procedure to return a newly created employee ID. Is this correct?

If so, then you might try something like this:

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sqlConn As New SqlClient.SqlConnection
    Dim intEmployeeID As Integer
    Dim perEmployee_ID As New SqlParameter("@employee_id", SqlDbType.Int)
    Dim sqlCmd As New SqlCommand

    Dim _blnUpdated As Boolean = True

    If _blnUpdated Then
        sqlCmd.CommandText = "sp_ManageEmployee"
        perEmployee_ID.Value = intEmployeeID
        perEmployee_ID.Direction = ParameterDirection.Input
    Else
        sqlCmd.CommandText = "sp_ManageEmployee"
        perEmployee_ID.Value = -1
        perEmployee_ID.Direction = ParameterDirection.InputOutput
    End If

    sqlCmd.Parameters.Add(perEmployee_ID)
    sqlCmd.ExecuteNonQuery()

    If _blnUpdated Then
        intEmployeeID = Convert.ToInt32(sqlCmd.Parameters("@employee_id").Value)
    End If
End Sub

You can direct both conditions to the same stored procedure: if the employee id parameter is -1 then do the "add new" block; otherwise do the other block. This will help you keep related code in the same stored procedure, which will make maintenance a bit easier. Make sure that the @employee_id parameter in your stored procedure is flagged for output:

SQL

CREATE PROCEDURE sp_ManageEmployee
    @employee_id INT OUTPUT
    ...

If you are adding a new employee, set the parameter direction in your code to be input/output. Assuming that the EmployeeId field is flagged as Identity, you can use SELECT @employee_id = SCOPE_IDENTITY() after your insert to get the newly created id number. Because the parameter is flagged for output, it will be passed back up to your calling code.