current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I have an asp.net application in which I am inserting into an sql table using a GridView linked to an SqlDataSource. The problem is that I do not know the 'Identity' parameter to use on insertion. The 'Identity' column should be automatically incrementing, however upon inserting, I get an exception that the Identity param must not be null. How do I either craft the insert statement so that this works or how do I get the next correct identity # to specify? Thanks.

Here is my SqlDataSource code:

 <asp:SqlDataSource ID="VehiclesSqlDS" runat="server" 
        ConnectionString="<%$ ConnectionStrings:RamRideOpsConnectionString %>" 
        SelectCommand="SELECT * FROM [Vehicles]" 
        ConflictDetection="CompareAllValues" 
        InsertCommand="INSERT INTO [Vehicles] ([Identity], [CarNum], [MaxPassengers], [Status], [CurrPassengers], [StartAdd], [EndAdd], [AvgRideTime], [numRides]) VALUES (@Identity, @CarNum, @MaxPassengers, @Status, @CurrPassengers, @StartAdd, @EndAdd, @AvgRideTime, @numRides)" 
        OnInserting="VehiclesSqlDS_Insert"
        OldValuesParameterFormatString="original_{0}">         
        <InsertParameters>
            <asp:Parameter Name="Identity" Type="Int32 "/>
            <asp:Parameter Name="CarNum" Type="Int32" DefaultValue="-1"/>
            <asp:Parameter Name="MaxPassengers" Type="Int32" DefaultValue="3" />
            <asp:Parameter Name="Status" Type="String" DefaultValue="Ready" />
            <asp:Parameter Name="CurrPassengers" Type="Int32" DefaultValue="0" />
            <asp:Parameter Name="StartAdd" Type="String" />
            <asp:Parameter Name="EndAdd" Type="String" />
            <asp:Parameter DbType="Time" Name="AvgRideTime" />
            <asp:Parameter Name="numRides" Type="Int32" DefaultValue="0" />
        </InsertParameters>        
    </asp:SqlDataSource>
share|improve this question
add comment

4 Answers

up vote 2 down vote accepted

you need to set parameter direction to OUTPut, to get the identity value. from MSDN

and handle the onInserted event to get, the identity value 

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        string sID = e.Command.Parameters["@Identity"].Value.ToString();
       //Display new ID

    }

you can try like this. 

<asp:sqlDataSource ID="Datasource" 
  SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

  InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); 
                 SELECT @EmpID = SCOPE_IDENTITY()"
  UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName 
                   WHERE EmployeeID=@EmployeeID"
  DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

  ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
  OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
  RunAt="server">

  <SelectParameters>
    <asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>

  <InsertParameters>
    <asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>

</asp:sqlDataSource>
share|improve this answer
    
This seems correct but I am still getting the null exception, any idea why this might be? [It appears that SCOPE_IDENTITY is null] –  SHeinema Jan 31 '12 at 5:34
    
you debugged it ? you follow the MSDN links. may be it will help ful –  Ravi Gadag Jan 31 '12 at 5:37
    
Yeah, debugged, looked at the link, and even just put it back the way it was and picked a random default value that should have worked for one insert, but it always gives me the null error.. –  SHeinema Jan 31 '12 at 6:05
    
Wow, thank you all for the help.. as it turns out this problem had an easier solution that originally expected. The Identity setting in the db got turned off somehow.. Solution: turn it back on. ARG.. Thanks anyway! ( ; –  SHeinema Jan 31 '12 at 6:17
add comment
up vote 1 down vote

You should omit the Identity column from the insert statement. Only include the data columns. The Sql Server will create the auto-increment then.

INSERT INTO [Vehicles] ([CarNum], [MaxPassengers], [Status], [CurrPassengers], [StartAdd], [EndAdd], [AvgRideTime], [numRides]) VALUES (@CarNum, @MaxPassengers, @Status, @CurrPassengers, @StartAdd, @EndAdd, @AvgRideTime, @numRides)
share|improve this answer
    
That would be the obvious solution, but unfortunately, as I said I get the following exception: "Cannot insert the value NULL into column 'Identity', table 'CWIS29RamRideOps.dbo.Vehicles'; column does not allow nulls. INSERT fails. The statement has been terminated." –  SHeinema Jan 31 '12 at 5:02
add comment
up vote 1 down vote

Ever Present, that is assuming that Identity is an auto-incremeneted column. It could just be a regular column.

You need to somehow retrieve the latest Identity first before trying to insert.

Alternatively you could write a trigger on the database that automatically updates the Identity column when a new item is added.

share|improve this answer
    
I tried retrieving the previous identity, which was simple enough, but in this particular table, it is not uncommon for it to be empty, then on the first insert I have no previous entry to start with, especially since the table identity numbers do not reset to 1. I am somewhat new to asp.net and sql, do you have any good resources on writing triggers? That may do the trick.. –  SHeinema Jan 31 '12 at 5:04
add comment
up vote 1 down vote

These snippets may help:

<InsertParameters>
   <asp:Parameter Name="SalesRepID" Type="Int32" />
   ...more params

    <asp:Parameter Name="QuoteID" Type="Int32" Direction="Output" />            
</InsertParameters>

Note the second command added

InsertCommand="INSERT INTO [MyTable] (fields); SET @QuoteID = Scope_Identity();"
share|improve this answer
    
This sure looks like the correct way to do it, but for some reason I am still getting the same error that Identity cannot be null. Any thoughts on what could be wrong? –  SHeinema Jan 31 '12 at 5:28
1  
It sounds like your database table isn't constructed to automatically increment the ID. –  Steve Wellens Jan 31 '12 at 17:15
    
Yup, you got it, stupid mistake, somehow got turned off. Thanks. –  SHeinema Jan 31 '12 at 20:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.