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

SQL login from vb.net form

Hello experts, I am writing an application that will require the user to enter a username and password in order to validate a sql login. As part of the application setup I am considering writing code that will use the master database and create a generic  username and password. The user would then use these credentials to log into the application. What I have now is:
 Dim objConnection As New SqlConnection("server=ajs-monster\SQL2005;database=master;user id = sa;password = ajszoso;") which is fine for my local box but I want the servername etc. to user entered so it can be installed on other boxes.


I tried:  Dim objConnection As New SqlConnection("server=txtserver.text;database=master;user id = username; password=password;") which doesn't work.


Thanks in advance for your help.
0
webquests
Asked:
webquests
  • 6
  • 5
1 Solution
 
deicidedxCommented:
Ok ...try this one.
Dim objConnection As New SqlConnection("server=" & txtserver.text & ";database=master;user id = username; password=password;")
0
 
webquestsAuthor Commented:
Thanks but no luck.
I will paste the code beloe so that you have a better idea of what I am trying to do.

Here is the code for the login form:

Imports Microsoft.SqlServer
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Management
Imports Microsoft.SqlServer.Management.Smo

Public Class frmLogin
 

    Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        On Error Resume Next
        'Let's check to see if setup has ever been run and if not let's run it otherwise just show the login screen

        If Not My.Computer.FileSystem.DirectoryExists("c:\Inventory") Then
            MessageBox.Show("You need to run setup")
            frmSetup.Show()
            Me.Hide()
        End If

        ' List local servers
        Dim dataTable = SmoApplication.EnumAvailableSqlServers(True)
        cboServers.ValueMember = "Name"
        cboServers.DataSource = dataTable

    End Sub


    Private Sub cboServers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboServers.SelectedIndexChanged
        cboDatabase.Items.Clear()
        If cboServers.SelectedIndex <> -1 Then
            Dim serverName As String = cboServers.SelectedValue.ToString()
            Dim server As Server = New Server(serverName)
            Try
                For Each database As Database In server.Databases
                    cboDatabase.Items.Add(database.Name)
                Next
            Catch ex As Exception
                Dim exception As String = ex.Message
            End Try
        End If

    End Sub

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        MDIParent1.Show()
        Me.Hide()
    End Sub


    Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
        Me.Close()
    End Sub



End Class


and here is the code for the setup form:

Imports System.Data
Imports System.Data.SqlClient

' Import Data and SQLClient Namespaces

Public Class frmSetup
    'Declare Objects
    Dim objConnection As New SqlConnection("server=ajs-monster\sql2005;database=master;user id = sa; password=ajszoso")



    Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click

        Call Make_It()

        Dim objConnection As New SqlConnection("server=ajs-monster\sql2005;database=master;user id = sa; password=ajszoso")
        Dim objCommand As SqlCommand = New SqlCommand()
        objConnection.Open()
        objCommand.Connection = objConnection
        objCommand.CommandText = "CREATE DATABASE [Inventory] ON  PRIMARY( NAME = N'Inventory', FILENAME = N'C:\inventory\inventory.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )log on( NAME = N'inventory_log', FILENAME = N'C:\inventory\inventory_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)"


        'Execute the sql

        Try
            objCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Close the connection
        objConnection.Close()
        Call Create_inventory()
        MessageBox.Show("Database and tables have been created")
        Me.Close()
        MDIParent1.Show()

    End Sub

    Private Sub Create_inventory()
        Dim objConnection As New SqlConnection("server=ajs-monster\sql2005;database=master;user id = sa; password=ajszoso")
        Dim objCommand As SqlCommand = New SqlCommand()
        objConnection.Open()
        objCommand.Connection = objConnection
        objCommand.CommandText = "CREATE TABLE [dbo].[Inventory]([Name] [varchar](50) ,      [URL] [varchar](50) ,[Username] [varchar](50) ,      [Password] [varchar](50) ) ON [PRIMARY] "


        'Execute the sql

        Try
            objCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Close the connection
        objConnection.Close()
        Call Create_vendors()
    End Sub


    Private Sub Create_vendors()
        Dim objConnection As New SqlConnection("server=ajs-monster\sql2005;database=master;user id = sa; password=ajszoso")
        Dim objCommand As SqlCommand = New SqlCommand()
        objConnection.Open()
        objCommand.Connection = objConnection
        objCommand.CommandText = "CREATE TABLE [dbo].[vendors]([CompanyName] [varchar](50) ,[Address1] [varchar](50) ,      [Address2] [varchar](50) ,[City] [varchar](50), [State] [varchar](50),[zip] [varchar](12), [Phone] [varchar](50), [fax] [varchar](50), [Contact] [varchar](50), [Website] [varchar](50) ) ON [PRIMARY] "

        'Execute the sql

        Try
            objCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Close the connection
        objConnection.Close()
    End Sub

    Public Sub Make_It()
        My.Computer.FileSystem.CreateDirectory("c:\Inventory")
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

 

End Class
0
 
webquestsAuthor Commented:
I also discovered and fixed a couple of errors such as the use master for the table creation. It has been changed to use inventory.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
deicidedxCommented:
I don't understand where is your problem. If you replace your line with mine then the users will be able to send the program the server parameter. Do you get an error while trying it ? Please do explain ..the code is no use in this case until you clearly state your problem so i can guide/help you.
Cheers
0
 
webquestsAuthor Commented:
Sorry. I am trying your code again. Stand by.......
0
 
webquestsAuthor Commented:
ok, the specific issue is that when I enter your code it bypasses the setup routine. In other words, it displays the "you need to run setup" messagebox but doesn't open the setup form. It just displays the login form.
0
 
webquestsAuthor Commented:
this is the routine with your code, note that it is a combo box, not a text field.

 Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click

        My.Computer.FileSystem.CreateDirectory("c:\Inventory")

        Dim objConnection As New SqlConnection("server=" & cboServer.Text & ";database=inventory;user id = sa; password=ajszoso;")
        Dim objCommand As SqlCommand = New SqlCommand()
        objConnection.Open()
        objCommand.Connection = objConnection
        objCommand.CommandText = "CREATE DATABASE [Inventory] ON  PRIMARY( NAME = N'Inventory', FILENAME = N'C:\inventory\inventory.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )log on( NAME = N'inventory_log', FILENAME = N'C:\inventory\inventory_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)"


        'Execute the sql

        Try
            objCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Close the connection
        objConnection.Close()
        Call Create_inventory()
        MessageBox.Show("Database and tables have been created")
        Me.Close()
        MDIParent1.Show()

    End Sub
0
 
deicidedxCommented:
But the code for this button click event has nothing to do with the setup form !I can see you're creating the database then show a message box with the confirmation that it was created. What form doesnt show up ?
0
 
deicidedxCommented:
Did you found the problem ?
0
 
webquestsAuthor Commented:
Yes, did you get the points?
0
 
deicidedxCommented:
Yes mate and thanks !
Have a happy new year ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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