| Author |
Topic |
|
vynx
Starting Member
8 Posts |
 Posted - 2004-12-09 : 19:58:05
|
| i found crosstab / pivot stored procedure created by rob volk, but when i trying to use this in visual basic 6, it returned error like object not open... some one can help me with give me example to make application with visual basic 6 and ADO to retrieve result / recordset from this stored procedure?thx b4 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-09 : 20:33:53
|
| try puttingset nocount onat the top of the sp if it's not already there.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vynx
Starting Member
8 Posts |
Posted - 2004-12-09 : 21:16:22
|
| in beginning sp i have already put set nocount on , but it still error.... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-09 : 21:18:05
|
| Have you tried running the procedure in Query Analyzer? Can you post the VB code you're using? |
|
|
|
vynx
Starting Member
8 Posts |
Posted - 2004-12-09 : 21:28:11
|
| this is my code, it return 0 recordset, i try this in query analyzer, successful...cnConnection.ConnectionString = "Provider=SQLOLEDB.1;User ID=sa;password=andrih5;Initial Catalog=expt;Data Source = silvia;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"cnConnection.OpenDim PtrlCmd As New adodb.CommandPtrlCmd.ActiveConnection = cnConnectionPtrlCmd.CommandText = "crosstab"PtrlCmd.CommandType = adCmdStoredProcPtrlCmd.Parameters.Append PtrlCmd.CreateParameter("@select", adVarChar, adParamInput, 8000, "SELECT regional,cabang,kode_cust,nama_cust FROM temp1 where periode='11/2004' GROUP BY regional,cabang,kode_cust,nama_cust")PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("@sumfunc", adVarChar, adParamInput, 100, "sum(jumlah)")PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("@pivot", adVarChar, adParamInput, 100, "kode_brg")PtrlCmd.Parameters.Append PtrlCmd.CreateParameter("@table", adVarChar, adParamInput, 100, "temp1")Set rsRecordset = PtrlCmd.ExecuteDo While (Not rsRecordset Is Nothing) If rsRecordset.State = adStateClosed Then Exit Do While Not rsRecordset.EOF................rsRecordset.MoveNext'LoopWendLoop |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-09 : 22:12:19
|
| A couple of things:- Try removing the "Use Procedure for Prepare" from the connection string. I'm not sure what it does, but it might be interfering somehow- The "Do While (Not Recordset Is Nothing)" loop isn't needed, nor the If statement immediately after- Is temp1 a temp table, or a regular table? If it is a temp table, this won't work through VB. Temp tables are preceded by a # |
|
|
|
vynx
Starting Member
8 Posts |
Posted - 2004-12-09 : 22:51:26
|
| i have already remove that, but it still error...temp1 is a regular table, not temporary table... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-10 : 07:08:58
|
| Can you post the actual error message, and the line where it occurs? |
|
|
|
vynx
Starting Member
8 Posts |
Posted - 2004-12-10 : 07:28:59
|
| i'm sorry, not error, but it return 0 recordset... like query with 0 result... i was trying in query analyzer, the result at least 4000 result... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-10 : 07:44:46
|
| Assuming that the database and login are correct in the connection string, and the user has the correct permissions, the only suggestion I have is to try running it without using a command object, like so:rsRecordset.Open "exec crosstab 'SELECT regional,cabang,kode_cust,nama_cust FROM temp1 where periode=''11/2004'' GROUP BY regional,cabang,kode_cust,nama_cust', 'sum(jumlah)', 'kode_brg', 'temp1'", cnConnectionMake sure that is all on one line. |
|
|
|
|
retrieve result from sp