Connecting to pre-created databases

Question

Okay so what I am trying to accomplish is setting up a program that multiple users can use that connects to a database they have already created.

Example: user1 connects to theirsite.com's database,  user15 connects to theirownsite.com's database... etc etc..

How would I set up a login system that would allow them to login to their own already created database, with their own username and password that is already setup?

I watched some videos on setting up a login form, which most use a access file and what not... I also read some posts on this forum, but most are logging into just 1 database already setup...

What im looking to do is setup the program with a login, that looks for each ones own already made database.. how would I be able to do that?  For other parts of the program I can do myself or I can find with no problems... But its that connection part that is stopping me from continuing on.

Would I need to setup the login to where they can enter their website where their pre-existing login is at? Or how would attempt to setup something like that?

VB 2010

Thanks in advance for any help or direction.

Answer 1

It depends on the manufacturer of the databae the method you use to connect to the database.  Some vendors supply there own drives.  Others you can use one of the Microsoft generic drivers.  Sme you can use either type of driver but may get different results depending on the driver you are using.  You can also setup connections on a PC if you go to control Panel

Administrator tools - Data sources (ODBC).

Once you setup your ODBC datasources you can use the code on the webpage below to get a list of these sources

http://stackoverflow.com/questions/562016/listing-odbc-data-sources-in-c-sharp

---

jdweng

Answer 2

Is there something like that, but in Visual basic code?  I'm not using c#.. ;)

I "think" basically, I would need a setup that they input their database info.. (database name, db_user, db_password, and a specific table name that info needs extracted from)

So a possible box like:

url

database name

username

password

table name

(connect button)

It would connect to that specific database + table, and when its changed, it sends them a message or alert sound that a new row has been added, and if clicked would take them to a webpage where that information is layed out..

Know what I mean?

Answer 3

I converted the code to Basic.  Can't guarentee it will work, but it should be close.  The easiest way of using C# code is just to drop it into a new project and then refernce the project.

Imports Microsoft.Win32
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Windows.Forms
Imports System.Environment
Module Module1
    Sub Main()
        Dim reg As RegistryKey = (Registry.CurrentUser).OpenSubKey("Software")
        reg = reg.OpenSubKey("ODBC")
        reg = reg.OpenSubKey("ODBC.INI")
        reg = reg.OpenSubKey("ODBC Data Sources")
    End Sub
    Public Sub ListODBCsources()
        Dim envHandle As Integer = 0
        Const SQL_FETCH_NEXT As Integer = 1
        Const SQL_FETCH_FIRST_SYSTEM As Integer = 32
        If OdbcWrapper.SQLAllocEnv(envHandle) <> -1 Then
            Dim ret As Integer
            Dim serverName As StringBuilder = New StringBuilder(1024)
            Dim driverName As StringBuilder = New StringBuilder(1024)
            Dim snLen As Integer = 0
            Dim driverLen As Integer = 0
            ret = OdbcWrapper.SQLDataSources(envHandle, _
               SQL_FETCH_FIRST_SYSTEM, serverName, _
               serverName.Capacity, snLen, _
               driverName, driverName.Capacity, driverLen)
            Do While (ret = 0)
                Dim message As String = serverName.ToString() + System.Environment.NewLine + driverName.ToString()
                System.Windows.Forms.MessageBox.Show(message)
                ret = OdbcWrapper.SQLDataSources(envHandle, SQL_FETCH_NEXT, serverName, serverName.Capacity, snLen, _
                 driverName, driverName.Capacity, driverLen)
            Loop
        End If
    End Sub
    Private Function EnumDsn() As List(Of String)
        Dim list As List(Of String) = New List(Of String)
        Dim sublist() As String
        sublist = Registry.CurrentUser.GetSubKeyNames()
        list.AddRange(sublist)
        sublist = Registry.LocalMachine.GetSubKeyNames()
        list.AddRange(sublist)
        Return list
    End Function
    Private Function EnumDsn(ByVal rootKey As RegistryKey) As IEnumerable(Of String)
        Dim regKey As RegistryKey = rootKey.OpenSubKey("Software\ODBC\ODBC.INI\ODBC Data Sources")
        Dim returnstr As List(Of String) = New List(Of String)
        If Not regKey Is Nothing Then
            For Each name In regKey.GetValueNames()
                Dim value As String = regKey.GetValue(name, "").ToString()
                returnstr.Add(value)
            Next name
        End If
        Return returnstr
    End Function
End Module
Public Class OdbcWrapper
    '[DllImport("odbc32.dll")]         
    'public static extern int SQLDataSources(int EnvHandle, 
    'int Direction, StringBuilder ServerName, 
    'int ServerNameBufferLenIn,     
    'ref int ServerNameBufferLenOut, 
    'StringBuilder Driver, int DriverBufferLenIn, 
    'ref int DriverBufferLenOut);         
    Declare Function SQLDataSources Lib "odbc32.dll" (ByVal EnvHandle As Integer, _
         ByVal Direction As Integer, ByVal ServerName As StringBuilder, _
         ByVal ServerNameBufferLenIn As Integer, _
         ByRef ServerNameBufferLenOut As Integer, _
         ByVal Driver As StringBuilder, ByVal DriverBufferLenIn As Integer, _
         ByRef DriverBufferLenOut As Integer) As Integer
    '[DllImport("odbc32.dll")] 
    'public static extern int SQLAllocEnv(ref int EnvHandle);     } 
    Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef EnvHandle As Integer) _
         As Integer
End Class

---

jdweng

Answer 4

I actually went a I guess complete different way with this, which the connection worked...  So I have gotten that far...

However what I want to do is connect to the database by text input.

here is what I have at the moment for the connection:

[code]

connectionstring = "server=serverIP;user id=username;password=password;database=databasename"

[/code]

Now with that, I get the connection... Now how can I set that up so that the information put in textbox1.text, textbox2.text, etc, puts in the information for the server, user id, etc

I tried a few things, but pretty much failed at it..  Any ideas?

Answer 5

The connection string is a string which you can create in code.

Dim conectionString as string = "server=" & Textbox1.text & ";" & _

   "user id=" & Textbox2.Text & ";" _ &

   "password=" & Textbox3.Text & ";" & _

   "database=" & Textbox4.Text

 

Now you could change the name of the textboxes to be server,userid,password, and database to make this code a little easier to maintain.  Simple go to the form and right click each textbox and select property to open the porperty window.  Then change the name of each textbox in the property window.  I neve like using the default names of the controls on a form (except for labels which I don't access from my code).

---

jdweng

Answer 6

The connection string is a string which you can create in code.

Dim conectionString as string = "server=" & Textbox1.text & ";" & _

   "user id=" & Textbox2.Text & ";" _ &

   "password=" & Textbox3.Text & ";" & _

   "database=" & Textbox4.Text

 

Now you could change the name of the textboxes to be server,userid,password, and database to make this code a little easier to maintain.  Simple go to the form and right click each textbox and select property to open the porperty window.  Then change the name of each textbox in the property window.  I neve like using the default names of the controls on a form (except for labels which I don't access from my code).

---

jdweng

Awesome, Thanks so much, that was exactly what I needed... And yes, I rarely use the default names for text boxes and buttons... txt_name btn_name, and "sometimes"  lbl_name is what I use.  I merely used textbox1.text etc.. for examples :D

Answer 7

Curious to know.

Is there a way a "noticification" (sound or popup message) can be set if a row in a specific table has been updated?

And is there a way to display the table better than the datagridview?

Answer 8

The DataGridView gives more option that any other control.  It is sometimes harder to use than other controls.  Better is trade-off in this case.   Simplified code and less options, or more complicated code with more options.

See this webpage for a list of binding events

http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview_events.aspx

I think BindingContextChange may be the correct one to use.

---

jdweng

Answer 9

Helpful, but not so much for me lol....

I tried something with an example they have, but changed it to datagridview1, but it didnt work, So I can only surmise that i'm not using it correctly.

[code]

    Private Sub AddEventHandler()
        AddHandler DataGridView1.BindingContextChanged, _
            AddressOf BindingContext_Changed
    End Sub

    Private Sub BindingContext_Changed(ByVal sender As Object, ByVal e As EventArgs)
        MsgBox("New Ticket Submitted")
    End Sub

[/code]

But its not auto updating the row, unless I click a refresh button I setup, so then no msgbox is displayed.  Am I using it incorrectly ?

Is there a way to have the table itself auto update while the program is running, maybe using a timer?  Im not to familiar on how to properly use timers for specific events.

Any help with that?

Answer 10

See if this webpage helps. 

http://msdn.microsoft.com/en-us/library/h974h4y2.aspx

I think you need to use the 

http://msdn.microsoft.com/en-us/library/ms184414.aspx

---

jdweng