-
Jul 28th, 2022, 02:40 AM
#1
Thread Starter
Junior Member
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
-
Jul 28th, 2022, 03:07 AM
#2
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.
-
Jul 28th, 2022, 03:10 AM
#3
Thread Starter
Junior Member
Re: multiple multiline textbox insert to ms sql database
sorry dear ,as new mistake happened
rgds
-
Jul 28th, 2022, 03:14 AM
#4
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.
-
Jul 28th, 2022, 05:32 AM
#5
Thread Starter
Junior Member
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
-
Jul 28th, 2022, 06:28 AM
#6
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.
-
Jul 29th, 2022, 12:40 AM
#7
Thread Starter
Junior Member
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
-
Jul 29th, 2022, 02:45 AM
#8
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.
-
Jul 29th, 2022, 07:55 AM
#9
Thread Starter
Junior Member
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.
-
Jul 29th, 2022, 09:43 AM
#10
Re: multiple multiline textbox insert to ms sql database
Please use CODE tags for code rather than QUOTE tags, which are for quotes.
-
Jul 29th, 2022, 09:57 AM
#11
Thread Starter
Junior Member
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
-
Jul 29th, 2022, 10:25 AM
#12
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.
-
Jul 29th, 2022, 01:18 PM
#13
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)
-
Jul 29th, 2022, 10:28 PM
#14
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.
-
Jul 29th, 2022, 10:53 PM
#15
Thread Starter
Junior Member
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".
-
Jul 30th, 2022, 01:50 AM
#16
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
-
Jul 30th, 2022, 02:02 AM
#17
Thread Starter
Junior Member
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
-
Jul 30th, 2022, 08:25 AM
#18
Thread Starter
Junior Member
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
-
Jul 30th, 2022, 09:14 AM
#19
Re: multiple multiline textbox insert to ms sql database
Originally Posted by padma_n
. . .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>
-
Jul 30th, 2022, 10:16 AM
#20
Thread Starter
Junior Member
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
-
Jul 30th, 2022, 12:00 PM
#21
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.
-
Jul 30th, 2022, 10:36 PM
#22
Thread Starter
Junior Member
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
-
Jul 30th, 2022, 10:50 PM
#23
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:
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)
-
Jul 31st, 2022, 02:21 AM
#24
Re: multiple multiline textbox insert to ms sql database
Originally Posted by 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
I'd suggest putting the Excel data into a datatable and then binding the datatable to a datagridview. Your choice.
-
Jul 31st, 2022, 03:50 AM
#25
Thread Starter
Junior Member
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
-
Jul 31st, 2022, 06:19 AM
#26
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
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.
-
Jul 31st, 2022, 06:42 AM
#27
Thread Starter
Junior Member
Re: multiple multiline textbox insert to ms sql database
Originally Posted by wes4dbt
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/
-
Jul 31st, 2022, 09:05 AM
#28
Re: multiple multiline textbox insert to ms sql database
Originally Posted by padma_n
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>
-
Aug 2nd, 2022, 04:20 PM
#29
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|