Easier Way To Store SQL Results?

I Am Using SQL Alot In A Project, But For Some Reason I Feel I'm Doing Way More Work Then I Have To. Curently, I Am Storing Variables By Checking Each Field, Then Storing Accordingly.

[code]
Do Until rs.EOF
EmpSum.CurRec = EmpSum.CurRec + 1
CmdEdit(EmpSum.CurRec).Visible = True
For Each fld In rs.Fields
If fld.Name = "11a" Then
EmpSum.SumHours(1, 1, 1) = fld.Value
ElseIf fld.Name = "12a" Then
EmpSum.SumHours(1, 2, 1) = fld.Value
ElseIf fld.Name = "13a" Then
EmpSum.SumHours(1, 3, 1) = fld.Value
ElseIf fld.Name = "14a" Then
EmpSum.SumHours(1, 4, 1) = fld.Value
End If
Next
Loop
[/code]

There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.

Any Ideas Would Be Appreciated.

Comments

  • : I Am Using SQL Alot In A Project, But For Some Reason I Feel I'm Doing Way More Work Then I Have To. Curently, I Am Storing Variables By Checking Each Field, Then Storing Accordingly.
    :
    : [code]
    : Do Until rs.EOF
    : EmpSum.CurRec = EmpSum.CurRec + 1
    : CmdEdit(EmpSum.CurRec).Visible = True
    : For Each fld In rs.Fields
    : If fld.Name = "11a" Then
    : EmpSum.SumHours(1, 1, 1) = fld.Value
    : ElseIf fld.Name = "12a" Then
    : EmpSum.SumHours(1, 2, 1) = fld.Value
    : ElseIf fld.Name = "13a" Then
    : EmpSum.SumHours(1, 3, 1) = fld.Value
    : ElseIf fld.Name = "14a" Then
    : EmpSum.SumHours(1, 4, 1) = fld.Value
    : End If
    : Next
    : Loop
    : [/code]
    :
    : There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.
    :
    : Any Ideas Would Be Appreciated.
    :


    Have you considered referencing each field directly? ie:

    [code]
    Do Until rs.EOF
    EmpSum.CurRec = EmpSum.CurRec + 1
    CmdEdit(EmpSum.CurRec).Visible = True

    EmpSum.SumHours(1, 1, 1) = rs!11a
    EmpSum.SumHours(1, 2, 1) = rs!12a
    EmpSum.SumHours(1, 3, 1) = rs!13a
    EmpSum.SumHours(1, 4, 1) = rs!14a
    rs.MoveNext
    Loop
    [/code]

  • : : I Am Using SQL Alot In A Project, But For Some Reason I Feel I'm Doing Way More Work Then I Have To. Curently, I Am Storing Variables By Checking Each Field, Then Storing Accordingly.
    : :
    : : [code]
    : : Do Until rs.EOF
    : : EmpSum.CurRec = EmpSum.CurRec + 1
    : : CmdEdit(EmpSum.CurRec).Visible = True
    : : For Each fld In rs.Fields
    : : If fld.Name = "11a" Then
    : : EmpSum.SumHours(1, 1, 1) = fld.Value
    : : ElseIf fld.Name = "12a" Then
    : : EmpSum.SumHours(1, 2, 1) = fld.Value
    : : ElseIf fld.Name = "13a" Then
    : : EmpSum.SumHours(1, 3, 1) = fld.Value
    : : ElseIf fld.Name = "14a" Then
    : : EmpSum.SumHours(1, 4, 1) = fld.Value
    : : End If
    : : Next
    : : Loop
    : : [/code]
    : :
    : : There Must Be An Easier Way To Do This Then To Check The Field Name For Every Record.
    : :
    : : Any Ideas Would Be Appreciated.
    : :
    :
    :
    : Have you considered referencing each field directly? ie:
    :
    : [code]
    : Do Until rs.EOF
    : EmpSum.CurRec = EmpSum.CurRec + 1
    : CmdEdit(EmpSum.CurRec).Visible = True
    :
    : EmpSum.SumHours(1, 1, 1) = rs!11a
    : EmpSum.SumHours(1, 2, 1) = rs!12a
    : EmpSum.SumHours(1, 3, 1) = rs!13a
    : EmpSum.SumHours(1, 4, 1) = rs!14a
    : rs.MoveNext
    : Loop
    : [/code]
    :
    :

    Yes I Have, I Just Didn't Know How To Go About It In VB. I've Done It In PHP, So That's Why I Was Wondering If I Could Do It In VB. Thanks Alot.
  • Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This
    [code]
    Compile Error:

    Expected: End Of Statement
    [/code]

    Is There Any Way To Get Around This?
  • There's several ways to reference the field and each has tradeoffs.

    rs!field - good performance but not for fieldnames with a space.
    rs("field name") - slow but fine with any valid fieldname
    rs(1), rs(2), etc. - best perf. and fieldname irrelevant but poor for code readability

    In Access you can also use: rs![field name] but that may not apply


    : Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This
    : [code]
    : Compile Error:
    :
    : Expected: End Of Statement
    : [/code]
    :
    : Is There Any Way To Get Around This?
    :

  • : There's several ways to reference the field and each has tradeoffs.
    :
    : rs!field - good performance but not for fieldnames with a space.
    : rs("field name") - slow but fine with any valid fieldname
    : rs(1), rs(2), etc. - best perf. and fieldname irrelevant but poor for code readability
    :
    : In Access you can also use: rs![field name] but that may not apply
    :
    :
    : : Can My Field Not Start With A Number? If The Field Starts With A Number, I Get This
    : : [code]
    : : Compile Error:
    : :
    : : Expected: End Of Statement
    : : [/code]
    : :
    : : Is There Any Way To Get Around This?

    rs.Fields("FIELDNAME").Value & vbnullstring

    This is the safest way to refer to recordset fields in my experience. The vbnullstring is to prevent "Invalid Use of NULL" errors when assigning a database value to a non-Variant variable.

    I have found the ! shortcut also has other odd behavior in some cases.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories