[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Converting a function for Access Visual Basic to Visual Studio for use in ASPX

Posted on 2013-05-14
16
Medium Priority
?
385 Views
Last Modified: 2013-05-18
Im converting my simple access database form to my website.

I just have a textbox where a user enters a number, clicks a search button, and it runs the following function to display info in textbox's.

I have mysql tables working and the datasource on the aspx page.

Im need to get this function from my Access Visual Basic to a format that Visual Studio ASPX can understand.

Here is the function
Function fnSearchAndPopulate() As Boolean
Dim d As DAO.Database, r As DAO.Recordset, strSQL As String
Set d = CurrentDb
If Me.txtEnterNumber.value = "" Then
    MsgBox "Please Enter Number", , "Error"
    Exit Function
End If
strSQL = " SELECT * FROM jdsubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=jdsubs.oempartnumber WHERE " & _
         " jdsubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " jdsubs.oemsubnumber= '" & txtEnterNumber.value & "'" & _
         " UNION SELECT * FROM mfsubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=mfsubs.oempartnumber WHERE " & _
         " mfsubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " mfsubs.oemsubnumber= '" & txtEnterNumber.value & "'" & _
         " UNION SELECT * FROM nhsubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=nhsubs.oempartnumber WHERE " & _
         " nhsubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " nhsubs.oemsubnumber= '" & txtEnterNumber.value & "'" & _
         " UNION SELECT * FROM AgcoSubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=AgcoSubs.oempartnumber WHERE " & _
         " AgcoSubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " AgcoSubs.oemsubnumber= '" & txtEnterNumber.value & "'" & _
         " UNION SELECT * FROM GleanerSubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=GleanerSubs.oempartnumber WHERE " & _
         " GleanerSubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " GleanerSubs.oemsubnumber= '" & txtEnterNumber.value & "'" & _
         " UNION SELECT * FROM ihsubs Inner Join amipartnumbers on " & _
         " amipartnumbers.oemitem=ihsubs.oempartnumber WHERE " & _
         " ihsubs.oempartnumber= '" & txtEnterNumber.value & "' or " & _
         " ihsubs.oemsubnumber= '" & txtEnterNumber.value & "'"
Debug.Print strSQL
Set r = d.OpenRecordset(strSQL)
If r.EOF Then
   MsgBox "OEM # " & Me.txtEnterNumber & " does not exist!", , "No AMI #"
  Set d = Nothing
  Exit Function
End If
'get here if there is a record
r.MoveFirst
'populate whatever textboxes
Me.txtAMINumber = r!Item
Me.txtDescription = r!Description
Me.txtOEMsubnumber = r!oemsubnumber

Set d = Nothing
Exit Function
End Function

Open in new window

0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
16 Comments
 
LVL 4

Expert Comment

by:ruffone
ID: 39167441
Ok so if you were to upgrade your app to this level then we are talking about first gen dotnet, Not a recommendation I like to make but something I think you may understand given your style of coding

    Public Function fnSearchAndPopulate() As DataTable
        Dim ConString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
        Dim dt As New DataTable()
        Using con As New System.Data.SqlClient.SqlConnection(ConString)
            Using cmd As New System.Data.SqlClient.SqlCommand
                cmd.Connection = con
                cmd.CommandText = "select * from dbo.MONTHLY_PTEL_ACTIVATION_REPORT where MONTH =" & monthandyear
                cmd.CommandType = CommandType.Text
                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader()
                    dt.Load(rdr)
                End Using

                con.Close()
            End Using
        End Using
        Return dt
    End Function 

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39167475
It would be more like

    Public Function fnSearchAndPopulate() As Boolean
        Dim ConString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
        Dim RetVal As Boolean
        Using con As New System.Data.SqlClient.SqlConnection(ConString)
            Using cmd As New System.Data.SqlClient.SqlCommand
                cmd.Connection = con
                cmd.CommandText = "select * from dbo.MONTHLY_PTEL_ACTIVATION_REPORT where MONTH =" & monthandyear
                cmd.CommandType = CommandType.Text
                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader()
                    If rdr.HasRows Then
                        RetVal = True
                        rdr.Read
                        Me.txtAMINumber.Text = rdr.Item("Item")
                        Me.txtDescription.Text = rdr.Item("Description")
                        Me.txtOEMsubnumber.Text = rdr.Item("oemsubnumber")
                    Else
                         RetVal = False
                    End If
                End Using
                con.Close()
            End Using
        End Using
        Return RetVal
    End Function

Open in new window

0
 
LVL 4

Accepted Solution

by:
ruffone earned 2000 total points
ID: 39167701
No, More like this

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        
        If Me.txtEnterNumber.Text = "" Then
            MsgLabel = "Please Enter Number"
            Exit Sub
        End If
        Dim dt As DataTable = Nothing
        Dim RetVal As Boolean
        dt = fnSearchAndPopulate(Me.txtEnterNumber.Text)
        If dt.GetHashCode() Then
            RetVal = True
            Me.txtAMINumber.Text = dt("Item")
            Me.txtDescription.Text = dt("Description")
            Me.txtOEMsubnumber.Text = dt("oemsubnumber")
        Else
            RetVal = False
        End If
    End Sub

    Public Function fnSearchAndPopulate(enterNumber As String) As DataTable
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
        Dim retVal As New DataTable
        Dim strSql = " SELECT * FROM jdsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=jdsubs.oempartnumber WHERE " &
            " jdsubs.oempartnumber= '" & enterNumber & "' or " &
            " jdsubs.oemsubnumber= '" & enterNumber & "'" &
            " UNION SELECT * FROM mfsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=mfsubs.oempartnumber WHERE " &
            " mfsubs.oempartnumber= '" & enterNumber & "' or " &
            " mfsubs.oemsubnumber= '" & enterNumber & "'" &
            " UNION SELECT * FROM nhsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=nhsubs.oempartnumber WHERE " &
            " nhsubs.oempartnumber= '" & enterNumber & "' or " &
            " nhsubs.oemsubnumber= '" & enterNumber & "'" &
            " UNION SELECT * FROM AgcoSubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=AgcoSubs.oempartnumber WHERE " &
            " AgcoSubs.oempartnumber= '" & enterNumber & "' or " &
            " AgcoSubs.oemsubnumber= '" & enterNumber & "'" &
            " UNION SELECT * FROM GleanerSubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=GleanerSubs.oempartnumber WHERE " &
            " GleanerSubs.oempartnumber= '" & enterNumber & "' or " &
            " GleanerSubs.oemsubnumber= '" & enterNumber & "'" &
            " UNION SELECT * FROM ihsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=ihsubs.oempartnumber WHERE " &
            " ihsubs.oempartnumber= '" & enterNumber & "' or " &
            " ihsubs.oemsubnumber= '" & enterNumber & "'"

        Using con As New System.Data.SqlClient.SqlConnection(conString)
            Using cmd As New System.Data.SqlClient.SqlCommand
                cmd.Connection = con
                cmd.CommandText = strSql
                cmd.CommandType = CommandType.Text
                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader()
                    If rdr.HasRows Then
                        retVal.Load(rdr)
                    End If
                End Using
                con.Close()
            End Using
        End Using
        Return retVal
    End Function

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 2

Author Comment

by:bignadad
ID: 39167834
ruffone, I tried your solution and only get an error on the following

Line 22:             Me.txtAMINumber.Text = dt("Item")
Line 23:             Me.txtDescription.Text = dt("Description")
Line 24:             Me.txtOEMsubnumber.Text = dt("oemsubnumber")

Compiler Error Message: BC30311: Value of type 'System.Data.DataRow' cannot be converted to 'String'.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39167866
Not sure if this is right but i added

Dim myRow As DataRow = Nothing

Then for results i added

Me.txtAMINumber.Text = myRow("Item")
Me.txtDescription.Text = myRow("Description")
Me.txtOEMsubnumber.Text = myRow("oemsubnumber")

No i get the following error

Line 31:     Public Function fnSearchAndPopulate(ByVal enterNumber As String) As DataTable
Line 32:         Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
Line 33:         Dim retVal As New DataTable
Line 34:         Dim strSql = " SELECT * FROM jdsubs Inner Join amipartnumbers on " &

Open in new window


Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

[NullReferenceException: Object reference not set to an instance of an object.]
   Interchange.fnSearchAndPopulate(String enterNumber) in D:\Hosting\11100286\html\Interchange.aspx.vb:32
   Interchange.cmdSearchNumber_Click(Object sender, EventArgs e) in D:\Hosting\11100286\html\Interchange.aspx.vb:20
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9553178
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39167871
Not sure if you need it or not but here is my web.config

<configuration>
  <system.data>
    <DbProviderFactories>
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
  <add name="baminterchangeConnectionString" connectionString="server=baminterchange.db.11100286.hostedresource.com;User Id=baminterchange;password=Mmmgoodd1@;Persist Security Info=True;database=baminterchange"
   providerName="MySql.Data.MySqlClient" />
 </connectionStrings>
	<system.web>
		<compilation targetFramework="4.0"/>
		<pages controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID"/></system.web>
  <system.web>
    <customErrors mode="Off"/>
  </system.web>
</configuration>

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39167880
okay, just taking shots in the dark and renamed my connection string in the line that was giving the error. changed it to

Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("baminterchangeConnectionString").ConnectionString

Now i get the following error

Exception Details: System.ComponentModel.Win32Exception: The network path was not found

Line 64:                 con.Open()
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39167913
@ruffone

            Me.txtAMINumber.Text = dt("Item")
            Me.txtDescription.Text = dt("Description")
            Me.txtOEMsubnumber.Text = dt("oemsubnumber")

Does datatable work like that?


@bignadad
If you follow this approach, its not longer SearchandPopulate its simply search

Did you try http:#a39167475 ?
0
 
LVL 4

Expert Comment

by:ruffone
ID: 39168099
dt = fnSearchAndPopulate(Me.txtEnterNumber.Text)
        If dt.GetHashCode() Then
            RetVal = True
            For i As Integer = 0 To dt.Rows.Count - 1
                Me.txtAMINumber.Text = dt.Rows(i)("Item")
                Me.txtDescription.Text = dt.Rows(i)("Description")
                Me.txtOEMsubnumber.Text = dt.Rows(i)("oemsubnumber")
            Next

           
        Else
            RetVal = False
        End If
    End Sub
0
 
LVL 2

Author Comment

by:bignadad
ID: 39168281
Getting this error on the MsgLabel

'MsgLabel' is not declared. It may be inaccessible due to its protection label


If i take that out to test the function i get this error

Exception Details: System.ComponentModel.Win32Exception: The network path was not found

Line 40:                 cmd.CommandText = strSql
Line 41:                 cmd.CommandType = CommandType.Text
Line 42:                 con.Open()
Line 43:                 Using rdr As SqlDataReader = cmd.ExecuteReader()
Line 44:                     If rdr.HasRows Then

Open in new window


Here is what i have all together

Partial Class Interchange
    Inherits System.Web.UI.Page

    Private Property Label As String

    Protected Sub cmdSearchNumber_Click(ByVal sender As Object, ByVal e As EventArgs) Handles cmdSearchNumber.Click

        Dim dt As DataTable = Nothing
        Dim myRow As DataRow = Nothing
        Dim RetVal As Boolean
        dt = fnSearchAndPopulate(Me.txtEnterNumber.Text)
        If dt.GetHashCode() Then
            RetVal = True
            For i As Integer = 0 To dt.Rows.Count - 1
                Me.txtAMINumber.Text = dt.Rows(i)("Item")
                Me.txtDescription.Text = dt.Rows(i)("Description")
                Me.txtOEMsubnumber.Text = dt.Rows(i)("oemsubnumber")
            Next
        Else
            RetVal = False
        End If
    End Sub

    Public Function fnSearchAndPopulate(ByVal enterNumber As String) As DataTable
        Dim conString As String = System.Configuration.ConfigurationManager.ConnectionStrings("baminterchangeConnectionString").ConnectionString
        Dim retVal As New DataTable
        Dim strSql = " SELECT * FROM jdsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=jdsubs.oempartnumber WHERE " &
            " jdsubs.oempartnumber= '" & enterNumber & "' or " &
            " jdsubs.oemsubnumber= '" & enterNumber & "'"

        Using con As New System.Data.SqlClient.SqlConnection(conString)
            Using cmd As New System.Data.SqlClient.SqlCommand
                cmd.Connection = con
                cmd.CommandText = strSql
                cmd.CommandType = CommandType.Text
                con.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader()
                    If rdr.HasRows Then
                        retVal.Load(rdr)
                    End If
                End Using
                con.Close()
            End Using
        End Using
        Return retVal
    End Function
End Class

Open in new window

0
 
LVL 4

Expert Comment

by:ruffone
ID: 39168379
Add this to your aspx page

<asp:Label ID="MsgLabel" runat="server" Text="Label"></asp:Label>

Open in new window

0
 
LVL 2

Author Comment

by:bignadad
ID: 39168423
Got that added and now get this error

MsgLabel = "Please Enter Number"

'Value of type String cannot be converted to System.Web.UI.WebControls.Label
0
 
LVL 2

Author Comment

by:bignadad
ID: 39168436
Okay, i got that fixed by changing to

MsgLabel.Text = "Please Enter Number"


Any idea why i am getting this error now?
System.ComponentModel.Win32Exception: The network path was not found
0
 
LVL 4

Expert Comment

by:ruffone
ID: 39168543
Connecting to MySQL is a bit more work intensive than using SQL Express

http://dev.mysql.com/downloads/dotnet.html
http://dev.mysql.com/tech-resources/articles/dotnet/
0
 
LVL 2

Author Comment

by:bignadad
ID: 39168747
So i updated to SQL Server to see if it could make the connection.

Dim strSql = " SELECT * FROM jdsubs Inner Join amipartnumbers on " &
            " amipartnumbers.oemitem=jdsubs.oempartnumber WHERE " &
            " jdsubs.oempartnumber= '" & enterNumber & "' or " &
            " jdsubs.oemsubnumber= '" & enterNumber & "'"

Based on that what should my main query be to import datasource?
0
 
LVL 2

Author Comment

by:bignadad
ID: 39168882
I have it working now.

just converted to SQL Server and used this select command and the code Ruffone provided worked great!

SELECT amipartnumbers.*, jdsubs.* FROM amipartnumbers, jdsubs
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

649 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