Results 1 to 29 of 29

Thread: multiple multiline textbox insert to ms sql database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    multiple multiline textbox insert to ms sql database

    Good afternoon to all

    Im new to vb.net ,earlier had programmed in vba now want to do in vs code and ms sql server database

    we have 2 simple text box and 3 multiline text box in winform ,we need to insert data in ms sql database

    if multiline textbox has 5 item then each line to be inserted into next row,simultaneously the single line textbox value to be copied according to the no of rows of the multiline

    please advise me

    regards
    padma

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

    Re: multiple multiline textbox insert to ms sql database

    You have posted in the CodeBank forum, which is for sharing working code samples, not asking questions. I have asked the mods to move this thread to the appropriate forum.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    sorry dear ,as new mistake happened

    rgds

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

    Re: multiple multiline textbox insert to ms sql database

    I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data. Among the examples is one that uses a data adapter to insert multiple records into a database from a DataTable. That is what you should do. You would create the DataTable first, then use a For loop over the Lines properties of the multiline TextBoxes and add rows to the table, then save the lot to the database.

    The question is, will the three Textboxes always have the same number of lines in them and, if not, how should you handle that. Also, note that the Lines property of a TextBox generates a new array each time you get it, so make sure that you only get it once. You'll need to access the array multiple times so get the property once and assign the result to a variable, which you can then use multiple times.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    hi
    thanks for your reply three multiline textbox will have same nos of lines, but also we have additional two single textboxes ,which has to copied nos of times till it matches the multiline rows

    can u pls send the link of" Retrieving & Saving Data" not able to find out ,since new to these app.

    please ...

    rgds/padma

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

    Re: multiple multiline textbox insert to ms sql database

    The link to my CodeBank threads is in the signature at the bottom of all my posts. You can find that thread listed there. I'd have to find the link that way so, if I can do it, you can too.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    hi
    i tried in the forum ,but im not getting the solution required

    so please help me out

    Thanks in advance

    Rgds/Padma

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

    Re: multiple multiline textbox insert to ms sql database

    I've told you where to go to find information about populating a DataTable and saving the contents and I've provided instructions on how to populate the DataTable in this specific case. What have you done and where are you stuck? I'm here to help but I've already done that, so now it's your turn to do something. I'm willing to help further but I need to see some effort on your part first. I'm not here to just write your code for you. You can even forget the saving part for now and just show that you can get the data required. Can you write an appropriate loop as I described above and then display the data for each record, e.g. using Console.WriteLine? If you aren't even prepared to try to do that then I'm not prepared to do anything more either. To try and fail is OK. To not try at all is not.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    Be Cool dear ,im not irritating u or helping in to write code for me,im layman in vb.net ,since you are great coder ,just seeking your kind advise on my problem

    the code is below

    Code:
    Private Sub insc_item(item As String)
            Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values As String() = itmrep.Split(vbCrLf)
            Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
            Dim cmd As New SqlCommand("insert into import$(container) values (@parm1)", conn)
    
            cmd.Parameters.AddWithValue("parm1", item)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
    
        End Sub
        Private Sub inss_item(item As String)
            Dim itmrep As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values As String() = itmrep.Split(vbCrLf)
            Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
            Dim cmd As New SqlCommand("insert into import$(size) values (@parm1)", conn)
    
            cmd.Parameters.AddWithValue("parm1", item)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
    
        End Sub
        Private Sub inssiz()
            Dim itmrep As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values As String() = itmrep.Split(vbCrLf)
            For Each i As String In values
                inss_item(i)
            Next
    
        End Sub
    what i get one (Textbox1)copy the multi line in database and another three field(textbox2,textbox3) to the the next row instead in the same row

    other than this i have 2 textbox (single line )which has to copied

    all 2 normal text box ,3 multiline text box should be in one query,so that it insert date in one row

    this is my problem,

    rgds/Padma
    Last edited by Shaggy Hiker; Jul 31st, 2022 at 09:08 AM. Reason: Swapped tags.

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

    Re: multiple multiline textbox insert to ms sql database

    Please use CODE tags for code rather than QUOTE tags, which are for quotes.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    sorry

    Code:
    Private Sub insc_item(item As String)
    Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
    Dim values As String() = itmrep.Split(vbCrLf)
    Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
    Dim cmd As New SqlCommand("insert into import$(container) values (@parm1)", conn)
    
    cmd.Parameters.AddWithValue("parm1", item)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()
    
    End Sub
    Private Sub inss_item(item As String)
    Dim itmrep As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
    Dim values As String() = itmrep.Split(vbCrLf)
    Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
    Dim cmd As New SqlCommand("insert into import$(size) values (@parm1)", conn)
    
    cmd.Parameters.AddWithValue("parm1", item)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()
    
    End Sub
    Private Sub inssiz()
    Dim itmrep As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
    Dim values As String() = itmrep.Split(vbCrLf)
    For Each i As String In values
    inss_item(i)
    Next
    
    End Sub

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

    Re: multiple multiline textbox insert to ms sql database

    And now you've copied the code from your previous post without indenting, making it much harder to read.

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

    Re: multiple multiline textbox insert to ms sql database

    Code:
    Dim cmd As New SqlCommand("insert into import$(container) values (@parm1)", conn)
    Every time you execute an Insert command it will create a new row in the database table. So, if you want all the textbox data in one row then include them all in your Insert command.

    Code:
    Dim cmd As New SqlCommand("insert into import$(container, field2, field3,) values (@parm1, @param2, @param3)", conn)

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

    Re: multiple multiline textbox insert to ms sql database

    No use of the Lines property and no DataTable. Clearly not interested in following the advice I've provided so far so I see no point offering more.

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    i tried with below changes in the code

    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values As String() = itmrep.Split(vbCrLf)
            Dim itmrep1 As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values1 As String() = itmrep1.Split(vbCrLf)
            Dim itmrep2 As String = tbtype.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values2 As String() = itmrep2.Split(vbCrLf)
            For Each i As String In values
                insc_item(i)
            Next
    
            For Each i As String In values1
                insc_item(i)
            Next
    
            For Each i As String In values2
                insc_item(i)
            Next
    
            Display()
            MessageBox.Show("Record updated succesfully")
    
        End Sub
        Private Sub inscon()
    
    
        End Sub
        Private Sub insc_item(item As String)
    
            Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
            Dim cmd As New SqlCommand("insert into import$(container,size,type) values (@parm1,@param2,param3)", conn)
    
            cmd.Parameters.AddWithValue("@parm1, @param2, @param3", item)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
    
        End Sub
    but got an error

    Message=Incorrect syntax near ','.
    Must declare the scalar variable "@parm1".

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

    Re: multiple multiline textbox insert to ms sql database

    You should always state which line the error occurs on. Otherwise we are just guessing and that can waste everyones time.

    This line is definitely wrong

    Code:
    cmd.Parameters.AddWithValue("@parm1, @param2, @param3", item)
    You need to add the parameters one at a time.

    Code:
    cmd.Parameters.AddWithValue("@parm1", somevalue1)
    cmd.Parameters.AddWithValue("@parm2",somevalue2)
    cmd.Parameters.AddWithValue("@param3", somevale3)
    https://stackoverflow.com/questions/...ccess-database

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    ok will check and revert,im lady from india ,your message came at night ,so i cheked in morning and replied , so it delayed

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    now im geting the required 3 textbox added to database ,but it is placing all the multitine in one cell in database ,it should be placed next row

    think i m missing split ,can your goodself please advise

    Code:
    Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
            Dim cmd As New SqlCommand("insert into import$(container,size,type) values (@parm1,@param2,@param3)", conn)
            Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
            Dim itmrep1 As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim itmrep2 As String = tbtype.Text.Replace(Environment.NewLine, vbCrLf)
    
            cmd.Parameters.AddWithValue("@parm1", itmrep)
            cmd.Parameters.AddWithValue("@param2", itmrep1)
            cmd.Parameters.AddWithValue("@param3", itmrep2)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
    Attachment 185402Attachment 185403

  19. #19
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: multiple multiline textbox insert to ms sql database

    Quote Originally Posted by padma_n View Post
    . . .but it is placing all the multitine in one cell in database ,it should be placed next row
    No, you don't want to do this.

    Your data is ok as multi-line text in a single "cell in database" and you'll thank me later.

    If this is an exercise or homework of some kind then you have to be more clear how do you want them split the 3 multi-line textboxes.

    Do you want second lines from all 3 multi-line textboxes on a separate row in the database?

    Can you give some sample multi-line texts and expected rows in the database?

    cheers,
    </wqw>

  20. #20

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    thanks for reply

    we want second lines from all 3 multi-line textboxes on a separate row in the database

    the nos of rows may be n times as user inputs and all the three textbox field will have same nos of rows

    Text box 1- contains CONTAINER NOS , varchar of 11digit eg- TEXU1234567,on enter next line CRXU12345456( only the for alphabet and 7 numeric will change
    text box 2- container SIZE ,integer of two digit eg 20, on enter next line 40 and 45 ( only 20/40/45 this is fix will not change)
    text box3 - container TYPE,varchar of two digit eg HD, on enter next line DV,TK,HC,FR,OT and RF ( only HD, DV,TK,HC,FR,OT and RF this is fix will not change)

    once again thanks for your help

    RGDS/Paran

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

    Re: multiple multiline textbox insert to ms sql database

    If each line of the textbox represents a separate record, then why are you using a Textbox for display? There are better controls, my first thought would be a DataGridView.

  22. #22

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    Hi
    Good Morning

    Your suggestion will be right only

    but the multiline will some time 250 lines ,if textbox we can copy date from excel sheet and paste in textbox and execute the code

    please suggest

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

    Re: multiple multiline textbox insert to ms sql database

    I can see that my initial advice will continue to be ignored so, in the interests of the eventual code not being garbage, let me provide a hint:
    vb.net Code:
    1. Dim fieldValue = singleLineTextBox.Text
    2. Dim lines = multiLineTextBox.Lines
    3.  
    4. For i = 0 To lines.GetUpperBound(0)
    5.     myDataTable.Rows.Add(fieldValue, lines(i))
    6. Next
    7.  
    8. myDataAdapter.Update(myDataTable)

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

    Re: multiple multiline textbox insert to ms sql database

    Quote Originally Posted by padma_n View Post
    Hi
    Good Morning

    Your suggestion will be right only

    but the multiline will some time 250 lines ,if textbox we can copy date from excel sheet and paste in textbox and execute the code

    please suggest
    I'd suggest putting the Excel data into a datatable and then binding the datatable to a datagridview. Your choice.

  25. #25

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    hi, the string are getting in below row, but my loop is doing three times ,the data updating is three times in database ,how can i loop for once with all three values,values1 and values2
    below is the cade
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values As String() = itmrep.Split(vbCrLf)
            Dim itmrep1 As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values1 As String() = itmrep1.Split(vbCrLf)
            Dim itmrep2 As String = tbtype.Text.Replace(Environment.NewLine, vbCrLf)
            Dim values2 As String() = itmrep2.Split(vbCrLf)
            For Each i As String In values
                insert_item(i)
            Next
            For Each i As String In values1
                insert_item(i)
            Next
            cmd.ExecuteNonQuery()
            For Each i As String In values2
                insert_item(i)
            Next
            cmd.ExecuteNonQuery()
    
            Display()
            MessageBox.Show("Record updated succesfully")
            conn.Close()
        End Sub
    
        Private Sub insert_item(item As String)
    
            Dim conn As New SqlConnection("Data Source=asus\sqlexpress;Initial Catalog=nspldata;Integrated Security=True")
            Dim cmd As New SqlCommand("insert into import$(container,size,type) values (@parm1,@param2,@param3)", conn)
            Dim itmrep As String = tbcontnos.Text.Replace(Environment.NewLine, vbCrLf)
            Dim itmrep1 As String = tbsize.Text.Replace(Environment.NewLine, vbCrLf)
            Dim itmrep2 As String = tbtype.Text.Replace(Environment.NewLine, vbCrLf)
    
            cmd.Parameters.AddWithValue("@parm1", item)
            cmd.Parameters.AddWithValue("@param2", item)
            cmd.Parameters.AddWithValue("@param3", item)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
    
        End Sub
    rgds/Paran

  26. #26
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: multiple multiline textbox insert to ms sql database

    the request sounds strange.

    here a split with regex every 19 Characters
    you woulf have to save the data to a single field with the spaces

    Code:
    Imports System.Text.RegularExpressions
    
    Public Class Form5
    
       
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim TextinDatabase As String = "this is some Text          DV,TK,HC,FR,OT               19 Chars in each row!"
            Dim groups As List(Of String) = (
                From m As Match In Regex.Matches(TextinDatabase, "(.{0,19})(?: |$)")
                Select m.Value).ToList()
            For Each i In groups
                TextBox1.AppendText(i & vbNewLine)
            Next
        End Sub
    End Class
    and a Image
    Name:  multiline.jpg
Views: 945
Size:  11.1 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  27. #27

    Thread Starter
    Junior Member
    Join Date
    Jul 2022
    Posts
    16

    Re: multiple multiline textbox insert to ms sql database

    Quote Originally Posted by wes4dbt View Post
    I'd suggest putting the Excel data into a datatable and then binding the datatable to a datagridview. Your choice.
    thanks for your quote
    we have user input to three multiline textbox and another simple textbox is also there ,we will input three multiline textbox from excel to data base but what about other simple textbox ,so in one query it should go and all should be in one row only with multiline showing no rows with user input

    rgds/

  28. #28
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: multiple multiline textbox insert to ms sql database

    Quote Originally Posted by padma_n View Post
    we have user input to three multiline textbox and another simple textbox is also there ,we will input three multiline textbox from excel to data base but what about other simple textbox ,so in one query it should go and all should be in one row only with multiline showing no rows with user input
    Now, how this has anything to do with this input you already gave above

    Text box 1- contains CONTAINER NOS , varchar of 11digit eg- TEXU1234567,on enter next line CRXU12345456( only the for alphabet and 7 numeric will change
    text box 2- container SIZE ,integer of two digit eg 20, on enter next line 40 and 45 ( only 20/40/45 this is fix will not change)
    text box3 - container TYPE,varchar of two digit eg HD, on enter next line DV,TK,HC,FR,OT and RF ( only HD, DV,TK,HC,FR,OT and RF this is fix will not change)
    You have to figure it out on yourself what you want before implementing it into actual code. Programming is dealing with minute exact details.

    You cannot just say "3 multi-line textboxes go into a table". You have to say exactly how do you split each of these, what happens if one of these is shorter or empty?

    You need exact implementation details before starting to code. You cannot slap for loops and pray that the implementation works out somehow by itself in the end.

    cheers,
    </wqw>

  29. #29
    Lively Member
    Join Date
    Dec 2021
    Posts
    100

    Re: multiple multiline textbox insert to ms sql database

    How I did this.

    Code:
    Private Sub F5_FormClosed(sender As Object, e As FormClosedEventArgs) Handles F5.FormClosed
            With TextBox15
                .AppendText(F5.Data)
            End With
            TextBox15.Text += vbNewLine
        End Sub
    So what it does is I call for a Form with DataGridView and select the row I need. I do it multiple times if necessary, and each record comes in a multi-row Textbox. Whole multi-row Textbox data go to single varchar(500) and later on I can use it to create reports with a new line set up llready

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