Adding value from database to datatable

[kriday]

I have written the following code to add data from db to datatable
before this i have specified connection string.

Code:

strSQL = " Select * from UserId"
Set objResults = objDB.Execute(strSQL)
nrow=1
Do Until objResults.EOF
    strUserid = objResults.Fields("UserId")
    Datatable.SetCurrentRow(nrow)
    Datatable.GlobalSheet.AddParameter "UserId" , strUserid
    msgbox Datatable("UserId",dtGlobalSheet)
    Reporter.ReportEvent micDone, strUserid , " "
    objResults.MoveNext
    nrow=nrow+1
Loop

now my problem is data is added to the same row like
UserId UserId1
12 31
but i want them like this
UserId
12
31

I need help on this
Thanks in advance

[SteveS]

You are adding a new datatable column each time, move this line outside the loop and remove strUserid so you just create a single new datatable column:

Datatable.GlobalSheet.AddParameter "UserId" , ""


Then in place of that line in the loop just assign the value strUserid to the datatable so:

Datatable("UserId", dtGlobalSheet) = strUserid

Code:

strSQL = " Select * from UserId"
Set objResults = objDB.Execute(strSQL)
nrow=1
Datatable.GlobalSheet.AddParameter "UserId" , ""
Do Until objResults.EOF
     strUserid = objResults.Fields("UserId")
     Datatable.SetCurrentRow(nrow)
     Datatable("UserId", dtGlobalSheet) = strUserid
     msgbox Datatable("UserId",dtGlobalSheet)
     Reporter.ReportEvent micDone, strUserid , " "
     objResults.MoveNext
     nrow=nrow+1
Loop

[Staff]

Please ensure to include your code between [code] tags while asking or replying to questions. I have done this for you for this time.

[kriday]

Thank you for the reply.It is still adding 2 columns like this
UserId, UserId1
12
31

please help me.

thank you

[SteveS]

You must still have the AddParameter line within the loop as it works perfectly fine for me exactly as I posted above?

[kordirko]

If a parameter with name userid already exists in datatable, the AddParameter call will add a new parameter with name userid1, userid2 etc.

Run tis sample code:

Code:

For i = 1 to 3
    ParamName = DataTable.GlobalSheet.AddParameter(  "Param", "12" ).Name
    MsgBox ParamName
Next

You must check if a parameter exists, and add the parameter only if not exists, here is an example how to do it:

Code:

Sub AddParameterIfNotExists( byval Sheet, byval ParamName, byval strValue )
     On error resume next
     Sheet.getParameter( ParamName ).value = strValue
     If Err.Number <> 0  Then
           Sheet.AddParameter ParamName, strValue
     End If
End Sub

For i = 1 to 3
    Call AddParameterIfNotExists( DataTable.GlobalSheet, "ParamName", i )
    MsgBox DataTable.GlobalSheet.GetParameter( "ParamName" )
Next

[kriday]

Than you so much for your reply.