Why won't this simple SQL statement INSERT a value?

[Blarneystone]

Ok, I've got a simple access 97 db. named S_tracking.mdb

It has two tables
1- Jobs
2- Employees

I've set up the references:
Imports System.Data.OleDb
Imports System.IO
Imports System.data



I am simply trying to insert an employee number and name with the
following code and the code runs, but never puts anything in the
database!
'initializecomponent:
Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=|DataDirectory|\S_tracking.mdb"
'end initializecomponent

cn = OleDbConnection1
cn.Open()

Dim DOrigin, Salesperson, JDescription, DTemplate, DInstall,
InvNum, Notes As String
DOrigin = dt_origin.Text
Dim SQL As String

SQL = "INSERT INTO Employees (EE_Number, Salesperson) VALUES
('5', 'Keith')"
Dim cmd As New System.Data.OleDb.OleDbCommand(SQL, cn)
cmd.CommandText = SQL
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
Throw New Exception(ex.ToString)
End Try


When I use cn.close, to test if the database is even connecting, the
code errors saying must have an open connection. So I have the
connection, the data is going to the right places, but not being
accepted!

PS, my sql read statements are working...

What's wrong with the INSERTs?

Thanks,
Brad

 

[Scott M.]

Using cn.Close to see if the database is even connecting is not correct.
You can call .close() on a connection in .NET that is not even open without
throwing an error.

Try this cleaned up code (as yours is excessive):

Using con As New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\S_tracking.mdb")
Dim num, value As String
num = "5"
value = "Keith"
Dim sqlString As String = "INSERT INTO Employees (EE_Number, Salesperson)
VALUES (" & num & "," & value & " )"
Dim cmd As New OleDBCommand(sqlString, con)
con.open()
cmd.ExecuteNonQuery()
End Using

Also, you should really be using command parameters, rather than injecting
values directly into the SQL string, but for the purposes of testing, it's
fine.

-Scott

 

[Blarneystone]

Thanks Scott,

I assume by command parameters you mean the variables like what you
have?
num = "5"
value = "Keith"

I do use variables, but I wanted to make it simple to eliminate all
potential problems. But if you mean something else, please forward a
link or something (I always want to improve.)

Try this cleaned up code (as yours is excessive):

oops I meant to cut out the variables I wasn't using.

You know what the problem ended up being? I tried your code and it
was behaving the same way as mine, (worked well, but no change.) The
problem was to do with the VS2005 built connection string DataSource=|
DataDirectory|\S_tracking.mdb"

I don't know what it was connecting to, but when I changed from |
DataDirectory| to .\S_tracking.mdb, it connected to the db in the
build directory.

I'm off to code again...
THANKS!

 

[Scott M.]

Thanks Scott,

I assume by command parameters you mean the variables like what you
have?
num = "5"
value = "Keith"

I do use variables, but I wanted to make it simple to eliminate all
potential problems. But if you mean something else, please forward a
link or something (I always want to improve.)

No. A command object has a Parameters collection of OleDBParameter objects.
After creating and adding a parameter to the parameters collection, you can
use the parameter in your SQL statements preceeded by a ?. The parameters
must be added to the colleciton in the order that they will appear in your
SQL Statement.

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx

oops I meant to cut out the variables I wasn't using.

You know what the problem ended up being? I tried your code and it
was behaving the same way as mine, (worked well, but no change.) The
problem was to do with the VS2005 built connection string DataSource=|
DataDirectory|\S_tracking.mdb"

I don't know what it was connecting to, but when I changed from |
DataDirectory| to .\S_tracking.mdb, it connected to the db in the
build directory.

I thought that you put |DataDirectory| in there because you didn't want to
post your actual path. Yes, of course your connection string must contain a
valid path to your database.

Good luck,

Scott

 

[Blarneystone]

I thought that you put |DataDirectory| in there because you didn't want to
post your actual path. Yes, of course your connection string must contain a
valid path to your database.

<Smacks forehead> Ouch! I'm new to creating new databases in VS2005
- I figured that since DataDirectory was put in there by the
designer, it knew what it was doing and put the path there
automatically since I included the db in the project.

Anyway...it's working now. I'm happy about that.

I'll study up on command objects