Results 1 to 11 of 11

Thread: [RESOLVED] INSERT INTO using SqlConnection and SqlCommand

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

    Resolved [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.
    Last edited by ebellounisoft; May 20th, 2018 at 07:56 PM.

  2. #2
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by ebellounisoft View Post
    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

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

    Re: INSERT INTO using SqlConnection and SqlCommand

    This has nothing specifically to do with databases. If you did this:
    vb.net Code:
    1. Dim message = "The TextBox contains 'theText'."
    2.  
    3. 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.

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by PlausiblyDamp View Post
    Isn't this pretty much the same scenario as was covered in your other thread http://www.vbforums.com/showthread.p...om-an-inputbox
    Yes it is. What I am trying is this approach and see how it works.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: INSERT INTO using SqlConnection and SqlCommand

    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.

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

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by PlausiblyDamp View Post
    Isn't this pretty much the same scenario as was covered in your other thread http://www.vbforums.com/showthread.p...om-an-inputbox
    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.

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

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by ebellounisoft View Post
    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.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by jmcilhinney View Post
    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.
    Last edited by ebellounisoft; May 20th, 2018 at 08:11 PM.

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

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by ebellounisoft View Post
    Trying to use as VALUE a varchar variable.
    As I was typing while you were also typing:
    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.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    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.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jun 2017
    Posts
    165

    Re: INSERT INTO using SqlConnection and SqlCommand

    Quote Originally Posted by jmcilhinney View Post
    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 !!!
    Last edited by ebellounisoft; May 20th, 2018 at 08:38 PM.

Tags for this Thread

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