Help with ADO SHAPE command

Hello everyone,

I have recently migrated my database program to Microsoft SQL Server 2005.
I was using access and Microsoft Jet 4.0 to access data, but decided to move to something faster and stronger.

Since that migration the ADO SHAPE command I used for Report grouping no longer works. Here is my code.

[code]
SQL$ = "SHAPE(SHAPE(SHAPE" _
& "(SELECT PayrollNum,DeptNum,CheckNumber,FirstN,LastN,HourlyRate, PieceRate, CNARate, CNA," _
& "SSN,CURNet,CURGross,CURRegHrs,CURPcHrs,CURFICA,CURFIT,CURMDCR,BRCUR,SLCUR,CSCUR,OTHCUR," _
& "MTDNet,MTDGross,MTDRegHrs,MTDPcHrs,MTDFICA,MTDFIT,MTDMDCR,BRMTD,SLMTD,CSMTD,OTHMTD," _
& "QTDNet,QTDGross,QTDRegHrs,QTDPcHrs,QTDFICA,QTDFIT,QTDMDCR,BRQTD,SLQTD,CSQTD,OTHQTD," _
& "YTDNet,YTDGross,YTDRegHrs,YTDPcHrs,YTDFICA,YTDFIT,YTDMDCR,BRYTD,SLYTD,CSYTD,OTHYTD FROM " _
& "[REGISTAR" & Combo1.Text & "]" & varProgText & ") AS grp1 " _
& "COMPUTE grp1 BY PayrollNum,DeptNum,SSN,FirstN,LastN,CheckNumber,CNA) AS grp2 " _
& "COMPUTE grp2 BY PayrollNum,DeptNum) AS grp3 " _
& "COMPUTE grp3 BY PayrollNum "
[/code]

I keep getting this error:

Run-time error '-2147217900(80040e14)':

Data shape command text contains a syntax error at or near position
24 in the command. The text near the error is:
"SHAPE(SHAPE(SHAPE(SELECT
PayrollNu,DeptNum,CheckNumber,FirstN,LastN,HourlyRate,".

I know i had to make some changes to the query before, like adding brackets between Variables within the SQL statement for Table Names. I am not sure what the error is or modifications I need to make to be able to group this data. It used to work just fine when I was opening the data from an Access Database.

Once the Recordset connects I then assign it as a datasource to a DataReport.

Comments

  • :: "SHAPE(SHAPE(SHAPE(SELECT
    : PayrollNu,DeptNum,CheckNumber,FirstN,LastN,HourlyRate,".
    :
    I am not sure but I think you have replace [b]([/b] by [b]{[/b]
    like
    [code]
    SHAPE[b]{[/b]SHAPE[b]{[/b]SHAPE[b]{[/b]SELECT ...[b]}}}[/b]
    [/code]

  • : :: "SHAPE(SHAPE(SHAPE(SELECT
    : : PayrollNu,DeptNum,CheckNumber,FirstN,LastN,HourlyRate,".
    : :
    : I am not sure but I think you have replace [b]([/b] by [b]{[/b]
    : like
    : [code]
    : SHAPE[b]{[/b]SHAPE[b]{[/b]SHAPE[b]{[/b]SELECT ...[b]}}}[/b]
    : [/code]
    :
    :

    Thanks for the quick responce Lion, ok I tried that and now I have this error.

    [code]

    If PRDB_SQL_SRVER.State = adStateOpen Then
    PRDB_SQL_SRVER.Close
    End If

    PRDB_SQL_SRVER.Open "Provider = MSDataShape; Data Provider=SQLOLEDB; Data Source=servername;" _
    & "Initial Catalog=TVNSPAYROLL; User Id= SQLDBUSER; Password=pwd;"

    If viewCheckRS.State = adStateOpen Then
    viewCheckRS.Close
    End If

    With viewCheckRS
    .ActiveConnection = PRDB_SQL_SRVER 'viewcCnn
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Source = SQL$
    .Open
    End With

    [/code]

    This was my connection code. now it says

    Syntax error or access violation. Same Error code. It appears on the .Open command of my recordset. Any ideas anyone? Or can I not use ADO shape with SQL server?

  • Syntax error or access violation. Same Error code. It appears on the .Open command of my recordset. Any ideas anyone? Or can I not use ADO shape with SQL server?
    :
    Honestly speaking, I am working as business programmer almost 10 years and last 7 using MS SQL Server as DB and never used SHAPE command. However, I think your problem is about syntax of SQL statement that includes SHAPE. Try to run your query using SQL Server Query Analyzer. It will show you your problem.
  • : Syntax error or access violation. Same Error code. It appears on the .Open command of my recordset. Any ideas anyone? Or can I not use ADO shape with SQL server?
    : :
    : Honestly speaking, I am working as business programmer almost 10 years and last 7 using MS SQL Server as DB and never used SHAPE command. However, I think your problem is about syntax of SQL statement that includes SHAPE. Try to run your query using SQL Server Query Analyzer. It will show you your problem.
    :

    Ok apparently I got it to work. I cannot explain exactly why it is working now, only that the first two { bracket call outs in my SHAPE decleration needed to be changed to ( parenthesis. The inner most SHAPE decleration needed to remain a { bracket but the other two changed to parenthesis () and it worked. I am still trying to read and research as to why this would happen.

    Here is the code in case anyone wanted to know.

    [code]
    SQL$ = "SHAPE(SHAPE(SHAPE" _
    & "{SELECT PayrollNum,DeptNum,CheckNumber,FirstN,LastN,HourlyRate, PieceRate, CNARate, CNA," _
    & "SSN,CURNet,CURGross,CURRegHrs,CURPcHrs,CURFICA,CURFIT,CURMDCR,BRCUR,SLCUR,CSCUR,OTHCUR," _
    & "MTDNet,MTDGross,MTDRegHrs,MTDPcHrs,MTDFICA,MTDFIT,MTDMDCR,BRMTD,SLMTD,CSMTD,OTHMTD," _
    & "QTDNet,QTDGross,QTDRegHrs,QTDPcHrs,QTDFICA,QTDFIT,QTDMDCR,BRQTD,SLQTD,CSQTD,OTHQTD," _
    & "YTDNet,YTDGross,YTDRegHrs,YTDPcHrs,YTDFICA,YTDFIT,YTDMDCR,BRYTD,SLYTD,CSYTD,OTHYTD FROM " _
    & "[REGISTAR" & Combo1.Text & "]" & varProgText & "} AS grp1 " _
    & "COMPUTE grp1 BY PayrollNum,DeptNum,SSN,FirstN,LastN,CheckNumber,CNA) AS grp2 " _
    & "COMPUTE grp2 BY PayrollNum,DeptNum) AS grp3 " _
    & "COMPUTE grp3 BY PayrollNum "

    'Checking for instance of open connection, if open it will close
    If viewcCnn.State = adStateOpen Then
    viewcCnn.Close
    End If

    With viewcCnn
    .Provider = "MSDataShape"
    .Open "Data Provider=SQLOLEDB; Data Source=Datasource;" _
    & "Initial Catalog=TVNSPAYROLL; User Id= SQLDBUSER; Password=Password;"
    End With

    If viewCheckRS.State = adStateOpen Then
    viewCheckRS.Close
    End If

    With viewCheckRS
    .ActiveConnection = viewcCnn
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Source = SQL$
    .Open
    End With
    [/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