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?

@Codexer i did. i checked the query and it seems to get all the value i wanted, but it doesn't insert the data to database — procatmer, Jan 07 '16 at 04:48
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Jan 07 '16 at 05:53
@marc_s it seems like the solution i need but i'm having a hard time to understand on how to use parameterized store procedure, but i'm looking forward to it. thanks for that. aside from that, how come it works in the first part but not in the second? — procatmer, Jan 07 '16 at 06:44

score 1 · Answer 1 · answered Jan 07 '16 at 04:34

1

You seem to have 12 parameters you wish to insert, and 13 arguments in the VALUES part of your insert query. is the Default seen in the values section below intentional?

INSERT INTO Worker (ID, ... VisaStatus) VALUES (default,"

ensure you have the correct number of parameters defined and added, then let us know, but i could be missing something else.

answered Jan 07 '16 at 04:34

Byren Higgin

552
4
13

yes, i forgot to add one more parameter. i just added it. but still doesn't work tho – procatmer Jan 07 '16 at 04:46

score 1 · Accepted Answer · edited May 23 '17 at 12:23

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)

data is not inserted to database

2 Answers2