0

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.

AddUser.vb

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
        Else
            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
            Case "Level3  (CAN ONLY USE THE ORDERS PAGE AND VIEW THE BILLS)"
                Acc_Lvl = 3
        End Select
        AddUserFun(user, passwd, Acc_Lvl)
        MsgBox("The User has been created")

End Sub

SQL.vb

Function AddUserFun(ByRef user, ByRef pass, ByRef acclvl) As Integer
        Dim i As Integer
        Try
            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
            conn.Open()
            addusr.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            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!!

2
Contributors
1
Reply
4
Views
5 Years
Discussion Span
Last Post by Reverend Jim
0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.