[RESOLVED] INSERT INTO using SqlConnection and SqlCommand
I have a Database with a Table named Orders and a column OrderNo. In a Form with a textbox ctlOrder I write the OrderNo value. This is the code:
Code:
Dim theText As String
theText = ctlOrder.Text
Dim CNstring As String = "Data Source=EBELLO-PC\SQLEXPRESS;Initial " &
"Catalog=Orders;Integrated Security=True"
If CNstring.Trim = "" Then Exit Sub
Dim CN As New SqlConnection(CNstring)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "INSERT INTO Orders(OrderNo) VALUES ('theText')"
cmd.Connection = CN
CN.Open()
cmd.ExecuteNonQuery()
When I open the Table Orders in the field OrderNo the value is the word theText instead of the value.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
ebellounisoft
I have a Database with a Table named Orders and a column OrderNo. In a Form with a textbox ctlOrder I write the OrderNo value. This is the code:
Code:
Dim theText As String
theText = ctlOrder.Text
Dim CNstring As String = "Data Source=EBELLO-PC\SQLEXPRESS;Initial " &
"Catalog=Orders;Integrated Security=True"
If CNstring.Trim = "" Then Exit Sub
Dim CN As New SqlConnection(CNstring)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "INSERT INTO Orders(OrderNo) VALUES ('theText')"
cmd.Connection = CN
CN.Open()
cmd.ExecuteNonQuery()
When I open the Table Orders in the field OrderNo the value is the word
theText instead of the value.
Isn't this pretty much the same scenario as was covered in your other thread http://www.vbforums.com/showthread.p...om-an-inputbox
Re: INSERT INTO using SqlConnection and SqlCommand
This has nothing specifically to do with databases. If you did this:
vb.net Code:
Dim message = "The TextBox contains 'theText'."
MessageBox.Show(message)
what would you expect to see? How would you normally change that to concatenate the Text of a TextBox with other text to make a single String? You can do it the same way in your case because a String is a String, regardless of what it is being used for. As a hint, you're already using string concatenation on the connection string, so you definitely know how to concatenate multiple Strings. You just have to concatenate the right Strings. I think that you already know how to get the Text from a TextBox.
That said, there's a better way to insert values into SQL code than using string concatenation, which can lead to a number of issues. You can follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data to see various ADO.NET examples, a number of which use parameters. You can also follow the Blog link in my signature and check out my post dedicated to Parameters In ADO.NET for specific information.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
PlausiblyDamp
Yes it is. What I am trying is this approach and see how it works.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
When I open the Table Orders in the field OrderNo the value is the word theText instead of the value.
That is exactly what what should be in the OrderNo field because that is exactly what this statement puts in that field,
Code:
cmd.CommandText = "INSERT INTO Orders(OrderNo) VALUES ('theText')"
What value would you expect to in in OrderNo after executing that Insert command?
If you want to insert the value from a Textbox, then you use that value in your Insert Command. If you don't know how to do that (there is more than one way) then just Google "visual basic insert command using textbox"
Give it a try then post back if your still having problems.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
PlausiblyDamp
So it is. @ebellounisoft, in that other thread you were directed to where you can find examples, which I know you like, and other relevant information that will help you avoid other issues in the future. Is there something that you don't understand about that information? If so, we could probably address that so that you don't have to keep asking the same question.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
ebellounisoft
Yes it is. What I am trying is this approach and see how it works.
Trying what approach? You asked the same question previously and were told where to find information on how to do it properly. What you are doing here is exactly like what you did before and not like what that information indicates is the proper way to do it. Can we see your attempt to do it the proper way?
It's worth noting that my blog post on using parameters starts by showing the wrong way to do it, i.e. using string concatenation. That means that, even if you don't want to do it the right way, you could learn how to properly do it the wrong way if you follow the link suggested.
There's really no good reason to do it this way though. How it works is sometimes it will do what you expect and other times it will not and, in extreme cases, a malicious user will delete everything in your database. If you don't know the differences between those cases and what you need to do in each then the logical approach is to just do it the right way, i.e. use parameters, and then it will just work every time.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
jmcilhinney
Trying what approach? You asked the same question previously and were told where to find information on how to do it properly. What you are doing here is exactly like what you did before and not like what that information indicates is the proper way to do it. Can we see your attempt to do it the proper way?
Trying to use as VALUE a varchar variable from theText = ctlOrder.Text in the code.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
ebellounisoft
Trying to use as VALUE a varchar variable.
As I was typing while you were also typing:
Quote:
There's really no good reason to do it this way though. How it works is sometimes it will do what you expect and other times it will not and, in extreme cases, a malicious user will delete everything in your database. If you don't know the differences between those cases and what you need to do in each then the logical approach is to just do it the right way, i.e. use parameters, and then it will just work every time.
If you're really determined to do it the wrong way though, you obviously need to use the actual value you want to save. As I have already said, you need to use string concatenation and you obviously already know how to do that because you're already doing it for the connection string. You know how to concatenate Strings so go ahead and do it, but just use the right Strings. I have confidence that, with a bit of thought, you can work out what the right strings are.
Re: INSERT INTO using SqlConnection and SqlCommand
If you want to use the value in theText (though using a Parameter is preferred).
Code:
cmd.CommandText = "INSERT INTO Orders(OrderNo) VALUES ('" & theText & "')"
jmc, is giving you some good advise but I'll just show you this and hopefully it will get you kick started.
Re: INSERT INTO using SqlConnection and SqlCommand
Quote:
Originally Posted by
jmcilhinney
As I was typing while you were also typing:
If you're really determined to do it the wrong way though, you obviously need to use the actual value you want to save. As I have already said, you need to use string concatenation and you obviously already know how to do that because you're already doing it for the connection string. You know how to concatenate Strings so go ahead and do it, but just use the right Strings. I have confidence that, with a bit of thought, you can work out what the right strings are.
Yes I read in a book about it. I am going to stick with the parameter aprroach. Thank you and good bye.. Just for trying, post of wes4dbt # 10 Works !!!
