how to insert sql statement in array (vb 2005)?

Posted 22 June 2009 - 01:46 AM

hi guys, im not sure how to explain my problem and what are the ways to solve it but i will try my best to explain what i'm trying to do here

- in my database, there's 2 table....Log, Application

Attached Image

- im trying to construct a sentence for my log, something like..

(eg: Apply Annual Leave from Monday to Friday)

-words in bold are to be retrieved from the Application table (database)

here's my vb code (im not sure whether i should be using array or not...and im not so sure how to use it too )

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click


con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; data source= H:\Project\user.mdb"
con.Open()
sql = "SELECT * From Log"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "LogList")
con.Close()
maxrows = ds.Tables("LogList").Rows.Count

Dim sqlstr1 As String
Dim sqlstr2 As String
Dim sqlstr3 As String

sqlstr1 = "select Type from Application where ID ='" & txtcancel.Text & "'"
sqlstr2 = "select Dayfrom from Application where ID ='" & txtcancel.Text & "'"
sqlstr3 = "select Dayto from Application where ID ='" & txtcancel.Text & "'"


Try
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("LogList").NewRow()


dsNewRow.Item("Sentence") = "Apply " & sqlstr1 & "from" & sqlstr2 & "to" & sqlstr3


ds.Tables("LogList").Rows.Add(dsNewRow)
da.Update(ds, "LogList")
MessageBox.Show("Data insert into log", "Confirm")


End If
Catch ex As Exception
MessageBox.Show("Error in one of the fields", "Error")

End Try

End Sub

but when i view my Log..something like this appear

Attached Image

-how do i let the array know that the statement is an sql statement, not text??

-or is there any other way to do it??

-can someone provide me a sample code or something??

thanks