Home ›
ASP.NET Forums » General ASP.NET » Getting Started » Insert Problem

Insert Problem

Last post 05-14-2008 5:52 PM by john1506. 11 replies.

Sort Posts:

  • Insert Problem

    05-04-2008, 11:37 AM
    • Loading...
    • john1506
    • Joined on 06-18-2007, 2:20 PM
    • Posts 248

     I am having a problem with trying to insert additional user data in a custom table when using the CreateUserWizard and adding a second step. I get the error (both the asp.net and my custom table (UserInfo) are updated correctly):

      Exception Details: System.Data.SqlClient.SqlException: The variable name '@UserID' has already been declared. Variable names must be unique within a query batch or stored procedure.

    Source Error: 

    Line 16:         DataSource.InsertParameters.Add("gsgroupid", gsgroupid.Text())
Line 17: 
Line 18:         DataSource.Insert()
Line 19: 
Line 20:

     The vb file is:

    Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser

    Dim UserNameTextBox As TextBox = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName"), TextBox)

    Dim DataSource As SqlDataSource = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("SqlDataSource1"), SqlDataSource)

    Dim User As MembershipUser = Membership.GetUser(UserNameTextBox.Text)

    Dim UserGUID As Object = Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey.ToString()

    DataSource.InsertParameters.Add("UserID", UserGUID.ToString())

    DataSource.InsertParameters.Add("UserName", User.ToString())DataSource.InsertParameters.Add("gsgroupid", gsgroupid.Text())

    DataSource.Insert()

    End Sub

    The sql is:

     <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>"

    InsertCommand="INSERT INTO [UserInfo] ([UserID], [UserName], [GSGroupID], [FirstName], [LastName], [Address1], [Address2], [City], [State], [Zip], [Country], [PhoneNumber]) VALUES ( @UserID, @UserName, @GSGroupID, @FirstName, @LastName, @Address1, @Address2, @City, @State, @Zip, @Country, @PhoneNumber)"

     

    ProviderName="<%$ConnectionStrings:New_ASPNET.ProviderName %>">

    <InsertParameters>

    <asp:ControlParameter Name="FirstName" Type="String" ControlID="txtFirstName" />

    <asp:ControlParameter Name="LastName" Type="String" ControlID="txtLastName" />

    <asp:ControlParameter Name="Address1" Type="String" ControlID="txtstreetAddress" />

    <asp:ControlParameter Name="Address2" Type="String" ControlID="txtApartment" />

    <asp:ControlParameter Name="City" Type="String" ControlID="txtCity" />

    <asp:ControlParameter Name="State" Type="String" ControlID="txtState" />

    <asp:ControlParameter Name="Zip" Type="String" ControlID="txtZip" />

    <asp:ControlParameter Name="Country" Type="String" ControlID="txtCountry" />

    <asp:ControlParameter Name="PhoneNumber" Type="String" ControlID="txtPhoneNumber" />

    </InsertParameters>

    </asp:SqlDataSource>

     If I remove the ["UserID"], @UserID from the sql insert statement, I get the following error:

    Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'UserID', table 'C:\INETPUB\WWWROOT\FFL\GS\APP_DATA\ASPNETDB.MDF.dbo.UserInfo'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    Source Error: 

    Line 16:         DataSource.InsertParameters.Add("gsgroupid", gsgroupid.Text())
Line 17: 
Line 18:         DataSource.Insert()
Line 19: 
Line 20: 
    This is my first attemp in this area and I just cannot understand how to fix my problem.
    Any help would be appreciated, Thanks

  • Re: Insert Problem

    05-04-2008, 11:48 AM
    Answer
    • Loading...
    • KelseyThornton
    • Joined on 07-15-2007, 12:01 PM
    • Breda, The Netherlands
    • Posts 199

    Hi.

    What's your table structure like? Is the UserID, maybe an IDENTITY field?

    Kelsey Thornton
    (In the Netherlands)

    Don't forget - Mark the post which answered your question with "Answer", then that user will get some kudos, and your post will be marked as "Answered" for future readers!

  • Re: Insert Problem

    05-04-2008, 1:47 PM
    Answer
    • Loading...
    • john1506
    • Joined on 06-18-2007, 2:20 PM
    • Posts 248

    Yes the UserID is an uniqueidentifyer and primary key in both the UserInfo table and the asp.net tables.

  • Re: Insert Problem

    05-04-2008, 2:14 PM
    Answer
    • Loading...
    • kpboucher
    • Joined on 08-15-2003, 7:34 PM
    • Posts 29

    It is likely that your ID value needs to be cast as a GUID before passing. (Currently you a re passing a string)

  • Re: Insert Problem

    05-04-2008, 2:45 PM
    • Loading...
    • john1506
    • Joined on 06-18-2007, 2:20 PM
    • Posts 248

    Thanks, I did have:

    Dim userId As Guid = DirectCast(user.ProviderUserKey, Guid) instead of

    Dim UserGUID As Object = Membership.GetUser(CreateUserWizard1.UserName).ProviderUserKey.ToString(), but I could not get that to work.  I am new to this so any help would be appreciated.

  • Re: Insert Problem

    05-04-2008, 2:48 PM
    Answer
    • Loading...
    • arshadtop
    • Joined on 01-22-2007, 5:02 PM
    • India
    • Posts 255

    Hi,

    when you define an ID Field (Primary Key Field), you need to set the Identity to "Yes", Identity seed to 1 and Identity increment to 1.
     

    Identity needs to be set to yes for the primary key.  Select the column that is the primary key and check idenity specification in column properties - I'm assuming your using Server Management Studio here.  Identity set to yes handles the auto-increment of the values.

    Hope this will solve your problem 

    Thanks.

     

    Regards

    Arshad

     

     

    Please Mark as Answer if the post is helpful to you.

  • Re: Insert Problem

    05-04-2008, 3:00 PM
    • Loading...
    • john1506
    • Joined on 06-18-2007, 2:20 PM
    • Posts 248

    Thanks Arshad, the primary key is set and controlled by the asp.net tables, which does create unique keys.  I beleive my problem is due to some kink of conflict between  the insert statement in the vb code and my SqlDataSource insertCommad, but I have not been able to figure it out.

  • Re: Insert Problem

    05-09-2008, 4:20 PM
    • Loading...
    • anncao
    • Joined on 04-27-2006, 1:42 PM
    • Posts 149

    I have similar problem as yours, have you got it figured out?

    Can your share if it works? Thanks

  • Re: Insert Problem

    05-09-2008, 5:08 PM
    • Loading...
    • john1506
    • Joined on 06-18-2007, 2:20 PM
    • Posts 248

    I have not finished testing, but yes it works!  A lot of thanks to several experts on this web site.  I also added a delete function if the second insert fails.  There are probably better ways of doing this, since this is my first attempt at modifying the CreateUserWizard.

     I hope it helps you.

    John

    <%@ Page Language="VB" MasterPageFile="~/masterPage_gs2.master" AutoEventWireup="false" CodeFile="addUser6.aspx.vb" Inherits="_Default" title="Add User" %>

    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

     

    <asp:CreateUserWizard ID="CreateUserWizard1" OnCreatedUser="CreateUserWizard1_CreatedUser" runat="server" Style="position: relative" ContinueDestinationPageUrl="~/UsersOnly/introduction1_gs.aspx" CreateUserButtonText="Create Account" Width="519px" BackColor="#EFF3FB" BorderColor="#B5C7DE" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" DisplayCancelButton="True">

    <WizardSteps>

    <asp:WizardStep ID="CreateUserWizardStep1" runat="server" Title="Add User Information" >

     

    <table border="0" style="font-size: 100%; width: 500

    px; font-family: Verdana; position: relative">

    <tr>

    <td align="right">

    First Name

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtFirstName" runat="server" Style="position: relative"></asp:TextBox>

    <span style="color: red">*</span>

    <asp:RequiredFieldValidator ID="rfvFirstName" runat="server" ControlToValidate="txtFirstName"

    ErrorMessage="First Name is Required" Style="position: relative"></asp:RequiredFieldValidator>

    </td>

    </tr>

    <tr>

    <td align="right">

    Last Name

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtLastName" runat="server" Style="position: relative"></asp:TextBox>

    <span style="color: red">*</span>

    <asp:RequiredFieldValidator ID="rfvLastName" runat="server" ControlToValidate="txtLastName"

    ErrorMessage="Last Name is Required" Style="position: relative"></asp:RequiredFieldValidator>

    </td>

    </tr>

    <tr>

    <td align="right" >

    Street Address

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtStreetAddress" runat="server" Style="position: relative" ></asp:TextBox>

    <span style="color: red"></span>

    </td>

    </tr>

    <tr>

    <td align="right">

    Apartment Number&nbsp;</td>

    <td style="width: 336px">

    <asp:TextBox ID="txtApartment" runat="server" Style="position: relative" ></asp:TextBox>

    <span style="color: red"></span>

    </td>

    </tr>

    <tr>

    <td align="right">

    City

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtCity" runat="server" Style="position: relative"></asp:TextBox>

    <span style="color: red"></span>

    </td>

    </tr>

    <tr>

    <td align="right">

    State/Province

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtState" runat="server" Style="position: relative" ></asp:TextBox>

    </td>

    </tr>

    <tr>

    <td align="right">

    Zip/Postal Code

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtZip" runat="server" Style="position: relative"></asp:TextBox>

    </td>

    </tr>

    <tr>

    <td align="right">

    Country

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtCountry" runat="server" Style="position: relative"></asp:TextBox>

    </td>

    </tr>

    <tr>

    <td align="right">

    Phone Number

    </td>

    <td style="width: 336px">

    <asp:TextBox ID="txtPhoneNumber" runat="server" Style="position: relative"></asp:TextBox>

    </td>

    </tr>

    <tr>

    <td align="center" colspan="2">

    <span style="font-size: 8pt; color: red">*Required Field&nbsp;</span></td>

    </tr>

    </table>

    </asp:WizardStep>

    <asp:CreateUserWizardStep ID="CreateUserWizardStep2" runat="server" Title="Add Login Information" >

    <ContentTemplate>

    <table border="0">

    <tr>

    <td align="center" colspan="2">

    </td>

    </tr>

    <tr>

    <td align="right">

    <asp:Label ID="UserNameLabel" runat="server" AssociatedControlID="UserName">User Name:</asp:Label></td>

    <td style="width: 392px">

    <asp:TextBox ID="UserName" runat="server"></asp:TextBox><span style="color: red">*</span>&nbsp;

     

    <asp:RequiredFieldValidator ID="rfvUserName" runat="server" ControlToValidate="UserName"

    Display="Dynamic" ErrorMessage="User Name is Required" Style="position: relative" ValidationGroup="CreateUserWizard1"></asp:RequiredFieldValidator></td>

    </tr>

    <tr>

    <td align="right">

    <asp:Label ID="PasswordLabel" runat="server" AssociatedControlID="Password">Password:</asp:Label></td>

    <td style="width: 392px">

    <asp:TextBox ID="Password" runat="server" TextMode="Password"></asp:TextBox><span style="color: red">*

    (5 position)</span>

     

    <asp:RequiredFieldValidator ID="PasswordRequired" runat="server" ControlToValidate="Password"

    ErrorMessage="Password must be at least 5 positions." ToolTip="Password must be at least 5 positions." Display="Dynamic" ValidationGroup="CreateUserWizard1"></asp:RequiredFieldValidator>

    </td>

    </tr>

    <tr>

    <td align="right">

    <asp:Label ID