• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Write 1 Query SQL in VB 6.0

I have 1 query....

DECLARE @Col1 int,
     @sql nvarchar(1000)

DECLARE curA CURSOR FOR
SELECT distinct ID1 FROM ID

OPEN curA
FETCH NEXT FROM curA INTO @Col1

WHILE @@FETCH_STATUS=0
BEGIN
     if exists (select * from dbo.sysobjects where id = object_id(N'[IDTEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
          SELECT @sql = 'ALTER TABLE IDTEMP ADD [' + convert(varchar,@Col1) + '] int'
          exec sp_executesql @sql
     END
     ELSE
     BEGIN          
         
          SELECT @sql = 'CREATE TABLE IDTEMP([' + convert(varchar,@Col1)+ '] int)'
          exec sp_executesql @sql
     END    

FETCH NEXT FROM curA INTO @Col1
END

CLOSE curA
DEALLOCATE curA

How do I Write this query in VB 6.0

help me
Thank you.



0
thang74us
Asked:
thang74us
  • 2
  • 2
1 Solution
 
nmcdermaidCommented:
You would be best off wrapping it up in a stored procedure then call the stored procedure from VB.

You'll need to check the VB section to find out how to run a stored procedure using ADO, but below is how to create it as a stored procedure.




CREATE PROC p_DoStuff AS

DECLARE @Col1 int,
     @sql nvarchar(1000)

DECLARE curA CURSOR FOR
SELECT distinct ID1 FROM ID

OPEN curA
FETCH NEXT FROM curA INTO @Col1

WHILE @@FETCH_STATUS=0
BEGIN
     if exists (select * from dbo.sysobjects where id = object_id(N'[IDTEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
          SELECT @sql = 'ALTER TABLE IDTEMP ADD [' + convert(varchar,@Col1) + '] int'
          exec sp_executesql @sql
     END
     ELSE
     BEGIN          
         
          SELECT @sql = 'CREATE TABLE IDTEMP([' + convert(varchar,@Col1)+ '] int)'
          exec sp_executesql @sql
     END    

FETCH NEXT FROM curA INTO @Col1
END

CLOSE curA
DEALLOCATE curA

GO

0
 
thang74usAuthor Commented:
Please Help me  Call a stored procedure in VB
0
 
nmcdermaidCommented:
Its a while since I've done any VB. If you ask in the VB newsgroup someone will help you quickly.

You don't have to award me any points if you need them for the VB group.
0
 
thang74usAuthor Commented:
Thank you
I will need you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now