Addie Baker
asked on
Trying to convert a Access Visual Basic Import/Export Script to VB.net in Visual Studio
I have a function in Access that i am trying to make work in my ASPX file in Visual Studio
Here is the working function from Access Visual Basic
I started by trying to do the file import but it keeps telling me that file could not be uploaded.
Here is the working function from Access Visual Basic
Sub Import2()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim LUser As String
Dim d As Database
Dim response As Integer
LUser = CurrentUser()
Set d = CurrentDb()
response = MsgBox("You will need an XLS or XLSX worksheet with your OEM part numbers on Sheet1 starting at Row 1 with NO titles. Also you will need to have a Sheet2 that is blank", vbYesNo, "Do you have your worksheet setup?")
If response = vbYes Then
MsgBox ("Default location set to Desktop")
Else
Exit Sub
End If
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Old Excel File (*.xls)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "New Excel File (*.xlsx)", "*.XLSX")
Dim CustomerFile As Variant
CustomerFile = ahtCommonFileOpenSave(InitialDir:="C:\Users\" & LUser & "\Desktop", _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Choose spreadsheet of OEM Numbers")
If lngFlags And ahtOFN_ALLOWMULTISELECT Then
If IsArray(CustomerFile) Then
Dim i As Integer
For i = 0 To UBound(CustomerFile)
MsgBox CustomerFile(i)
Next i
Else
MsgBox CustomerFile
End If
Else
MsgBox CustomerFile
End If
d.Execute "DELETE * FROM MyTable"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", CustomerFile, False, "sheet1!A:A"
d.Execute "UPDATE (MyTable INNER JOIN JDSubs ON MyTable.F1 = JDSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON JDSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item] MyTable.F3 = AMIPartNumbers.Description;"
d.Execute "UPDATE (MyTable INNER JOIN JDSubs ON MyTable.F1 = JDSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON JDSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item] MyTable.F3 = AMIPartNumbers.Description;"
d.Execute "UPDATE (MyTable INNER JOIN IHSubs ON MyTable.F1 = IHSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON IHSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN IHSubs ON MyTable.F1 = IHSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON IHSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN AgcoSubs ON MyTable.F1 = AgcoSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON AgcoSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN AgcoSubs ON MyTable.F1 = AgcoSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON AgcoSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN GleanerSubs ON MyTable.F1 = GleanerSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON GleanerSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN GleanerSubs ON MyTable.F1 = GleanerSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON GleanerSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN MFSubs ON MyTable.F1 = MFSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON MFSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN MFSubs ON MyTable.F1 = MFSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON MFSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN NHSubs ON MyTable.F1 = NHSubs.OEMPartNumber)INNER JOIN AMIPartNumbers ON NHSubs.OEMPartNumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
d.Execute "UPDATE (MyTable INNER JOIN NHSubs ON MyTable.F1 = NHSubs.OEMsubnumber)INNER JOIN AMIPartNumbers ON NHSubs.OEMsubnumber = AMIPartNumbers.OEMItem SET MyTable.F2 = [AMIPartNumbers].[Item];"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTable", CustomerFile, False, "sheet2!A:B"
MsgBox ("File was updated successfully")
Exit Sub
End Sub
I started by trying to do the file import but it keeps telling me that file could not be uploaded.
Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
If IsPostBack Then
Dim path As String = Server.MapPath("~/")
Dim fileOK As Boolean = False
If FileUpload1.HasFile Then
Dim fileExtension As String
fileExtension = System.IO.Path. _
GetExtension(FileUpload1.FileName).ToLower()
Dim allowedExtensions As String() = _
{".xls", ".xlsx"}
For i As Integer = 0 To allowedExtensions.Length - 1
If fileExtension = allowedExtensions(i) Then
fileOK = True
End If
Next
If fileOK Then
Try
FileUpload1.PostedFile.SaveAs(path & _
FileUpload1.FileName)
MessageBox("File uploaded!")
Catch ex As Exception
MessageBox("File could not be uploaded.")
End Try
Else
MessageBox("Cannot accept files of this type.")
End If
End If
End If
End Sub
what is the exception being thrown on line 22?
Instead of displaying ("File could not be uploaded."), use (ex.Message). You will then get the reason why the file cannot be uploaded.
ASKER
ok, i will try that.
That is a piece of code i found on the msdn website I was trying to get this process started.
Is it possible to do this? I need to import the users excel file, put it in a table on my sql server, run the query to get results to column B then export the excel file back to the users pc.
That is a piece of code i found on the msdn website I was trying to get this process started.
Is it possible to do this? I need to import the users excel file, put it in a table on my sql server, run the query to get results to column B then export the excel file back to the users pc.
@bignadad
please post the exception in order to find out what is the problem.
please post the exception in order to find out what is the problem.
ASKER
Catch ex As Exception
MessageBox(Message)
I tried that and it did not work. What exactly do i need to input.
MessageBox(Message)
I tried that and it did not work. What exactly do i need to input.
ASKER
@sedgwick
How to i get the exception? Normally it will throw up the error, but since I am displaying that message box thats all i get
How to i get the exception? Normally it will throw up the error, but since I am displaying that message box thats all i get
replace this:
with this:
Try
FileUpload1.PostedFile.SaveAs(path & _
FileUpload1.FileName)
MessageBox("File uploaded!")
Catch ex As Exception
MessageBox("File could not be uploaded.")
End Try
with this:
Try
FileUpload1.PostedFile.SaveAs(path & _
FileUpload1.FileName)
MessageBox("File uploaded!")
Catch ex As Exception
MessageBox(ex.Message)
End Try
ASKER
K, got that added.
Once i press my import button it removes the file name from the fileupload control but nothing happens. No file is uploaded, or no message box.
This is my MessageBox Function. Could it be the problem? It works on other things i have on same page
Once i press my import button it removes the file name from the fileupload control but nothing happens. No file is uploaded, or no message box.
This is my MessageBox Function. Could it be the problem? It works on other things i have on same page
Private Sub MessageBox(ByVal strMsg As String)
Dim lbl As New Label
lbl.Text = "<script language='javascript'>" & Environment.NewLine _
& "window.alert(" & "'" & strMsg & "'" & ")</script>"
Page.Controls.Add(lbl)
End Sub
did u debug the code?
put breakpoint inside MessageBox and see what the message is.
put breakpoint inside MessageBox and see what the message is.
ASKER
So im working on my website via FTP in Visual Studio.
I set the break point on the "MessageBox(ex.Message)" line and saved it. Viewed it on the web and clicked the button. Where is the message?
I set the break point on the "MessageBox(ex.Message)" line and saved it. Viewed it on the web and clicked the button. Where is the message?
comment the Messagebox line, let the exception be throw back to the client and post what it is.
Show us your aspx code (simplified if necessary).
ASKER
I commented the messagebox line and still throws no exception to the client.
Here is my aspx code
Here is my aspx code
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Interchange.aspx.vb" Inherits="Interchange" Debug="true"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Abilene Machine Interchange Database</title>
<link rel="SHORTCUT ICON" href="ami.ico" />
<link href='http://fonts.googleapis.com/css?family=Roboto+Condensed' rel='stylesheet' type='text/css'>
<style type="text/css">
.style1
{
width: 224px;
height: 227px;
margin-top: 12px;
}
.style2
{
width: 50%;
vertical-align: middle;
}
.style3
{
width: 232px;
}
.style4
{
height: 26px;
width: 232px;
}
.style5
{
height: 26px;
}
.style8
{
font-size: medium;
padding: 3px 10px
}
.style9
{
height: 28px;
}
.style10
{
height: 28px;
width: 232px;
}
.style11
{
font-size: large;
}
.style12
{
text-align:center;
}
.style13
{
font-family: 'Roboto Condensed', sans-serif;
}
.style14
{
font-family: 'Roboto Condensed', sans-serif;
font-size: large;
font-weight: 700;
}
.style15
{
height: 35px;
}
</style>
<script language="javascript" type="text/javascript">
<!--
function txtEnterNumber_click() {
document.getElementById("MsgLabel").style.visibility = "hidden"
}
// -->
</script>
</head>
<body style="margin-left: 0px; margin-right: 0px; margin-top: 0px">
<form id="form1" runat="server">
<div style="background-position: center top; text-align: center; background-image: url('images/home.jpg'); background-repeat: no-repeat;">
<img alt="" class="style1"
src="/images/App-Icon2.png" /><br />
<br />
<asp:Label ID="Label1" runat="server" Text="Enter OEM Part Number"
BorderStyle="None" BorderWidth="5px"
style="font-size: x-large; margin-top: 0px; margin-bottom: 3px"
Width="274px" CssClass="style13"></asp:Label>
<br />
<asp:TextBox ID="txtEnterNumber" runat="server" Font-Size="Large"
CssClass="style12"></asp:TextBox>
<asp:Button ID="cmdSearchNumber" runat="server" Text="Search"
Font-Size="Large" Height="31px" />
<br />
<br />
<span class="style11">-
OR -</span><br />
<br />
<asp:FileUpload ID="FileUpload1" runat="server" Height="31px"
style="font-size: large" />
<asp:Button ID="cmdImport" runat="server" Text="Convert Excel File" Height="27px"
style="font-size: large" />
<br />
<br />
<br />
<table class="style2" align="center">
<tr>
<td align="center" class="style15" colspan="2"
style="text-decoration: underline"
valign="middle">
<asp:Label ID="Label4" runat="server" CssClass="style14"
Text="AMI Part Information"></asp:Label>
</td>
</tr>
<tr>
<td align="right" class="style10">
<asp:Label ID="Label2" runat="server" CssClass="style13" Text="Part Number:"></asp:Label>
</td>
<td align="left" class="style9">
<asp:TextBox ID="txtAMINumber" runat="server" Width="200px" style="font-size: medium"
BorderColor="#EFEFEF" BorderStyle="Solid" BorderWidth="1px"
Font-Underline="False" Height="30px" CssClass="style8" ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right" class="style4">
<asp:Label ID="Label3" runat="server" CssClass="style13"
Text="Part Description:"></asp:Label>
</td>
<td align="left" class="style5">
<asp:TextBox ID="txtDescription" runat="server" Width="200px" CssClass="style8"
BorderColor="#EFEFEF" BorderStyle="Solid" BorderWidth="1px" Height="30px"
ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td align="center" class="style15" colspan="2"
style="text-decoration: underline"
valign="middle">
<asp:Label ID="Label8" runat="server" CssClass="style14"
Text="OEM Part Information"></asp:Label>
</td>
</tr>
<tr>
<td align="right" class="style3">
<asp:Label ID="Label5" runat="server" CssClass="style13" Text="Part Number:"></asp:Label>
</td>
<td align="left">
<asp:TextBox ID="txtOEMPartNumber" runat="server" Width="200px"
CssClass="style8" BorderColor="#EFEFEF" BorderStyle="Solid"
BorderWidth="1px" Height="30px" ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right" class="style3">
<asp:Label ID="Label6" runat="server" CssClass="style13" Text="Sub Number:"></asp:Label>
</td>
<td align="left">
<asp:TextBox ID="txtOEMsubnumber" runat="server" Width="200px" CssClass="style8"
BorderColor="#EFEFEF" BorderStyle="Solid" BorderWidth="1px" Height="30px"
ReadOnly="True"></asp:TextBox>
</td>
</tr>
<tr>
<td align="right" class="style3">
<asp:Label ID="Label7" runat="server" CssClass="style13" Text="Description:"></asp:Label>
</td>
<td align="left">
<asp:TextBox ID="txtOEMDescription" runat="server" Width="200px" CssClass="style8"
BorderColor="#EFEFEF" BorderStyle="Solid" BorderWidth="1px" Height="30px"
ReadOnly="True"></asp:TextBox>
</td>
</tr>
</table>
<br />
<br />
<br />
<br />
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:baminterchangerConnectionString %>"
SelectCommand="SELECT amipartnumbers.*, jdsubs.* FROM amipartnumbers, jdsubs">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
did u debug your server side code?
do u get valid FileUpload1.PostedFile?
do u get valid FileUpload1.PostedFile?
ASKER
I dont think i can debug on server side. Its a windows GoDaddy Account.
The file was not posted in the location i told it to go.
The file was not posted in the location i told it to go.
ASKER
If i want this to go to the temp directory in root of website is this right?
Dim path As String = Server.MapPath("~/temp/")
Dim path As String = Server.MapPath("~/temp/")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured it out on my own