Newb VB.NET/Access Q: INSERT INTO...

chakotha

I am just getting VB.NET and Access talking for the first time and I cannot get this INSERT SQL code to work:
[PHP]Public Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\myDB.mdb"

Dim strSQL As String = "INSERT INTO Companies(CompanyName) VALUES('" & txtNewCompany & "')"

' create Objects of ADOConnection and ADOCommand
Dim myConnectionString As New OleDbConnection(strDSN) '
Dim myQuery As New OleDbCommand(strSQL, myConnectionString)

Try
myConnectionString.Open()
myQuery.ExecuteNonQuery()
Catch myError As Exception
MsgBox("Uh oh! Something wrong!")
Finally
myConnectionString.Close()
End Try

End Sub
[/PHP]
This is causing build error

C:\MyPrograms\ConnectAccess\Form1.vb(153): Operator '&' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.

I have a text entry in Form1 called txtNewCompany and if I try

[PHP]Dim strSQL As String = "INSERT INTO Companies(CompanyName) VALUES('txtNewCompany')"
[PHP]

the actual string 'txtNewCompany' goes into the table, not the value

Any help much appreciated

aidan_walsh

I think you have to use the .Text function (not VB.NET savvy, me), otherwise VB thinks you are trying to pass an object reference to the SQL statement...

chakotha

Thanks that did it!