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
