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

Meir Rivkin

what is the exception being thrown on line 22?

Jacques Bourgeois (James Burger)

Instead of displaying ("File could not be uploaded."), use (ex.Message). You will then get the reason why the file cannot be uploaded.

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.

Meir Rivkin

@bignadad
please post the exception in order to find out what is the problem.

Addie Baker

ASKER

Catch ex As Exception
MessageBox(Message)

I tried that and it did not work. What exactly do i need to input.

Addie Baker

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

Meir Rivkin

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

Addie Baker

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

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

Meir Rivkin

did u debug the code?
put breakpoint inside MessageBox and see what the message is.

Addie Baker

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?

Meir Rivkin

comment the Messagebox line, let the exception be throw back to the client and post what it is.

Nasir Razzaq

Show us your aspx code (simplified if necessary).

Addie Baker

ASKER

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

Meir Rivkin

did u debug your server side code?
do u get valid FileUpload1.PostedFile?

Addie Baker

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.

Addie Baker

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/")

ASKER CERTIFIED SOLUTION

Addie Baker

membership