[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

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
bignadad
Asked:
bignadad
  • 9
  • 5
  • 2
1 Solution
 
ruffoneCommented:
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
 
CodeCruiserCommented:
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
 
ruffoneCommented:
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 your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
bignadadAuthor Commented:
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
 
bignadadAuthor Commented:
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
 
bignadadAuthor Commented:
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
 
bignadadAuthor Commented:
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
 
CodeCruiserCommented:
@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
 
ruffoneCommented:
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
 
bignadadAuthor Commented:
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
 
ruffoneCommented:
Add this to your aspx page

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

Open in new window

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now