468,170 Members | 2,103 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,170 developers. It's quick & easy.

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


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
Jun 27 '08 #1
3 1268
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" <Br*******@gmail.comwrote in message
news:92**********************************@j22g2000 hsf.googlegroups.com...
>
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

Jun 27 '08 #2
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!
On Jun 5, 3:07 pm, "Scott M." <s...@nospam.nospamwrote:
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" <BradIs...@gmail.comwrote in message

news:92**********************************@j22g2000 hsf.googlegroups.com...
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
Jun 27 '08 #3
On Jun 5, 4:45 pm, "Scott M." <s...@nospam.nospamwrote:
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 foreheadOuch! 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
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.