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

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
 
shwaqar82Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Munawar HussainPrincipal Software EngineerCommented:
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
shwaqar82Author Commented:
i just need to get the CompanyCode in array from Company table
0
Munawar HussainPrincipal Software EngineerCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

shwaqar82Author Commented:
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
shwaqar82Author Commented:
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
Munawar HussainPrincipal Software EngineerCommented:
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
shwaqar82Author Commented:
what about if i need the number of occurances of specific record in a column
0
Munawar HussainPrincipal Software EngineerCommented:
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
shwaqar82Author Commented:
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
shwaqar82Author Commented:
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
Munawar HussainPrincipal Software EngineerCommented:
change this line
dbread=dbcomm.ExecuteNonQuery()

to
dbread=dbcomm.ExecuteReader()
0
Munawar HussainPrincipal Software EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shwaqar82Author Commented:
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
shwaqar82Author Commented:
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
Munawar HussainPrincipal Software EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.