Solved

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

Posted on 2013-05-14
16
372 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

919 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

14 Experts available now in Live!

Get 1:1 Help Now