[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

trying to insert values without success or error message:

Posted on 2010-09-06
20
Medium Priority
?
513 Views
Last Modified: 2012-05-10
Although the database connection is valid and  active,  the code attached does not provide an exception, nor does it succeed, even though affrows shows a value of 1, when I examine the database with database explorer  there are no additional rows.

What might I have failed to do?  

Thanks for anything that might steer me in the right direction...

 
Public Function InsertSQLwithVars(ByVal strsql As String, ByVal vars As ArrayList, ByRef lex As LastException) As Boolean
        Dim SQLCeAdapter As System.Data.SqlServerCe.SqlCeDataAdapter = Nothing
        Dim sparm As SqlServerCe.SqlCeParameter
        Dim retval As Boolean = True
        Dim sqlcomm As New SqlServerCe.SqlCeCommand
        sqlcomm.Connection = _conobj
        sqlcomm.CommandText = strsql
        Dim affrows As Integer = 0
        Try
            SQLCeAdapter = New System.Data.SqlServerCe.SqlCeDataAdapter(strsql, _conobj)
            SQLCeAdapter.InsertCommand = sqlcomm
            For Each sparm In vars
                SQLCeAdapter.InsertCommand.Parameters.Add(sparm)
            Next
            affrows = SQLCeAdapter.InsertCommand.ExecuteNonQuery()


        Catch ex As Exception
            retval = False
            lex.ErrorMessage = ex.Message
            If Not ex.InnerException Is Nothing Then
                lex.ErrorMessage = lex.ErrorMessage & vbCrLf & ex.InnerException.Message
            End If

        Finally
            SQLCeAdapter.Dispose()
        End Try

        Return retval
    End Function

Open in new window

0
Comment
Question by:codefinger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 5
  • +1
20 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 33613583
what is the commandtype? stored procedure?
What is the value of strsql?
Can you put strsql to a label to see what is actually being passed to the database
If all else fails, try removing the try/catch/finally/end try keywords to see if anything is thrown up
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 33613606
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33614913
most probably your transaction is being rolled-back.

you can be sure by testing an AutoIncrement field, and see if the seed changes, if it does then, it has been added, then the addition has been rolled back.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:codefinger
ID: 33615265
integam, I think you may be on to something.  I didn't have nearly as much trouble with the database before I started trying to take advantage of the Auto-increment feature.  But I don't know how to check the AutoIncrement field in code.  Can you help me with that?

0
 
LVL 29

Expert Comment

by:QPR
ID: 33615275
select max(fieldname)
go
do your inserts
go
select max(fieldname)
0
 

Author Comment

by:codefinger
ID: 33615345
QPR:

If the inserts are being rolled back, won't max(fieldname) always return the same value and therefore tell me nothing useful?
0
 
LVL 29

Expert Comment

by:QPR
ID: 33615366
If the transaction was rolled back and the row(s) deleted, you can't reclaim the autonumber assigned.... at least that is how it works for a manual delete (I'm not 100% if a rolled back transaction has the same effect). Truncating a table resets autonumbers but not deletes.

I was answering the Q about how to check it based on intergams post about transactions rolling back but having had the autonumber incremented
0
 
LVL 29

Expert Comment

by:QPR
ID: 33615374
A quick check suggests that rolled back transactions do lose their autonumbers.
The business rule for an identity field is purely to be unique, not necessarily sequential
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33615490
i am positive that the autonumber does NOT reset in a roll back,

so, if you have set autonumbering true to a field, and added a record, it will take id 1, if you roll back, and then inset a new record, the new record will take the id 2.
0
 

Author Comment

by:codefinger
ID: 33617199
Ok, I think either I am not clear (most likely), or you guys are kind of missing the point of my question...which is how to determine in code what the AutoIncrement number was BEFORE the transaction got rolled back.  Here:

Select(max(autoincrement field)) = 5
In code, do insert,
Transaction gets rolled back (by database?)
Select(max(autoincrement field)) = 5

0
 

Author Comment

by:codefinger
ID: 33617253
SQL being passed to database is:

INSERT INTO COMMANDCODES (DeviceCode,VerbalCommand,ControlType,X10Command, ExternalProgram,RemoteCodesString,RemoteCodesBinary,RemoteCodesLength,RemoteCodesFrequency,Description) VALUES (@xDeviceCode,@xVerbalCommand,@xControlType,@xX10Command,@xExternalProgram,@xRemoteCodesString, @xRemoteCodesBinary,@xRemoteCodesLength,@xRemoteCodesFrequency,@xDescription)
0
 
LVL 29

Expert Comment

by:QPR
ID: 33622504
Can u try running your SQL at the database level (management studio) and see if it works?
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33622978
if it is AutoIncrement, you do not need to use the Max function

Instead use the @@SCOPE_IDENTITY constant


INSERT INTO COMMANDCODES (DeviceCode,VerbalCommand,ControlType,X10Command, ExternalProgram,RemoteCodesString,RemoteCodesBinary,RemoteCodesLength,RemoteCodesFrequency,Description) VALUES (@xDeviceCode,@xVerbalCommand,@xControlType,@xX10Command,@xExternalProgram,@xRemoteCodesString, @xRemoteCodesBinary,@xRemoteCodesLength,@xRemoteCodesFrequency,@xDescription)


SELECT @RETURN=SCOPE_IDENTITY

Transaction gets rolled back (by database?)
0
 

Author Comment

by:codefinger
ID: 33643748
In my case, the identity column DOES increment on every insert.

(It took me awhile to figure this out because SQLSERVER CE has some slightly different rules.  It does not support SCOPE_IDENTITY() or Parameter.Direction = Output.)

So it does appear my transaction is being made, then rolled back by the database.  I really wish the database would speak up and tell me WHY
0
 
LVL 29

Expert Comment

by:QPR
ID: 33644144
Do you have a trigger on the table? Any constraints? Have you tried capturing the SQL string prior to execution to see if it looks as expected?
0
 

Author Comment

by:codefinger
ID: 33644494
I would not know how to put a trigger on the database if I wanted to.  Violated constraints and bad SQL have always returned exceptions.

0
 
LVL 29

Expert Comment

by:QPR
ID: 33644523
What dies the generated SQL statement look like?
0
 

Accepted Solution

by:
codefinger earned 0 total points
ID: 33659661
Ok, figured this out, finally.    I had created one connection to the database in the location where I decided to keep it, however after adding the database to my project, when I run in debug mode, as I usually do, the entire database is copied to a different location.

So the database I am actually updating is NOT the one shown in the Database Explorer, it is the one in the bin/Debug directory.   So I would make my updates in my program then look for them in Database Explorer and be puzzled as to why they were not there.

I added a second connection to DataBase explorer to the database in the bin/Debug directory and changed the properties of the DataSource and the DataSet in the project to Copy Always.    Now when I make my updates in my program I can see them in Database Explorer under the second connection.

That just leaves me with one question  --- how are the updates I make in Debug mode supposed to get back to the "original" database file.  As far as I can tell, copy always only goes one direction.  

Thanks for any responses....
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33660796
dont use the copy always property.
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33669602
>>That just leaves me with one question  --- how are the updates I make in Debug mode supposed to get back to the "original" database file.  As far as I can tell, copy always only goes one direction.

this is another question, no?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question