Trying to insert textboxdata into database

  • Thread starter Thread starter PGM
  • Start date Start date
P

PGM

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim Projname, ProjSpon As String

Dim AppName, AppType, AppSupp, AppDesc As String

Dim VendName, VendPhone As String

Dim ReqName, ReqPurp As String

Dim BudgItem, BudgNum As String

Dim BudgOnly As Integer

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

Projname = Convert.ToString(txtProjName.Text)

ProjSpon = Convert.ToString(txtProjSpon.Text)

AppNAme = Convert.ToString(txtAppName.Text)

VendName = Convert.ToString(txtVendName.Text)

VendPhone = Convert.ToString(txtVendPhone.Text)

AppType = Convert.ToString(cmbAppType.SelectedItem)

AppSupp = Convert.ToString(cmbAppSupp.SelectedItem)

AppDesc = Convert.ToString(txtAppDes.Text)

ReqName = Convert.ToString(txtReqName.Text)

ReqPurp = Convert.ToString(cmbReqPurp.SelectedItem)

BudgItem = Convert.ToString(cmbBudgetItem.SelectedItem)

BudgNum = Convert.ToString(txtbudgetNum.Text)

Dim strSQL As String

Dim strConn As String

strSQL = "Insert Into system.ServerRequest Values('" & BudgOnly & "','" &
Projname & "','" & ProjSpon & "','" & AppName & "','" & VendName & "','" &
VendPhone & "','" & AppType & "','" & AppSupp & "','" & AppDesc & "','" &
ReqName & "','" & ReqPurp & "','" & BudgItem & "','" & BudgNum & "')"

MsgBox("SQL:" & strSQL)

strConn = "Provider=SQLOLEDB.1;Password=vbuser;Persist Security
Info=True;User ID=VBuser;Initial Catalog=Masterserver;Data
Source=patmtest\sqlexpress"

MyConnObj = New ADODB.Connection

MyConnObj.Open(strConn)

SQLcmd = New ADODB.Command

SQLcmd.ActiveConnection = MyConnObj

SQLcmd.CommandText = strSQL

SQLcmd.Execute()

MyConnObj.Close()

MyConnObj = Nothing
 
PGM said:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim Projname, ProjSpon As String
Click to expand...

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.SqlClient rather than OleDb.

HTH

Andrew
 
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.ToString(txtProjName.Text)

ProjSpon = Convert.ToString(txtProjSpon.Text)

AppName = Convert.ToString(txtAppName.Text)

VendName = Convert.ToString(txtVendName.Text)

VendPhone = Convert.ToString(txtVendPhone.Text)

AppType = Convert.ToString(cmbAppType.SelectedItem)

AppSup = Convert.ToString(cmbAppSupp.SelectedItem)

AppDesc = Convert.ToString(txtAppDes.Text)

ReqName = Convert.ToString(txtReqName.Text)

ReqPurp = Convert.ToString(cmbReqPurp.SelectedItem)

BudgItem = Convert.ToString(cmbBudgetItem.SelectedItem)

BudgNum = Convert.ToString(txtbudgetNum.Text)



'Create the Database Connection String

Dim MserverConnection As New SqlConnection("Data
Source=patmtest\sqlexpress;Integrated Security=sspi;Initial
Catalog=MasterServer;User ID=Vbuser;Password=Vbuser")

'Create SQL Select query command

Dim SQLcmd As SqlCommand = New SqlCommand("dbo.InsertNewServerRequest",
MserverConnection)

SQLcmd.CommandType = CommandType.StoredProcedure

SQLcmd.Parameters.Add(New SqlParameter("@BudgetOnly", BudgetOnly))

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

SQLcmd.Parameters.Add(New SqlParameter("@ProjSpon", ProjSpon))

SQLcmd.Parameters.Add(New SqlParameter("@AppName", AppName))

SQLcmd.Parameters.Add(New SqlParameter("@VendName", VendName))

SQLcmd.Parameters.Add(New SqlParameter("@VendPhone", VendPhone))

SQLcmd.Parameters.Add(New SqlParameter("@AppType", AppType))

SQLcmd.Parameters.Add(New SqlParameter("@AppSup", AppSup))

SQLcmd.Parameters.Add(New SqlParameter("@AppDesc", AppDesc))

SQLcmd.Parameters.Add(New SqlParameter("@ReqName", ReqName))

SQLcmd.Parameters.Add(New SqlParameter("@ReqPurp", ReqPurp))

SQLcmd.Parameters.Add(New SqlParameter("@BudgItem", BudgItem))

SQLcmd.Parameters.Add(New SqlParameter("@BudgNum", BudgNum))

MsgBox("The Command: " & SQLcmd.Parameters.Count)

'Set a Command Timeout Value

SQLcmd.CommandTimeout = 30

'Create SQLdataAdapter

Dim ServerInfoDA As SqlDataAdapter = New SqlDataAdapter(SQLcmd.CommandText,
MserverConnection)

MsgBox("Created Dataadapter")

'Open Database Connection

SQLcmd.Connection.Open()

MsgBox("SQL Connection established")

'Execute the SQL Stored Procedure with Parameters

SQLcmd.ExecuteReader()

MsgBox("SQL Command Executed")

'Close the connection to the database

SQLcmd.Connection.Close()

MsgBox("Connection Closed")

End Sub
 
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
 
Back
Top