Solved

How to display reference number after a form is submitted

Posted on 2011-09-29
25
285 Views
Last Modified: 2012-05-12
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
Comment
Question by:sarah_siu
  • 10
  • 9
  • 4
  • +1
25 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36816143
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
 
LVL 12

Expert Comment

by:Kaushal Arora
ID: 36816165
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
 

Author Comment

by:sarah_siu
ID: 36816166
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
 
LVL 33

Accepted Solution

by:
paulmacd earned 400 total points
ID: 36816208
...
        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
 

Author Comment

by:sarah_siu
ID: 36816230
Thanks KaushalArora for the link - looking into it right now.

Paulmacd - is it possible to incorporate another try statement a try statement?
0
 

Author Comment

by:sarah_siu
ID: 36816239
i mean - is it possible to incorporate a try statement in another try statement?

0
 
LVL 12

Expert Comment

by:Kaushal Arora
ID: 36816330
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36816345
Yes, you can nest Try...Catches.  Note my sample is just pseudo code, although it's close to what you'd want.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36816346
certainly possible to have nested try catch blocks in a stored procedure...


to which code layer are you refering?
0
 

Author Comment

by:sarah_siu
ID: 36816805
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36816828
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
 

Author Comment

by:sarah_siu
ID: 36816863
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:paulmacd
ID: 36816893
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
 

Author Comment

by:sarah_siu
ID: 36816958
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36816971
Well it's CATCHing an error somewhere.  What line does it fail on?  Does it get as far as the
     strReferenceNumber =...
line?
0
 
LVL 12

Assisted Solution

by:Kaushal Arora
Kaushal Arora earned 100 total points
ID: 36816989
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
 

Author Comment

by:sarah_siu
ID: 36817033
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36817053
What you posted is missing a space before "AS".  Is your code like that?  That would break it...
0
 

Author Comment

by:sarah_siu
ID: 36817173
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
 
LVL 12

Expert Comment

by:Kaushal Arora
ID: 36817180
Have you checked stored procedure method?
0
 
LVL 33

Expert Comment

by:paulmacd
ID: 36817255
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
 

Author Comment

by:sarah_siu
ID: 36817710
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36817795
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
 

Author Closing Comment

by:sarah_siu
ID: 36956872
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
 
LVL 33

Expert Comment

by:paulmacd
ID: 36956882
Happy to help.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now