?
Solved

Data Value (First Row) in TextBox to SQL Database

Posted on 2009-04-26
19
Medium Priority
?
1,439 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:slb2008
  • 8
  • 6
  • 5
19 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24238539
Change this
     strSQL += " VALUES ('" & TextBox1.Text & "')
to this
     strSQL += " VALUES ('" & TextBox1.Text.Split(vbcrlf)(0) & "')
0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24238557
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
 

Author Comment

by:slb2008
ID: 24244052
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 7

Expert Comment

by:Alpha Au
ID: 24246974
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
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24247264
Did you try my code?

That is already working...
0
 

Author Comment

by:slb2008
ID: 24252605
Hi JackOfPH,
I didn't try your code, but I will let you soon today.
Thanks
 
0
 

Author Comment

by:slb2008
ID: 24257070
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
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24257094
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
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24258733
What would be the output if you do this?

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

Can you post the output?
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24258773
*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
 

Author Comment

by:slb2008
ID: 24265649
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
 
LVL 15

Expert Comment

by:JackOfPH
ID: 24266174
Okey, how did you insert the value in textbox? This is important since this will be the basis in spliting the its contents...
0
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24266237
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
 

Author Comment

by:slb2008
ID: 24272464
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
 

Author Comment

by:slb2008
ID: 24272519
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
 
LVL 7

Expert Comment

by:Alpha Au
ID: 24288981
yes, you can replace the vbCrlf and trim the string before insert.
0
 

Author Comment

by:slb2008
ID: 24291396
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
 
LVL 7

Accepted Solution

by:
Alpha Au earned 2000 total points
ID: 24292065
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
 

Author Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question