Hello all,
I have an issue, not that familiar with ASP but the environment I'm working on requires this to be designed here. I have a file with a string, integer, and another integer seperated by commas. I need the website to allow the user to upload a csv or text file that can read the content of the file into the database.
I haven't found anything yet that is quite what I'm looking for. I'm hoping you guys are able to assist.
I'm having an issue with the following code and I need some assistance. This is using ASP.NET VB Langauge. The original syntax was for Oledb and now I'm also in the process of converting it to support SqlClient
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" debug ="true" %>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then
Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)
'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub
'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As SQLConnection
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Data source = test;" & _
"Initial Catalog = test;" & _
"UID = test;" & _
"PWD = test123;"
objConn = New SQLConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function InsertToDatabase(ByVal dt)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
strConnString = "Driver={SQL Server};" & _
"Server=test;" & _
"Database=Test;" & _
"UID=test;" & _
"PWD=test123;" & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1
Try
strSQL = "INSERT INTO HR_Time (empFullName,sickTime,VacationTime) " & _
"VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"
End Try
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Function
</script>
<html>
<head>
<title>Intranet</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick" value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
The error I get is
Server Error in '/' Application.
Invalid object name 'hrTime.csv'.
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: Invalid object name 'hrTime.csv'.
Source Error:
Line 38:
Line 39: dtAdapter = New SqlDataAdapter(strSQL, objConn)
Line 40: dtAdapter.Fill(dt)
Line 41:
Line 42: dtAdapter = Nothing
Source File: C:\Inetpub\Intranet-Test\HR_TimeUpload.aspx Line: 40
Stack Trace:
[SqlException (0x80131904): Invalid object name 'hrTime.csv'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073550
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064508
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.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +166
System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +115
ASP.hr_timeupload_aspx.CsvCreateDataTable(Object strPath, Object strFilesName) in C:\Inetpub\Intranet-John\HR_TimeUpload.aspx:40
ASP.hr_timeupload_aspx.btnUpload_OnClick(Object sender, EventArgs e) in C:\Inetpub\Intranet-John\HR_TimeUpload.aspx:13
System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) +118
System.Web.UI.HtmlControls.HtmlInputButton.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.HtmlControls.HtmlInputButton.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) +176
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
Fill with a DataSet, not a DataTable?
Dim dt As New DataSet
Going by your suggestion, I got the following error:
Server Error in '/' Application.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30311: Value of type 'System.Data.DataSet' cannot be converted to 'System.Data.DataTable'.
Source Error:
Line 46: objConn = Nothing
Line 47:
Line 48: Return dt '*** Return DataTable ***'
Line 49:
Line 50: End Function
You need to change the return type on the function to DataSet .... line 21 shown above
EDIT. Went back to DataTable and it's now working. I may open this thread again for a few tweaks that may require your assistance. Thanks.
OK, so what did you do to fix it.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.