Click here to Skip to main content
15,885,890 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I am developing a vb.net winform project to take in details of visitors into a business. I am using a sql server 2008 express database to store the details.
When the application starts, it checks for a database, if it's there it continues on but if it's not it creates the database in sql server 2008 express.
The code attached is supposed to create the database if it doesn't exist (well, it's supposed to anyway). The program will go through the code that's supposed to create the database no problem but will throw an error in the form load at sqlCon.Open, the error is: 'Cannot open database "Visitors" requested by the login. The login failed.' It would be greatly appreciated if someone could tell me where I am going wrong?

VB
Private Sub allVisitors_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.Text = "All Visitors"

        If Not checkDatabaseExists() Then

            Dim Result1 = MessageBox.Show("Database does not exist, press OK to continue and create database. Press Cancel to close.", "Create database", MessageBoxButtons.OKCancel)
            If Result1 = Windows.Forms.DialogResult.OK Then
                createVisitorsDatabase() 'Calls sub to create database
                createTablesForVisitors() 'Calls sub to create tables in database
            ElseIf Result1 = Windows.Forms.DialogResult.Cancel Then
                Me.Close()
                Exit Sub
            End If

            MessageBox.Show("Database has been created. Press OK to continue.", "Database created", MessageBoxButtons.OK)
        Else

            Dim Result2 = MessageBox.Show("Datebase exists, press OK to continue. Press Cancel to close.", "Dateabase exists", MessageBoxButtons.OKCancel)
            If Result2 = Windows.Forms.DialogResult.OK Then

            ElseIf Result2 = Windows.Forms.DialogResult.Cancel Then
                Me.Close()
                Exit Sub
            End If

        End If

        connectionString = "Data Source=.\SQLExpress;Initial Catalog=Visitors;Integrated Security=True;MultipleActiveResultSets=True"

        sql = "SELECT idNumber, firstName, lastName, company FROM visitorDetails"

        sqlCon = New SqlConnection(connectionString)

        sqlCon.Open() 'Error thrown here.
        'Error is : Cannot open database "Visitors" requested by the login. The login failed.
        
        sqlCmd = New SqlCommand(sql, sqlCon)

        da = New SqlDataAdapter(sql, sqlCon)

        dt = loadDtVisitorDetails()
        fillDgvVisitorDetails(dt)

        sqlCmd.Dispose()
        sqlCon.Close()

    End Sub

Public Sub createVisitorsDatabase()

        connectionString = "Data Source=.\SQLExpress;Integrated Security=True;MultipleActiveResultSets=True"

        sql = "CREATE DATABASE Visitors"

        sqlCon = New SqlConnection(connectionString)

        sqlCon.Open()
        sqlCmd = New SqlCommand(sql, sqlCon)

        da = New SqlDataAdapter(sql, sqlCon)

        sqlCmd.Dispose()
        sqlCon.Close()

    End Sub

    Public Sub createTablesForVisitors()

        connectionString = "Data Source=.\SQLExpress;Integrated Security=True;MultipleActiveResultSets=True"

        sql = " USE Visitors " & _
       "CREATE TABLE visitorDetails " & _
       "(idNumber int NOT NULL, firstName varchar(25) NOT NULL, lastName varchar(40) NOT NULL, company varchar(150) NOT NULL, " & _
       "contactNumber varchar(50) NOT NULL, countryCode varchar(1000) NOT NULL, photoId varchar(1000) NULL, email varchar(150) NULL, PRIMARY KEY (idNumber)) " & _
       "CREATE TABLE reasonForVisit " & _
       "(idNumber int, dateOnSite varchar(20), reasonForVisit varchar(150))"

        sqlCon.Open()
        sqlCmd = New SqlCommand(sql, sqlCon)

        da = New SqlDataAdapter(sql, sqlCon)

        sqlCmd.Dispose()
        sqlCon.Close()
    End Sub
Posted

You never appear to execute the command.
instead of:
da = New SqlDataAdapter(sql, sqlCon)

put in
sqlCmd.ExecuteNonQuery()

The same applies to your createTablesForVisitors method.
 
Share this answer
 
Comments
Member 10804519 13-Aug-14 10:13am    
Thanks @Pheonyx, that solved the problem.
Pheonyx 13-Aug-14 10:18am    
No problem, it can be easy miss a line or put the wrong line in when you've been doing other things and never notice.
Replace
SQL
da = New SqlDataAdapter(sql, sqlCon)

with
SQL
SQLCmd.ExecuteNonQuery()


SQLDataAdapter represents a set of data commands and a database connection that are used to fill a DataSet and update a SQL Server database. It should not be used to execute DDL statements.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900