Link to home
Create AccountLog in
Avatar of Addie Baker
Addie BakerFlag for United States of America

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

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

Open in new window


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

Open in new window

Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

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.
Avatar of Addie Baker

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.
@bignadad
please post the exception in order to find out what is the problem.
Catch ex As Exception
                        MessageBox(Message)

I tried that and it did not work. What exactly do i need to input.
@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
replace this:
 Try
                        FileUpload1.PostedFile.SaveAs(path & _
                             FileUpload1.FileName)
                        MessageBox("File uploaded!")
                    Catch ex As Exception
                        MessageBox("File could not be uploaded.")
                    End Try

Open in new window


with this:
 Try
                        FileUpload1.PostedFile.SaveAs(path & _
                             FileUpload1.FileName)
                        MessageBox("File uploaded!")
                    Catch ex As Exception
                        MessageBox(ex.Message)
                    End Try

Open in new window

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

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

Open in new window

did u debug the code?
put breakpoint inside MessageBox and see what the message is.
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?
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).
I commented the messagebox line and still throws no exception to the client.

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>
        &nbsp;<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" />
&nbsp;
        <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>
                    &nbsp;&nbsp;&nbsp;
                </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>
&nbsp;&nbsp;&nbsp;
                </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>
&nbsp;&nbsp;&nbsp; </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>
                    &nbsp; &nbsp; </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>
                    &nbsp; &nbsp; </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 />
        &nbsp;&nbsp; &nbsp;
        <br />
        <br />
        &nbsp;<br />
        &nbsp;
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:baminterchangerConnectionString %>" 
            
            
            SelectCommand="SELECT amipartnumbers.*, jdsubs.* FROM amipartnumbers, jdsubs">
        </asp:SqlDataSource>
    
    
    </div>
    </form>
</body>
</html>

Open in new window

did u debug your server side code?
do u get valid FileUpload1.PostedFile?
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.
If i want this to go to the temp directory in root of website is this right?

Dim path As String = Server.MapPath("~/temp/")
ASKER CERTIFIED SOLUTION
Avatar of Addie Baker
Addie Baker
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Figured it out on my own