ID Module

[andybonse2012]

Hello,

I'm trying to set a global variable from a sql string, unsure on how to do it.

My module is:

Module AgentIDNumber

    Dim AgentIDNumber As Integer

    Sub Main()
        AgentIDNumber = 

    End Sub

End Module

I want the AgentIDNumber to come from the Users table and AgentID Column.

Not sure whats the best way, I want it to be set when a specific user logs in.

E.g

Andrew Smith puts in his username and password, presses logon and it sets the global variable to whatever his AgentID is in the sql table.

[Shaggy Hiker]

Do you have any more done than that? I ask because the answer is different depending on how things are set up.

If I assume that the AgentID column is a column in the same table that has username and password (which would make sense, as it could easily be either a primary key, or something else), then I would be writing the code something like this (assuming a SQL Server DB):

Using cn As New SQLClient.SQLConnection(some connection string)
 Using cmd As SQLClient.SQLCommand = cn.CreateCommand
  Try
    cn.Open
    cmd.CommandText = "SELECT AgentID FROM Users WHERE Username = @p1 AND Password = @p2"
    cmd.Parameters.AddWithValue("@p1",some user name)
    cmd.Parameters.AddWithValue("@p2",some password)
    Dim obj As Object = cmd.ExectueScalar
    If Obj Is Not Nothing Then
     AgentIDNumber = CInt(obj)
    Else
      'You got nothing, what do you want to do?
    End If
  Catch ex As Exception
    'Do something here, even if just show a message.
   End Try
  End Using
 End Using

[andybonse2012]

Hi,

I tried to do it on my logon form, as you can see my failed attempt puts the actual text into the variable not the returned value from SQL Table, yes the username and password is in the same table.

Private Sub LoginButton_Click(sender As System.Object, e As System.EventArgs) Handles LoginButton.Click
        Dim connection As New SqlClient.SqlConnection
        Dim command As New SqlClient.SqlCommand
        Dim adaptor As New SqlClient.SqlDataAdapter
        Dim AID As String = AgentIDNum.AgentIDNumber
        Dim AIDGET As String = "SELECT AgentID FROM [Users] WHERE (username='" + UserNameBox.Text + "')AND (password='" + PasswordBox.Text + "');"

        Dim dataset As New DataSet

        connection.ConnectionString = "Data Source=ANDY-PC\LETTINGS;Initial Catalog=easylet_database;User Id=sa;Password=g3m1n1;"
        command.CommandText = "SELECT * FROM [Users] WHERE (username='" + UserNameBox.Text + "')AND (password='" + PasswordBox.Text + "');"
        connection.Open()

        command.Connection = connection

        adaptor.SelectCommand = command

        adaptor.Fill(dataset, "0")

        Dim count = dataset.Tables(0).Rows.Count



        If count > 0 Then
            EasyMain.Show()
            Me.Hide()
            command.CommandText = AIDGET
            AgentIDNum.AgentIDNumber = AIDGET

        Else
            MsgBox("Invalid Username or Password", 16, "Error")
            PasswordBox.Clear()

        End If



    End Sub

Not sure what I need to change here.

AgentIDNum.AgentIDNumber is a global variable in a module, the returned value need to be put into here, i.e

AgentID column in the sql table is somthing like 343ffd-dd4-fdsf333r and I need to pass it into this variable when they log on.

[Shaggy Hiker]

There are reasons why you don't want to directly concatenate textbox contents into SQL strings. The most important one is that it leaves you open to SQL Injection attacks. That is why I showed the use of parameters, which is safer. I would also suggest that you use & rather than + for string concatenation. Both work, but there are cases where + will cause you trouble, unless you have Option Strict ON (which you should do anyways).

I'm not quite sure why you did it that way. You create a whole dataset for what should be a single row. Technically, that will work, but it is the slowest of all possible choices. You also name the table "0", which is a really unusual name, but does have a certain beauty to it, since Dataset.Tables(0) would return the same thing as Dataset.Tables("0"). I would suggest that giving the table a better name would make things easier in the long run. Not using a dataset and dataadapter at all would be better still. The only reason you are creating the dataset is to get a single value from a single row, if that single row exists. That is what the very fast ExecuteScalar is ideal for, which is why I used it in my example.

One other point is that you use SELECT *. You only want a single field, yet you are telling the DB to send back ALL the fields, whatever they are. Using * is inefficient, even if you want ALL the fields, though I would add that I sometimes use * anyways when I want all the fields, because it is much easier to type than typing out all the fields for a large table. In your case, you don't want all the fields, you want only a single field, so don't use *.