Updating SQL DB from Web Form

[rawky76]

Hello

I have this to ponder over...

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

Dim sFN As String
Dim sLN As String
Dim sJT As String
Dim sUN As String
Dim sPW As String
sFN = txtFirstName.Text.ToString
sLN = txtLastName.Text.ToString
sJT = txtJobTitle.Text.ToString
sUN = txtUserName.Text.ToString
sPW = txtPassword.Text.ToString
Const sConnection As String = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=HelpdeskApp;Data Source=MARK"

Dim objConn As New System.Data.OleDb.OleDbConnection(sConnection)

objConn.Open()

Dim sSQL As String

Dim objCmd As New System.Data.OleDb.OleDbCommand(sSQL, objConn)

sSQL = "INSERT INTO STAFF (First Name, Last Name, Job Title, Username, Password, StaffID) VALUES (sFN, sLN, sJT, sUN, sPW, 1)"

Try

objCmd.ExecuteNonQuery()

Catch myException As System.Exception

Console.WriteLine(myException.Message)

End Try

txtFirstName.Text = ""

txtLastName.Text = ""

txtJobTitle.Text = ""

txtUserName.Text = ""

txtPassword.Text = ""

txtConfirmPassword.Text = ""

End Sub

The txtXxX are the names of the text boxes on the form, the extra 1 on the INSERT is for testing (it fills the Primary Key field). It appears to work, without any errors and clears the form afterwards but no data appears in the table on the SQL server???????

 

[Tinbeard]

Just a suggestion, maybe try these changes to your code

<%@ Import NameSpace="System.Data.OleDB" %>
 
 [color=#0000ff]Private[size=2]Sub[/size][/color][size=2] btnOK_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] btnOK.Click

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] sFN [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] sLN [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] sJT [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] sUN [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] sPW [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim[size=2] sSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
 
Dim[size=2] objConn [/size][size=2][color=#0000ff]As [/color][/size][size=2]OleDbConnection)
Dim[size=2] objCmd [/size][size=2][color=#0000ff]As [/color][/size][size=2]OleDbCommand[/size][/size]

[/color][/size][size=2][color=#0000ff]Const[/color][/size][size=2] sConnection [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=HelpdeskApp;Data Source=MARK"[/size]
[size=2]
objConn = New OleDbConnection(sConnection)[/size]
[size=2]objCmd = New OleDbCommand(sSQL,objConn)
[/size][size=2]
sSQL = "INSERT INTO STAFF (First Name, Last Name, Job Title, Username, Password, StaffID) VALUES (?, ?, ?, ?, ?, 1)"
 
[size=2]objCmd.Parameters.Add("@sFN",sFN = txtFirstName.Text)[/size]
[size=2]objCmd.Parameters.Add("@sLN",sJT = txtJobTitle.Text)
objCmd.Parameters.Add("@sUN",sUN = txtUserName.Text )
objCmd.Parameters.Add("@sPW",sPW = txtPassword.Text)[/size]
[/size][size=2][color=#0000ff]
[/color][/size]
[size=2] 
objConn.Open()

[/size][size=2][color=#0000ff]Try

[/color][/size][size=2]objCmd.ExecuteNonQuery()

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] myException [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Exception

Console.WriteLine(myException.Message)

[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try

[/color][/size][size=2]txtFirstName.Text = ""

txtLastName.Text = ""

txtJobTitle.Text = ""

txtUserName.Text = ""

txtPassword.Text = ""

txtConfirmPassword.Text = ""

[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub[/color][/size]

 

[jmcilhinney]

In your code you have assigned the variable sSQL to the Commandtext property of the new command before you assign the SQL statement to it. Strings are value types so, as a result, making changes to sSQL has no effect on the CommandText of the command object. They are two different objects, not two references to the same object.

Also, Tinbeard is quite correct to suggest parameters. In the context of your SQL statement, the variables you are using don't exist. I think what you were trying to achieve was this:

[size=2] sSQL = "INSERT INTO STAFF (First Name, Last Name, Job Title, Username, Password, StaffID) VALUES ('" & sFN & "', '" & sLN & "', '" & sJT & "', '" & sUN & "', '" & sPW & "', 1)"[/size]

which is acceptable, but the "proper" way to do this is with parameters.