Results 1 to 5 of 5

Thread: How do I detail which database to create a view in SQL using vb.net?

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    How do I detail which database to create a view in SQL using vb.net?

    My first attempt: (obviously not the whole query, but this is where its having issues)

    Code:
    Dim viewCreationString As String = "USE [" & databaseName & "];
                                                CREATE VIEW [dbo].[" & ProjectNameTextBox.Text & "_view] AS SELECT DISTINCT "

    Error: 'CREATE VIEW' must be the first statement in a query batch.


    My second attempt: (again not the whole query, but this is where its having issues)

    Code:
     Dim viewCreationString As String = "USE [" & databaseName & "]
                                                GO
                                                SET ANSI_NULLS ON
                                                GO
                                                SET QUOTED_IDENTIFIER ON
                                                GO 
                                                CREATE VIEW [dbo].[" & ProjectNameTextBox.Text & "_view] AS SELECT DISTINCT "
    Error: Incorrect syntax near 'GO'.

    Incorrect syntax near 'GO'.

    Incorrect syntax near 'GO'.

  2. #2

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    Re: How do I detail which database to create a view in SQL using vb.net?

    Turns out using the database prefix later on in the query helped determined which database the view was to be created in.

    This worked:

    Code:
    Dim viewCreationString As String = " CREATE VIEW [dbo].[" & ProjectNameTextBox.Text & "_view] AS SELECT DISTINCT "

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How do I detail which database to create a view in SQL using vb.net?

    Well, if you're connected to the correct database in the first place, then it'll be created in that database and then you don't need to worry about it.

    And I'm not sure what you meant by
    database prefix later on in the query helped determined which database the view was to be created in.
    because what prefix you use in the query has no bearing on where it gets created... what has bearing is the context at the time the query is created.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    116

    Re: How do I detail which database to create a view in SQL using vb.net?

    That's weird because I am connected with the sever admin creds, which has access to all databases and its still creating the view in the correct database...

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How do I detail which database to create a view in SQL using vb.net?

    You don't use "GO" in ADO.NET. That is specific to SSMS I believe.

    What does your connection string look like? Are you specifying an Initial Catalog value? If so, that is the database that any SQL you execute over that connection will affect. If you don't specify a database, I believe that you connect to the default database for the login. You have to call ChangeDatabase on your connection in order to change the database you're connected to.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width