Cannot use " or ' in text box, when you run the save function, an insert query

[bkedersha]

Continuation of and " problem. The previous developer created and insert query for the save function. The function he created is below the error.

Line 1: Incorrect syntax near uuu. Unclosed quotation mark before the character string .
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near uuu. Unclosed quotation mark before the character string .

Source Error:


Line 136: dim Cmd as new SQLCommand(strSQL,conn)
Line 137: Cmd.connection.open()
Line 138: Cmd.ExecuteNonQuery()
Line 139: Cmd.connection.close()
Line 140:


Source File: D:\GrantManagementWeb\GrantAddNew.aspx Line: 138

Stack Trace:


[SqlException: Line 1: Incorrect syntax near uuu.
Unclosed quotation mark before the character string .]
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +180
ASP.GrantAddNew_aspx.ExeStatement(Object strSQL) in D:\GrantManagementWeb\GrantAddNew.aspx:138
ASP.GrantAddNew_aspx.dataSave_onClick(Object Sender, EventArgs e) in D:\GrantManagementWeb\GrantAddNew.aspx:128
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

Sub dataSave_onClick(Sender as Object, e as EventArgs)
Insert New Data
dim i as integer

dim booleanGO as boolean=true

if not booleanGo then
exit sub
end if

dim strSQL as string
strSql = strSQL & "InsertNewGrant @GrantNumber =" & GrantNumber.text &", "
strSQL = strSQL & "@GrantProjectName =" & ProjectName.text & ","
strSQL = strSQL & "@CountryId =" & Country.SelectedItem.value & ","
strSQL = strSQL & "@Description = null,"
strSQL = strSQL & "@CreateUserId = 0, "
strSQL = strSQL & "@UpdateUserId = 0, "
strSQL = strSQL & "@ObligationDate =" & ObligationDate.SelectedDate.ToShortDateString & ", "
strSQL = strSQL & "@OrigionalExpDate=" & CurrentExpirationDate.SelectedDate.ToShortDateString & ", "
strSQL = strSQL & "@CurrentExpDate =" & CurrentExpirationDate.SelectedDate.ToShortDateString & ", "
strSQL = strSQL & "@Terminated =0,"
strSQL = strSQL & "@Suspended =0, "
strSQL = strSQL & "@Locked =0, "
strSQL = strSQL & "@LockedByUserId =0, "
strSQL = strSQL & "@ACTNumber= null, "
strSQL = strSQL & "@GranteeName =" & GranteeName.text & ", "
strSQL = strSQL & "@GranteeAddress1 =" & AddressLine1.text & ","
strSQL = strSQL & "@GranteeAddress2 =" & AddressLine2.text & ","
strSQL = strSQL & "@GranteeAddress3 =" & AddressLine3.text & ""
strSQL = strSQL & "@ProgramId =null"


ExeStatement(strSQL)
response.write("RecordUpdated")
response.redirect("GrantManagementWelcome.aspx")

End Sub

function ExeStatement(strSQL)

dim Cmd as new SQLCommand(strSQL,conn)
Cmd.connection.open()
Cmd.ExecuteNonQuery()
Cmd.connection.close()

end function


</script>

 

[PlausiblyDamp]

You would probably better off using stored procedures or even parameterised queries rather than just concatenating strings together, as well as removing this problem it also protects you against certain forms of security exploits.
Search these forums and you will find several examples of how to do them.

 

[Joe Mamma]

is it too late to stop payment on his paycheck????

[indent]dim Cmd as new SQLCommand("InsertNewGrant",conn)

 
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@GrantNumber", GrantNumber.Text.Trim())
cmd.Parameters.Add("@GrantProjectName", ProjectName.Text.Trim())
cmd.Parameters.Add("@CountryId", Country.SelectedItem.Value)
cmd.Parameters.Add("@Description", System.DBNull)
cmd.Parameters.Add("@CreateUserId", 0)
cmd.Parameters.Add("@UpdateUserId", 0)
cmd.Parameters.Add("@ObligationDate", ObligationDate.SelectedDate)
cmd.Parameters.Add("@OrigionalExpDate", CurrentExpirationDate.SelectedDate)
cmd.Parameters.Add("@CurrentExpDate", CurrentExpirationDate.SelectedDate)
cmd.Parameters.Add("@Terminated", 0)
cmd.Parameters.Add("@Suspended", 0)
cmd.Parameters.Add("@Locked", 0)
cmd.Parameters.Add("@LockedByUserId", 0)
cmd.Parameters.Add("@ACTNumber", System.DBNull)
cmd.Parameters.Add("@GranteeName", GranteeName.Text.Trim())
cmd.Parameters.Add("@GranteeAddress1", AddressLine1.Trim())
cmd.Parameters.Add("@GranteeAddress2", AddressLine2.Trim())
cmd.Parameters.Add("@GranteeAddress3", AddressLine3.Trim())
Cmd.connection.open()
Cmd.ExecuteNonQuery()
Cmd.connection.close()
 

[/indent]

 

[trend]

is it too late to stop payment on his paycheck????

[indent]dim Cmd as new SQLCommand("InsertNewGrant",conn)

 
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@GrantNumber", GrantNumber.Text.Trim())
cmd.Parameters.Add("@GrantProjectName", ProjectName.Text.Trim())
cmd.Parameters.Add("@CountryId", Country.SelectedItem.Value)
cmd.Parameters.Add("@Description", System.DBNull)
cmd.Parameters.Add("@CreateUserId", 0)
cmd.Parameters.Add("@UpdateUserId", 0)
cmd.Parameters.Add("@ObligationDate", ObligationDate.SelectedDate)
cmd.Parameters.Add("@OrigionalExpDate", CurrentExpirationDate.SelectedDate)
cmd.Parameters.Add("@CurrentExpDate", CurrentExpirationDate.SelectedDate)
cmd.Parameters.Add("@Terminated", 0)
cmd.Parameters.Add("@Suspended", 0)
cmd.Parameters.Add("@Locked", 0)
cmd.Parameters.Add("@LockedByUserId", 0)
cmd.Parameters.Add("@ACTNumber", System.DBNull)
cmd.Parameters.Add("@GranteeName", GranteeName.Text.Trim())
cmd.Parameters.Add("@GranteeAddress1", AddressLine1.Trim())
cmd.Parameters.Add("@GranteeAddress2", AddressLine2.Trim())
cmd.Parameters.Add("@GranteeAddress3", AddressLine3.Trim())
Cmd.connection.open()
Cmd.ExecuteNonQuery()
Cmd.connection.close()
 

[/indent]

Heh, Joe Mamma, are you suprised at how many people try to parameratize queries? (For others, he justhelped me learn this lesson..granted, he had to tell me how to do this 4x before i got it....)