14 Replies - 750 Views - Last Post: 24 April 2015 - 11:23 AM Rate Topic: -----

#1 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

sql exec return code 1

Posted 23 April 2015 - 07:18 PM

The code below does the inserts and updates as expected but returns a code of 1, and is driving me crazy beacuase I do not see the reason why plus stops the rest of the application since I check the RetCode before continuing to the other processes associated with the record, I'm new a vb.net but an old DB2/mainframe programmer. Can anyone help?

        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If

        conn.Open()

        RetCode = 0

        Dim sqlStatement As String = _
            "USE DeFORDDb" & vbCrLf & _
            "IF NOT EXISTS (SELECT DefId FROM DefInfo WHERE DefId = '" & DefKeyId & "')" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "       INSERT INTO DefInfo(DefId, DefName, DefRace, DefSex, DefDob, DefDLic, DefPas)" & _
            "       VALUES ('" & DefKeyId & "', '" & InDefName & "', '" & InDefRace & "', '" & InDefSex & "'," & _
            "               '" & InDefDob & "', '" & InDefDLic & "', '" & InDefPas & "')" & vbCrLf & _
            "   END " & vbCrLf & _
            "ELSE" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "         UPDATE DefInfo " & vbCrLf & _
            "            SET DefDLic = '" & InDefDLic & "'," & _
            "                DefPas = '" & InDefPas & "'" & _
            "       WHERE DefId = '" & DefKeyId & "'" & _
            "   END "

        cmd = New SqlCommand(sqlStatement, conn)

        Try
            RetCode = cmd.ExecuteNonQuery()
            'reader = cmd.ExecuteReader()
        Catch ae As SqlException
            Console.WriteLine(ae.Message.ToString())
        End Try

        Console.WriteLine(" Insert/Update DefInfo ErrorCode = " & CStr(RetCode))

        conn.Close()

This post has been edited by andrewsw: 23 April 2015 - 07:34 PM
Reason for edit:: Added [code][/code] tags


Is This A Good Question/Topic? 0
  • +

Replies To: sql exec return code 1

#2 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: sql exec return code 1

Posted 23 April 2015 - 07:25 PM

You need to learn how to pass parameters to the command object to avoid SQL injection.

cmd.Parameters.AddWithValue("@id", DefKeyId)
'... continue with other values



P.S. please use the code button to insert your code.
Was This Post Helpful? 0
  • +
  • -

#3 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

Re: sql exec return code 1

Posted 23 April 2015 - 07:29 PM

I tried but it was giving me all kinds of errors and not compiling at all
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6448
  • View blog
  • Posts: 26,084
  • Joined: 12-December 12

Re: sql exec return code 1

Posted 23 April 2015 - 07:35 PM

How to use code tags:

Attached Image
Was This Post Helpful? 0
  • +
  • -

#5 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: sql exec return code 1

Posted 23 April 2015 - 07:37 PM

Then show what you tried. Don't resort to bad code practices. We don't mind helping. Tell what the error are when you get them. Always show your code as it changes.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6448
  • View blog
  • Posts: 26,084
  • Joined: 12-December 12

Re: sql exec return code 1

Posted 23 April 2015 - 07:38 PM

View PostM2ses, on 24 April 2015 - 02:29 AM, said:

I tried but it was giving me all kinds of errors and not compiling at all

That is not a good reason to give up on a secure feature.

ExecuteNonQuery returns the number of rows affected, what value are you expecting it to return?

This post has been edited by andrewsw: 23 April 2015 - 07:42 PM

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6448
  • View blog
  • Posts: 26,084
  • Joined: 12-December 12

Re: sql exec return code 1

Posted 23 April 2015 - 07:46 PM

If you were executing a stored procedure then this can return a value, such as an error code, perhaps this is what you were thinking of?
Dim retValue As SqlParameter = cmd.Parameters.Add("return", SqlDbType.Int)
retValue.Direction = ParameterDirection.ReturnValue

Was This Post Helpful? 0
  • +
  • -

#8 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

Re: sql exec return code 1

Posted 24 April 2015 - 06:35 AM

Im getting the return value on RetCode find is just giving me a 1 eventhough the columns are either inserted or added.

I tried the cmd.parameter.addwithvalue suggestion and I'm now getting Must declare the scalar variable "@Iitem1". no matter how I code it see below

"       VALUES (@Iitem1, @Iitem2, @Iitem3, @Iitem4, @Iitem5, @Iitem6, @Iitem7)" & _
             "               cmd.Parameters.AddWithValue(@Iitem1, DefKeyId), " & _
                                               OR
             "               cmd.Parameters.AddWithValue('@Iitem1', DefKeyId), " & _
                                               OR
             "               cmd.Parameters.AddWithValue('@Iitem', SqlDbType.NVarChar).Value = DefKeyId, " & _


Any ideas

This post has been edited by andrewsw: 24 April 2015 - 06:40 AM
Reason for edit:: fixed botched code tags

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6448
  • View blog
  • Posts: 26,084
  • Joined: 12-December 12

Re: sql exec return code 1

Posted 24 April 2015 - 06:44 AM

That is definitely not how you use AddWithValue. See the example at the docs:

SqlParameterCollection.AddWithValue Method
Was This Post Helpful? 0
  • +
  • -

#10 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

Re: sql exec return code 1

Posted 24 April 2015 - 07:26 AM

View Post_HAWK_, on 23 April 2015 - 07:25 PM, said:

You need to learn how to pass parameters to the command object to avoid SQL injection.

cmd.Parameters.AddWithValue("@id", DefKeyId)
'... continue with other values



P.S. please use the code button to insert your code.


_HAWK_ Now that I have added the parameters as you suggested I'm getting Incorrect syntax near "cmd", I don't see why, maybe you can and can pin-point where the mistake is, here is the code below

Dim sqlStatement As String = _
            "USE DeFORDDb" & vbCrLf & _
            "IF NOT EXISTS (SELECT DefId FROM DefInfo WHERE DefId = '" & DefKeyId & "')" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "       INSERT INTO DefInfo(DefId, DefName, DefRace, DefSex, DefDob, DefDLic, DefSSN)" & _
            "       VALUES ('@Iitem1', '@Iitem2', '@Iitem3', '@Iitem4', '@Iitem5', '@Iitem6', '@Iitem7')" & _
            "               cmd.Parameters.AddWithValue('@Iitem1', DefKeyId)," & _
            "               cmd.Parameters.AddWithValue('@Iitem2', InDefName)," & _
            "               cmd.Parameters.AddWithValue('@Iitem3', InDefRace)," & _
            "               cmd.Parameters.AddWithValue('@Iitem4', InDefSex)," & _
            "               cmd.Parameters.AddWithValue('@Iitem5', InDefDob)," & _
            "               cmd.Parameters.AddWithValue('@Iitem6', InDefDLic)," & _
            "               cmd.Parameters.AddWithValue('@Iitem7', InDefSSN) " & _
            "   END " & vbCrLf & _
            "ELSE" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "         UPDATE DefInfo" & vbCrLf & _
            "            SET DefDLic = '@Uitem1'," & _
            "                DefSSN = '@Uitem2'" & _
            "       WHERE DefId = '@Kitem'" & _
            "               cmd.Parameters.AddWithValue('@Kitem', SqlDbType.NVarChar).Value = DefKeyId," & _
            "               cmd.Parameters.AddWithValue('@Uitem1', InDefDLic)," & _
            "               cmd.Parameters.AddWithValue('@Uitem2', InDefSSN)" & _
            "   END "

Was This Post Helpful? 0
  • +
  • -

#11 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: sql exec return code 1

Posted 24 April 2015 - 08:25 AM

That is not part of the string. They are separate. I gave you a link so you would have an example.

Dim sql = "INSERT INTO DefInfo(DefId, DefName, DefRace, DefSex, DefDob, DefDLic, DefSSN) VALUES (@Iitem1, @Iitem2, @Iitem3, @Iitem4, @Iitem5, @Iitem6, @Iitem7)"
cmd.Parameters.AddWithValue("@Iitem1", DefKeyId)
' continue other fields


Was This Post Helpful? 0
  • +
  • -

#12 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

Re: sql exec return code 1

Posted 24 April 2015 - 09:50 AM

ok guys I modified the code to correctly apply the Parameters but I'm still getting a return code of 1, I guess SQL Injection is not my problem, I also just notice that the Database and table create is returning a code of -1. Don't know what's up, any ideas?

Here is the new code for the insert & update:

        Dim sqlStatement As String = _
            "USE DeFORDDb" & vbCrLf & _
            "IF NOT EXISTS (SELECT DefId FROM DefInfo WHERE DefId = @Kitem)" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "       INSERT INTO DefInfo(DefId, DefName, DefRace, DefSex, DefDob, DefDLic, DefPas)" & _
            "       VALUES (@Iitem1, @Iitem2, @Iitem3, @Iitem4, @Iitem5, @Iitem6, @Iitem7)" & _
            "   END " & vbCrLf & _
            "ELSE" & vbCrLf & _
            "   BEGIN " & vbCrLf & _
            "         UPDATE DefInfo" & vbCrLf & _
            "            SET DefDLic = @Uitem1," & _
            "                DefPas = @Uitem2" & _
            "       WHERE DefId = @Kitem" & _
            "   END "

        cmd = New SqlCommand(sqlStatement, conn)

        cmd.Parameters.Add("@Kitem", SqlDbType.NVarChar)
        cmd.Parameters("@Kitem").Value = DefKeyId
        cmd.Parameters.AddWithValue("@Iitem1", DefKeyId)
        cmd.Parameters.AddWithValue("@Iitem2", InDefName)
        cmd.Parameters.AddWithValue("@Iitem3", InDefRace)
        cmd.Parameters.AddWithValue("@Iitem4", InDefSex)
        cmd.Parameters.AddWithValue("@Iitem5", InDefDob)
        cmd.Parameters.AddWithValue("@Iitem6", InDefDLic)
        cmd.Parameters.AddWithValue("@Iitem7", InDefSSN)
        cmd.Parameters.AddWithValue("@Uitem1", InDefDLic)
        cmd.Parameters.AddWithValue("@Uitem2", InDefPas)

        Try
            RetCode = cmd.ExecuteNonQuery()
            'reader = cmd.ExecuteReader()
        Catch ae As SqlException
            Console.WriteLine(ae.Message.ToString())
        End Try

Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • blow up my boots
  • member icon

Reputation: 6448
  • View blog
  • Posts: 26,084
  • Joined: 12-December 12

Re: sql exec return code 1

Posted 24 April 2015 - 10:00 AM

You are either inserting or updating a single row, which is why ExecuteNonQuery returns 1, as I explained earlier.

Concerning a return value of -1, the page I already linked to explains this:

Quote

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Was This Post Helpful? 0
  • +
  • -

#14 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: sql exec return code 1

Posted 24 April 2015 - 10:07 AM

Of course SQL injection is still a concern - or we would not be telling you to use Parameters.
Was This Post Helpful? 0
  • +
  • -

#15 M2ses  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 23-April 15

Re: sql exec return code 1

Posted 24 April 2015 - 11:23 AM

View Postandrewsw, on 24 April 2015 - 10:00 AM, said:

You are either inserting or updating a single row, which is why ExecuteNonQuery returns 1, as I explained earlier.

Concerning a return value of -1, the page I already linked to explains this:

Quote

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.


Thanks for the info and the links, coming from the mainframe DB2 area I expected the return value to be 0 whenever the process executes successfully, I guess I have some homework to do. I really appreciate your help.

View Post_HAWK_, on 24 April 2015 - 10:07 AM, said:

Of course SQL injection is still a concern - or we would not be telling you to use Parameters.


I know is important, thanks for your guidance and help, I really appreciate it
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1