Nothing inserted in database?

Question

I have the following code, it executes without any error and even reports that 1 row was affected. However, nothing is saved. Why not? i can retrieve from the database though.. but cannot write

Dim SQLConn As New SqlConnection() 'The SQL Connection
SQLConn.ConnectionString = connstring 'Set the Connection String
SQLConn.Open()
Dim SQLCmd As New SqlCommand() 'The SQL Command
Dim SQLStr As String = "INSERT into region_table VALUES(10,23,4,'test')"
SQLCmd.CommandText = SQLStr 
sqlCmd.ExecuteNonQuery()
SQLConn.Close()

SOLUTION:: Open right click on database connection in server explorer, Select Properties And use that Connection String! Hopes this solves anyone else's problem too :D

Do you have the same problem I did? http://stackoverflow.com/questions/7223223/sqlce-query-having-no-effect — Ry-, Jan 08 '12 at 18:25
Are you inserting into the right database? You don't have a copy in Master by mistake or anything do you? — MatBailie, Jan 08 '12 at 18:31
What does your table `region_table` look like? Columns, datatypes etc. ??? — marc_s, Jan 08 '12 at 21:03

score 1 · Accepted Answer · answered Jan 08 '12 at 19:34

1

You are not assigning the connection to the command:

After this line:

SQLCmd.CommandText = SQLStr

add this one:

SQLCmd.Connection = SQLConn

You should also use using statements to ensure everything is cleaned up correctly:

    Using SQLConn As New SqlConnection() 'The SQL Connection
        SQLConn.ConnectionString = connstring 'Set the Connection String
        SQLConn.Open()
        Using SQLCmd As New SqlCommand() 'The SQL Command
            Dim SQLStr As String = "INSERT into region_table VALUES(10,23,4,'test')"
            SQLCmd.CommandText = SQLStr
            SQLCmd.Connection = SQLConn
            SQLCmd.ExecuteNonQuery()
            SQLConn.Close()
        End Using
    End Using

answered Jan 08 '12 at 19:34

competent_tech

44,465
11
90
113

Is SQL case-sensitive? Should it be `INSERT INTO`? – Hand-E-Food Jan 08 '12 at 22:03
@user1137385: are there any exceptions thrown? The only way that this wouldn't write anything into the database is if an exception were being thrown. You can add a try/catch statement and/or walk through the code in debug to see where it is breaking. Any exception that is generated should give you a very good clue as to what is happening. – competent_tech Jan 09 '12 at 01:38
it's already in a try catch. i found a similar code in a friend's program though... his code works fine! strange and frustrating! :-/ – Afzal.h Jan 09 '12 at 03:35
Are you sure you're writing to the correct database? If this is full sql server, you can run Sql Profile and see exactly what is getting written and to where. I find it extremely helpful in cases like this. – competent_tech Jan 09 '12 at 03:39
Yeah it was the wrong database! thnx for your help :) wasted 1 day with it. – Afzal.h Jan 09 '12 at 14:21

score 0 · Answer 2 · answered Jan 09 '12 at 17:32

0

Have you tried checking the database path in the connection string? XD

answered Jan 09 '12 at 17:32

nushoo

1

Nothing inserted in database?

2 Answers2