Updating SQL DB from Web Form

rawky76

New member
Joined
May 28, 2005
Messages
1
Programming Experience
1-3
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

Member
Joined
May 30, 2005
Messages
16
Location
UK
Programming Experience
1-3
Just a suggestion, maybe try these changes to your code

VB.NET:
<%@ 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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,475
Location
Sydney, Australia
Programming Experience
10+
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:
VB.NET:
[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.
 
Top Bottom