Trying to insert textboxdata into database

**Andrew Morton** · Jun 27 '08, 08:13 PM

Re: Trying to insert textboxdata into database

PGM wrote:

Private Sub Button1_Click_1 (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
>
Dim Projname, ProjSpon As String

There's your first problem: consider what happens if there's an apostrophe
in one of the names: it will break your SQL string. Or worse, your database
could be the victim of an SQL injection attack.

You will need to use a parameterized query. I can't find a basic explanation
quickly, google has the answer somewhere.

And if you're using SQL Server, you might as well go through
System.Data.Sql Client rather than OleDb.

HTH

Andrew

**PGM** · Jun 27 '08, 08:13 PM

Re: Trying to insert textboxdata into database

Thank you a ton,
I am a newbie to this .Net stuff, as if you couldn't tell. You pointed
me in the right direction and now I have it working. Thank You!! Changed
Code:

Dim Projname, ProjSpon As String

Dim AppName, AppType, AppSup, AppDesc As String

Dim VendName, VendPhone As String

Dim ReqName, ReqPurp As String

Dim BudgItem, BudgNum As String

Dim BudgetOnly As Integer

If chkBudg.Checked = True Then BudgetOnly = 1 Else BudgetOnly = 0

Projname = Convert.ToStrin g(txtProjName.T ext)

ProjSpon = Convert.ToStrin g(txtProjSpon.T ext)

AppName = Convert.ToStrin g(txtAppName.Te xt)

VendName = Convert.ToStrin g(txtVendName.T ext)

VendPhone = Convert.ToStrin g(txtVendPhone. Text)

AppType = Convert.ToStrin g(cmbAppType.Se lectedItem)

AppSup = Convert.ToStrin g(cmbAppSupp.Se lectedItem)

AppDesc = Convert.ToStrin g(txtAppDes.Tex t)

ReqName = Convert.ToStrin g(txtReqName.Te xt)

ReqPurp = Convert.ToStrin g(cmbReqPurp.Se lectedItem)

BudgItem = Convert.ToStrin g(cmbBudgetItem .SelectedItem)

BudgNum = Convert.ToStrin g(txtbudgetNum. Text)

'Create the Database Connection String

Dim MserverConnecti on As New SqlConnection(" Data
Source=patmtest \sqlexpress;Int egrated Security=sspi;I nitial
Catalog=MasterS erver;User ID=Vbuser;Passw ord=Vbuser")

'Create SQL Select query command

Dim SQLcmd As SqlCommand = New SqlCommand("dbo .InsertNewServe rRequest",
MserverConnecti on)

SQLcmd.CommandT ype = CommandType.Sto redProcedure

SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgetOnly", BudgetOnly))

SQLcmd.Paramete rs.Add(New SqlParameter("@ ProjName", Projname))

SQLcmd.Paramete rs.Add(New SqlParameter("@ ProjSpon", ProjSpon))

SQLcmd.Paramete rs.Add(New SqlParameter("@ AppName", AppName))

SQLcmd.Paramete rs.Add(New SqlParameter("@ VendName", VendName))

SQLcmd.Paramete rs.Add(New SqlParameter("@ VendPhone", VendPhone))

SQLcmd.Paramete rs.Add(New SqlParameter("@ AppType", AppType))

SQLcmd.Paramete rs.Add(New SqlParameter("@ AppSup", AppSup))

SQLcmd.Paramete rs.Add(New SqlParameter("@ AppDesc", AppDesc))

SQLcmd.Paramete rs.Add(New SqlParameter("@ ReqName", ReqName))

SQLcmd.Paramete rs.Add(New SqlParameter("@ ReqPurp", ReqPurp))

SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgItem", BudgItem))

SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgNum", BudgNum))

MsgBox("The Command: " & SQLcmd.Paramete rs.Count)

'Set a Command Timeout Value

SQLcmd.CommandT imeout = 30

'Create SQLdataAdapter

Dim ServerInfoDA As SqlDataAdapter = New SqlDataAdapter( SQLcmd.CommandT ext,
MserverConnecti on)

MsgBox("Created Dataadapter")

'Open Database Connection

SQLcmd.Connecti on.Open()

MsgBox("SQL Connection established")

'Execute the SQL Stored Procedure with Parameters

SQLcmd.ExecuteR eader()

MsgBox("SQL Command Executed")

'Close the connection to the database

SQLcmd.Connecti on.Close()

MsgBox("Connect ion Closed")

End Sub

"Andrew Morton" <akm@in-press.co.uk.inv alidwrote in message
news:680ps0F2ps vl1U1@mid.indiv idual.net...

PGM wrote:

>Private Sub Button1_Click_1 (ByVal sender As System.Object, ByVal e As
>System.EventAr gs) Handles Button1.Click
>>
>Dim Projname, ProjSpon As String

>
There's your first problem: consider what happens if there's an apostrophe
in one of the names: it will break your SQL string. Or worse, your
database could be the victim of an SQL injection attack.
>
You will need to use a parameterized query. I can't find a basic
explanation quickly, google has the answer somewhere.
>
And if you're using SQL Server, you might as well go through
System.Data.Sql Client rather than OleDb.
>
HTH
>
Andrew
>

**Michel Posseth [MCP]** · Jun 27 '08, 08:13 PM

Re: Trying to insert textboxdata into database

PGM

You did not needed to use a SP , you could have done it with a
parameterized SQL command ( although a SP is fine )

But if you were really smart , you would have used the datadapter from a
dataset as you then get the benefit of strong typing in your code
add a new dataset to your project , go to design view , add a new datadapter
and use the wizzard , select your base Table or View
now you can extend the table adapter with anny query you like parameters are
typed with a @ prefix in SQL server in Access ?

also another remark a textbox is returning a string so why convert a string
to a string ?

HTH

Michel

"PGM" <pmatthews@chw. orgschreef in bericht
news:%23%23sUzV HrIHA.4376@TK2M SFTNGP06.phx.gb l...

Thank you a ton,
I am a newbie to this .Net stuff, as if you couldn't tell. You pointed
me in the right direction and now I have it working. Thank You!! Changed
Code:
>
Dim Projname, ProjSpon As String
>
Dim AppName, AppType, AppSup, AppDesc As String
>
Dim VendName, VendPhone As String
>
Dim ReqName, ReqPurp As String
>
Dim BudgItem, BudgNum As String
>
Dim BudgetOnly As Integer
>
If chkBudg.Checked = True Then BudgetOnly = 1 Else BudgetOnly = 0
>
Projname = Convert.ToStrin g(txtProjName.T ext)
>
ProjSpon = Convert.ToStrin g(txtProjSpon.T ext)
>
AppName = Convert.ToStrin g(txtAppName.Te xt)
>
VendName = Convert.ToStrin g(txtVendName.T ext)
>
VendPhone = Convert.ToStrin g(txtVendPhone. Text)
>
AppType = Convert.ToStrin g(cmbAppType.Se lectedItem)
>
AppSup = Convert.ToStrin g(cmbAppSupp.Se lectedItem)
>
AppDesc = Convert.ToStrin g(txtAppDes.Tex t)
>
ReqName = Convert.ToStrin g(txtReqName.Te xt)
>
ReqPurp = Convert.ToStrin g(cmbReqPurp.Se lectedItem)
>
BudgItem = Convert.ToStrin g(cmbBudgetItem .SelectedItem)
>
BudgNum = Convert.ToStrin g(txtbudgetNum. Text)
>
>
>
'Create the Database Connection String
>
Dim MserverConnecti on As New SqlConnection(" Data
Source=patmtest \sqlexpress;Int egrated Security=sspi;I nitial
Catalog=MasterS erver;User ID=Vbuser;Passw ord=Vbuser")
>
'Create SQL Select query command
>
Dim SQLcmd As SqlCommand = New SqlCommand("dbo .InsertNewServe rRequest",
MserverConnecti on)
>
SQLcmd.CommandT ype = CommandType.Sto redProcedure
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgetOnly", BudgetOnly))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ ProjName", Projname))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ ProjSpon", ProjSpon))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ AppName", AppName))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ VendName", VendName))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ VendPhone", VendPhone))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ AppType", AppType))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ AppSup", AppSup))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ AppDesc", AppDesc))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ ReqName", ReqName))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ ReqPurp", ReqPurp))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgItem", BudgItem))
>
SQLcmd.Paramete rs.Add(New SqlParameter("@ BudgNum", BudgNum))
>
MsgBox("The Command: " & SQLcmd.Paramete rs.Count)
>
'Set a Command Timeout Value
>
SQLcmd.CommandT imeout = 30
>
'Create SQLdataAdapter
>
Dim ServerInfoDA As SqlDataAdapter = New
SqlDataAdapter( SQLcmd.CommandT ext, MserverConnecti on)
>
MsgBox("Created Dataadapter")
>
'Open Database Connection
>
SQLcmd.Connecti on.Open()
>
MsgBox("SQL Connection established")
>
'Execute the SQL Stored Procedure with Parameters
>
SQLcmd.ExecuteR eader()
>
MsgBox("SQL Command Executed")
>
'Close the connection to the database
>
SQLcmd.Connecti on.Close()
>
MsgBox("Connect ion Closed")
>
End Sub
>
>
>
"Andrew Morton" <akm@in-press.co.uk.inv alidwrote in message
news:680ps0F2ps vl1U1@mid.indiv idual.net...

>PGM wrote:

>>Private Sub Button1_Click_1 (ByVal sender As System.Object, ByVal e As
>>System.EventA rgs) Handles Button1.Click
>>>
>>Dim Projname, ProjSpon As String

>>
>There's your first problem: consider what happens if there's an
>apostrophe in one of the names: it will break your SQL string. Or worse,
>your database could be the victim of an SQL injection attack.
>>
>You will need to use a parameterized query. I can't find a basic
>explanation quickly, google has the answer somewhere.
>>
>And if you're using SQL Server, you might as well go through
>System.Data.Sq lClient rather than OleDb.
>>
>HTH
>>
>Andrew
>>

>
>

Trying to insert textboxdata into database

Trying to insert textboxdata into database

Comment

Comment

Comment