Please take a look at the code below. It is a copy of the coding of a function and a subroutine I've coded in my program.
The SQL Database VBDB has the table Users defined so that the default access level is 0.

The problem is that the access level is 0 no mater what access level is put in the program. I've run the same command in SQL, there it runs fine.

Please do tell me what the problem is.


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Acc_Lvl As Integer
        Dim user, passwd, var As String
        passwd = ""
        user = NameBox.Text
        If (String.Compare(PassBox.Text, PassBox2.Text)) = 0 Then
            passwd = PassBox.Text
            MsgBox("Password and Re Enter password fields don't match")
        End If
        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
                Acc_Lvl = 3
        End Select
        AddUserFun(user, passwd, Acc_Lvl)
        MsgBox("The User has been created")

End Sub


Function AddUserFun(ByRef user, ByRef pass, ByRef acclvl) As Integer
        Dim i As Integer
            Dim conn As New SqlClient.SqlConnection("server=PG-COMP1;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
        Catch ex As Exception
            i = 1
        End Try
        i = 0
        Return i
End Function

I am going out of my mind,the code worked fine till I uninstalled and reinstalled SQL Server 2008 R2, now it doesn't work!!

It is the code I developed for my final project at college, please do help me out!!

Are the fields in the database in the same order as specified in the insert? The usual format of an insert is to name the fields that correspond to the parameters as in

addusr.CommandText = "INSERT INTO dbo.Users (username,password,access) " & _
                     "VALUES('" & user & "','" & pass & "'," & acclvl & ")"

You don't specify the parameter types in the definition of AddUserFun. You should rewrite this as

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

You don't need to put single quotes around acclvl in the insert. It's possible that by fixing these three things (named fields, typed parameters and removing ') you will fix your problem.

And before someone else mentions it (and they will) you should use parameterized entry rather than just compose the query by concatenating fields to guard against SQL injection attacks.

This article has been dead for over six months. Start a new discussion instead.