Insert from Multiline Textbox

Question

Post reply

Insert from Multiline Textbox

jocast

Old Hand

Points: 332
More actions
December 15, 2008 at 5:12 pm

#383579

Hello forum, this is my first treat. Wish me luck.
I have a webform with a multiline textbox, i have data in each row that i need to insert into my table using something like this:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DBConn As SqlConnection
Dim DBCommand As SqlCommand
Dim affectedRows As Integer
DBConn = New SqlConnection( _
"server=SQLSERVER;" & _
"Initial Catalog=TEMTABLE;" & _
"User Id=ADMIN;" & _
"Password=TEST123;")
DBConn.Open()
DBCommand = New SqlCommand("insert into receiptgral values('" & Me.textbox1.Text & "',getdate())", DBConn)
affectedRows = DBCommand.ExecuteNonQuery()
Me.Label1.Text = "Inserted Lines: " & affectedRows
DBConn.Close()
End Sub
-----------------------------------------------------------------------------------------------------------------
Where Me.textbox1.Text is the multiline textbox that has a list of items needed in the table.
Thank you for your help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you should always use parameters to prevent sql injection attacks;

here's what you need to change:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim DBConn As SqlConnection

Dim DBCommand As SqlCommand

Dim affectedRows As Integer

DBConn = New SqlConnection( _

"server=SQLSERVER;" & _

"Initial Catalog=TEMTABLE;" & _

"User Id=ADMIN;" & _

"Password=TEST123;")

DBConn.Open()

DBCommand = New SqlCommand("insert into receiptgral values(@myParameter,getdate())", DBConn)

Dim param = new SqlParameter("myParameter", SqlDbType.VarChar)

param.Value = Me.textbox1.Text

DBCommand.Parameters.Add(param)

affectedRows = DBCommand.ExecuteNonQuery()

Me.Label1.Text = "Inserted Lines: " & affectedRows

DBConn.Close()

End Sub

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jocast Old Hand Points: 332 More actions · Answer 2

Thank you for your response.

I already tried that and this is the result

ID HAWB ADDDATE

1123456 789456 2008-12-16 09:52:42.967

I was expecting something like

ID HAWB ADDDATE

1123456 2008-12-16 09:52:42.967

2789456 2008-12-16 09:52:42.967

Is there a way to do this?

Lowell SSC Guru Points: 323518 More actions · Answer 3

you tried what? you didn't really say what you tried...i doubt you tried using parameters.

what are you doing? are you expecting that if the Me.textbox1.Text contains 8 lines of data, it's going to insert eight lines? your example shows two lines of data...your insert statement is not constructed to insert multiple rows of data i.e. VALUES(.... means one and only one line.

if the field "HAWB" is to be populated with TWO records from a string "123456 789456", you need to do split that field, ie for each value, and call an insert for each value found.....that's not what you originally described i think.

dim myArray() as String

myArray = Split(Me.textbox1.Text," ") '--empty space or CrLf?

for i = myarray.Lbound to myArray.UBound

'call the same insert statement

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jocast Old Hand Points: 332 More actions · Answer 4

I tired the changes you suggest.

In your last post the commands

myArray.Lbound

myArray.UBound

give me an error "Lbound is nota a member of system.array. Do you know why is this happening?

Also i add Dim i As Integer to get rid of an error in "i"

Thank you for your help.

Lowell SSC Guru Points: 323518 More actions · Answer 5

i didn't have studio in fromt of me; this is the correct syntax:

For i = 0 To myArray.Length - 1

DbCommand = New SqlCommand("insert into receiptgral values('" & myArray(i) & "',getdate())", DBConn)

affectedRows = DbCommand.ExecuteNonQuery()

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!