asked on

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

Ruffone

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

Nasir Razzaq

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

ASKER CERTIFIED SOLUTION

Ruffone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Addie Baker

ASKER

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'.

Addie Baker

ASKER

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

Addie Baker

ASKER

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

Addie Baker

ASKER

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()

Nasir Razzaq

@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 ?

Ruffone

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

Addie Baker

ASKER

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

Ruffone

Add this to your aspx page

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

Open in new window

Addie Baker

ASKER

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

Addie Baker

ASKER

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

Ruffone

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/

Addie Baker

ASKER

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?

Addie Baker

ASKER

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