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....)