asked on

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

Munawar Hussain

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

shwaqar82

ASKER

i just need to get the CompanyCode in array from Company table

Munawar Hussain

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

shwaqar82

ASKER

this will make me more confused. can u please make it more simple. i only need the array for companycode content from company table

shwaqar82

ASKER

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

Munawar Hussain

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

shwaqar82

ASKER

what about if i need the number of occurances of specific record in a column

Munawar Hussain

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.

shwaqar82

ASKER

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

shwaqar82

ASKER

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

Munawar Hussain

change this line
dbread=dbcomm.ExecuteNonQuery()

to
dbread=dbcomm.ExecuteReader()

ASKER CERTIFIED SOLUTION

Munawar Hussain

membership

This solution is only available to members.

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

Start Free Trial

shwaqar82

ASKER

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

shwaqar82

ASKER

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.

Munawar Hussain

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