?
Solved

how can i iterate through one field in a table and query sql command into another table

Posted on 2008-02-06
15
Medium Priority
?
354 Views
Last Modified: 2008-03-06
Hi
i need to iterate thorugh each record in 'companycode' field of 'Company' table and injecting sql query for each record in 'companycode' in 'Customer' table

Let say i hav 3 different company code in one table. For each 3 different copanies i need to count how many members for each company are there in another table

For that i need to iterate through each record in companycode field of company table and put that in an array. How can i get that field in as array......?
How can i iterate through each record and inject sql query into another table

Please help me this is urgent

Regards
Shaukat
 
0
Comment
Question by:shwaqar82
  • 8
  • 7
15 Comments
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20835247
assuming that you have filled 2 datatables in VB.NET code from ur database named as
dtCompany and dtCustomer
you may count records as follows:

 Dim dtCompany As New DataTable() 'contains all companies
        Dim dtCustomer As New DataTable() 'contains all customers for all companies
        Dim dtRows() As DataRow ' declare an array of datarow type
        Dim dr As DataRow
        For Each dr In dtCompany.Rows
            dtRows = dtCustomer.Select("CompanyCode='" & dr("CompanyCode").ToString & "'") ' returns rows for each company means all customers for each company
        Next
0
 

Author Comment

by:shwaqar82
ID: 20835302
i just need to get the CompanyCode in array from Company table
0
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20835390
the array gives you all columns.. you may use anyone as per need
like
dtRows(0).Item(0) 'fist col
dtRows(0).Item(1)'second col or you may use name of cols like  
dtRows(0).Item("companyCode")
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:shwaqar82
ID: 20835539
this will make me more confused. can u please make it more simple. i only need the array for companycode content from company table
0
 

Author Comment

by:shwaqar82
ID: 20835924
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("abc.mdb"))
dbconn.Open()

Dim sqlGBN As String  = "select count(*) from company"
dbcomm=New OleDbCommand(sqlGBN,dbconn)
dbread=dbcomm.ExecuteReader()
Dim str As String = Nothing

While dbread.Read()
str &= dbread.GetInt32(0) & ", " & dbread.Value(0)
End While

im trying to get the value present in each record .. help me in getting the value please
0
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20835983
oh seems you need just number of records in the company tables? is that?

then just use executescaler

dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("abc.mdb"))
dbconn.Open()

Dim sqlGBN As String  = "select count(*) from company"
dbcomm=New OleDbCommand(sqlGBN,dbconn)
Dim tCount as int=dbcomm.ExecuteScaler() 'THIS LINE will give you count of rows

' NO NEED FOR THIS CODE
Dim str As String = Nothing
While dbread.Read()
str &= dbread.GetInt32(0) & ", " & dbread.Value(0)
End While


THANKS
0
 

Author Comment

by:shwaqar82
ID: 20836004
what about if i need the number of occurances of specific record in a column
0
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20836072
hey dear fellow seems you are not telling exactly what you need ..
I believe all u need can be done by just simple queries.. but seems you are not able to explain what u need.
0
 

Author Comment

by:shwaqar82
ID: 20836122
if u read my actual question you will be able to understand wat i exactly need.

here is some more explanation. For the first step i need to make an array that contains data present in a field(companycode). Let say i have a table(Company). This table contains field(companycode). I need all the entries of companycode in a string array. Now using the oledbdatareader how can i achieve it. I think this time i explain it much better..? Please let me know if you have any more question to help me solve this problem

Regards
Shaukat  
0
 

Author Comment

by:shwaqar82
ID: 20850402
i got it to work....now if someone can help me fixing the below problem that would be great:
i have the following code:

sqlUpdate = "Update company Set count='asd' where companycode='det'"
dbcomm=New OleDbCommand(sqlUpdate,dbconn)
dbread=dbcomm.ExecuteNonQuery()

and it gives me the following error:
Compiler Error Message: BC30311: Value of type 'Integer' cannot be converted to 'System.Data.OleDb.OleDbDataReader'.
on line:
dbread=dbcomm.ExecuteNonQuery()

any solution for the problem will be rewarded.

Best Regards
Shaukat
0
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20850439
change this line
dbread=dbcomm.ExecuteNonQuery()

to
dbread=dbcomm.ExecuteReader()
0
 
LVL 12

Accepted Solution

by:
Munawar Hussain earned 1000 total points
ID: 20850462
sorry wrong comments

actually you want to execute an update query that mean no result from query then why assigning to a reader???

if want to executeNonQuery then use this
sqlUpdate = "Update company Set count='asd' where companycode='det'"
dbcomm=New OleDbCommand(sqlUpdate,dbconn)
dbcomm.ExecuteNonQuery()

if want to select something frm db and wnat to use reader then use this
sqlUpdate = "Update company Set count='asd' where companycode='det'"
dbcomm=New OleDbCommand(sqlUpdate,dbconn)
dbread=dbcomm.ExecuteReader()

0
 

Author Comment

by:shwaqar82
ID: 20850496
i tried before but its giving me update syntax error:

Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.
i also tried ExecuteScalar()
0
 

Author Comment

by:shwaqar82
ID: 20850684
actually all i wanted to do is to update the value in the database. i tried using ur command :
dbcomm.ExecuteNonQuery()
still giving me the same error:
Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

0
 
LVL 12

Expert Comment

by:Munawar Hussain
ID: 20850782
seems you are trying to update a column of data type int with a string (varchat or text value)

what type of "count" column is ?? int or text or varchar??
if it is of type int or numeric then use query without single qoutes like this


sqlUpdate = "Update company Set [count]=10 where [companycode]='det'"
dbcomm=New OleDbCommand(sqlUpdate,dbconn)
dbcomm.ExecuteNonQuery()
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

588 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