We help IT Professionals succeed at work.

trying to insert values without success or error message:

538 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

Comment
Watch Question

QPR

Commented:
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
Shahan AyyubSenior Software Engineer
CERTIFIED EXPERT

Commented:

Commented:
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.

Author

Commented:
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?

QPR

Commented:
select max(fieldname)
go
do your inserts
go
select max(fieldname)

Author

Commented:
QPR:

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

Commented:
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
QPR

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

Commented:
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.

Author

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

Author

Commented:
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)
QPR

Commented:
Can u try running your SQL at the database level (management studio) and see if it works?

Commented:
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?)

Author

Commented:
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
QPR

Commented:
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?

Author

Commented:
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.

QPR

Commented:
What dies the generated SQL statement look like?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
dont use the copy always property.

Commented:
>>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?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.