• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 748
  • Last Modified:

How do I check for presence of record before trying to insert another unique ID?

Dear Experts,

I need to insert students into a MSSql db. Their student ID number (student_id) is the Primary Key for the table. If I try to insert a student already in the db I get the yellow and white error page telling me I'm trying to insert a duplicate value. I am using a DetailsView Control. I've attached the code.

In the code behind I need to check for the presence of the student in the db BEFORE inserting the student. If the student is present in the db then the user needs to be redirected to another page. If the student is not present in the db the record needs to be inserted.

This is the code behind I have so far:

Partial Class Teachers_AddStudentInfoA
    Inherits System.Web.UI.Page

    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
        e.KeepInInsertMode = True

        Dim myConnection As New SqlConnection("Data Source=****;Initial Catalog=****************;uid=********;pwd=*************")
        myConnection.Open()
        Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = DetailsView1.student_id.value", myConnection)
        Dim count As Integer = CInt(myCommand.ExecuteScalar())

        If count > 0 Then
            myConnection.Close()

            Response.Redirect("http://www.finaosms.com/application/episd2/teachers/AddStudentInfoA.aspx", True)

        End If
        myConnection.Close()

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        DetailsView1.ChangeMode(DetailsViewMode.Insert)


    End Sub

End Class

The error message I receive is:
The multi-part identifier "DetailsView1.student_id.value" could not be bound.

This should be a simple process. However, I'm somewhat new to ASP.net and VB so I'm sure I'm missing something.

Can you help me with my code to check for the presence of the student_id in the db and then depending on whether it's there or not either rredirecting the user or inserting the data.

Thank you.

<form id="form1" runat="server">
    <div>
    
    <h2>            Enter an SID</h2>         
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:EpisdConnectionString %>" 
            DeleteCommand="DELETE FROM [StudentInfoA_local] WHERE [student_id] = @student_id" 
            InsertCommand="INSERT INTO [StudentInfoA_local] ([last_name], [first_name], [student_id]) VALUES (@last_name, @first_name, @student_id)" 
            SelectCommand="SELECT [last_name], [first_name], [student_id] FROM [StudentInfoA_local]" 
            UpdateCommand="UPDATE [StudentInfoA_local] SET [last_name] = @last_name, [first_name] = @first_name WHERE [student_id] = @student_id">
            <DeleteParameters>
                <asp:Parameter Name="student_id" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="last_name" Type="String" />
                <asp:Parameter Name="first_name" Type="String" />
                <asp:Parameter Name="student_id" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="last_name" Type="String" />
                <asp:Parameter Name="first_name" Type="String" />
                <asp:Parameter Name="student_id" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" 
            AutoGenerateRows="False" DataKeyNames="student_id" 
            DataSourceID="SqlDataSource1" Height="50px" Width="125px">
            <Fields>
                <asp:BoundField DataField="last_name" HeaderText="last_name" 
                    SortExpression="last_name" />
                <asp:BoundField DataField="first_name" HeaderText="first_name" 
                    SortExpression="first_name" />
                <asp:BoundField DataField="student_id" HeaderText="student_id" ReadOnly="True" 
                    SortExpression="student_id" />
                <asp:CommandField ButtonType="Button" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
    </div>
    </form>

Open in new window

0
bobbellows
Asked:
bobbellows
  • 9
  • 7
  • 2
  • +2
1 Solution
 
HainKurtSr. System AnalystCommented:
use exception

try
...insert here
catch
... check for duplicate error, put a message on page
finally
... cleanup db connections
end try
0
 
HainKurtSr. System AnalystCommented:
oops, I guess I got it wrong...

use inserting event to check the id before insert really happens, if there is one, cancel the event...
if there is id in db, set cancel=true in this event

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx
0
 
ajb2222Commented:
it is an sql error in this line

    Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id = DetailsView1.student_id.value", myConnection)

it should look like this

   Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id =  " & DetailsView1.student_id.value.ToString(),myConnection)
0
Independent Software Vendors: 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!

 
HooKooDooKuCommented:
I'm not an ASP programmer, so there might be something I'm missing...

But to me, it seems the simple answer is to write a "StudentExists( ID )" subroutine that determines if the record already exists by either doing a "count" Query  (SELECT COUNT(*) AS COUNTER FROM tablename WHERE ID=thegiveid and determine the value of "COUNTER"), or simply get a recordset and use EOF to determine if the recordset is empty.  If no records were found that match the given ID, the function can return FALSE.
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

You primary key is identity column then no problem exist.

But if not then get Max ID from table and increment and insert it in else redirect to another page,
0
 
bobbellowsAuthor Commented:
PatelAlpesh: The student_id is given by the client. I cannot change it. It is not an identity column.

HooKooDooKu: That's pretty much what I'm trying to do. See lines in code behind.

HainKurt: Thanks for pointnig me to "Inserting" -- I missed this too. However I think the cancel event might serve the same purpose as the redirect. PLEASE CHECK CODE BELOW to see if that might work. There is still a problem with the command line. Please also double check my If/Then statements and cancel statements -- first time I ever used them -- since I'm fairly new to all of this. The error message I get is: 'student_id' is not a member of 'System.Web.UI.WebControls.DetailsView'.



ajb2222:  PLEASE CHECK CODE BELOW. There is still a problem with the command line. Please also double check my If/Then statements and cancel statements -- first time I ever used them -- since I'm fairly new to all of this. The error message I get is: 'student_id' is not a member of 'System.Web.UI.WebControls.DetailsView'.

Imports System.Web.UI.WebControls.DetailsViewInsertEventArgs

Partial Class Application_episd2_Teachers_ValidateSIDtest
    Inherits System.Web.UI.Page

    Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
        Dim myConnection As New SqlConnection("Data Source=******;Initial Catalog=**************;uid=*************;pwd=**************")
        myConnection.Open()
 
        Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id =  " & DetailsView1.student_id.value.ToString(), myConnection)
        Dim count As Integer = CInt(myCommand.ExecuteScalar())

        If count > 0 Then
            e.cancel = True
        End If

        myConnection.Close()

    End Sub

    Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
        e.KeepInInsertMode = True


    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        DetailsView1.ChangeMode(DetailsViewMode.Insert)


    End Sub

End Class

0
 
ajb2222Commented:
sorry,

you need to use the values being passed in  e.values which are the values being inserted.

      Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id =  " & e.Values["student_id"].ToString(), myConnection)
0
 
bobbellowsAuthor Commented:
ajb2222:
I think we're getting closer. Now I get the error:
 Identifier expected.

I don't know what it's expecting.
Thanks
Bob
0
 
ajb2222Commented:
what line is giving you the error?
0
 
bobbellowsAuthor Commented:
It was the command line. I have copied it below from the error page.



Dim myCommand As New SqlCommand("select count(*) from StudentInfoA_local where student_id =  " & e.Values["student_id"].ToString(), myConnection)
 

0
 
ajb2222Commented:
is  e.Values["student_id"].ToString() giving you a valid value?  

is student_id a string value in the database?  if so then the select needs single quates added.

0
 
bobbellowsAuthor Commented:
ajb2222:

is  e.Values["student_id"].ToString() giving you a valid value?  

     I don't know how to check this independent from the command line?

is student_id a string value in the database?  if so then the select needs single quates added.
     No, an integer in the db.



0
 
ajb2222Commented:
ok,  

In the development environment,  place a stop on the line that is giving you the error.  This will sto the programs execution before running that line.

when the program stops at that line.  At the bottom of the screen you will find a watch window.  select this.  THen type in "e.Values["student_id"].ToString()". this will give you the value at that point of execution.  

here is a tutorial about debugging.
http://dotnetperls.com/debugging

 
0
 
bobbellowsAuthor Commented:
ajb2222:

I'll read the article today and try to do some of the debugging and let you know how it goes.

Bob Bellows
0
 
bobbellowsAuthor Commented:
ajb2222:

I started following the instructions -- realized the problem might be more complicated since I was using  the web application that I developed. So, I created a new application with just the default page as the one which does the checking and inserting. I'll let you know how that goes. Thanks Bob
0
 
bobbellowsAuthor Commented:
ajb2222:

I put a stop in the line that throws the error "Identifier Expected" I ran debugging and I get a dialog box that tells me there were errors and did I want to run the last successful build. This confused me because there wasn't a last successful build. I chose yes. The web browser launched and I got the yellow/white error page with the Identifier Expected" error.

Ran debug again -- at the dialog box chose no. It doesn't appear to stop at the line where I put the break point. An errors panel comes up with the Identifier expected error and a new one "name count is not declared." Not worried about the 2nd one yet because don't have the first one fixed.

I don't get a watch window. All I get is the error window. I went to debugging>windows to open the locals window, as suggested by the article, so I could see the variables and there was no option to choose locals.

Also, if I choose yes at the dialog box and then close the browser, the output panel displays this at the endo of one of the lines: "Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled." so I may not have a debug setting correct.

Where do I go from here?
0
 
ajb2222Commented:
If you get that messagebox, there are errors that are preventing a build.  There is a tab at the bottom that says Error List.  This will list errors, warnings, and notices.  If you click on an error it will take you to the line the error is on.

You might want to get a good book about visual studio.  I get books from the local library.

 
0
 
bobbellowsAuthor Commented:
ajb2222:

The error was in your code. You had ...e.Values["student_id"].To... and the [ ] should have been ( ). I got it to finally work. I'll post the code as the solution when I get a chance.

Bob Bellows
0
 
ajb2222Commented:
sorry,  

The hazards of using C# and VB.   One uses [] and the other ()

0
 
bobbellowsAuthor Commented:
The chosen solution had an error I had to research and correct
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.

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