?
Solved

SQL login from vb.net form

Posted on 2009-12-29
11
Medium Priority
?
360 Views
Last Modified: 2012-05-08
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
Comment
Question by:webquests
  • 6
  • 5
11 Comments
 
LVL 4

Expert Comment

by:deicidedx
ID: 26143197
Ok ...try this one.
Dim objConnection As New SqlConnection("server=" & txtserver.text & ";database=master;user id = username; password=password;")
0
 

Author Comment

by:webquests
ID: 26146845
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
 

Author Comment

by:webquests
ID: 26147047
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 4

Expert Comment

by:deicidedx
ID: 26147144
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
 

Author Comment

by:webquests
ID: 26147200
Sorry. I am trying your code again. Stand by.......
0
 

Author Comment

by:webquests
ID: 26147243
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
 

Author Comment

by:webquests
ID: 26147264
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
 
LVL 4

Accepted Solution

by:
deicidedx earned 2000 total points
ID: 26147369
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
 
LVL 4

Expert Comment

by:deicidedx
ID: 26147390
Did you found the problem ?
0
 

Author Comment

by:webquests
ID: 26147532
Yes, did you get the points?
0
 
LVL 4

Expert Comment

by:deicidedx
ID: 26149018
Yes mate and thanks !
Have a happy new year ...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

839 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