Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create Database Programatically in asp.net

Posted on 2009-06-28
3
Medium Priority
?
448 Views
Last Modified: 2012-05-07
I need to be able to code a page for an asp.net website (using vb.net) that can a:) Create a database based on user input (name of db only) and b:) create it in my sites "App_Data" folder. I've been able to create a database using the code below, but it places it in the default SQL Server folder, rather than the App_Data folder. I am using VS 2008 and SQL Server 2005. So how can I arrange for the created database to be in my sites "App_Data" folder and attach it to the project.
dim c as sqlconnection
dim com as sqlcommand
dim w as string = "Create Database " & "textbox1.text
 
com = new sqlcommand(w,c)
c.open
com.executenonquery
c.close

Open in new window

0
Comment
Question by:SwingSt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Solar_Flare earned 2000 total points
ID: 24733446
if you use the "CREATE DATABASE" sql command then you can specify the filename and path, see http://msdn.microsoft.com/en-us/library/aa258257(SQL.80).aspx


if you are using SQLExpress another approach you could take is to use system.io to make a copy of an mdf file that you have prepared earlier and put it in the App_Data folder - you can then use a sqlconnection with a connectionstring that specifies you are attaching a user instance, something like this:

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
0
 

Author Comment

by:SwingSt
ID: 24733640
No, these db's are to be original. The link to MSDN would be great, but when I use any of the examples other than
     dim w as string = "create database " & fileName.text
I start getting errors like the following:

Incorrect syntax near 'FILENAME'

So, could I get an example of the proper syntax one would use in this situation?
0
 

Author Comment

by:SwingSt
ID: 24733710
Nevermind, I finally worked out the syntax, thanks for the help - pushed me in the right direction. For anyone looking for a similar solution - I have posted the final code below. It creates a brand new Db in the folder specified, you have to then create and your needed tables but it is right there.
dim c as sqlconnection = YOUR CONNECTION
dim com as sqlcommand
Dim w As String
        w = "CREATE DATABASE " & nameBox.Text & " " _
        & "ON " _
        & "( NAME = " & nameBox.Text & "," _
        & " FILENAME = 'C:\Users\Thomas\Desktop\WebSite1\App_Data\" & nameBox.Text & ".mdf')" _
        & " LOG ON" _
        & " ( NAME = '" & nameBox.Text & "_log'," _
        & " FILENAME = 'C:\Users\Thomas\Desktop\WebSite1\App_Data\" & nameBox.Text & ".ldf')"
 
com = new sqlcommand(w,c)
c.open
com.executenonquery
c.close

Open in new window

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question