# Visual Basic > Visual Basic .NET >  multiple multiline textbox insert to ms sql database

## padma_n

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

----------


## jmcilhinney

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.

----------


## padma_n

sorry dear ,as new mistake happened

rgds

----------


## jmcilhinney

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.

----------


## padma_n

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

----------


## jmcilhinney

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.

----------


## padma_n

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

so please help me out

Thanks in advance

Rgds/Padma

----------


## jmcilhinney

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.

----------


## padma_n

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



```
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

----------


## jmcilhinney

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

----------


## padma_n

sorry



```
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
```

----------


## jmcilhinney

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

----------


## wes4dbt

```
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.



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

----------


## jmcilhinney

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.

----------


## padma_n

i tried with below changes in the 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".

----------


## wes4dbt

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



```
cmd.Parameters.AddWithValue("@parm1, @param2, @param3", item)
```

You need to add the parameters one at a time. 



```
cmd.Parameters.AddWithValue("@parm1", somevalue1)
cmd.Parameters.AddWithValue("@parm2",somevalue2)
cmd.Parameters.AddWithValue("@param3", somevale3)
```

https://stackoverflow.com/questions/...ccess-database

----------


## padma_n

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

----------


## padma_n

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



```
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

----------


## wqweto

> . . .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>

----------


## padma_n

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

----------


## wes4dbt

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.

----------


## padma_n

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

----------


## jmcilhinney

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:
Dim fieldValue = singleLineTextBox.Text
Dim lines = multiLineTextBox.Lines
 For i = 0 To lines.GetUpperBound(0)
    myDataTable.Rows.Add(fieldValue, lines(i))
Next
 myDataAdapter.Update(myDataTable)

----------


## wes4dbt

> 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.

----------


## padma_n

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


```
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

----------


## ChrisE

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



```
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

----------


## padma_n

> 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/

----------


## wqweto

> 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>

----------


## ivansmo

How I did this.



```
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

----------

