Solved

Trying to convert a Access Visual Basic Import/Export Script to VB.net in Visual Studio

Posted on 2013-05-15
18
375 Views
Last Modified: 2013-05-21
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
Comment
Question by:bignadad
18 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39170417
what is the exception being thrown on line 22?
0
 
LVL 40
ID: 39170667
Instead of displaying ("File could not be uploaded."), use (ex.Message). You will then get the reason why the file cannot be uploaded.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39171081
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39171109
@bignadad
please post the exception in order to find out what is the problem.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39171115
Catch ex As Exception
                        MessageBox(Message)

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

Author Comment

by:bignadad
ID: 39171120
@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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39171136
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
 
LVL 2

Author Comment

by:bignadad
ID: 39171266
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39171284
did u debug the code?
put breakpoint inside MessageBox and see what the message is.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:bignadad
ID: 39171310
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39171313
comment the Messagebox line, let the exception be throw back to the client and post what it is.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39171392
Show us your aspx code (simplified if necessary).
0
 
LVL 2

Author Comment

by:bignadad
ID: 39171578
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39171619
did u debug your server side code?
do u get valid FileUpload1.PostedFile?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39171696
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
 
LVL 2

Author Comment

by:bignadad
ID: 39171715
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
 
LVL 2

Accepted Solution

by:
bignadad earned 0 total points
ID: 39172147
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
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39183790
Figured it out on my own
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now