Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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

0
bignadad
Asked:
bignadad
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
what is the exception being thrown on line 22?
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
Instead of displaying ("File could not be uploaded."), use (ex.Message). You will then get the reason why the file cannot be uploaded.
0
 
bignadadAuthor Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Meir RivkinFull stack Software EngineerCommented:
@bignadad
please post the exception in order to find out what is the problem.
0
 
bignadadAuthor Commented:
Catch ex As Exception
                        MessageBox(Message)

I tried that and it did not work. What exactly do i need to input.
0
 
bignadadAuthor Commented:
@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
0
 
Meir RivkinFull stack Software EngineerCommented:
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

0
 
bignadadAuthor Commented:
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

0
 
Meir RivkinFull stack Software EngineerCommented:
did u debug the code?
put breakpoint inside MessageBox and see what the message is.
0
 
bignadadAuthor Commented:
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?
0
 
Meir RivkinFull stack Software EngineerCommented:
comment the Messagebox line, let the exception be throw back to the client and post what it is.
0
 
CodeCruiserCommented:
Show us your aspx code (simplified if necessary).
0
 
bignadadAuthor Commented:
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

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

Dim path As String = Server.MapPath("~/temp/")
0
 
bignadadAuthor Commented:
I went with a different approach and got it to work

Protected Sub cmdImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdImport.Click
        Dim savedFiles As String
        If Me.FileUpload1.PostedFile Is Nothing Then
            MessageBox("this file not correct")
        Else
            Dim strExt As String = Path.GetExtension(Me.FileUpload1.PostedFile.FileName)
            If strExt.ToLower() = ".xlsx" Then
                savedFiles = Path.GetFileName(Date.Now.Day & Date.Now.Month & Date.Now.Year & Date.Now.Hour & Date.Now.Minute & Date.Now.Second & ".xlsx")
                Me.FileUpload1.PostedFile.SaveAs(Server.MapPath("temp\") & savedFiles)
                MessageBox("upload successful ")

                Dim Excel As String = Server.MapPath("temp\" & savedFiles)
                Dim ExcelConnection As New OdbcConnection("Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" & Excel & ":\dev;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;")

                ExcelConnection.Open()

                Dim expr As String = "SELECT * FROM [Sheet1$]"
                Dim objCmdSelect As OdbcCommand = New OdbcCommand(expr, ExcelConnection)
                Dim objDR As OdbcDataReader
                Dim SQLconn As New SqlConnection()
                Dim ConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("baminterchangerConnectionString").ConnectionString

                SQLconn.ConnectionString = ConnString
                SQLconn.Open()

                Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)

                    bulkCopy.DestinationTableName = "imports"

                    Try

                        objDR = objCmdSelect.ExecuteReader
                        bulkCopy.WriteToServer(objDR)
                        ExcelConnection.Close()

                        'objDR.Close()

                        SQLconn.Close()

                    Catch ex As Exception

                        MessageBox(ex.ToString)
                    End Try
                End Using
            End If
        End If
    End Sub

Open in new window

0
 
bignadadAuthor Commented:
Figured it out on my own
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now