Thursday, March 29, 2012

Error when updating database

Hi all,
I'm getting an error when updating my database. The connection is fine and the dropdown menu is good also so i think it's the way i've formed my sql string. Here's the line i think i'm having probs with:

SQLString = "UPDATE project_descriptions SET " & _
"lecturer_name = '" & Replace(LecturerName.Text,"'","''") & "'," & _
"project_title = '" & Replace(ProjectTitle.Text,"'","''") & "', " & _
"Project_description = '" & Replace(ProjectDescription.Text,"'","''") & "'," & _
" WHERE project_code = '" & ProjectCode.SelectedItem.Value & "'"

Can anyone rearrange it so i stop getting an error?

Here's my code. if it helps.

<%@. Import Namespace="System.Data.OleDb" %
<SCRIPT language="VB" runat="server"
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String

Sub Page_Load

If Not Page.IsPostBack Then

'-- Load drop-down list with item numbers
DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\finalyearproject2\projectallocation.mdb")
DBConnection.Open()
SQLString = "SELECT project_code FROM project_descriptions ORDER BY project_code"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ProjectCode.DataSource = DBReader
ProjectCode.DataTextField = "project_code"
ProjectCode.DataValueField = "project_code"
ProjectCode.DataBind()
DBReader.Close()
DBConnection.Close()

End If

End Sub

Sub SelectRecord (Src As Object, Args As EventArgs)

DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\finalyearproject2\projectallocation.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM project_descriptions WHERE " & _
"project_code = '" & ProjectCode.SelectedItem.Value & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DBReader.Read()
LecturerName.Text = DBReader("lecturer_name")
ProjectTitle.Text = DBReader("project_title")
ProjectDescription.Text = DBReader("project_description")
DBReader.Close()
DBConnection.Close()
UpdateButton.Visible = True

End Sub

Sub UpdateRecord (Src As Object, Args As EventArgs)

'-- CHECK FOR VALID RECORD --
Dim ValidRecord As Boolean = True

'-- Check for missing Project Title
If ProjectTitle.Text = "" Then
ProjectTitleMessage.Text = "Missing Project Title"
ValidRecord = False
End If

'-- Check for missing Lecturer Name
If LecturerName.Text = "" Then
LecturerNameMessage.Text = "Missing Lecturer Name"
ValidRecord = False
End If

'-- Check for missing Project Description
If ProjectDescription.Text = "" Then
ProjectDescriptionMessage.Text = "Missing Project Description"
ValidRecord = False
End If

If ValidRecord = True Then

'-- UPDATE A RECORD --
Try
DBConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\finalyearproject2\projectallocation.mdb")
DBConnection.Open()

SQLString = "UPDATE project_descriptions SET " "lecturer_name = '" & Replace(LecturerName.Text,"'","''") & "'," "project_title = '" & Replace(ProjectTitle.Text,"'","''") & "', " "Project_description = '" & Replace(ProjectDescription.Text,"'","''") & "'," " WHERE project_code = '" & ProjectCode.SelectedItem.Value & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery
DBConnection.Close()
UpdateMessage.Text = "Record updated"
Catch
UpdateMessage.Text = "Update problem. Record not changed. " & SQLString
End Try

End If

End Sub

</SCRIPT
<html>
<head
<style>
.head {font-family:arial; font-size:12pt; font-weight:bold; width:560px;
border:outset 1 #F0F0F0; padding:2pt; background-color:#990000;
color:#FFFFFF; text-align:center}
.box {font-family:arial; font-size:8pt; line-height:9pt; width:200px;
height:20px; overflow:auto}
.right {text-align:right}
.center {text-align:center}
table {font-family:arial; font-size:8pt; line-height:9pt; width:560px;
background-color:#F9F9F9; border-collapse:collapse}
th {font-family:arial; font-size:8pt; line-height:14pt;
font-weight:bold; text-align:center; vertical-align:bottom;
background-color:#990000; color:#FFFFFF}
td {font-family:arial; font-size:8pt; line-height:9pt;
vertical-align:top}
</style>
</head
<body>
<form runat="server"
<div class="head">Project Update</div
<table id="UpdateTable" border="1" rules="rows">
<tr>
<th> Project Code: </th>
<td><asp:DropDownList id="ProjectCode" runat="server"/>
<asp:Button Text="Select" OnClick="SelectRecord" runat="server"/>
</td>
<td></td>
</tr>
<tr>
<th> Lecturer Name: </th>
<td><asp:TextBox id="LecturerName" runat="server"
Columns="40"
MaxLength="50"/></td>
<td><asp:Label id="LecturerNameMessage" runat="server"
ForeColor="#FF0000"
EnableViewState="False"/></td>
</tr
<tr>
<th> Project Title: </th>
<td><asp:TextBox id="ProjectTitle" runat="server"
Columns="40"
MaxLength="50"/></td>
<td><asp:Label id="ProjectTitleMessage" runat="server"
ForeColor="#FF0000"
EnableViewState="False"/></td>
</tr
<tr>
<th> Project Description: </th>
<td><asp:TextBox id="ProjectDescription" runat="server"
TextMode="MultiLine"
Columns="45"
rows="3"/></td>
<td><asp:Label id="ProjectDescriptionMessage" runat="server"
ForeColor="#FF0000"
EnableViewState="False"/></td>
</tr
</asp:Panel
</table>
<br>
<asp:Button id="UpdateButton" runat="server"
Text="Update Record"
Visible="False"
OnClick="UpdateRecord"/>
<asp:Label id="UpdateMessage" runat="server"
ForeColor="#FF0000"
EnableViewState="False"/>
</form>
</body>
</html>Please, please use parameterized SQL statements, not SQL statements concatenated together with user-supplied data!! What you have now is exposing your application/server/network to SQL injection attacks.

See theServer Side Data Access QuickStart Tutorial, in particularInserting Data in a SQL Database. The techniques used here should be directly applicable to your situation.

Terri|||Thanks for the reply Terri, but in this case I have to do it this way (long story). Any ideas?|||You should post exact error messages -- these go a long way to solving the problem. I am reasonably sure the problem is that you have a comma before your WHERE.

It would be interesting to know why you have to do it this way. It is an ill-advised practice and I have to strongly urge you to use parameters instead.

Terri|||Got it workin, thanks a lot!!! This method is for an assignment. We have to do it both ways. Thanks againsql

No comments:

Post a Comment