Cycle Through DB Fields 1

[trbyrne]

I have a table w/ approx 50 fields in it. Basically what I want to do is for each record in the table I want to insert a new record but only change a handful of fields. The table contains customer data and now I want to add spousal information for each customer. Essentially all of the data will remain the same, but the names will obviously change as well as the relation type.

I do not want to have to create an insert command for all 50 fields, and I am looking for a quick way to do this. Is there a way to cycle through the fields in a Reader object and easily append values to a string? I guess I'm looking for something like:

strsql="insert into <TABLE> ("
for each field in reader
strsql+=field.name
loop

strsql+=") values ("
for each field in reader
if field.name="fieldtochange" then
strsql+="newvalue"
else
strsql+=field.value
end if
loop

 

[jebenson]

You already have the basic structure, so here's a quick example that should get you going on the specifics:

(Assumes a DataReader named dr)

Dim s As String

While dr.Read
For fc As Integer = 0 To dr.FieldCount - 1
s &= dr.GetName(fc) & " = " & dr.GetValue(fc) & ", "
Next

MsgBox(s)

End While

dr.GetName - get's the column name
dr.GetValue(fc) - get's the column's value

One thing to watch out for is that you need to put string values in single quotes in your SQL, numeric values without quotes, etc.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[trbyrne]

That's perfect! So is there any way to tell in VB if the field is a string, numeric, or date value? That would be the final hurdle in this thing.

Man, thanks again for the response.

 

[jebenson]

try dr.GetDataTypeName or dr.GetFieldType



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[trbyrne]

Thanks jebenson for the great help!!! That works perfectly!