By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,190 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,190 IT Pros & Developers. It's quick & easy.

reading a csv file and inserting it to the aspnet_membership table

P: 29
I am trying to read a csv file with user info (username, password, email, address, city, zip, state, etc.) I am inserting the username, password, email into the aspnet_memberhsip table using the membership class and trying to insert the rest of the related info with a stored procedure into the custom table I created called aspnet_UserInfo using a one - one relationship by UserID but its not working. can someone look at my stored procedure for me? and this error below?

No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type. 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.ArgumentException: No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type.


Source Error:

Line 44: command.Parameters.AddWithValue("@phone", data(8))
Line 45: line = sr.ReadLine()
Line 46: command.ExecuteNonQuery()
Line 47:
Line 48: End While



Expand|Select|Wrap|Line Numbers
  1.  
  2. store proc.
  3.  
  4. ALTER PROCEDURE [dbo].[InsertUserInfo]
  5.  @UserId UNIQUEIDENTIFIER OUTPUT, 
  6.  @UserName  NVARCHAR(30),
  7.  @firstname NVARCHAR(30),
  8.  @lastname  NVARCHAR(30),
  9.  @address1  NVARCHAR(30),
  10.  @address2  NVARCHAR(30),
  11.  @city      NVARCHAR(15),
  12.  @state     NVARCHAR(15),
  13.  @zip       NVARCHAR(10),
  14.  @phone     NVARCHAR(15)
  15.  
  16. AS
  17.  
  18. SELECT @UserId = UserId FROM aspnet_users WHERE UserName = @UserName 
  19.  
  20. INSERT aspnet_UserInfo 
  21.     (
  22.            userID,
  23.            firstname,
  24.            lastname,
  25.            address1,
  26.            address2,
  27.            city,
  28.            state,
  29.            zip,
  30.            phone
  31.     ) 
  32.     VALUES
  33.     (
  34.            @userID,
  35.            @firstname,
  36.            @lastname,
  37.            @address1,
  38.            @address2,
  39.            @city,
  40.            @state,
  41.            @zip,
  42.            @phone
  43.     )
  44.  
  45.  code in load event:
  46.  
  47.    Dim filepath As String = Server.MapPath("test_user_pass.csv")
  48.         Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
  49.         Dim regex As New Regex(pattern)
  50.         Dim sr As New StreamReader(File.OpenRead(filepath))
  51.         Dim line As String = sr.ReadLine()
  52.         Dim conn As String = ConfigurationManager.ConnectionStrings("ConnMSSQLASP").ConnectionString
  53.         Dim connection As SqlConnection = New SqlConnection(conn)
  54.         connection.Open()
  55.  
  56.         While line IsNot Nothing
  57.  
  58.             'Splitting at comma and storing in a string array 
  59.             Dim data As String() = regex.Split(line)
  60.  
  61.             'inserts username and passwword and email in aspnet_membership table
  62.             Membership.CreateUser(data(0), data(1), data(2))
  63.  
  64.             'inserts the rest of the userInfo in the aspnet_UserInfo table
  65.             Dim command As SqlCommand = New SqlCommand("InsertUserInfo", connection)
  66.             command.CommandType = CommandType.StoredProcedure
  67.  
  68.             Dim outParameter As New SqlParameter()
  69.             outParameter.ParameterName = "@UserID"
  70.             outParameter.Direction = ParameterDirection.Output
  71.             outParameter.DbType = DbType.String
  72.  
  73.             command.Parameters.AddWithValue("@UserID", outParameter)
  74.             command.Parameters.AddWithValue("@firstname", data(0))
  75.             command.Parameters.AddWithValue("@lastname", data(1))
  76.             command.Parameters.AddWithValue("@address1", data(2))
  77.             command.Parameters.AddWithValue("@address2", data(3))
  78.             command.Parameters.AddWithValue("@city", data(4))
  79.             command.Parameters.AddWithValue("@state", data(5))
  80.             command.Parameters.AddWithValue("@zip", data(6))
  81.             command.Parameters.AddWithValue("@phone", data(8))
  82.             line = sr.ReadLine()
  83.             command.ExecuteNonQuery()
  84.  
  85.         End While
  86.         connection.Close()
  87.  
  88.  
Oct 1 '08 #1
Share this Question
Share on Google+
2 Replies


P: 29
my csv file looks like this:

Expand|Select|Wrap|Line Numbers
  1. user1    pass1    email1    first1    last1    add11    add12    city1    state1    zip1    phone1
  2. user2    pass2    email2    first2    last2    add11    add12    city2    state2    zip2    phone2
  3. user3    pass3    email3    first3    last3    add11    add12    city3    state3    zip3    phone3
  4. user4    pass4    email4    first4    last4    add11    add12    city4    state4    zip4    phone4
  5. user5    pass5    email5    first5    last5    add11    add12    city5    state5    zip5    phone5
Oct 1 '08 #2

P: 29
OK I modified my code to this:
Expand|Select|Wrap|Line Numbers
  1.          Dim filepath As String = Server.MapPath("test_user_pass.csv")
  2.         Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
  3.         Dim regex As New Regex(pattern)
  4.         Dim sr As New StreamReader(File.OpenRead(filepath))
  5.         Dim line As String = sr.ReadLine()
  6.         Dim conn As String = ConfigurationManager.ConnectionStrings("ConnMSSQLASP").ConnectionString
  7.         Dim connection As SqlConnection = New SqlConnection(conn)
  8.         connection.Open()
  9.  
  10.         While line IsNot Nothing
  11.  
  12.             'Splitting at comma and storing in a string array 
  13.             Dim data As String() = regex.Split(line)
  14.  
  15.             'inserts username and passwword and email in aspnet_membership table
  16.             Membership.CreateUser(data(0), data(1), data(2))
  17.  
  18.             'inserts the rest of the userInfo in the aspnet_UserInfo table
  19.             Dim command As SqlCommand = New SqlCommand("InsertUserInfo", connection)
  20.             command.CommandType = CommandType.StoredProcedure
  21.  
  22.             AddParamToSQLCmd(command, "@UserID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output, DBNull.Value)
  23.             AddParamToSQLCmd(command, "@firstname", SqlDbType.NChar, 30, ParameterDirection.Input, data(3))
  24.             AddParamToSQLCmd(command, "@lastname", SqlDbType.NChar, 30, ParameterDirection.Input, data(4))
  25.             AddParamToSQLCmd(command, "@address1", SqlDbType.NChar, 30, ParameterDirection.Input, data(5))
  26.             AddParamToSQLCmd(command, "@address2", SqlDbType.NChar, 30, ParameterDirection.Input, data(6))
  27.             AddParamToSQLCmd(command, "@city", SqlDbType.NChar, 15, ParameterDirection.Input, data(7))
  28.             AddParamToSQLCmd(command, "@state", SqlDbType.NChar, 15, ParameterDirection.Input, data(8))
  29.             AddParamToSQLCmd(command, "@zip", SqlDbType.NChar, 10, ParameterDirection.Input, data(9))
  30.             AddParamToSQLCmd(command, "@phone", SqlDbType.NChar, 15, ParameterDirection.Input, data(10))
  31.  
  32.             line = sr.ReadLine()
  33.             command.ExecuteNonQuery()
  34.             Dim returnValue As Guid = command.Parameters("@ReturnValue").Value
  35.  
  36.         End While
  37.         connection.Close()
Expand|Select|Wrap|Line Numbers
  1.  USE [ASPNETDB]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[InsertUserInfo]    Script Date: 10/01/2008 14:31:21 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:        <Author,,Name>
  10. -- Create date: <Create Date,,>
  11. -- Description:    <Description,,>
  12. -- =============================================
  13. ALTER PROCEDURE [dbo].[InsertUserInfo]
  14.  @UserName  NVARCHAR(30),
  15.  @firstname NVARCHAR(30),
  16.  @lastname  NVARCHAR(30),
  17.  @address1  NVARCHAR(30),
  18.  @address2  NVARCHAR(30),
  19.  @city      NVARCHAR(15),
  20.  @state     NVARCHAR(15),
  21.  @zip       NVARCHAR(10),
  22.  @phone     NVARCHAR(15)
  23.  
  24. AS
  25. DECLARE @UserId UNIQUEIDENTIFIER 
  26.  
  27. SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @UserName 
  28. IF NOT EXISTS( SELECT UserId  FROM aspnet_UserInfo WHERE UserId = @UserId)
  29. BEGIN
  30.  
  31. INSERT aspnet_UserInfo 
  32.     (
  33.            userID,
  34.            firstname,
  35.            lastname,
  36.            address1,
  37.            address2,
  38.            city,
  39.            state,
  40.            zip,
  41.            phone
  42.     ) 
  43.     VALUES
  44.     (
  45.            @userID,
  46.            @firstname,
  47.            @lastname,
  48.            @address1,
  49.            @address2,
  50.            @city,
  51.            @state,
  52.            @zip,
  53.            @phone
  54.     )
  55.  
  56.  RETURN @@IDENTITY
  57. END
  58. ELSE
  59.  RETURN 1
  60.  
and now I recieve this error:
Procedure or function 'InsertUserInfo' expects parameter '@UserName', which was not supplied.
Oct 1 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.