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


"Blarneystone" <BradIsaac@xxxxxxxxx> wrote in message
news:697d9a74-90fd-4c64-b6d6-54d60fc10d8c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


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 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


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


On Jun 5, 3:07 pm, "Scott M." <s...@xxxxxxxxxxxxx> wrote:
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...@xxxxxxxxx> wrote in message

news:92294c29-cd0b-48a3-8621-de3408dd2a73@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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



.

Relevant Pages

  • Re: Syntax
    ... tablename above except that it is an mdb rather than a table in SQL server. ... Thus my connection table is now the mdb file name minus the ".mdb" extension ... the Access mdb connection string should be the variable name as ...
    (microsoft.public.sqlserver.dts)
  • Re: Unable to Locate Server
    ... I am using the copy of SQL Express that came with Visual Studio. ... > Boolean& failoverDemandDone, String host, String failoverPartner, String ... > owningObject, SqlConnectionString connectionOptions, String newPassword, ... >> trying to make a remote database connection while you think your database is ...
    (microsoft.public.sqlserver.setup)
  • Re: Setting a combo Rowsource from SQL server
    ... Since you set the RowSource to the SQL String, ... the Connection you established is not used. ...
    (microsoft.public.access.forms)
  • Re: Why wont this simple SQL statement INSERT a value?
    ... You can call .closeon a connection in .NET that is not even open without ... Dim num, value As String ...
    (microsoft.public.dotnet.languages.vb)
  • Re: 2nd db connection from 12/21
    ... Never used DAO to connect to a Sql Back end but if you can use ADO this ... if the server accepts remote connections on the ... Dim strCon As String, strSql As String ... There is a great resource for Connection strings here ...
    (microsoft.public.access.formscoding)