Problems Adding values into an MS SQL table

[indrajeet6]

Hi all,

I had originally installed MS SQL Server 2008 R2 in my C: when I was developing the application. While I had the SQL DErver in my C:, everything was fine, no problems at all. Then I began getting low disc space messages, so I uninstalled it from my C: and Installed it in a new external hard drive. But then I started having problems adding values in on field of a table.

SPECIFICS:

The application in concern is an application that has a table named Users that is the cause of my trouble. It has 3 fields, of which the application can update 2 faultlessly. It's a field called Access_Level that is having trouble getting valuse added to it. Whatever choice I enter in the form, the field only gets "0" as the value, which is not even in the list of choices! I would welcome help on determining why this happens, and how to get the table to take the value I want it to!!

CODE SNIPPETS:

FORM:

Public Class AddUser

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Acc_Lvl, done As Integer
        Dim user, passwd, var As String
        passwd = ""
        user = NameBox.Text
        If (String.Compare(PassBox.Text, PassBox2.Text)) = 0 Then
            passwd = PassBox.Text
            var = ListBox1.SelectedItem
            Select Case var
                Case "Level 1 ( ACCESS EVERYTHING)"
                    Acc_Lvl = 1
                Case "Level 2 (CAN NOT ADD AND DELETE USER)"
                    Acc_Lvl = 2
                Case "Level3  (CAN ONLY USE THE ORDERS PAGE AND VIEW THE BILLS)"
                    Acc_Lvl = 3
            End Select
            done = AddUserFun(user, passwd, Acc_Lvl)

            If (done = 1) Then
                MsgBox("User creation encountered an error")
            Else
                MsgBox("User Created")
            End If

        Else
            MsgBox("Password and Re Enter password fields don't match")
        End If



    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Hide()
        Opening_Form.Show()

    End Sub
End Class

AddUserFun Function:

 Function AddUserFun(ByVal user, ByVal pass, ByVal acclvl) As Integer
        Dim i As Integer
        Try
            Dim conn As New SqlClient.SqlConnection("server=THE-507FC9ABDEB;uid=indrajeet6;pwd=Indrani7&;database=VBDB")
            Dim addusr As New SqlClient.SqlCommand
            addusr.CommandText = "INSERT INTO dbo.Users VALUES('" & user & "','" & pass & "','" & acclvl & "')"
            addusr.CommandType = CommandType.Text
            addusr.Connection = conn
            conn.Open()
            addusr.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            i = 1
        End Try
        i = 0
        Return i
    End Function

I have the SQL Generated Script fo the database generation also, if needed. The basic thing is that the table is defined As Follows:

CREATE TABLE [dbo].[Users](
    [Username] [varchar](50) NOT NULL,
    [Password] [varchar](50) NOT NULL,
    [Access_Level] [int] NOT NULL
) ON [PRIMARY]
GO

[Reverend Jim]

First suggestion is to declare the parameter types in AddUserFun as

Function AddUserFun(user As String, pass As String, acclvl As Integer) As Integer

and at the top of this function you can add (for now)

Debug.WriteLine("ADD user=" & user & "  pass=" & pass & "  acclvl=" & acclvl.ToString())

If all of the acclcl values are coming in as 0 then your problem is in Button_Click where you are setting the value of Acc_Lvl, in which case you will have to do some debugging in your Select/Case clause. It would help to add an Else clause as in

Select Case var
    Case "Level 1 ( ACCESS EVERYTHING)"
        Acc_Lvl = 1
    Case "Level 2 (CAN NOT ADD AND DELETE USER)"
        Acc_Lvl = 2
    Case "Level3 (CAN ONLY USE THE ORDERS PAGE AND VIEW THE BILLS)"
        Acc_Lvl = 3
    Case Else
        MsgBox("unexpected value " & var)
End Select

It might help prevent typos is you did your Select/Case on only the first few chars of the string as in

Select Case var.Substring(0,7)

I also couldn't help but notice your strings start with

"Level 1"
"Level 2"
"Level3"

note the lack of a blank in the third entry. Typos like this can screw up the code. That's why you need the "Case Else" to catch these types of errors.

[Icone]

Some additional information, avoid using PASSWORD as your field, it is a reserved word I think including USERNAME. I suggest to do some '_' in your field naming.

[indrajeet6]

Hi Reverend Jim and Icone, I solved the problem. The issue was a basic lack of understanding the difference between the ListBox ( What I had Used ) and a Drop Box (What I intended to use). What was happening was that I'd set the size of the list box to show only one item. I would Navigate to the Access Level I wanted by the scrolling Arrows, but not select anything, believing that since it was showing, I'd selected it. It was resolved by way of the following tutorial.

Thanks for your help guys, and I wil have to make it that if the user meakes this kind of error, the error is caught and explained to the user!!