data is not inserted to database

Question

i tried to insert the data into database with this code

Public Sub AddUser()
    Dim con As dbConn = New dbConn()
    Dim SqlSelect As String
    SqlSelect = "SELECT * FROM login Where user_id='" & WorkerID_.Text & "'"
    Dim cmd As New OleDbCommand(SqlSelect, con.oleconnection)
    Dim reader As OleDbDataReader
    Dim da As New OleDbDataAdapter

    con.open()
    reader = cmd.ExecuteReader()
    reader.Read()

    If reader.HasRows() Then
        reader.Close()
        con.close()
        FailureText.Text = "User ID already exists!"
    Else
        reader.Close()
        con.close()
        Dim InsertSQL As String
        InsertSQL = "INSERT INTO login (user_id, user_role, user_password, user_status) VALUES "
        InsertSQL &= "('" & WorkerID_.Text & "', "
        InsertSQL &= "'Worker', "
        InsertSQL &= "'12345', 1)"

        Dim SqlUpdate As String

        SqlUpdate = "INSERT INTO Worker (ID, WorkerID, WorkerName, DoB, Address, Phone, Email, CompanyName, PassportNum, PassportExp, VisaExp, VisaStatus, user_id) VALUES (default,"
        SqlUpdate &= "'" & WorkerID_.Text & "', "
        SqlUpdate &= "'" & WorkerName.Text & "', "
        SqlUpdate &= "'" & DoB.Text & "', "
        SqlUpdate &= "'" & Address.Text & "', "
        SqlUpdate &= "'" & Phone.Text & "', "
        SqlUpdate &= "'" & Email.Text & "', "
        SqlUpdate &= "'" & Company.SelectedValue & "', "
        SqlUpdate &= "'" & PassNum.Text & "', "
        SqlUpdate &= "'" & PassExp.Text & "', "
        SqlUpdate &= "'" & VisaExp.Text & "', "
        SqlUpdate &= "'No Visa', "
        SqlUpdate &= "'" & WorkerID_.Text & "') "

        Dim insertCommand As New OleDbCommand(SqlUpdate, con.oleconnection)
        Dim cmd1 As New OleDbCommand(InsertSQL, con.oleconnection)
        Try
            con.open()
            cmd1.ExecuteNonQuery()
            insertCommand.ExecuteNonQuery()
        Catch
            FailureText.Text = "Unable to add user"
        Finally
            con.close()
        End Try

    End If
    Response.Redirect("Workers.aspx")
End Sub

the Insert into login part is working. the data is well inserted. but for the insert into worker part is not working. the data is not inserted into the table. the program shows no error and it still can work. what could possibly wrong with this?

Answer 1

Read another answer on OleDb I just answered on another post. You will be wide open to sql-injection too. Parmaeterize queries. By you concatenating strings to build one command, what if one value has a single-quote within the text entry. You are now hosed. What if someone puts malicious SQL commands and then deletes your records or entire table(s). Learn to parameterize your queries and also clean values, especially if coming from a web interface.

Your commands should probably be updated something like

Dim con As dbConn = New dbConn()
Dim SqlSelect As String
SqlSelect = "SELECT * FROM login Where user_id= @parmUserID"
Dim cmd As New OleDbCommand(SqlSelect, con.oleconnection)
cmd.Parameters.AddWithValue( "parmUserID", WorkerID_.Text )

Follow-suit with the Insert and update commands... parameterize them but using @variable place-holders in your commands.

Dim InsertSQL As String
InsertSQL = "INSERT INTO login (user_id, user_role, user_password, user_status) "
InsertSQL &= " VALUES ( @parmUser, @parmRole, @parmPwd, @parmStatus )"
Dim cmdInsert As New OleDbCommand(InsertSQL, con.oleconnection)
cmdInsert.Parameters.AddWithValue( "parmUser", WorkerID_.Text )
cmdInsert.Parameters.AddWithValue( "parmRole", "Worker" )
cmdInsert.Parameters.AddWithValue( "parmPwd", "12345" )
cmdInsert.Parameters.AddWithValue( "parmStatus", 1 )


Dim SqlUpdate As String
SqlUpdate = "INSERT INTO Worker (ID, WorkerID, WorkerName, DoB, Address, Phone, Email, CompanyName, PassportNum, PassportExp, VisaExp, VisaStatus, user_id) "
SqlUpdate &= " VALUES ( @parmID, @parmName, @parmDoB, etc... ) "
Dim cmdUpdate As New OleDbCommand(SqlUpdate, con.oleconnection)
cmdUpdate.Parameters.AddWithValue( "parmID", WorkerID_.Text )
cmdUpdate.Parameters.AddWithValue( "parmName", WorkerName.Text )
cmdUpdate.Parameters.AddWithValue( "parmDoB", DoB.Text )
-- etc with the rest of the parameters.

Final note. Make sure the data types you are trying to insert or update are of same type expected in the table. Such example is your "Birth Date" (DoB) field. If you are trying to insert as simple text, and it is not in an auto-converted format, the SQL-Insert might choke on it and fail. If you have a textbox bound to a DateTime type, then your parameter might be Dob.SelectedDate (such as a calendar control), or you could pre-convert from text to a datetime and then use THAT as your parameter value.

Other numeric values, leave as they are too, they should directly apply for the insert. You could also identify the AddWithValue() call the data type the parameter should represent (string, int, double, datetime, whatever)