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 -
-
store proc.
-
-
ALTER PROCEDURE [dbo].[InsertUserInfo]
-
@UserId UNIQUEIDENTIFIER OUTPUT,
-
@UserName NVARCHAR(30),
-
@firstname NVARCHAR(30),
-
@lastname NVARCHAR(30),
-
@address1 NVARCHAR(30),
-
@address2 NVARCHAR(30),
-
@city NVARCHAR(15),
-
@state NVARCHAR(15),
-
@zip NVARCHAR(10),
-
@phone NVARCHAR(15)
-
-
AS
-
-
SELECT @UserId = UserId FROM aspnet_users WHERE UserName = @UserName
-
-
INSERT aspnet_UserInfo
-
(
-
userID,
-
firstname,
-
lastname,
-
address1,
-
address2,
-
city,
-
state,
-
zip,
-
phone
-
)
-
VALUES
-
(
-
@userID,
-
@firstname,
-
@lastname,
-
@address1,
-
@address2,
-
@city,
-
@state,
-
@zip,
-
@phone
-
)
-
-
code in load event:
-
-
Dim filepath As String = Server.MapPath("test_user_pass.csv")
-
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
-
Dim regex As New Regex(pattern)
-
Dim sr As New StreamReader(File.OpenRead(filepath))
-
Dim line As String = sr.ReadLine()
-
Dim conn As String = ConfigurationManager.ConnectionStrings("ConnMSSQLASP").ConnectionString
-
Dim connection As SqlConnection = New SqlConnection(conn)
-
connection.Open()
-
-
While line IsNot Nothing
-
-
'Splitting at comma and storing in a string array
-
Dim data As String() = regex.Split(line)
-
-
'inserts username and passwword and email in aspnet_membership table
-
Membership.CreateUser(data(0), data(1), data(2))
-
-
'inserts the rest of the userInfo in the aspnet_UserInfo table
-
Dim command As SqlCommand = New SqlCommand("InsertUserInfo", connection)
-
command.CommandType = CommandType.StoredProcedure
-
-
Dim outParameter As New SqlParameter()
-
outParameter.ParameterName = "@UserID"
-
outParameter.Direction = ParameterDirection.Output
-
outParameter.DbType = DbType.String
-
-
command.Parameters.AddWithValue("@UserID", outParameter)
-
command.Parameters.AddWithValue("@firstname", data(0))
-
command.Parameters.AddWithValue("@lastname", data(1))
-
command.Parameters.AddWithValue("@address1", data(2))
-
command.Parameters.AddWithValue("@address2", data(3))
-
command.Parameters.AddWithValue("@city", data(4))
-
command.Parameters.AddWithValue("@state", data(5))
-
command.Parameters.AddWithValue("@zip", data(6))
-
command.Parameters.AddWithValue("@phone", data(8))
-
line = sr.ReadLine()
-
command.ExecuteNonQuery()
-
-
End While
-
connection.Close()
-
-
2 2607
my csv file looks like this: - user1 pass1 email1 first1 last1 add11 add12 city1 state1 zip1 phone1
-
user2 pass2 email2 first2 last2 add11 add12 city2 state2 zip2 phone2
-
user3 pass3 email3 first3 last3 add11 add12 city3 state3 zip3 phone3
-
user4 pass4 email4 first4 last4 add11 add12 city4 state4 zip4 phone4
-
user5 pass5 email5 first5 last5 add11 add12 city5 state5 zip5 phone5
OK I modified my code to this: - Dim filepath As String = Server.MapPath("test_user_pass.csv")
-
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
-
Dim regex As New Regex(pattern)
-
Dim sr As New StreamReader(File.OpenRead(filepath))
-
Dim line As String = sr.ReadLine()
-
Dim conn As String = ConfigurationManager.ConnectionStrings("ConnMSSQLASP").ConnectionString
-
Dim connection As SqlConnection = New SqlConnection(conn)
-
connection.Open()
-
-
While line IsNot Nothing
-
-
'Splitting at comma and storing in a string array
-
Dim data As String() = regex.Split(line)
-
-
'inserts username and passwword and email in aspnet_membership table
-
Membership.CreateUser(data(0), data(1), data(2))
-
-
'inserts the rest of the userInfo in the aspnet_UserInfo table
-
Dim command As SqlCommand = New SqlCommand("InsertUserInfo", connection)
-
command.CommandType = CommandType.StoredProcedure
-
-
AddParamToSQLCmd(command, "@UserID", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Output, DBNull.Value)
-
AddParamToSQLCmd(command, "@firstname", SqlDbType.NChar, 30, ParameterDirection.Input, data(3))
-
AddParamToSQLCmd(command, "@lastname", SqlDbType.NChar, 30, ParameterDirection.Input, data(4))
-
AddParamToSQLCmd(command, "@address1", SqlDbType.NChar, 30, ParameterDirection.Input, data(5))
-
AddParamToSQLCmd(command, "@address2", SqlDbType.NChar, 30, ParameterDirection.Input, data(6))
-
AddParamToSQLCmd(command, "@city", SqlDbType.NChar, 15, ParameterDirection.Input, data(7))
-
AddParamToSQLCmd(command, "@state", SqlDbType.NChar, 15, ParameterDirection.Input, data(8))
-
AddParamToSQLCmd(command, "@zip", SqlDbType.NChar, 10, ParameterDirection.Input, data(9))
-
AddParamToSQLCmd(command, "@phone", SqlDbType.NChar, 15, ParameterDirection.Input, data(10))
-
-
line = sr.ReadLine()
-
command.ExecuteNonQuery()
-
Dim returnValue As Guid = command.Parameters("@ReturnValue").Value
-
-
End While
-
connection.Close()
- USE [ASPNETDB]
-
GO
-
/****** Object: StoredProcedure [dbo].[InsertUserInfo] Script Date: 10/01/2008 14:31:21 ******/
-
SET ANSI_NULLS ON
-
GO
-
SET QUOTED_IDENTIFIER ON
-
GO
-
-- =============================================
-
-- Author: <Author,,Name>
-
-- Create date: <Create Date,,>
-
-- Description: <Description,,>
-
-- =============================================
-
ALTER PROCEDURE [dbo].[InsertUserInfo]
-
@UserName NVARCHAR(30),
-
@firstname NVARCHAR(30),
-
@lastname NVARCHAR(30),
-
@address1 NVARCHAR(30),
-
@address2 NVARCHAR(30),
-
@city NVARCHAR(15),
-
@state NVARCHAR(15),
-
@zip NVARCHAR(10),
-
@phone NVARCHAR(15)
-
-
AS
-
DECLARE @UserId UNIQUEIDENTIFIER
-
-
SELECT @UserId = UserId FROM aspnet_Users WHERE UserName = @UserName
-
IF NOT EXISTS( SELECT UserId FROM aspnet_UserInfo WHERE UserId = @UserId)
-
BEGIN
-
-
INSERT aspnet_UserInfo
-
(
-
userID,
-
firstname,
-
lastname,
-
address1,
-
address2,
-
city,
-
state,
-
zip,
-
phone
-
)
-
VALUES
-
(
-
@userID,
-
@firstname,
-
@lastname,
-
@address1,
-
@address2,
-
@city,
-
@state,
-
@zip,
-
@phone
-
)
-
-
RETURN @@IDENTITY
-
END
-
ELSE
-
RETURN 1
-
and now I recieve this error:
Procedure or function 'InsertUserInfo' expects parameter '@UserName', which was not supplied.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |