ExecuteNonQuery - problem

  • Thread starter Thread starter b_r
  • Start date Start date
B

b_r

Hi,

I'm trying to make a simple operation (insert into DB) in VB 2005 and
SQL Server.

The code is as follows:

Dim sConnectionString As String = _
"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB.mdf....

Dim cnDB As New SqlConnection(sConnectionString)
cnDB.Open()
Dim strSQL As String
strSQL = "INSERT INTO Company (CompanyName) VALUES ('four')"
Dim command As SqlCommand = New SqlCommand(strSQL, cnDB)
command.ExecuteNonQuery()
cnDB.Close()

But the row is not inserted into DB and also there is no any error.

Initialy I wanted to use stored procedure but in order to make it
working I made the simplest operation - without success...

Do you have any idea what is wrong?

Regards
BR
 
Dubug using this construct and see what the value of ex.message tells you

Try

'//Your Code

Catch ex as Exception '//breakpoint here



End try
 
'// Is this correct ? should it not be just CompanyName ?
Company (CompanyName)

strSQL = "INSERT INTO Company (CompanyName) VALUES ('four')"
 
Try this, and see what it says about the rows updated:

Dim RecordsAffected As Integer = command.ExecuteNonQuery()
Debug.WriteLine("Query Affected {0} row(s).", RecordsAffected)

Does it say 0?

If so, can you open SQLServer and try the query there?

Do you have write access to the table?

Does the table have a different field that is the primary
key and you're not setting the value for that?

Is the record you're trying to add already there?

Robin S.
 
Thank you for suggestions.

RecordsAffected value is equal to 1 - so it is correct value ... but new
row doesn't appear in the table

Query in SQLServer works fine...


RobinS napisa³(a):
 
Put in a breakpoint at the Debug.WriteLine statement. Run it up until
that
point, and then go look in your database and see if the record is there.

If it's not there, open your immediate window and check your connection
string and make sure you're really connecting to the right database.

Debug.Print sConnectionString

If you are connecting to the right database, try reading the record back
right after you write it, ***using the same connection***.

strSQL = "SELECT CompanyName FROM Company WHERE CompanyName = 'four' "
Dim command2 As SqlCommand = New SqlCommand(strSQL, cnDB)
Dim da As New SqlDataAdapter()
da.SelectCommand = command2
Dim dt as New DataTable()
da.fill(dt)
For Each dr As DataRow in dt.rows
Debug.print "Found CompanyName: " & dt.rows("CompanyName")
Next dr
cnDB.Close()

If that doesn't work, and the table isn't huge, try writing the whole
table and
see if it looks right.

The only thing I can think of is that you are not pointing to the same
database
you think you are, especially since you get a rowcount of 1 back when
you
execute the insert query. If there was any problem on the SQLServer
side,
it would return 0. That's my opinion, anyway.

Robin S.
--------------------------------------
 
Database is correct - for sure...

When I want to verify inserted row I got the following information:

A first chance exception of type 'System.InvalidCastException' occurred
in Microsoft.VisualBasic.dll



RobinS napisa³(a):
 
b_r said:
Database is correct - for sure...

When I want to verify inserted row I got the following information:

A first chance exception of type 'System.InvalidCastException' occurred
in Microsoft.VisualBasic.dll
Click to expand...
<snip>

I guess Robin actually meant:

<snip>

HTH.

Regards,

Branco.
 
I've made some additional tests and the results are as follows:
- the record is inserted but somwhere in the memory ??? - it is not
visible in table
- before closing connection to database I can see this record (via
dataadapter and data set) - Select * From Company
- afer closing connection of course recordset disappears

It looks that this record is not finaly transited to database...but why ???


Branco Medeiros napisa?(a):
 
You're not using any kind of transactions, are you?

So the problem is that you have an insert statement
that you execute, and if you turn back around and
do a select before closing the connection, the data
is there. But after you close your connection, the
data disappears, right?

What if you then re-open the connection and once
again read for the data in your program? Does it
find it then?

Robin S.
----------------------------
 
RobinS napisa³(a):
You're not using any kind of transactions, are you?
Click to expand...

- i'm not using any transaction
So the problem is that you have an insert statement
that you execute, and if you turn back around and
do a select before closing the connection, the data
is there. But after you close your connection, the
data disappears, right?
Click to expand...

- yes, it is just as you wrote
What if you then re-open the connection and once
again read for the data in your program? Does it
find it then?
Click to expand...

- after re-open the connection, there is no expected record
 
BR,

This errors comes often in these newsgroup. There is than no key or a wrong
key declared in your database. The way you do it now, assumes that you have
used an autoidentifier key.

However I think that you have not declared any key in your database or that
company is the key.
(If it is no identifier, than you have to give the key and its value in the
Insert Statement)

I hope this helps,

Cor
 
I asked him a few posts ago if he was filling a primary key and he
didn't answer, so this is a good question to ask.

So does your table have a primary key defined? What field is it?
Are you filling it with your Insert statement?

Robin S.
 
Yes, of course there is primary key.
Initialy there was only one field "CompanyName" which was also set as
primary key - in order to simplify databse as much as possible.

I made a lof of tests and now I have table:
Column Name DataType
ID int
CompanyName nvarchar(50)

where
Columns - ID (ASC)
Is Unique - Yes
Type - Primary Key

and the SQL is "INSERT INTO Company(ID, CompanyName) VALUES (5,'four')"

Everything looks ok but record is not inserted to database. As I wrote
before there is something strange because before closing connection to
DB I can see this record (select *...) from VB level but directly in
database it doesn't exist.



RobinS napisa³(a):
 
I note that in your connection you are using the |DataDirectory| macro.

Are you absolutely that the expansion of the |DataDirectory| macro results
in what you think you should.

You can find out by including a line at a point where the database is
'open':

Console.WriteLine(AppDomain.CurrentDomain.GetData("DataDirectory"))

What it resolves to will suspend on how the application is deployed.

I strongly suspect is that the result of this test will indicate thet the
database being updated is different to what you think.
 
That's what I thought originally. There's just no way you could
write it, be able to read it back, and then it's not there.
So either he's writing to a different database, or he's somehow
doing transactions, and he's doing a rollback instead of a
commit (he said he is not doing transactions).

But I asked him to *close* the connection and *reopen* it and
read the data back, and he says he did that and it's not there.
Assuming he used the same connection string and everything,
it would open the same database it wrote the record to,
and should find the record. Right?

Unless he didn't really do what I recommended, and just said
he did because he didn't believe it would tell him anything.
*GASP* Surely not.

Robin S.
--------------------------------------
 
I used the following statement:
Debug.Print(AppDomain.CurrentDomain.GetData("DataDirectory")) - but it
shows nothing...???

In order to verify if I connect to right database I modified Column Name
in INSERT statement (different than in database) and of course error
appeared - maybe the way is not so "beautiful" but it gives results.

We are still talking about connectivity to wrong database what in my
opinion is correct.

Thank you guys for help but there is no sense to continue this topic -
as we can see even if problem seems simple - the solution isn't...

Regards
BR

RobinS napisa³(a):
 
If AppDomain.CurrentDomain.GetData("DataDirectory") does not retuen anything
then that means that it is not set.

In this case, the 'connection' will be using the DB.mdf that is in the same
directory as the executable you are running.

Is this the database that you expect it to be using?
 
Back
Top