Insert into Database using OleDbConnection

Question

I am almost finished with my code but never inserted data into the database before. So far my SQL statement works fine but the final execution of the code I don't know how to do.

The new record doesn't show up in my database so I think I am missing a few lines at the end that makes the new record execute.

Protected Sub Insert_Click(sender As Object, e As EventArgs) Handles Insert.Click

    Dim v_Date = DateText.Text
    Dim v_Username = UserText.Text
    Dim v_Phone = PhoneText.Text
    Dim v_Email = EmailText.Text
    Dim v_Category = CategoryList.Text
    Dim v_Short = ShortText.Text
    Dim v_Long = longText.Value

    Dim conn As OleDbConnection = New OleDbConnection("Provider=""*********"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)

    Dim ClassifiedStr As OleDbCommand = New OleDbCommand("INSERT INTO TABLENAME (Date, Username, Phonenbr, Email, Category, Description, Fulldescription) values('" & v_Date & "',lower('" & v_Username & "'),'" & v_Phone & "','" & v_Email & "','" & v_Category & "','" & v_Short & "','" & v_Long & "'", conn)
    'ClassifiedStr.CommandType = CommandType.StoredProcedure
    Dim OracleDataAdapterAds As OleDbDataAdapter = New OleDbDataAdapter
    OracleDataAdapterAds.SelectCommand = ClassifiedStr
    Dim DsAds As DataSet = New DataSet
    DsAds.Clear()
    'OracleDataAdapterAds.Fill(DsAds, "TABLENAME")
    conn.Open()
    'ClassifiedStr.ExecuteNonQuery()


End Sub

UPDATE

ERROR

    Dim v_Date = DateText.Text
    Dim v_Username = UserText.Text
    Dim v_Phone = PhoneText.Text
    Dim v_Email = EmailText.Text
    Dim v_Category = CategoryList.Text
    Dim v_Short = ShortText.Text
    Dim v_Long = longText.Value

    Dim conn As OleDbConnection = New OleDbConnection("Provider=""********"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)
    conn.Open()

    Dim ClassifiedStr As OleDbCommand = New OleDbCommand("INSERT INTO TABLENAME (Date, Username, Phonenbr, Email, Category, Description, Fulldescription) values(@v_Date, lower(@v_Username), @v_Phone, @v_Email, @v_Category, @v_Short, @v_Long)", conn)
    ClassifiedStr.Parameters.Add("@v_Date", OleDbType.Date).Value = v_Date
    ClassifiedStr.Parameters.Add("@v_Username", OleDbType.VarChar).Value = v_Username
    ClassifiedStr.Parameters.Add("@v_Phone", OleDbType.VarChar).Value = v_Phone
    ClassifiedStr.Parameters.Add("@v_Email", OleDbType.VarChar).Value = v_Email
    ClassifiedStr.Parameters.Add("@v_Category", OleDbType.VarChar).Value = v_Category
    ClassifiedStr.Parameters.Add("@v_Short", OleDbType.VarChar).Value = v_Short
    ClassifiedStr.Parameters.Add("@v_Long", OleDbType.VarChar).Value = v_Long

    ClassifiedStr.ExecuteNonQuery()
    conn.Close()

Answer 1

For some reason INSERT SQL was giving me constant issues so I decided to go for a stored procedure instead.

With my stored procedure inside Oracle, the following is my VB.net code:

Dim ClassifiedStr As New OleDbCommand

    ClassifiedStr.CommandType = CommandType.StoredProcedure
    ClassifiedStr.CommandText = "Insert_classifieds"
    ClassifiedStr.Connection = conn

    'Must be organized based on Stored Procedure
    ClassifiedStr.Parameters.Add("val_date", OleDbType.Date).Value = DateText.Text
    ClassifiedStr.Parameters.Add("val_category", OleDbType.VarChar, 40).Value = CategoryList.Text
    ClassifiedStr.Parameters.Add("val_user", OleDbType.VarChar, 20).Value = UserText.Text
    ClassifiedStr.Parameters.Add("val_phone", OleDbType.VarChar, 20).Value = PhoneText.Text
    ClassifiedStr.Parameters.Add("val_email", OleDbType.VarChar, 50).Value = EmailText.Text
    ClassifiedStr.Parameters.Add("val_shortDes", OleDbType.VarChar, 100).Value = ShortText.Text
    ClassifiedStr.Parameters.Add("val_longDes", OleDbType.VarChar, 4000).Value = longText.Value
    conn.Open()

    ClassifiedStr.ExecuteNonQuery()
    conn.Close()

Answer 2

You don't need to use a data adaptor, as you're not returning and data back to the client, instead, use ExecuteNonQuery(), so something like (with added parametrisation):

Protected Sub Insert_Click(sender As Object, e As EventArgs) Handles Insert.Click
    Using conn As New OleDbConnection("Provider=""*********"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)
        conn.Open()

        Using ClassifiedStr As New OleDbCommand("INSERT INTO t_classifieds (""Date"", Username, Phonenbr, Email, Category, Description, Fulldescription) values(@v_Date, lower(@v_Username), @v_Phone, @v_Email, @v_Category, @v_Short, @v_Long)", conn)
            ClassifiedStr.Parameters.Add("@v_Date", OleDbType.Date).Value = DateText.Text
            ClassifiedStr.Parameters.Add("@v_Username", OleDbType.VarChar).Value  = UserText.Text
            ClassifiedStr.Parameters.Add("@v_Phone", OleDbType.VarChar).Value = PhoneText.Text
            ClassifiedStr.Parameters.Add("@v_Email", OleDbType.VarChar).Value = EmailText.Text
            ClassifiedStr.Parameters.Add("@v_Category", OleDbType.VarChar).Value = CategoryList.Text
            ClassifiedStr.Parameters.Add("@v_Short", OleDbType.VarChar).Value = ShortText.Text
            ClassifiedStr.Parameters.Add("@v_Long", OleDbType.VarChar).Value = longText.Value

            ClassifiedStr.ExecuteNonQuery()
        End Using
    End Using
End Sub

Answer 3

Protected Sub Insert_Click(sender As Object, e As EventArgs) Handles Insert.Click

    Dim v_Date = DateText.Text
    Dim v_Username = UserText.Text
    Dim v_Phone = PhoneText.Text
    Dim v_Email = EmailText.Text
    Dim v_Category = CategoryList.Text
    Dim v_Short = ShortText.Text
    Dim v_Long = longText.Value

    Dim conn As OleDbConnection = New OleDbConnection("Provider=""*********"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)
    conn.Open();
    Dim ClassifiedStr As OleDbCommand = New OleDbCommand("INSERT INTO t_classifieds ([Date], Username, Phonenbr, Email, Category, Description, Fulldescription) values(@v_Date, lower(@v_Username), @v_Phone, @v_Email, @v_Category, @v_Short, @v_Long)", conn)
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Date", v_Date))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Username", v_Username))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Phone", v_Phone))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Email", v_Email))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Category", v_Category))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Short", v_Short))
    ClassifiedStr.Parameters.Add(new OleDbParameter("@v_Long",v_Long))


    ClassifiedStr.ExecuteNonQuery()
    conn.Close()


End Sub