[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

How to display reference number after a form is submitted

Hi everyone,

I am building a web application that allows a user to submit and retrieve data that they have submitted.

I am able to do the coding to submit and search for these forms based on reference numbers but i can't seem to display the reference numbers once a form is submitted.

The reference number is created on the database as soon as a record hits the table.  I would like to make it so that when the user presses the "submit" button, a message indicating their reference number will pop up i.e. ("Reference Number ___ has been assigned to your record").  

Here's the code to submit one of the forms - i would like to make it so that i can retreive the reference number INTO the blank space above:

 
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim validated As Boolean

   
    'CI Submit and clear

    Protected Sub btnSubmit2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit2.Click
    

        ' Submit CI 

        'Open connection string and insert data into the database, Data table

        Dim ConnectionString As String
        Dim Connection As SqlConnection
        Dim Adapter As New SqlDataAdapter
        Dim sql As String
        Dim ds As New DataSet


        ConnectionString = "Data Source=00.0.000.00;Initial Catalog=QOI;User ID=user;pwd=pwd;"
        Connection = New SqlConnection(ConnectionString)


        sql = "insert into Reports_Entry(Customer_ID, Account_Number, Report_Description_1, PBR_ID, Report_Acronym, Date_Occurred, Review_Flag) VALUES ('" & tbCustomerid2.Text & "','" & tbaccountnumber2.Text & "', '" & DropDownList3.SelectedValue & "','" & tbPBRID.Text & "', '" & lblacronym.Text & "', Convert(datetime,'" & Trim(tbDate2.Text) & "',103), '" & "No" & "' );"



        Try
            Connection.Open()
            Adapter.InsertCommand = New SqlCommand(sql, Connection)
            Adapter.InsertCommand.ExecuteNonQuery()
	    Response.Write("<span style='color: green'>Reference Number ____ has been assigned to your record</span>")


        Catch ex As Exception
            Response.Write("<span style='color: red'>Reference Number ____ has NOT been assigned to your record</span>")

        End Try

       

        
        tbCustomerid2.Text = ""
        tbaccountnumber2.Text = ""
        DropDownList3.SelectedIndex = -1
        lblacronym.Text = ""
        lblreportheading.Text = ""
        ddlreportname.SelectedIndex = -1
        tbPBRID.Text = ""
        lblagentname.Text = ""
        lblpavilion.Text = ""
        lblmanager.Text = ""
        lblarea.Text = ""
        lblregion.Text = ""
        lblbusiness.Text = ""
        lblerror2.Text = ""
        Employeestatspanel.Visible = False
        EmployeePanel.Visible = False
        CIPanel.Visible = False
        RefNumPanel.Visible = False

    End Sub

   End Class

Open in new window



Thanks a lot for your time!
0
sarah_siu
Asked:
sarah_siu
  • 10
  • 9
  • 4
  • +1
2 Solutions
 
LowfatspreadCommented:
you need to start using stored procedures to do your database access it is unsecure , and not ideal to use direct dml sql from
your client side processes.

the stored procedure can return data to its caller via output (input/output really) variables.


how actually does the database/application allocate a reference number?
(identity, trigger, ...)

if its an identity you could append Select scope_identity() as refnum to your existing sql...

but i'd strongly advise you to utilise stored procedures.
0
 
Kaushal AroraTechnical AnalystCommented:
You can use the stored procedure for inserting and for returning the autonumber/identity column you can use the Scope_Identity().
and in code behind you can use ExecuteScaler() to get that value.

Kindly refer to this link for details.
http://www.4guysfromrolla.com/articles/062905-1.aspx

Hope it helps you.
0
 
sarah_siuAuthor Commented:
Thanks for your reply.  I am very new to all this coding - would you be able to provide a pseudo-code so i can better understand how i can incorporate this into my code?

Thanks!
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Paul MacDonaldDirector, Information SystemsCommented:
...
        Try
            Connection.Open()
            Adapter.InsertCommand = New SqlCommand(sql, Connection)
            Adapter.InsertCommand.ExecuteNonQuery()
           
            Dim strReferenceNumber as string
            Dim qryGetRefNum as string = SELECT TOP ReferenceNumber FROM Reports_Entry WHERE Customer_ID=tbCustomerid2.Text
            Dim commCommand as new SQLCommand(qryGetRefNum, Connection)
            strReferenceNumber = cstr(commCommand.ExecureScalar())

          Response.Write("<span style='color: green'>Reference Number" & strReferenceNumber  & "has been assigned to your record</span>")


        Catch ex As Exception
            Response.Write("<span style='color: red'>Reference Number ____ has NOT been assigned to your record</span>")

        End Try
...

I'm not sure you could reliably retrieve the Reference Number in the Catch, since you don't know if the database was updated or not.
0
 
sarah_siuAuthor Commented:
Thanks KaushalArora for the link - looking into it right now.

Paulmacd - is it possible to incorporate another try statement a try statement?
0
 
sarah_siuAuthor Commented:
i mean - is it possible to incorporate a try statement in another try statement?

0
 
Kaushal AroraTechnical AnalystCommented:
Yes it is possible to write a nested try/catch block in other try/catch block.

But the way my friend @paulmacd is saying to fetch the identity column can be redundant if the name field of your database is not unique. otherwise it is fine.

@Sarah_siu: Do you still want the pusedo-code for your problem?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Yes, you can nest Try...Catches.  Note my sample is just pseudo code, although it's close to what you'd want.
0
 
LowfatspreadCommented:
certainly possible to have nested try catch blocks in a stored procedure...


to which code layer are you refering?
0
 
sarah_siuAuthor Commented:
Hi KaushalArora, yes, if possible, i would still like the pseudocode for my problem. much thanks.

paulmacd: I've tried using your code and it doesn't seem to be working - not sure if i'm doing something wrong?  when i press submit, the data is actually submitted but the response.write message displays "Reference Number has NOT been assigned to your record" instead of the reference number...not sure why...but here's the code.

 
' Submit CI 

        'Open connection string and insert data into the database, Data table
        
	Dim ConnectionString As String
        Dim Connection As SqlConnection
        Dim Adapter As New SqlDataAdapter
        Dim sql As String
        Dim ds As New DataSet


        ConnectionString = "Data Source=0.0.000.00;Initial Catalog=QOI;User ID=user;pwd=pwd;"
        Connection = New SqlConnection(ConnectionString)


        sql = "insert into Reports_Entry(Customer_ID, Account_Number, Report_Description_1, PBR_ID, Report_Acronym, Date_Occurred, Review_Flag) VALUES ('" & tbCustomerid2.Text & "','" & tbaccountnumber2.Text & "', '" & DropDownList3.SelectedValue & "','" & tbPBRID.Text & "', '" & lblacronym.Text & "', Convert(datetime,'" & Trim(tbDate2.Text) & "',103), '" & "No" & "' );"



        Try
            Connection.Open()
            Adapter.InsertCommand = New SqlCommand(sql, Connection)
            Adapter.InsertCommand.ExecuteNonQuery()

            Try
                Connection.Open()
                Adapter.InsertCommand = New SqlCommand(sql, Connection)
                Adapter.InsertCommand.ExecuteNonQuery()

                Dim strReferenceNumber As String
                Dim qryGetRefNum As String = "SELECT MAX (Reports_Entry_ID)AS ID FROM Reports_Entry'"
                Dim commCommand As New SqlCommand(qryGetRefNum, Connection)
                strReferenceNumber = CStr(commCommand.ExecuteScalar())
		
                Response.Write("<span style='color: red'>Reference Number" & "strReferenceNumber" & "has been assigned to your record</span>")


            Catch ex As Exception
                Response.Write("<span style='color: red'>Reference Number has NOT been assigned to your record</span>")

            End Try


        Catch ex As Exception
            Response.Write("<span style='color: red'>Data Not Submitted</span>")

        End Try

Open in new window

0
 
Paul MacDonaldDirector, Information SystemsCommented:
Replace this line...
           Dim qryGetRefNum As String = "SELECT MAX (Reports_Entry_ID) AS ID FROM Reports_Entry'"
...with this...
           Dim qryGetRefNum As String = "SELECT MAX (Reports_Entry_ID) AS ID FROM Reports_Entry"
...and try again.

You had an extra apostrophe in there.
0
 
sarah_siuAuthor Commented:
Hi Paulmacd - still the same problem - the data is submitted into the table but the message i get is "Reference Number has NOT been assigned to your record" ... Did i place it in the wrong place?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
The placement of the code looks fine.  You're updating the table, then going back in to grab the most recent record.  I will note though, that unless there's only one person using this feature, grabbing the last entry doesn't mean your grabbing the one you just put into the database.

Are you using Visual Studio?  Can you step through the code to debug it?



Also, I just noticed this line is wrong as well...
                  Response.Write("<span style='color: red'>Reference Number" & "strReferenceNumber" & "has been assigned to your record</span>")
...should be...
               Response.Write("<span style='color: red'>Reference Number" & strReferenceNumber & "has been assigned to your record</span>")
0
 
sarah_siuAuthor Commented:
Yup - i tried debugging but it said nothing's wrong...and i've actually tried replacing response.write statement with the parameter to see if at least the reference number will come back up but it did not come back up.  

Not sure why it's not reading the parameter or the first response.write statement...

Also, yup - i do know if more than one person enters data at the same time, we run the risk of getting the wrong ref number - but not too many people will be using this application and it will likely be one at a time so i think we're okay using MAX for now...But thanks for the heads up =)
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Well it's CATCHing an error somewhere.  What line does it fail on?  Does it get as far as the
     strReferenceNumber =...
line?
0
 
Kaushal AroraTechnical AnalystCommented:
You can also work it this way:

int newRowID;

using (SqlConnection MyConnection = new SqlConnection("I'm hiding this info because I don't want you in my database"))
{
    DateTime postDate = DateTime.Now;

    string sqlString = "INSERT INTO MyTable (BoardID, Topic, UserID) VALUES (@BoardID, @Topic, @UserID); SELECT NewID = SCOPE_IDENTITY ()";
    SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
    objCmd.Connection.Open();
    objCmd.Parameters.Add(new SqlParameter("@BoardID", boardID));
    objCmd.Parameters.Add(new SqlParameter("@Topic", topic);
    objCmd.Parameters.Add(new SqlParameter("@UserID", currentUser.UserID));
    SqlDataReader dataReader = objCmd.ExecuteReader();

    if (dataReader.HasRows)
    {
        dataReader.Read();
        newRowID = Convert.ToInt32(dataReader["NewID"]);
    }

    dataReader.Close();
}

for Stored Procedure example:
CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category NVARCHAR(15)
AS
BEGIN
  SET NOCOUNT ON;
 
  -- Insert statements for procedure here
  INSERT INTO Categories (CategoryName) VALUES (@Category)
  SELECT SCOPE_IDENTITY()
END


And the Code Behind Code:
Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

For reference check the link:
http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Hope it helps.
0
 
sarah_siuAuthor Commented:
Hi PaulMacD - actually, when i toggled it for a breakpoint, it says my SQL Statement is incorrect...qryGetRefNum As String = "SELECT MAX (Reports_Entry_ID)AS Expr1 FROM Reports_Entry"

I checked the code to see if it gives me the correct results and it does...do you perhaps know why or if i'm doing something wrong?  

Thanks so much for your help.

KaushalArora: thank you for the psuedo code!  I will look into the stored procedure way as well
0
 
Paul MacDonaldDirector, Information SystemsCommented:
What you posted is missing a space before "AS".  Is your code like that?  That would break it...
0
 
sarah_siuAuthor Commented:
Still the same thing : Same line that is getting the error

Dim QueryRefNum As String = "SELECT MAX (Reports_Entry_ID) AS Expr1 FROM Reports_Entry"

 
Connection.Open()
                Adapter.InsertCommand = New SqlCommand(sql, Connection)
                Adapter.InsertCommand.ExecuteNonQuery()

                Dim strReferenceNumber As String
                Dim QueryRefNum As String = "SELECT MAX (Reports_Entry_ID) AS Expr1 FROM Reports_Entry"

                Dim commCommand As New SqlCommand(QueryRefNum, Connection)
                strReferenceNumber = CStr(commCommand.ExecuteScalar())

                Response.Write("<span style='color: green'>Reference Number" & "strReferenceNumber" & "has been assigned to your record</span>")


            Catch ex As Exception
                Response.Write("<span style='color: red'>Reference Number" & "strReferenceNumber" & "has been assigned to your record</span>")

            End Try

Open in new window

0
 
Kaushal AroraTechnical AnalystCommented:
Have you checked stored procedure method?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Beats me.  That line is pretty straightforward, as is the line before it.  

Does it just say it's a syntax error or is there more information?  
0
 
sarah_siuAuthor Commented:
no more information...but now it's saying the statement "executescalar" is wrong...

KaushalArora - i'm looking at the stored procedure as well, but it's a lot more complicated than Paulmacd's solution.  Let me see how to make this work.

thanks for all of your help!
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Yeah, sorry, I don't get it unless there's something funny about your data source.

You could try leveraging your DataAdapter instead, but I don't know what the implications of that are for the rest of your application:
      ...
      Adapter.InsertCommand.ExecuteNonQuery()

      Dim strReferenceNumber As String
      Dim qryGetRefNum As String = "SELECT MAX (Reports_Entry_ID)AS ID FROM Reports_Entry'"
      Adapter.SelectCommand = New SqlCommand(qryGetRefNum , Connection)
      strReferenceNumber = Adapter.SelectCommand.ExecuteScalar()

      Response.Write("<span style='color: green'>Reference Number" & strReferenceNumber & "has been assigned to your record</span>")
      ...
0
 
sarah_siuAuthor Commented:
It turns out that all i really needed was Paulmacd's code and i didn't need to embed in any other code!  so this is what it looks like:

  Try
            Connection.Open()
            Adapter.InsertCommand = New SqlCommand(sql, Connection)
            Adapter.InsertCommand.ExecuteNonQuery()

            Dim strReferenceNumber As String
            Dim QueryRefNum As String = "SELECT MAX ID FROM TABLE"

            Dim commCommand As New SqlCommand(QueryRefNum, Connection)
            strReferenceNumber = commCommand.ExecuteScalar()

            Response.Write("<span style='color: green'>Your entry has been submitted and the reference number " & strReferenceNumber & " has been assigned</span>")

        Catch ex As Exception
            Response.Write("<span style='color: red'>No Reference number has been assigned to your record</span>")

        End Try

Thanks for all your help!!
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Happy to help.
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.

  • 10
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now