Data Value (First Row) in TextBox to SQL Database

Change this
     strSQL += " VALUES ('" & TextBox1.Text & "')
to this
     strSQL += " VALUES ('" & TextBox1.Text.Split(vbcrlf)(0) & "')

you can split the textbox contain into array,
and you can choose to insert them to db as different record, or only insert the 1st row.

        Dim i As Integer
 
        Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
 
        For i = 0 To UBound(strArray)
            'for debug
            MsgBox(strArray(i))
            'you can prepare your sqlstr here.
        Next

Select allOpen in new window

Alphaau,
Thanks for your quick response.

After debug your code, displays the following error from MsgBox(strArray(i))
 
Dim i As Integer

        Dim strArray = Me.TextBox1.Text.Split(vbCrLf)

        For i = 0 To UBound(strArray)
            'for debug
            MsgBox(strArray(i))   <-----    Error here
            'you can prepare your sqlstr here.
        Next

 
Here is the Error Message :  Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification.
Slb2008

oh, the msgbox is only a message prompt to verify the strArray(i) value (in window application), you can direct insert the value in your db for test

for the below code, it insert all the contain to db , seperated by "enter"

if you really only need the 1st row.
JackOfPH has provided the answer.

cheers.

       Dim i As Integer
       Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
 
       strCnn = System.Configuration.ConfigurationManager.AppSettings("datasource")
       cnn = New SqlConnection(strCnn) 
 
       For i = 0 To UBound(strArray)
           'you can prepare your sqlstr here.
           strSQL = "INSERT INTO Companies(CustomerCompany)"
           strSQL += " VALUES ('" & strArray(i) & "')
           cmd = New SqlCommand(strSQL, cnn)
           cmd.ExecuteNonQuery()
       Next
 
        cnn.Close()
        cmd.Connection.Close()

Select allOpen in new window

Did you try my code?

That is already working...

Hi JackOfPH,
I didn't try your code, but I will let you soon today.
Thanks
 

I tried but didn't work .   What happens is:  After entering the company name, Address and Zip Code in textbox, saved one record for the first row, saved another record for the second row, and saved another record for the third...when I saw in SQL table I found the following results:
CustomerCompany
Intel Microsoft Inc.
123 Silicon Valley
NetNuts, CA 12345
and not
CustomerCompany
Intel Microsoft Inc.

 
 This is the code I use:
 Protected Sub InsertFirstRowTextBox1()

Dim cmd As SqlCommand
Dim strSQL As String
Dim strCnn As String
Dim cnn As SqlConnection
 
Dim i As Integer
Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
 
strCnn = System.Configuration.ConfigurationManager.AppSettings("datasource")
cnn = New SqlConnection(strCnn)
For i = 0 To UBound(strArray)
        strSQL = "INSERT INTO Companies(CustomerCompany)"
        strSQL += " VALUES ('" & strArray(i) & "')
        cmd = New SqlCommand(strSQL, cnn)cnn.Open()

        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Connection.Close()

Next

End Sub
Maybe I will try again tomorrow morning.
Thanks
 

the code i post insert all text in textbox to db.

if you really only need the 1st row,
you can use this.

Dim cmd As SqlCommand
Dim strSQL As String
Dim strCnn As String
Dim cnn As SqlConnection
 
Dim i As Integer
Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
 
strCnn = System.Configuration.ConfigurationManager.AppSettings("datasource")
 
cnn = New SqlConnection(strCnn)
 
'For i = 0 To UBound(strArray)
'1st row only
i = 0
        strSQL = "INSERT INTO Companies(CustomerCompany)"
 
       strSQL += " VALUES ('" & strArray(i) & "')
 
        cmd = New SqlCommand(strSQL, cnn)cnn.Open()
 
       cmd.ExecuteNonQuery()
        cnn.Close()
       cmd.Connection.Close()
 
'Next
 
End Sub 

Select allOpen in new window

What would be the output if you do this?

msgbox(TextBox1.Text.Split(vbcrlf)(0))

Can you post the output?

*PLEASE* try my code again after reading this comment...

What would be the output if you do this?

msgbox(TextBox1.Text.Split(vbcrlf)(0))

Can you post the output?

Did you press the enter key to move to the next row?

In order for our suggestions to work you must press the enter key to move to the next row, because we use the enter key <vbcrlf> as a delimeter to separate each row.

So if you just press space bar, the code we supplied won't work...

For example:

Intel Microsoft Inc. <Press enter key for next row>
123 Silicon Valley <Press enter key for next row>
NetNuts, CA 12345

Again , Please try my code:

msgbox(TextBox1.Text.Split(vbcrlf)(0))

What would its output?

JackOfPH,

Jehova is great!!!

JackofPH,
Below code is working and inserts the first row from textbox:
Dim cmd As SqlCommand
Dim strSQL As String
Dim strCnn As String
Dim cnn As SqlConnection
Dim i As Integer
Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
strCnn = System.Configuration.ConfigurationManager.AppSettings("datasource")
cnn = New SqlConnection(strCnn)
'1st row only
 i = 0
strSQL = "INSERT INTO Companies(CustomerCompany)" strSQL += " VALUES ('" & strArray(i) & "')
cmd = New SqlCommand(strSQL, cnn)
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
cmd.Connection.Close()
 End Sub  
 
Now I would like to see the following information in SQL table:
If text box displays this
Intel Company Inc.   -  first row
12345                        - second row
85258                       - third row
 and I would like to see the first row in the SQL table data field called "CustomerCompany"
the second row in the "MemberNumber" and third row in the "ZipCode
and displays :
CustomerCompany        MemberNumber       ZipCode
Intel Company Inc.           12345                         85258
 
When I put the msgbox(TextBox1.Text.Split(vbcrlf)(0)) , give an error Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification.
Instead of msgbox I could use label output.
Thanks

 
 

Okey, how did you insert the value in textbox? This is important since this will be the basis in spliting the its contents...

first row -> CustomerCompany
second row -> MemberNumber
third row -> ZipCode

you need to change the strSQL abit is okay

Dim cmd As SqlCommand
Dim strSQL As String
Dim strCnn As String
Dim cnn As SqlConnection
 
Dim i As Integer
Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
 
strCnn = System.Configuration.ConfigurationManager.AppSettings("datasource")
 
cnn = New SqlConnection(strCnn)
 
if UBound(strArray ) >= 2 then
 
        strSQL = "INSERT INTO Companies (CustomerCompany, MemberNumber, ZipCode)"
        strSQL += " VALUES ('" & strArray(0) & "', '"&strArray(1)&"','"&strArray(2)&"')"
        cmd = New SqlCommand(strSQL, cnn)
        cnn.Open()
         cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Connection.Close()
end if 
 
 
End Sub 

Select allOpen in new window

JackofPH
See below How to insert the value(s) in textbox:
I created a grid view, boundFields, select command and textbox control with textmode in multine in asp.net web form:
 
<asp:GridView
         ID="gridview1"
         runat="server"
         AutoGenerateColumns="false"
         DataKeyNames ="ID"
         OnRowCreated ="Gridview1_RowCreated">
 
<asp:BoundField DataField="ID" HeaderText="ID Num." > 
 </asp:BoundField>                     <!-- (Cell 0) ->
           
 <asp:BoundField DataField="CustomerCompany" >   
 </asp:BoundField>                     <!-- (Cell 1) ->
 
<asp:BoundField DataField="Address/>            <!-- (Cell 2) ->
<asp:BoundField DataField="City" HeaderText="City"  />               <!-- (Cell 3) ->
<asp:BoundField DataField="State" HeaderText="State"  />          <!-- (Cell 4) ->
<asp:BoundField DataField="Zip" HeaderText="Zip" />                   <!-- (Cell 5) ->
       
<asp:ButtonField CommandName="Select" Text="Select" />
 
<br/><br/>
 
<asp:TextBox ID="TextBox1" runat="server" Height="96px" Width="218px" TextMode ="MultiLine"  Font-Names ="tahoma" Font-Size ="8pt"></asp:TextBox>
 
<br/><br/>
<asp:Button runat=server Text="Insert" />
 
 

and then on VB.NET I put this code:
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Not IsPostBack Then
            BindGridView1()
        End If
    End Sub
 
Protected Sub BindGridView1()
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        Dim strSQL As String
         
        Try
 
            strSQL = "SELECT * FROM Customer 
 
            Dim conn As New SqlConnection("DataSource ")
 
            da = New SqlDataAdapter(strSQL, conn)
            ds = New Data.DataSet()
            da.Fill(ds, "Customer")
            Session("DataView") = ds.Tables("Customer").DefaultView()
            Me.gridview1.DataBind()
            GridLoad()
        Catch
      End try
End Sub
 
Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        ' only apply changes if its DataRow
        If e.Row.RowType = DataControlRowType.DataRow Then
        e.Row.Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
        End If
    End Sub
 
Protected Sub Insert()
        Dim cmd As SqlCommand
        Dim strSQL As String
        Dim strCnn As String
        Dim cnn As SqlConnection
 
strSQL = "INSERT INTO Customer(CustomerCompany, Address, City, State, ZipCode)"
        strSQL += " VALUES ('" & TextBox1.Text.ToString & "')"
 
strCnn = System.Configuration.ConfigurationManager.AppSettings("DataSource")
        cnn = New SqlConnection(strCnn)
        cmd = New SqlCommand(strSQL, cnn)
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
        cmd.Connection.Close()
    End Sub
 
Private Sub GridLoad()
        Dim dv As Data.DataView
        Me.gridview1.AutoGenerateColumns = False
        gridview1.Visible = True
        gridview1.Enabled = True
 
        ''fill the grid with data:
        gridview1.DataSource = Session("DataView")
        gridview1.DataBind()
 
        ''show the record count:
        dv = CType(Session("DataView"), Data.DataView)
 
    End Sub
 
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gridview1.SelectedIndexChanged
 
        ' Obtain the index of the selected row
        Dim selectedRow As Integer
        selectedRow = gridview1.SelectedIndex
        'Read the record from the grid
        'Dim row As GridViewRow = GridView1.Rows(selectedRow)
        Dim row As GridViewRow = gridview1.SelectedRow
 
        If row.Cells(0).Text <> "&nbsp;" Then
            TextBox1.Text = row.Cells(0).Text
        End If
 
        If row.Cells(1).Text <> "&nbsp;" Then
            TextBox1.Text = row.Cells(1).Text & Environment.NewLine
        End If
 
        If row.Cells(2).Text <> "&nbsp;" Then
            TextBox1.Text += row.Cells(2).Text & Environment.NewLine
        End If
 
        If row.Cells(3).Text <> "&nbsp;" Then
            TextBox1.Text += row.Cells(3).Text & Environment.NewLine
        End If
 
        If row.Cells(4).Text <> "&nbsp;" Then
            TextBox1.Text += row.Cells(4).Text & ", " 
        End If
 
        If row.Cells(5).Text <> "&nbsp;" Then
            TextBox1.Text += row.Cells(5).Text & " " 
        End If
    End Sub
 
I hope I helped you to answer to your question.
Thanks, slb2008

alphaau,
Your code worked great, but on SQL table I found on MemberNumber, ZipCode  a square before numbers like this    
CustomerCompany is fine
MemberNumber                ZipCode
 12345                               78925
How to avoid this square displaying on SQL data?  Remove  (vbCrLf)?

 Thanks, slb2008

yes, you can replace the vbCrlf and trim the string before insert.

Replace vbCrlf and trim the string before insert  like this  
From
Dim strArray = Me.TextBox1.Text.Split(vbCrLf)
to

Dim strArray = Me.TextBox1.Text.Split.Trim(vbCrLf)  
 
Is this correct?

It's working.   Your answer is accepted as final solution.  
Thanks alphaau for your help.
Sincerely, slb2008