Reply

Jonny11b

Member

2 Points

Need Help with INSERT INTO

Apr 26, 2010 10:24 PM|LINK

I have encountered an error that I don't quite understand. I am setting up a website for some friends of mine and I'm trying to setup an admin page that will allow future users to be assigned permissions after the site is deployed. I hit a few errors during the creation that had to do with my general lack of coding skills, but the error I'm getting now I don't think I caused. Here is the error that I'm getting:

Server Error in '/NstazMainSite/Nstaz' Application.

Conversion failed when converting from a character string to uniqueidentifier.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.

Source Error:

Line 16: 
Line 17:         Mycon.Open()
Line 18:         Mycmd.ExecuteNonQuery()
Line 19:         Mycon.Close()
Line 20:

Source File: C:\inetpub\NSTaZ\NstazMainSite\Nstaz\Admin\UserSettings.aspx.vb Line: 18

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +2840828
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +504
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +327
   Admin_UserSettings.btnAssign_Click(Object sender, EventArgs e) in C:\inetpub\NSTaZ\NstazMainSite\Nstaz\Admin\UserSettings.aspx.vb:18
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

Here is the .aspx and .aspx.vb that generated this error:

<%@ Page Title="User Config Settings" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeFile="UserSettings.aspx.vb" Inherits="Admin_UserSettings" %>


<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">


<p class = "clearRead">


This Page will contain an API connection to allow the changing of user groups and permissions.<br />
May take a bit as this is new to me.


    </p>
     <br />






    <asp:Label ID="lblUsers" runat="server" Text="Users: "></asp:Label>
    <asp:DropDownList ID="ddlUser" runat="server" DataSourceID="appUsersDS"
        DataTextField="UserName" DataValueField="UserId">
    </asp:DropDownList>


     <br />


    <asp:Label ID="lblRoles" runat="server" Text="Role to assgin: "></asp:Label>
    <asp:DropDownList ID="ddlRole" runat="server" DataSourceID="appRolesDS"
        DataTextField="RoleName" DataValueField="RoleId">
    </asp:DropDownList>


     <br />


    <asp:Button ID="btnAssign" runat="server" Text="Assign Role" />








    <asp:SqlDataSource ID="appUsersDS" runat="server"
        ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
        SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users]"></asp:SqlDataSource>


    <asp:SqlDataSource ID="appRolesDS" runat="server"
        ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
        SelectCommand="SELECT [RoleName], [RoleId] FROM [vw_aspnet_Roles]"></asp:SqlDataSource>
    

<asp:SqlDataSource ID="appUsersRolesDS" runat="server"
        ConnectionString="Server=VHS03.Jonny11b.com; Database=nstaz_AppData; User ID=*******;Password=*******;"
        SelectCommand="SELECT [UserId], [RoleId] FROM [vw_aspnet_UsersInRoles]"
        UpdateCommand ="UPDATE aspnet_UsersInRoles SET RoleId = @NewRole WHERE (UserId = '@sUser')"
        InsertCommand="INSERT INTO aspnet_UsersInRoles(UserId, RoleId) VALUES ('@sUser', '@NewRole')">
    <InsertParameters>
        <asp:Parameter Name="sUser" />
        <asp:Parameter Name="NewRole" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="NewRole" />
        <asp:Parameter Name="User" />
    </UpdateParameters>
    </asp:SqlDataSource>




</asp:Content>

Partial Class Admin_UserSettings
    Inherits System.Web.UI.Page

    Protected Sub btnAssign_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAssign.Click


        Dim Mycon As Data.SqlClient.SqlConnection
        Dim Mycmd As Data.SqlClient.SqlCommand
        Dim NewRole As String = ddlRole.SelectedValue
        Dim sUser As String = ddlUser.SelectedValue

        Mycon = New Data.SqlClient.SqlConnection("Server=VHS03.Jonny11b.com; Database=nstaz_AppData; User ID=*******;Password=*******;")
        Mycmd = New Data.SqlClient.SqlCommand(appUsersRolesDS.InsertCommand, Mycon)

        Mycon.Open()
        Mycmd.ExecuteNonQuery()
        Mycon.Close()

    End Sub

End Class

The database that I'm working with was generated by the asp.net tool Aspnet_regsql.exe and the pre-packaged asp.net web site tool. Please help, I don't understand why the table won't allow me to just take the UserId and RoleId and place them in to the correct table. What am I doing wrong?

If I'm answering questions something in the Universe is broken. :)

Moved by SGWellens on Apr 26, 2010 10:40 PM

Reply

MetalAsp.Net

All-Star

112141 Points

18246 Posts

Moderator

Re: Need Help with INSERT INTO

Apr 26, 2010 11:00 PM|LINK

Are the values of the two DDLs, GUID values? If so you need to convert the string to GUIDs, so something like this:

Guid NewRole = new Guid(ddlRole.SelectedValue);
Guid sUser = new Guid(ddlUser.SelectedValue);

(Not a VB person, so use my signature below to translate to VB).

Edit: Corrected code to be Guid instead of string.

C# <--> VB.Net Translator | jQuery Widgets

Edited by MetalAsp.Net on Apr 27, 2010 12:30 AM

Reply

Jonny11b

Member

2 Points

23 Posts

Re: Need Help with INSERT INTO

Apr 26, 2010 11:23 PM|LINK

I guess I should have clairified. Yes the ddl are databound to the database with the value fields bound to the *Id colums from the sql tables. The *Id colums in the database are set to the data type UniqueIdentifier. I was trying to just take the user id from the table users and the role id from the roles table and add them to the UsersInRoles table in the .net database. I'm going to give your suggestion a try next break I get at work and let you know how it goes.

If I'm answering questions something in the Universe is broken. :)

Reply

Jonny11b

Member

2 Points

23 Posts

Re: Need Help with INSERT INTO

Apr 26, 2010 11:57 PM|LINK

So I attempted to use the command that you posted after converting it to the VB language. Here's a screen shot of what VB studio had to say about it:

Don't know how to proceed.

If I'm answering questions something in the Universe is broken. :)

Edited by Jonny11b on Apr 26, 2010 11:59 PM
Edited by Jonny11b on Apr 27, 2010 12:06 AM

Reply

MetalAsp.Net

All-Star

112141 Points

18246 Posts

Moderator

Re: Need Help with INSERT INTO

Apr 27, 2010 12:27 AM|LINK

Dim NewRole as Guid = blah blah

Edit: it was a mistake in my posted code. I corrected the ealier post to use Guid instead of string. Sorry.

C# <--> VB.Net Translator | jQuery Widgets

Edited by MetalAsp.Net on Apr 27, 2010 12:27 AM
Edited by MetalAsp.Net on Apr 27, 2010 12:31 AM

Reply

Jonny11b

Member

2 Points

23 Posts

Re: Need Help with INSERT INTO

Apr 27, 2010 01:21 AM|LINK

No worries about the typo, believe me I'm grateful for the help. I have made the update to the code as noted in you post, I also removed the comented lines to avoid any possible issues. Here is how the page reads:

Partial Class Admin_UserSettings
    Inherits System.Web.UI.Page


    Protected Sub btnAssign_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAssign.Click




        Dim Mycon As Data.SqlClient.SqlConnection
        Dim Mycmd As Data.SqlClient.SqlCommand
        Dim NewRole As Guid = New Guid(ddlRole.SelectedValue)
        Dim sUser As Guid = New Guid(ddlUser.SelectedValue)






        Mycon = New Data.SqlClient.SqlConnection("Server=*******; Database=nstaz_AppData; User ID=*******;Password=*******;")
        Mycmd = New Data.SqlClient.SqlCommand(appUsersRolesDS.InsertCommand, Mycon)




        Mycon.Open()
        Mycmd.ExecuteNonQuery()
        Mycon.Close()


    End Sub








End Class

The error is the same:

Server Error in '/Nstaz' Application.

Conversion failed when converting from a character string to uniqueidentifier.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting from a character string to uniqueidentifier.

Source Error:

Line 18: 
Line 19:         Mycon.Open()
Line 20:         Mycmd.ExecuteNonQuery()
Line 21:         Mycon.Close()
Line 22:

Source File: Z:\NSTaZ\NstazMainSite\Nstaz\Admin\UserSettings.aspx.vb Line: 20

Stack Trace:

[SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +228
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +326
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   Admin_UserSettings.btnAssign_Click(Object sender, EventArgs e) in Z:\NSTaZ\NstazMainSite\Nstaz\Admin\UserSettings.aspx.vb:20
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

I don't understand this malfunction, shouldn't the GUID just transfer over? What ever information you want that'll make it easier on you, I'll provide. I wish I could be of more help :(

If I'm answering questions something in the Universe is broken. :)

Reply

Naom

All-Star

36004 Points

7901 Posts

Re: Need Help with INSERT INTO

Apr 27, 2010 01:37 AM|LINK

In your original code I noticed two problems

UpdateCommand ="UPDATE aspnet_UsersInRoles SET RoleId = @NewRole WHERE (UserId = '@sUser')"

Need Help with INSERT INTO RSS

44 replies

Jonny11b

Need Help with INSERT INTO

Server Error in '/NstazMainSite/Nstaz' Application.

Conversion failed when converting from a character string to uniqueidentifier.

MetalAsp.Net

Re: Need Help with INSERT INTO

Jonny11b

Re: Need Help with INSERT INTO

Jonny11b

Re: Need Help with INSERT INTO

MetalAsp.Net

Re: Need Help with INSERT INTO

Jonny11b

Re: Need Help with INSERT INTO

Server Error in '/Nstaz' Application.

Conversion failed when converting from a character string to uniqueidentifier.

Naom

Re: Need Help with INSERT INTO

Jonny11b

Re: Need Help with INSERT INTO

Naom

Re: Need Help with INSERT INTO

Jonny11b

Re: Need Help with INSERT INTO

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft