Data Value (First Row) in TextBox to SQL Database

Hi Experts Exchange,

I have a textbox with data values and one click button for inserting, see below:

Intel Microsoft Inc.
123 Silicon Valley
NetNuts, CA 12345


BtnInsert


I manually entered data information in textbox.  I would like to know how to insert just only the first row data value :  Intel Microsoft Inc. to SQL Database.  How can I do this? Here is my code and something is missing.   Thanks.

ASP.NET:
 
<asp:TextBox ID="TextBox1" runat="server" Height="96px" Width="218px" TextMode ="MultiLine"  Font-Names ="tahoma" Font-Size ="8pt"></asp:TextBox>
 
<br/><br/>
 
<asp:Button 
              ID="BtnInsert" 
              runat="server" 
              Text="Insert/Save"
              Font-Bold="true" 
              Font-Names ="Trebuchet MS"
              Font-Size ="9pt" 
              Height="24px" Width="96px" />
 
VB.NET:
 
Protected Sub BtnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnInsert.Click
 
Dim cmd As SqlCommand
        Dim strSQL As String
        Dim strCnn As String
        Dim cnn As SqlConnection
 
If row.Cells(0).Text <> "&nbsp;" Then
            TextBox1.Text = row.Cells(0).Text
 
        strSQL = "INSERT INTO Companies(CustomerCompany)"
        strSQL += " VALUES ('" & TextBox1.Text & "')
 
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 if

Open in new window

slb2008Asked:
Who is Participating?
 
Alpha AuConnect With a Mentor Commented:
Dim strArray = Me.TextBox1.Text.Split.Trim(vbCrLf)  is correct,

and add this


From
strSQL += " VALUES ('" & strArray(0) & "', '"&strArray(1)&"','"&strArray(2)&"')"
To
strSQL += " VALUES ('" & strArray(0).ToString().Trim() & "', '"&strArray(1).ToString().Trim()&"','"&strArray(2).ToString().Trim()&"')"
0
 
JackOfPHCommented:
Change this
     strSQL += " VALUES ('" & TextBox1.Text & "')
to this
     strSQL += " VALUES ('" & TextBox1.Text.Split(vbcrlf)(0) & "')
0
 
Alpha AuCommented:
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

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
slb2008Author Commented:
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
0
 
Alpha AuCommented:
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()

Open in new window

0
 
JackOfPHCommented:
Did you try my code?

That is already working...
0
 
slb2008Author Commented:
Hi JackOfPH,
I didn't try your code, but I will let you soon today.
Thanks
 
0
 
slb2008Author Commented:
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
 
0
 
Alpha AuCommented:
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 

Open in new window

0
 
JackOfPHCommented:
What would be the output if you do this?

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

Can you post the output?
0
 
JackOfPHCommented:
*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!!!
0
 
slb2008Author Commented:
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

 
 
0
 
JackOfPHCommented:
Okey, how did you insert the value in textbox? This is important since this will be the basis in spliting the its contents...
0
 
Alpha AuCommented:
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 

Open in new window

0
 
slb2008Author Commented:
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
0
 
slb2008Author Commented:
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
0
 
Alpha AuCommented:
yes, you can replace the vbCrlf and trim the string before insert.
0
 
slb2008Author Commented:
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?
0
 
slb2008Author Commented:
It's working.   Your answer is accepted as final solution.  
Thanks alphaau for your help.
Sincerely, slb2008
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.