-
Feb 20th, 2013, 04:57 PM
#1
Thread Starter
Lively Member
[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.
-
Feb 20th, 2013, 05:06 PM
#2
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
-
Feb 20th, 2013, 05:29 PM
#3
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!
-
Feb 20th, 2013, 06:18 PM
#4
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
-
Feb 20th, 2013, 10:25 PM
#5
Thread Starter
Lively Member
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.
-
Feb 20th, 2013, 11:07 PM
#6
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.
-
Feb 20th, 2013, 11:28 PM
#7
Thread Starter
Lively Member
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.
-
Feb 20th, 2013, 11:58 PM
#8
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.
-
Feb 21st, 2013, 10:53 AM
#9
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
-
Feb 21st, 2013, 11:03 AM
#10
Frenzied Member
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.
-
Feb 21st, 2013, 11:05 AM
#11
Frenzied Member
Re: SQL Code Executing without Error, Not Writing
Originally Posted by jmcilhinney
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.
-
Feb 21st, 2013, 11:17 AM
#12
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
-
Feb 21st, 2013, 01:15 PM
#13
Thread Starter
Lively Member
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.
-
Feb 21st, 2013, 07:14 PM
#14
Re: SQL Code Executing without Error, Not Writing
Originally Posted by claws135
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.
-
Feb 22nd, 2013, 12:44 PM
#15
Thread Starter
Lively Member
Re: SQL Code Executing without Error, Not Writing
Originally Posted by jmcilhinney
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.
-
Feb 22nd, 2013, 09:46 PM
#16
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|