473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

reading a csv file and inserting it to the aspnet_membership table

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
2 2607
cluce
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
cluce
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

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

Similar topics

7
by: John | last post by:
I have over 5000 thumbnail pictures of size 5kb each. I would like to able to load all 5000 pictures and view 50 per page using mysql_data_seek(). I would like to know what are the advantages and...
9
by: Lauren Quantrell | last post by:
Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has...
3
by: M Bourgon | last post by:
Howdy, all. We have a (log-type) file that's constantly being written to. We need to grab the latest rows out of this file, insert them into a table, and then process them. We've found out the...
2
by: Clodoaldo Pinto Neto | last post by:
Hi all, I'm trying to copy a table with a text field column containing a new line char to a file: ksDesenv=# create table page(line text) without oids; CREATE TABLE ksDesenv=# insert into...
8
by: LimaCharlie | last post by:
Hi, Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can I erase all the records in my excel file before i export the new data...
3
by: The Cool Giraffe | last post by:
Regarding the following code i have a problem. void read () { fstream file; ios::open_mode opMode = ios::in; file.open ("some.txt", opMode); char *ch = new char; vector <charv; while...
2
by: AlexanderDeLarge | last post by:
Hi! I got a problem that's driving me crazy and I'm desperately in need of help. I'll explain my scenario: I'm doing a database driven site for a band, I got these tables for their discography...
1
by: ayyanki | last post by:
Hello all, I'm still learning about c# and I have a major function that I need to write (in a forms application), and I have no idea how to go about it. Here's the scenario: I have a text file...
4
by: Miner Jeff | last post by:
I've written some code that reads a text file. I have a requirement that the text file also be in a format that's easy to read when it's printed. In a prior project, I had used some spreadsheet...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.