Solved

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

Posted on 2013-05-14
16
371 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
  • 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 500 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Running Very Slowly on Windows 7 PC 27 59
Achieve json result 2 34
Query 14 0
select over clause 1 0
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

20 Experts available now in Live!

Get 1:1 Help Now