Results 1 to 16 of 16

Thread: [RESOLVED] SQL Code Executing without Error, Not Writing

  1. #1

    Thread Starter
    Lively Member claws135's Avatar
    Join Date
    Oct 2012
    Posts
    106

    Resolved [RESOLVED] SQL Code Executing without Error, Not Writing

    I am using a SQL Code to write to the Database based on Data In the DataGrid. It is pulling the Data From the DataGrid Correctly, but Not writing to the DataBase. Here is the code
    Code:
       Dim MAXLine1Statement As String = "SELECT MAX(LineNumber) FROM ProductionLines WHERE ProductionKey = @ProductionKey"
    Dim MAXLine1Command As New SqlCommand(MAXLine1Statement, con)
    MAXLine1Command.Parameters.Add("@ProductionKey", SqlDbType.VarChar).Value = Val(cboProductionKey.Text)
     If con.State = ConnectionState.Closed Then con.Open()
      LastLineNumber = CInt(MAXLine1Command.ExecuteScalar)
      con.Close()
      NextLineNumber = LastLineNumber + 1
         
      cmd = New SqlCommand("Insert Into ProductionLines(ProductionKey, LineNumber, Part, PartDescription, Quantity, Cost, ExtendedCost, LineWeight) Values(@ProductionKey, @LineNumber, @Part, @PartDescription, @Quantity, @Cost, @ExtendedCost, @LineWeight)", con)
            With cmd.Parameters
                      .Add("@ProductionKey", SqlDbType.VarChar).Value = Val(cboProductionKey.Text)
                      .Add("@LineNumber", SqlDbType.VarChar).Value = NextLineNumber
                      .Add("@Part", SqlDbType.VarChar).Value = TapeType
                      .Add("@PartDescription", SqlDbType.VarChar).Value = "TAPE"
                      .Add("@Quantity", SqlDbType.VarChar).Value = LineQuantity * -1
                      .Add("@Cost", SqlDbType.VarChar).Value = TapeCost
                      .Add("@ExtendedCost", SqlDbType.VarChar).Value = LineQuantity * TapeCost * -1
                      .Add("@LineWeight", SqlDbType.VarChar).Value = LineWeight
             End With
        If con.State = ConnectionState.Closed Then con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    I have message boxes telling me the values, and they are the correct values for the DataType and for the DataBase.
    Thanks for the Help.
    Last edited by claws135; Feb 20th, 2013 at 10:31 PM. Reason: Cleaning up messy code. sorry was in a hurry when posting.
    Some Ponies just want to watch the cereal burn.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: SQL Code Executing without Error, Not Writing

    I was thinking this was the same question you asked over in CC, but I guess it isn't as that one had a WHERE clause, right? One thing to look at is the return from ExecuteNonQuery. You may not realize that it returns anything, but it returns the number of records affected. Therefore, if it returns 0, then nothing happend, but if it returns 1, then it really did insert a row. At that point, you'd just have to figure out why you can't see the row, which is a different problem entirely.

    However, I think I may see an additional error. You add a parameter for the SELECT statement, then add a whole bunch of new parameters when you get to the INSERT statement. The Parameters collection is just like any other collection. If you don't clear it out, it will keep on holding whatever you put in there. It sure doesn't look like you still want that parameter from the SELECT statement to still be there when you add the parameters for the INSERT statement, so you probably want to call .Clear on parameters before you do the INSERT.

    One other question I have is why you close the connection when you know you are about to open it again.
    My usual boring signature: Nothing

  3. #3
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: SQL Code Executing without Error, Not Writing

    However, I think I may see an additional error.
    Lost your glasses again? Two different commands used (and both New'ed anyway). If there were too many parameters that would raise an error.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: SQL Code Executing without Error, Not Writing

    Yup, lost the glasses.

    Actually, I was seeing what I expected to see. I always call Command objects cmd, too, so the code looked familiar. However, I always use just one and change the CommandText. Therefore, I didn't even look to see that he was doing it differently.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Lively Member claws135's Avatar
    Join Date
    Oct 2012
    Posts
    106

    Re: SQL Code Executing without Error, Not Writing

    @Shaggy
    Yes it is the same... sort of. I fixed the first problem, and still had an issue, so it became thread worthey.
    @Dunn
    It is finding the Largest Line Number with the First command, adding 1 underneath that, then writing a line with the seccond command.
    Some Ponies just want to watch the cereal burn.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Code Executing without Error, Not Writing

    As it clearly explains in the MSDN documentation that you should have read already, when you call ExecuteNonQuery there are only three possible outcomes:

    1. The call succeeds and returns zero, meaning that no records were affected.
    2. The call succeeds and retunrs a non-zero value, meaning that that number of records were affected.
    3. The call fails and an exception is thrown.

    Which is it in your case? I would expect that you'd know if an exception was being thrown but maybe you have a Catch block that simply swallows such errors.

  7. #7

    Thread Starter
    Lively Member claws135's Avatar
    Join Date
    Oct 2012
    Posts
    106

    Re: SQL Code Executing without Error, Not Writing

    @Jm
    i thought the same, so i removed the catch block, but it still isint writing any values. So i put in message boxes, telling me the values of the Variables directly before the Second command executed, and everything was being pulled properly.
    Some Ponies just want to watch the cereal burn.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Code Executing without Error, Not Writing

    Shaggy and I have both told you what to do and you have apparently ignored us both. It's a waste of my time to have to repeat myself so I won't be doing it again. Answer the question from my previous post.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: SQL Code Executing without Error, Not Writing

    I never like using messageboxes to look at values like this. For one thing, it seems like a whole lot of typing that will ultimately be wasted. For another, it just seems to break up the flow of things. Finally, it is very limiting. When you use a messagebox, you can see the value of ONE thing, unless you write the message to show you a whole bunch of things, and that is even more tedious even if you choose the right things to show. A breakpoint would be a much better solution, because you can look at ALL the variables, even the ones you forgot to include, and you can step forward through the code, or even highlight some future line and see what it returns (though I wouldn't try that with ExecuteNonQuery, as I'm not sure that wouldn't have a bad side effect).
    My usual boring signature: Nothing

  10. #10
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: SQL Code Executing without Error, Not Writing

    Just a bit curious, but is there any value in specifying sql datatypes like that? I mean, you can do:

    .AddWithValue("@Part", TapeType)

    why bother with:

    .Add("@Part", SqlDbType.VarChar).Value = TapeType

    Seems like something else that can go wrong.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  11. #11
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: SQL Code Executing without Error, Not Writing

    Quote Originally Posted by jmcilhinney View Post
    Shaggy and I have both told you what to do and you have apparently ignored us both. It's a waste of my time to have to repeat myself so I won't be doing it again. Answer the question from my previous post.
    i.e. do a Dim intResult as int = cmd.ExecuteNonQuery() and see if the value of intResult is 0 or 1. That will tell you a lot about what is happening.
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: SQL Code Executing without Error, Not Writing

    I believe that Add was the original option, and AddWithValue was added in a later framework. Therefore, I think Add is kind of like ArrayList: It's still there, lots of people see examples of it and use it, but it isn't the best option available anymore. Of course, once written, it is generally easier to leave it than change it.
    My usual boring signature: Nothing

  13. #13

    Thread Starter
    Lively Member claws135's Avatar
    Join Date
    Oct 2012
    Posts
    106

    Re: SQL Code Executing without Error, Not Writing

    Thanks for your patience guys. I found the Problem. I used the message box to get te integer result, and it was pulling a 1. So i decided to look further down the line, and i used NEXTLINENUMBER again without refinding it or adding a one. So the catch for that was updating the Table, writing over the Instance and i didnt know it was happening.
    Some Ponies just want to watch the cereal burn.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Code Executing without Error, Not Writing

    Quote Originally Posted by claws135 View Post
    Thanks for your patience guys. I found the Problem. I used the message box to get te integer result, and it was pulling a 1. So i decided to look further down the line, and i used NEXTLINENUMBER again without refinding it or adding a one. So the catch for that was updating the Table, writing over the Instance and i didnt know it was happening.
    So that means that Shaggy Hiker gave you the solution in the very first reply but you just ignored it. In future, please read all information provided carefully and especially answer questions when people ask them. We can only go by what you tell us so if you don't give us the relevant information, especially when we ask for it, then it's a big waste of your time and ours.

  15. #15

    Thread Starter
    Lively Member claws135's Avatar
    Join Date
    Oct 2012
    Posts
    106

    Re: SQL Code Executing without Error, Not Writing

    Quote Originally Posted by jmcilhinney View Post
    So that means that Shaggy Hiker gave you the solution in the very first reply but you just ignored it. In future, please read all information provided carefully and especially answer questions when people ask them. We can only go by what you tell us so if you don't give us the relevant information, especially when we ask for it, then it's a big waste of your time and ours.
    Sort Of. Yes and no. He did point out that i wasn't dumping the variables from .ExecuteNonQuery, But it was because i overwrote the Line later in the program. And also, I am sorry that i couldn't answer your questions accurately, its just that i work 9 to 5, and could not get back to the program, and i don't have any copies at home.
    Some Ponies just want to watch the cereal burn.

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: [RESOLVED] SQL Code Executing without Error, Not Writing

    For future reference, if someone asks a question and you're unable to answer it right away, post back and say exactly that. We only know what you tell us. If we ask a question and you then post again and make no reference to that question then it looks to us like you've ignored. If you take a few seconds to acknowledge the question and say that you'll address it when you can then everyone's happy. Etiquette doesn't change just because we're on the internet.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width