Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Counting the Number of Data Lines Pulled From the Sequel Server 1

Status
Not open for further replies.
dataman86

dataman86

Technical User
Oct 11, 2008
104
0
0
US
I have a need to have sub routine to count the number of data lines coming from the Sequel Server. Is this a possibility with a sub routine or function in VB?

DataMan86
 
Sort by date Sort by votes
How are you pulling the data in? You should be able to get a count of the lines directly from that object rather than having to actually count the lines.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

I am using a Transact Sequel Query to pull the data in from a Sequel Server 2005 DataBase. I have a form where I am asking Parameters such as Account Period, Control Number, and Posting Date. These parameters pull data directly out of the database which has tons of records. The data lines contain debit and credit amounts. If the transaction pulled with parameters has 12 lines, I will need 12 Lines created with Textboxes for all 12 lines that will hold data from this pulled transaction.

DataMan86
 
Upvote 0 Downvote
What I meant is in your program you have some type of DataTable, DataSet, RecordSet, etc. that is used to store that data so your program can use it. It is either ADO, ADODB, ADO.NET, etc. For instance if it is all in a DataTable then you would just do DataTable.Rows.Count and you have your total. If it is in a DataSet you first have to find the table then find the row count. If it is in a RecordSet then you would actually have to loop through the RecordSet and count the rows because it will not return a count.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

The data is stored in datatable called Prod. Actually I am pulling data with a Transact Sequel Query:

Look at this Code I have attached:

Imports System.Data.SqlClient


Public Class Form12
Private Function TestIt() As Array

Dim sParm As String = Chr(39) & Me.TextBox1.Text.ToString & Chr(39)
Dim tParm As String = Chr(39) & Me.TextBox2.Text.ToString & Chr(39)
Dim uParm As String = Chr(39) & Me.TextBox3.Text.ToString & Chr(39)
Dim vParm As String = Chr(39) & Me.TextBox4.Text.ToString & Chr(39)
Dim wParm As String = Chr(39) & Me.TextBox5.Text.ToString & Chr(39)
Dim connectionString As String = "Data Source=99.9.9.999; " & _
"Initial Catalog=PROD; " & _
"Persist Security Info=True; " & _
"User ID=Main; " & _
"Password=lawson"

Dim sSQL As String = "SELECT COMPANY, FISCAL_YEAR, ACCT_PERIOD, CONTROL_GROUP, LINE_NBR, POSTING_DATE, OBJ_ID, STATUS, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT, ACCT_AMOUNT, VAR_LEVELS FROM GLTRANS WHERE COMPANY = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"
Dim oCn As New SqlConnection(connectionString)
Dim oCm As New SqlCommand(sSQL, oCn)
oCm.CommandType = CommandType.Text

Dim oDa As New SqlDataAdapter(oCm)
Dim oTable As New DataTable()

oCn.Open()
oDa.Fill(oTable)
Dim MyDataReader As SqlDataReader = oCm.ExecuteReader()
Dim iLvalue As Integer
Dim iRows As Integer = oTable.Rows.Count
Dim sParams(iRows, 12) As String
With MyDataReader
If .HasRows Then
While .Read()
sParams(iLvalue, 0) = .GetValue(0).ToString()
sParams(iLvalue, 1) = .GetValue(1).ToString()
sParams(iLvalue, 2) = .GetValue(2).ToString()
sParams(iLvalue, 3) = .GetValue(3).ToString()
sParams(iLvalue, 4) = .GetValue(4).ToString()
sParams(iLvalue, 5) = .GetValue(5).ToString()
sParams(iLvalue, 6) = .GetValue(6).ToString()
sParams(iLvalue, 7) = .GetValue(7).ToString()
sParams(iLvalue, 8) = .GetValue(8).ToString()
sParams(iLvalue, 9) = .GetValue(9).ToString()
sParams(iLvalue, 10) = .GetValue(10).ToString()
sParams(iLvalue, 11) = .GetValue(11).ToString()
sParams(iLvalue, 12) = .GetValue(12).ToString()
iLvalue += 1
End While
End If
End With

oCn.Close()
oCn.Dispose()
Return sParams


End Function


Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
Me.Hide()

Dim AParams As Array
AParams = TestIt()
MAIN1.Show()
MAIN1.txtCompany1.Text = AParams(0, 0)
MAIN1.txtCompany2.Text = AParams(1, 0)
MAIN1.txtCompany3.Text = AParams(2, 0)
MAIN1.txtCompany4.Text = AParams(3, 0)
MAIN1.txtCompany5.Text = AParams(4, 0)
MAIN1.txtCompany6.Text = AParams(5, 0)
MAIN1.txtCompany7.Text = AParams(6, 0)
MAIN1.txtCompany8.Text = AParams(7, 0)
MAIN1.txtCompany9.Text = AParams(8, 0)
MAIN1.txtCompany10.Text = AParams(9, 0)
MAIN1.txtCompany11.Text = AParams(10, 0)
MAIN1.txtCompany12.Text = AParams(11, 0)
MAIN1.txtFiscalYear1.Text = AParams(0, 1)
MAIN1.txtFiscalYear2.Text = AParams(1, 1)
MAIN1.txtFiscalYear3.Text = AParams(2, 1)
MAIN1.txtFiscalYear4.Text = AParams(3, 1)
MAIN1.txtFiscalYear5.Text = AParams(4, 1)
MAIN1.txtFiscalYear6.Text = AParams(5, 1)
MAIN1.txtFiscalYear7.Text = AParams(6, 1)
MAIN1.txtFiscalYear8.Text = AParams(7, 1)
MAIN1.txtFiscalYear9.Text = AParams(8, 1)
MAIN1.txtFiscalYear10.Text = AParams(9, 1)
MAIN1.txtFiscalYear11.Text = AParams(10, 1)
MAIN1.txtFiscalYear12.Text = AParams(11, 1)
MAIN1.txtAcctPeriod1.Text = AParams(0, 2)
MAIN1.txtAcctPeriod2.Text = AParams(1, 2)
MAIN1.txtAcctPeriod3.Text = AParams(2, 2)
MAIN1.txtAcctPeriod4.Text = AParams(3, 2)
MAIN1.txtAcctPeriod5.Text = AParams(4, 2)
MAIN1.txtAcctPeriod6.Text = AParams(5, 2)
MAIN1.txtAcctPeriod7.Text = AParams(6, 2)
MAIN1.txtAcctPeriod8.Text = AParams(7, 2)
MAIN1.txtAcctPeriod9.Text = AParams(8, 2)
MAIN1.txtAcctPeriod10.Text = AParams(9, 2)
MAIN1.txtAcctPeriod11.Text = AParams(10, 2)
MAIN1.txtAcctPeriod12.Text = AParams(11, 2)
MAIN1.txtControlGroup1.Text = AParams(0, 3)
MAIN1.txtControlGroup2.Text = AParams(1, 3)
MAIN1.txtControlGroup3.Text = AParams(2, 3)
MAIN1.txtControlGroup4.Text = AParams(3, 3)
MAIN1.txtControlGroup5.Text = AParams(4, 3)
MAIN1.txtControlGroup6.Text = AParams(5, 3)
MAIN1.txtControlGroup7.Text = AParams(6, 3)
MAIN1.txtControlGroup8.Text = AParams(7, 3)
MAIN1.txtControlGroup9.Text = AParams(8, 3)
MAIN1.txtControlGroup10.Text = AParams(9, 3)
MAIN1.txtControlGroup11.Text = AParams(10, 3)
MAIN1.txtControlGroup12.Text = AParams(11, 3)
MAIN1.txtLineNbr1.Text = AParams(0, 4)
MAIN1.txtLineNbr2.Text = AParams(1, 4)
MAIN1.txtLineNbr3.Text = AParams(2, 4)
MAIN1.txtLineNbr4.Text = AParams(3, 4)
MAIN1.txtLineNbr5.Text = AParams(4, 4)
MAIN1.txtLineNbr6.Text = AParams(5, 4)
MAIN1.txtLineNbr7.Text = AParams(6, 4)
MAIN1.txtLineNbr8.Text = AParams(7, 4)
MAIN1.txtLineNbr9.Text = AParams(8, 4)
MAIN1.txtLineNbr10.Text = AParams(9, 4)
MAIN1.txtLineNbr11.Text = AParams(10, 4)
MAIN1.txtLineNbr12.Text = AParams(11, 4)
MAIN1.txtPostingDate1.Text = AParams(0, 5)
MAIN1.txtPostingDate2.Text = AParams(1, 5)
MAIN1.txtPostingDate3.Text = AParams(2, 5)
MAIN1.txtPostingDate4.Text = AParams(3, 5)
MAIN1.txtPostingDate5.Text = AParams(4, 5)
MAIN1.txtPostingDate6.Text = AParams(5, 5)
MAIN1.txtPostingDate7.Text = AParams(6, 5)
MAIN1.txtPostingDate8.Text = AParams(7, 5)
MAIN1.txtPostingDate9.Text = AParams(8, 5)
MAIN1.txtPostingDate10.Text = AParams(9, 5)
MAIN1.txtPostingDate11.Text = AParams(10, 5)
MAIN1.txtPostingDate12.Text = AParams(11, 5)
MAIN1.txtObjID1.Text = AParams(0, 6)
MAIN1.txtObjID2.Text = AParams(1, 6)
MAIN1.txtObjID3.Text = AParams(2, 6)
MAIN1.txtObjID4.Text = AParams(3, 6)
MAIN1.txtObjID5.Text = AParams(4, 6)
MAIN1.txtObjID6.Text = AParams(5, 6)
MAIN1.txtObjID7.Text = AParams(6, 6)
MAIN1.txtObjID8.Text = AParams(7, 6)
MAIN1.txtObjID9.Text = AParams(8, 6)
MAIN1.txtObjID10.Text = AParams(9, 6)
MAIN1.txtObjID11.Text = AParams(10, 6)
MAIN1.txtObjID12.Text = AParams(11, 6)
MAIN1.txtStatus1.Text = AParams(0, 7)
MAIN1.txtStatus2.Text = AParams(1, 7)
MAIN1.txtStatus3.Text = AParams(2, 7)
MAIN1.txtStatus4.Text = AParams(3, 7)
MAIN1.txtStatus5.Text = AParams(4, 7)
MAIN1.txtStatus6.Text = AParams(5, 7)
MAIN1.txtStatus7.Text = AParams(6, 7)
MAIN1.txtStatus8.Text = AParams(7, 7)
MAIN1.txtStatus9.Text = AParams(8, 7)
MAIN1.txtStatus10.Text = AParams(9, 7)
MAIN1.txtStatus11.Text = AParams(10, 7)
MAIN1.txtStatus12.Text = AParams(11, 7)
MAIN1.txtAcctUnit1.Text = AParams(0, 8)
MAIN1.txtAcctUnit2.Text = AParams(1, 8)
MAIN1.txtAcctUnit3.Text = AParams(2, 8)
MAIN1.txtAcctUnit4.Text = AParams(3, 8)
MAIN1.txtAcctUnit5.Text = AParams(4, 8)
MAIN1.txtAcctUnit6.Text = AParams(5, 8)
MAIN1.txtAcctUnit7.Text = AParams(6, 8)
MAIN1.txtAcctUnit8.Text = AParams(7, 8)
MAIN1.txtAcctUnit9.Text = AParams(8, 8)
MAIN1.txtAcctUnit10.Text = AParams(9, 8)
MAIN1.txtAcctUnit11.Text = AParams(10, 8)
MAIN1.txtAcctUnit12.Text = AParams(11, 8)
MAIN1.txtAccount1.Text = AParams(0, 9)
MAIN1.txtAccount2.Text = AParams(1, 9)
MAIN1.txtAccount3.Text = AParams(2, 9)
MAIN1.txtAccount4.Text = AParams(3, 9)
MAIN1.txtAccount5.Text = AParams(4, 9)
MAIN1.txtAccount6.Text = AParams(5, 9)
MAIN1.txtAccount7.Text = AParams(6, 9)
MAIN1.txtAccount8.Text = AParams(7, 9)
MAIN1.txtAccount9.Text = AParams(8, 9)
MAIN1.txtAccount10.Text = AParams(9, 9)
MAIN1.txtAccount11.Text = AParams(10, 9)
MAIN1.txtAccount12.Text = AParams(11, 9)
MAIN1.TranAmt1.Text = AParams(0, 10)
MAIN1.TranAmt2.Text = AParams(1, 10)
MAIN1.TranAmt3.Text = AParams(2, 10)
MAIN1.TranAmt4.Text = AParams(3, 10)
MAIN1.TranAmt5.Text = AParams(4, 10)
MAIN1.TranAmt6.Text = AParams(5, 10)
MAIN1.TranAmt7.Text = AParams(6, 10)
MAIN1.TranAmt8.Text = AParams(7, 10)
MAIN1.TranAmt9.Text = AParams(8, 10)
MAIN1.TranAmt10.Text = AParams(9, 10)
MAIN1.TranAmt11.Text = AParams(10, 10)
MAIN1.TranAmt12.Text = AParams(11, 10)
MAIN1.VARLEVEL1.Text = AParams(0, 12)
MAIN1.VARLEVEL2.Text = AParams(1, 12)
MAIN1.VARLEVEL3.Text = AParams(2, 12)
MAIN1.VARLEVEL4.Text = AParams(3, 12)
MAIN1.VARLEVEL5.Text = AParams(4, 12)
MAIN1.VARLEVEL6.Text = AParams(5, 12)
MAIN1.VARLEVEL7.Text = AParams(6, 12)
MAIN1.VARLEVEL8.Text = AParams(7, 12)
MAIN1.VARLEVEL9.Text = AParams(8, 12)
MAIN1.VARLEVEL10.Text = AParams(9, 12)
MAIN1.VARLEVEL11.Text = AParams(10, 12)
MAIN1.VARLEVEL12.Text = AParams(11, 12)
End Sub



End Class

 
Upvote 0 Downvote
Also,

Sorwen, do you know how to set a PC to Print a form in Landscaped Lettersize with VB Code so it will print in the center of the page? I believe it can be done with coding. Anyhow I need to learn how to do this.

DataMan86
 
Upvote 0 Downvote
Dim iRows As Integer = oTable.Rows.Count
Click to expand...
I'm kind of confused as you have your count right there. Is that something you added in and you were just showing or do I misunderstand something?

I don't do printing much, but if you have VS2k5 there is a power toy you can get that alls you to print a form.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

The VB Program that I sent you pulls 12 columns with 12 rows of data. Do you know how to write some coding to pull say row 13 through say 250. I might have a transaction that I would pull data from the server at the end of the year that might contain debits and credits by up to 250 lines. I believe my program will pull data and place in array but I need to be able to pull data out of the Array from row 13 through say up to 250 and be able to manipulate the data by each column name and row name with variable name and not hard code the: MAIN1.txtFiscalYear1.Text = AParams(0, 1)
MAIN1.txtFiscalYear2.Text = AParams(1, 1)
MAIN1.txtFiscalYear3.Text = AParams(2, 1)
MAIN1.txtFiscalYear4.Text = AParams(3, 1) as an example. Does this make sense?

I am an accountant with limited VB knowledge.

DataMan86
 
Upvote 0 Downvote
Ah, ok. The count is fine now and you want to discuss the next part. Ok. I'll need a bit more information though.

When you say you need to pull row 13 how do you mean exactly? Are you saying after the program loads and all of the rows are loaded you might press a button an then row 13 should show? If that is the case should it show just row 13 or should it show more if there is? For example if there are a total of 20 rows then when you press the button it should show rows 13 to 20? Why do you load the data in an array as well as a table? Do you really need the array? It would be easier just to always work with the table.

Ah, I just double check and it was you that was asking about multiple windows. Which direction did you take or did you want to take because that makes a difference as well. Are we working with one window (MAIN1) or multiple windows?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen, I just do not know what I am doing. I am an accountant with limited knowledge of VB. I am trying to learn how to use it, but are very slow learning VB.

The DataBase is an Accounting DataBase which has tons of security on it. The database will not allow you to connect and use the actual Prod Table. You might can use it one time, but the Accounting Application will re-index everything and lock you out. So I had to create a temporary connection to it as the Tsequel Query runs it pulls data and loads into an Array List. If this could be done a better way I am all for it. I do not understand Arrays that much and there is probably a better way to do this.

I need to just load data from the Prod Table into a form with textboxes under 13 columns and calculate things with the data and then write the calculated results out to a text file.

I need to be able to have infinite rows of data pulled not just be limited to 12 rows only. What I am trying to do is create Journal Voucher Lines. I need to be able to pull data from the Prod Table in the Accounting dataBase and be able to pull all transactions based on parameters I have indicated with the TSequel Query in the coding. A Transaction with a Control Group Number of 3 with an Accounting Period 1 with a Posting date of 11/15/2008 might have 50 lines with it. I need to pull all 50 lines out place the data into the textboxes on 50 lines with 13 columns based on these parameters, I just do not know how to write the code to do it. I think the coding is just limiting me to twelve lines and 12 columns. I will always have 13 columns, but I need the ability to pull any number of rows from the database based on the Transaction Parameters, Control Group Number, Accounting Period, and Posting Date. The Annual JV I might would do could have a much as 200 lines or greater. I need the ability to pull any number of rows and place in the textboxes by 13 columns.
I need the ability to reference each field pulled with a variable so the right data will come into the correct textbox.

I wish I understood VB better, I am learning but only touched the surface so far and have worked only briefly with it. The coding I have sent you probably is not doing what I need done to give me the required results I am looking far. I am limiting myself to 12 rows I believe, and I might not even need to use an ARray to do this. If you have some better coding, please feel free to share it with me. I am in a learning curve with this. I am just trying to get it to work.

THE QUERY Pulling Data is: "SELECT COMPANY, FISCAL_YEAR, ACCT_PERIOD, CONTROL_GROUP, LINE_NBR, POSTING_DATE, OBJ_ID, STATUS, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT, ACCT_AMOUNT, VAR_LEVELS FROM GLTRANS WHERE COMPANY = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"

I need all data to come into the textboxes that I have names for on the form. If there are 50 lines with a transaction, I need all 50 lines to show with data in 13 texboxes for each line.

DataMan

 
Upvote 0 Downvote
Comment out all of the old code and let me know if this works:

Code: 
Imports System.Data.SqlClient


Public Class Form12
    Private Function TestIt() As DataTable

        Dim sParm As String = Chr(39) & Me.TextBox1.Text.ToString & Chr(39)
        Dim tParm As String = Chr(39) & Me.TextBox2.Text.ToString & Chr(39)
        Dim uParm As String = Chr(39) & Me.TextBox3.Text.ToString & Chr(39)
        Dim vParm As String = Chr(39) & Me.TextBox4.Text.ToString & Chr(39)
        Dim wParm As String = Chr(39) & Me.TextBox5.Text.ToString & Chr(39)
        Dim connectionString As String = "Data Source=99.9.9.999; " & _
                                                              "Initial Catalog=PROD; " & _
                                                              "Persist Security Info=True; " & _
                                                              "User ID=Main; " & _
                                                              "Password=lawson"

        Dim sSQL As String = "SELECT COMPANY, FISCAL_YEAR, ACCT_PERIOD, CONTROL_GROUP, LINE_NBR, POSTING_DATE, OBJ_ID, STATUS, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT, ACCT_AMOUNT, VAR_LEVELS FROM GLTRANS WHERE COMPANY  = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"
        Dim oCn As New SqlConnection(connectionString)
        Dim oCm As New SqlCommand(sSQL, oCn)
        oCm.CommandType = CommandType.Text

        Dim oDa As New SqlDataAdapter(oCm)
        Dim oTable As New DataTable()

        oCn.Open()
        oDa.Fill(oTable)
        oCn.Close()
        oCn.Dispose()

        Return oTable
    End Function


    Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
        Me.Hide()

        Dim CurrentData As DataTable
        CurrentData = TestIt()
        MAIN1.Show()

        For count As Integer = 0 To 11
            MAIN1.Controls("txtCompany" & count + 1).Text = CurrentData.Rows(count).Item("COMPANY")
            MAIN1.Controls("txtFiscalYear" & count + 1).Text = CurrentData.Rows(count).Item("FISCAL_YEAR")
            MAIN1.Controls("txtAcctPeriod" & count + 1).Text = CurrentData.Rows(count).Item("ACCT_PERIOD")
            MAIN1.Controls("txtControlGroup" & count + 1).Text = CurrentData.Rows(count).Item("CONTROL_GROUP")
            MAIN1.Controls("txtLineNbr" & count + 1).Text = CurrentData.Rows(count).Item("LINE_NBR")
            MAIN1.Controls("txtPostingDate" & count + 1).Text = CurrentData.Rows(count).Item("POSTING_DATE")
            MAIN1.Controls("txtObjID" & count + 1).Text = CurrentData.Rows(count).Item("OBJ_ID")
            MAIN1.Controls("txtStatus" & count + 1).Text = CurrentData.Rows(count).Item("STATUS")
            MAIN1.Controls("txtAcctUnit" & count + 1).Text = CurrentData.Rows(count).Item("ACCT_UNIT")
            MAIN1.Controls("txtAccount" & count + 1).Text = CurrentData.Rows(count).Item("ACCOUNT")
            MAIN1.Controls("TranAmt" & count + 1).Text = CurrentData.Rows(count).Item("TRAN_AMOUNT")
            'ACCT_AMOUNT missing?  If not use below
            'MAIN1.Controls("ReplaceWithCorrectTextBoxName" & count + 1).Text = CurrentData.Rows(count).Item("ACCT_AMOUNT")
            MAIN1.Controls("VARLEVEL" & count + 1).Text = CurrentData.Rows(count).Item("VAR_LEVELS")
        Next
    End Sub
End Class

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

The program looks good. I will try it Monday morning when I get back to the server. I appreciate you taking your time to help me. I really need help trying to learn the VB.
It may take me five years to be able to be where you are at the moment.

I tried this coding today to see if I could get it to work.

The Coding:

Imports System.Data.SqlClient

Public Class Pull
Private Function TestIt() As Array

Dim sParm As String = Chr(39) & Me.TextBox1.Text.ToString & Chr(39)
Dim tParm As String = Chr(39) & Me.TextBox2.Text.ToString & Chr(39)
Dim uParm As String = Chr(39) & Me.TextBox3.Text.ToString & Chr(39)
Dim vParm As String = Chr(39) & Me.TextBox4.Text.ToString & Chr(39)
Dim wParm As String = Chr(39) & Me.TextBox5.Text.ToString & Chr(39)
Dim connectionString As String = "Data Source=9999999999; " & _
"Initial Catalog=PROD; " & _
"Persist Security Info=True; " & _
"User ID=9999999; " & _
"Password=999999"

Dim sSQL As String = "SELECT COMPANY, FISCAL_YEAR, ACCT_PERIOD, CONTROL_GROUP, LINE_NBR, POSTING_DATE, OBJ_ID, STATUS, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT, ACCT_AMOUNT, VAR_LEVELS FROM GLTRANS WHERE COMPANY = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"
Dim oCn As New SqlConnection(connectionString)
Dim oCm As New SqlCommand(sSQL, oCn)
oCm.CommandType = CommandType.Text

Dim oDa As New SqlDataAdapter(oCm)
Dim oTable As New DataTable()

oCn.Open()
oDa.Fill(oTable)
Dim MyDataReader As SqlDataReader = oCm.ExecuteReader()
Dim iLvalue As Integer
Dim iRows As Integer = oTable.Rows.Count
Form6.Count.Text = oTable.Rows.Count
Dim sParams(iRows, 12) As String
With MyDataReader
If .HasRows Then
While .Read()
sParams(iLvalue, 0) = .GetValue(0).ToString()
sParams(iLvalue, 1) = .GetValue(1).ToString()
sParams(iLvalue, 2) = .GetValue(2).ToString()
sParams(iLvalue, 3) = .GetValue(3).ToString()
sParams(iLvalue, 4) = .GetValue(4).ToString()
sParams(iLvalue, 5) = .GetValue(5).ToString()
sParams(iLvalue, 6) = .GetValue(6).ToString()
sParams(iLvalue, 7) = .GetValue(7).ToString()
sParams(iLvalue, 8) = .GetValue(8).ToString()
sParams(iLvalue, 9) = .GetValue(9).ToString()
sParams(iLvalue, 10) = .GetValue(10).ToString()
sParams(iLvalue, 11) = .GetValue(11).ToString()
sParams(iLvalue, 12) = .GetValue(12).ToString()
iLvalue += 1
End While
End If
End With

oCn.Close()
oCn.Dispose()
iLvalue = 0
Dim SName As String = "TextBox.Text"
Dim i As Integer
For i = 0 To 12
Dim TName As String = SName & CStr(iLvalue) & CStr(i)


For iLvalue = 0 To iRows - 1
TName = sParams(iLvalue, 0)
TName = sParams(iLvalue, 1)
TName = sParams(iLvalue, 2)
TName = sParams(iLvalue, 3)
TName = sParams(iLvalue, 4)
TName = sParams(iLvalue, 5)
TName = sParams(iLvalue, 6)
TName = sParams(iLvalue, 7)
TName = sParams(iLvalue, 8)
TName = sParams(iLvalue, 10)
TName = sParams(iLvalue, 11)
TName = sParams(iLvalue, 12)


Next
Next
Return sParams
End Function
Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
Me.Hide()
Dim AParams As Array
AParams = TestIt()
Form6.Show()

End Sub

End Class



If I place .Text after TName I get error. Text is not a member of string. So I placed .TEXT after
the Dim SName As String = "TextBox.Text". I will try o running this program again Monday too to see what happens.

All of my data is not appearing in the dynamic boxes I have created with Form6. The data pulled with Class Pull from the server should appear in the Dynamic Boxes created with Form6. It is not working. See if you see whats wrong.
DataMan86
The Code for Form6 is:

Imports System
Imports System.IO
Imports System.Drawing.Printing

Public Class Form6

Dim RowCount As Integer

Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.RowCount = CInt(Me.Count.Text)
SetupForm()
End Sub

Private Sub SetupForm()
Dim r, c, x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, y, z As Integer
x1 = 48
x2 = 87
x3 = 131
x4 = 178
x5 = 222
x6 = 278
x7 = 320
x8 = 358
x9 = 414
x10 = 458
x11 = 556
x12 = 650
x13 = 750
y = 42

If RowCount = 1 Then
Exit Sub
End If
For r = 2 To RowCount
z = 0
For c = 1 To 13
z = y + (35 * (r - 1))
Dim tb As New TextBox
With tb
.Name = "TextBox" & r & c
Select Case c ' position coordinates
Case 1
.Location = New Point(x1, z)
.Text = r & c
.Size = New Size(34, 42)
Case 2
.Location = New Point(x2, z)
.Text = r & c
.Size = New Size(38, 42)
Case 3
.Location = New Point(x3, z)
.Text = r & c
.Size = New Size(39, 42)
Case 4
.Location = New Point(x4, z)
.Text = r & c
.Size = New Size(37, 42)
Case 5
.Location = New Point(x5, z)
.Text = r & c
.Size = New Size(46, 42)
Case 6
.Location = New Point(x6, z)
.Text = r & c
.Size = New Size(35, 42)
Case 7
.Location = New Point(x7, z)
.Text = r & c
.Size = New Size(32, 42)
Case 8
.Location = New Point(x8, z)
.Text = r & c
.Size = New Size(49, 42)
Case 9
.Location = New Point(x9, z)
.Text = r & c
.Size = New Size(42, 42)
Case 10
.Location = New Point(x10, z)
.Text = r & c
.Size = New Size(83, 42)
Case 11
.Location = New Point(x11, z)
.Text = r & c
.Size = New Size(87, 42)
Case 12
.Location = New Point(x12, z)
.Text = r & c
.Size = New Size(92, 42)
Case 13
.Location = New Point(x13, z)
.Text = r & c
.Size = New Size(92, 42)



End Select

End With
Me.Controls.Add(tb)
Next
Next
TextBox00.Text = "00"
TextBox01.Text = "01"
TextBox02.Text = "02"
TextBox03.Text = "03"
TextBox04.Text = "04"
TextBox05.Text = "05"
TextBox06.Text = "06"
TextBox07.Text = "07"
TextBox08.Text = "08"
TextBox09.Text = "09"
TextBox10.Text = "10"
TextBox11.Text = "11"
TextBox12.Text = "12"







 
Upvote 0 Downvote
Code: 
 Dim SName As String = "TextBox.Text"
        Dim i As Integer
        For i = 0 To 12
            Dim TName As String = SName & CStr(iLvalue) & CStr(i)


            For iLvalue = 0 To iRows - 1
                TName = sParams(iLvalue, 0)
                TName = sParams(iLvalue, 1)
                TName = sParams(iLvalue, 2)
                TName = sParams(iLvalue, 3)
                TName = sParams(iLvalue, 4)
                TName = sParams(iLvalue, 5)
                TName = sParams(iLvalue, 6)
                TName = sParams(iLvalue, 7)
                TName = sParams(iLvalue, 8)
                TName = sParams(iLvalue, 10)
                TName = sParams(iLvalue, 11)
                TName = sParams(iLvalue, 12)


            Next
        Next
I'm not sure what you are trying to do here, but it isn't going to work.

Thinking about it if you were trying to be say was you wanted to create a textbox that would appear on Form6 then this is more what you would want.

Code: 
 Dim SName As String = "TextBox"
        For iLvalue = 0 To iRows - 1
            Dim i As Integer
            For i = 0 To 12
                Dim TName As New TextBox
                TName.Name = SName & CStr(iLvalue) & CStr(i)
                TName.Text = sParams(iLvalue, i)
                Form6.Controls.Add(TName)
            Next
        Next

That should work for what you wanted. You could really set up their position at that time as well. I can't take the time to understand how you wanted the format right now so just take out all of the ".Text = r & c" and see if that works.

There is one problem with all of this and that is you can run out of actual space on your form before you get all of your lines on the form. A form has a maximum size even with scroll bars. If you have several hundred records it could very well exceed the size of the form and you will not see those controls.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
And actually I should have changed this part for you as well.

This:
TName.Name = SName & CStr(iLvalue) & CStr(i)

Can simply be this:
TName.Name = SName & iLvalue & i

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

I got my coding to work, but I am running into a Counter Issue.

The Coding For Pull:


Imports System.Data.SqlClient

Public Class Pull
Private Function TestIt() As Array

Dim sParm As String = Chr(39) & Me.TextBox1.Text.ToString & Chr(39)
Dim tParm As String = Chr(39) & Me.TextBox2.Text.ToString & Chr(39)
Dim uParm As String = Chr(39) & Me.TextBox3.Text.ToString & Chr(39)
Dim vParm As String = Chr(39) & Me.TextBox4.Text.ToString & Chr(39)
Dim wParm As String = Chr(39) & Me.TextBox5.Text.ToString & Chr(39)
Dim connectionString As String = "Data Source=999999999999; " & _
"Initial Catalog=9999; " & _
"Persist Security Info=True; " & _
"User ID=lawson; " & _
"Password=999999"

Dim sSQL As String = "SELECT COMPANY, FISCAL_YEAR, ACCT_PERIOD, CONTROL_GROUP, LINE_NBR, POSTING_DATE, OBJ_ID, STATUS, ACCT_UNIT, ACCOUNT, TRAN_AMOUNT, ACCT_AMOUNT, VAR_LEVELS FROM GLTRANS WHERE COMPANY = " & sParm & " AND FISCAL_YEAR = " & tParm & " AND ACCT_PERIOD = " & uParm & " AND CONTROL_GROUP = " & vParm & " AND POSTING_DATE = " & wParm & ";"
Dim oCn As New SqlConnection(connectionString)
Dim oCm As New SqlCommand(sSQL, oCn)
oCm.CommandType = CommandType.Text

Dim oDa As New SqlDataAdapter(oCm)
Dim oTable As New DataTable()

oCn.Open()
oDa.Fill(oTable)
Dim MyDataReader As SqlDataReader = oCm.ExecuteReader()
Dim iLvalue As Integer
Dim iRows As Integer = oTable.Rows.Count

Dim sParams(iRows, 12) As String
With MyDataReader
If .HasRows Then
While .Read()
sParams(iLvalue, 0) = .GetValue(0).ToString()
sParams(iLvalue, 1) = .GetValue(1).ToString()
sParams(iLvalue, 2) = .GetValue(2).ToString()
sParams(iLvalue, 3) = .GetValue(3).ToString()
sParams(iLvalue, 4) = .GetValue(4).ToString()
sParams(iLvalue, 5) = .GetValue(5).ToString()
sParams(iLvalue, 6) = .GetValue(6).ToString()
sParams(iLvalue, 7) = .GetValue(7).ToString()
sParams(iLvalue, 8) = .GetValue(8).ToString()
sParams(iLvalue, 9) = .GetValue(9).ToString()
sParams(iLvalue, 10) = .GetValue(10).ToString()
sParams(iLvalue, 11) = .GetValue(11).ToString()
sParams(iLvalue, 12) = .GetValue(12).ToString()
iLvalue += 1
End While
End If
End With

oCn.Close()
oCn.Dispose()
iLvalue = 0
Dim SName As String = "TextBox.Text"
Dim i As Integer
For i = 0 To 12
Dim TName As String = SName & CStr(iLvalue) & CStr(i)


For iLvalue = 0 To iRows - 1
TName = sParams(iLvalue, 0)
TName = sParams(iLvalue, 1)
TName = sParams(iLvalue, 2)
TName = sParams(iLvalue, 3)
TName = sParams(iLvalue, 4)
TName = sParams(iLvalue, 5)
TName = sParams(iLvalue, 6)
TName = sParams(iLvalue, 7)
TName = sParams(iLvalue, 8)
TName = sParams(iLvalue, 9)
TName = sParams(iLvalue, 10)
TName = sParams(iLvalue, 11)


Next
Next
Return sParams
End Function
Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
Me.Hide()
Dim AParams As Array
AParams = TestIt()
Dim F6 As New Form6(AParams)


End Sub

End Class

Pull Coding is working now.


Form6 Coding is not working and giving me all of the Lines except the Hard Coded First Line.

I believe the counter is not working or it may need one.

Form 6 Coding:

Imports System
Imports System.IO
Imports System.Drawing.Printing

Public Class Form6
Public Sub New(ByVal BParams As Array)
' This call is required by the Windows Form Designer.
InitializeComponent()
SetupForm(BParams)
' Add any initialization after the InitializeComponent() call.

End Sub


Private Sub SetupForm(ByVal BParams As Array)
Dim RowCount As Integer
Dim r, c, x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, y, z As Integer
x1 = 48
x2 = 87
x3 = 131
x4 = 178
x5 = 222
x6 = 278
x7 = 320
x8 = 358
x9 = 414
x10 = 458
x11 = 556
x12 = 650
x13 = 750
y = 42

If RowCount = 1 Then
Exit Sub
End If
For r = 2 To RowCount
z = 0
For c = 1 To 13
z = y + (35 * (r - 1))
Dim tb As New TextBox
With tb
.Name = "TextBox" & r & c
Select Case c ' position coordinates
Case 1
.Location = New Point(x1, z)
.Text = BParams(r & c)
.Size = New Size(34, 42)
Case 2
.Location = New Point(x2, z)
.Text = BParams(r & c)
.Size = New Size(38, 42)
Case 3
.Location = New Point(x3, z)
.Text = BParams(r & c)
.Size = New Size(39, 42)
Case 4
.Location = New Point(x4, z)
.Text = BParams(r & c)
.Size = New Size(37, 42)
Case 5
.Location = New Point(x5, z)
.Text = BParams(r & c)
.Size = New Size(46, 42)
Case 6
.Location = New Point(x6, z)
.Text = BParams(r & c)
.Size = New Size(35, 42)
Case 7
.Location = New Point(x7, z)
.Text = BParams(r & c)
.Size = New Size(32, 42)
Case 8
.Location = New Point(x8, z)
.Text = BParams(r & c)
.Size = New Size(49, 42)
Case 9
.Location = New Point(x9, z)
.Text = BParams(r & c)
.Size = New Size(42, 42)
Case 10
.Location = New Point(x10, z)
.Text = BParams(r & c)
.Size = New Size(83, 42)
Case 11
.Location = New Point(x11, z)
.Text = BParams(r & c)
.Size = New Size(87, 42)
Case 12
.Location = New Point(x12, z)
.Text = BParams(r & c)
.Size = New Size(92, 42)
Case 13
.Location = New Point(x13, z)
.Text = BParams(r & c)
.Size = New Size(92, 42)



End Select

End With
Me.Controls.Add(tb)
Next
Next
TextBox00.Text = BParams(0, 0)
TextBox01.Text = BParams(0, 1)
TextBox02.Text = BParams(0, 2)
TextBox03.Text = BParams(0, 3)
TextBox04.Text = BParams(0, 4)
TextBox05.Text = BParams(0, 5)
TextBox06.Text = BParams(0, 6)
TextBox07.Text = BParams(0, 7)
TextBox08.Text = BParams(0, 8)
TextBox09.Text = BParams(0, 9)
TextBox10.Text = BParams(0, 10)
TextBox11.Text = BParams(0, 11)
TextBox12.Text = BParams(0, 12)



End Sub
End Class

 
Upvote 0 Downvote
Sorwen,

Correction: Form6 Coding is not working and is giving me ONLY the first Coded Line Hardcoded.

 
Upvote 0 Downvote
Sorwen,

I am trying to get an understanding of all this code. After studying through my coding, I noticed that I have to set the RowCount to the UpperBounds of the Array. I hope I said this right. Anyhow, what VB coding does this in my coding? I believe you have to do this for the Array to get passed the first line. Because I believe I am just telling to pass values to the first line and quit.

DataMan86
 
Upvote 0 Downvote
Sorwen,

I tried to do a little research myself on the Internet. Based on what I read you have to set the RowCount to the Upper Bounds of the Array. So typing BParams.GetUpperBounds(RowCount) will do this I believe, but where do I place it in the Form6 coding?

The Form6 Coding:

Imports System
Imports System.IO
Imports System.Drawing.Printing

Public Class Form6
Public Sub New(ByVal BParams As Array)
' This call is required by the Windows Form Designer.
InitializeComponent()
SetupForm(BParams)
' Add any initialization after the InitializeComponent() call.

End Sub


Private Sub SetupForm(ByVal BParams As Array)
Dim RowCount As Integer
Dim r, c, x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, y, z As Integer
x1 = 48
x2 = 87
x3 = 131
x4 = 178
x5 = 222
x6 = 278
x7 = 320
x8 = 358
x9 = 414
x10 = 458
x11 = 556
x12 = 650
x13 = 750
y = 42

If RowCount = 1 Then
Exit Sub
End If
For r = 2 To RowCount
z = 0
For c = 1 To 13
z = y + (35 * (r - 1))
Dim tb As New TextBox
With tb
.Name = "TextBox" & r & c
Select Case c ' position coordinates
Case 1
.Location = New Point(x1, z)
.Text = BParams(r & c)
.Size = New Size(34, 42)
Case 2
.Location = New Point(x2, z)
.Text = BParams(r & c)
.Size = New Size(38, 42)
Case 3
.Location = New Point(x3, z)
.Text = BParams(r & c)
.Size = New Size(39, 42)
Case 4
.Location = New Point(x4, z)
.Text = BParams(r & c)
.Size = New Size(37, 42)
Case 5
.Location = New Point(x5, z)
.Text = BParams(r & c)
.Size = New Size(46, 42)
Case 6
.Location = New Point(x6, z)
.Text = BParams(r & c)
.Size = New Size(35, 42)
Case 7
.Location = New Point(x7, z)
.Text = BParams(r & c)
.Size = New Size(32, 42)
Case 8
.Location = New Point(x8, z)
.Text = BParams(r & c)
.Size = New Size(49, 42)
Case 9
.Location = New Point(x9, z)
.Text = BParams(r & c)
.Size = New Size(42, 42)
Case 10
.Location = New Point(x10, z)
.Text = BParams(r & c)
.Size = New Size(83, 42)
Case 11
.Location = New Point(x11, z)
.Text = BParams(r & c)
.Size = New Size(87, 42)
Case 12
.Location = New Point(x12, z)
.Text = BParams(r & c)
.Size = New Size(92, 42)
Case 13
.Location = New Point(x13, z)
.Text = BParams(r & c)
.Size = New Size(92, 42)



End Select

End With
Me.Controls.Add(tb)
Next
Next
TextBox00.Text = BParams(0, 0)
TextBox01.Text = BParams(0, 1)
TextBox02.Text = BParams(0, 2)
TextBox03.Text = BParams(0, 3)
TextBox04.Text = BParams(0, 4)
TextBox05.Text = BParams(0, 5)
TextBox06.Text = BParams(0, 6)
TextBox07.Text = BParams(0, 7)
TextBox08.Text = BParams(0, 8)
TextBox09.Text = BParams(0, 9)
TextBox10.Text = BParams(0, 10)
TextBox11.Text = BParams(0, 11)
TextBox12.Text = BParams(0, 12)



End Sub
End Class


Currently, the coding is just telling the computer to write out the first line that is hardcoded.

DataMan86
 
Upvote 0 Downvote
Which set of code did you decide to use for the main form? If it is the last I gave you then just change this to add what is in red.

Code: 
  Private Sub BtnPull_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPull.Click
        Me.Hide()
        Dim AParams As Array
        AParams = TestIt()
        Dim F6 As New Form6(AParams)
        [Red]F6.Show()[/Red]
    End Sub

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!
 
Upvote 0 Downvote
Sorwen,

I had already added the F6.Show()on the Pull.VB Coding. I had everything in the wrong place on Form6 Coding. I have amended the coding now with this Coding: I am getting an error which I will indicate below.


Imports System
Imports System.IO
Imports System.Drawing.Printing

Public Class Form6
Public Sub New(ByVal BParams As Array)
' This call is required by the Windows Form Designer.
InitializeComponent()
SetupForm(BParams)
' Add any initialization after the InitializeComponent() call.


End Sub


Private Sub SetupForm(ByVal BParams As Array)
Dim RowCount As Integer
Dim r, c, x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, y, z As Integer
x1 = 48
x2 = 87
x3 = 131
x4 = 178
x5 = 222
x6 = 278
x7 = 320
x8 = 358
x9 = 414
x10 = 458
x11 = 556
x12 = 650
x13 = 750
y = 42
TextBox00.Text = BParams(0, 0)
TextBox01.Text = BParams(0, 1)
TextBox02.Text = BParams(0, 2)
TextBox03.Text = BParams(0, 3)
TextBox04.Text = BParams(0, 4)
TextBox05.Text = BParams(0, 5)
TextBox06.Text = BParams(0, 6)
TextBox07.Text = BParams(0, 7)
TextBox08.Text = BParams(0, 8)
TextBox09.Text = BParams(0, 9)
TextBox10.Text = BParams(0, 10)
TextBox11.Text = BParams(0, 11)
TextBox12.Text = BParams(0, 12)


RowCount = BParams.GetUpperBound(0)

If RowCount = 1 Then
Exit Sub
End If
For r = 1 To RowCount - 1


z = 0
For c = 0 To 12
z = y + (35 * (r - 1))
Dim tb As New TextBox
With tb
.Name = "TextBox" & r & c
Select Case c ' position coordinates
Case 0
.Location = New Point(x1, z)
.Text = BParams(r & c)
.Size = New Size(34, 42)
Case 1
.Location = New Point(x2, z)
.Text = BParams(r & c)
.Size = New Size(38, 42)
Case 2
.Location = New Point(x3, z)
.Text = BParams(r & c)
.Size = New Size(39, 42)
Case 3
.Location = New Point(x4, z)
.Text = BParams(r & c)
.Size = New Size(37, 42)
Case 4
.Location = New Point(x5, z)
.Text = BParams(r & c)
.Size = New Size(46, 42)
Case 5
.Location = New Point(x6, z)
.Text = BParams(r & c)
.Size = New Size(35, 42)
Case 6
.Location = New Point(x7, z)
.Text = BParams(r & c)
.Size = New Size(32, 42)
Case 7
.Location = New Point(x8, z)
.Text = BParams(r & c)
.Size = New Size(49, 42)
Case 8
.Location = New Point(x9, z)
.Text = BParams(r & c)
.Size = New Size(42, 42)
Case 9
.Location = New Point(x10, z)
.Text = BParams(r & c)
.Size = New Size(83, 42)
Case 10
.Location = New Point(x11, z)
.Text = BParams(r & c)
.Size = New Size(87, 42)
Case 11
.Location = New Point(x12, z)
.Text = BParams(r & c)
.Size = New Size(92, 42)



End Select

End With
Me.Controls.Add(tb)
Next
Next

End Sub
End Class


Error: Attempted to operate on an array with incorrect number of dimensions.

R = 1
C = 0

When Debugged
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
89
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
how to read the coordinates in from this file
Replies
3
Views
131
Ken01
Ken01
Swi
  • Locked
  • Question
API - multipart/form-data
Replies
3
Views
142
Swi
Swi
2009luca
  • Locked
  • Question
SORT msflexgrid column 1 with decimal number 1
Replies
4
Views
113
2009luca
2009luca
sal21
  • Locked
  • Question
FORMAT output of Debug.Print .responseText (from api rest)
Replies
4
Views
99
2009luca
2009luca

Part and Inventory Search

Sponsor

Back
Top