I have spent a week and a half trying to figure this out. Can someone help me? Here is the error
Incorrect syntax near 'fn_salesrep_lookup_custzip'.
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: Incorrect syntax near 'fn_salesrep_lookup_custzip'.
[SqlException (0x80131904): Incorrect syntax near 'fn_salesrep_lookup_custzip'.]
System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection) +857626
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) +735238
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader() +84
ASP.findrep_aspx.fn_salesrep_lookup_custzip(Object strZipCode) +146
ASP.findrep_aspx.FindRep_Click(Object s, ImageClickEventArgs e) +34
System.Web.UI.WebControls.ImageButton.OnClick(Imag eClickEventArgs e) +105
System.Web.UI.WebControls.ImageButton.RaisePostBac kEvent(String eventArgument) +115
System.Web.UI.WebControls.ImageButton.System.Web.U I.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Here is my code
Sub FindRep_Click(s As Object, e As ImageClickEventArgs)
fn_salesrep_lookup_custzip(txtZipCode.Text)
End Sub
Function fn_salesrep_lookup_custzip(strZipCode)
Dim strConString as String
Dim conZip as SqlConnection
Dim cmdFindRep as SqlCommand
Dim parmName As SqlParameter
Dim dtrRep as SqlDataReader
strConString = ConfigurationSettings.AppSettings("constring")
conZip = New SqlConnection(strConString)
cmdFindRep = New SqlCommand("fn_salesrep_lookup_custzip", conZip)
cmdFindRep.Parameters.Add("@CustomerZip", strZipCode)
conZip.Open()
dtrRep = cmdFindRep.ExecuteReader()
dlstRep.DataSource = dtrRep
dlstRep.DataBind()
dtrRep.Close()
conZip.Close()
End Function
<asp:TextBox
ID="txtZipCode"
runat="server" />
<asp:RequiredFieldValidator
ControlToValidate="txtZipCode"
Text="<br>Required<br>"
runat="server" />
<asp:DataList
ID="dlstRep"
Runat="Server">
<ItemTemplate>
<table>
<tr>
<td align="right">Name:</td>
<td><%# Container.DataItem("Name")%></td>
</tr>
<tr>
<td align="right">Address:</td>
<td><%# Container.DataItem("Address")%></td>
</tr>
<tr>
<td align="right">City:</td>
<td><%# Container.DataItem("City")%></td>
</tr>
<tr>
<td align="right">State:</td>
<td><%# Container.DataItem("State")%></td>
</tr>
<tr>
<td align="right">Zip Code:</td>
<td><%# Container.DataItem("Zip")%></td>
</tr>
<tr>
<td align="right">Phone Number:</td>
<td><%# Container.DataItem("Phone")%></td>
</tr>
<tr>
<td align="right">Email:</td>
<td><%# Container.DataItem("email")%></td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
This is a function that someone else put together
CREATE FUNCTION dbo.fn_salesrep_lookup_custzip(@CustomerZip CHAR(6))
RETURNS @retSalesRep TABLE
(
-- Columns returned by the function
Name nvarchar(50) PRIMARY KEY NOT NULL
,Address nvarchar(50) NULL
,City nvarchar(50) NULL
,State nvarchar(50) NULL
,Zip nvarchar(50) NULL
,Phone nvarchar(50) NULL
,email nvarchar(50) NULL
)
AS
BEGIN
DECLARE
@Name nvarchar(50)
,@Address nvarchar(50)
,@City nvarchar(50)
,@State nvarchar(50)
,@Zip nvarchar(50)
,@Phone nvarchar(50)
,@email nvarchar(50);
-- Get contact information
SELECT
@Name =LTRIM(RTRIM(T1.[SM-NAME]))
,@Address =LTRIM(RTRIM(T1.[SM-ADDRESS1]))+
CASE
WHEN ISNULL(T1.[SM-ADDRESS2],'')='' THEN ''
ELSE ', '+LTRIM(RTRIM(T1.[SM-ADDRESS2]))
END
,@City =LTRIM(RTRIM(T1.[SM-CITY]))
,@State =LTRIM(RTRIM(T1.[SM-STATE]))
,@Zip =CASE
WHEN LEN(LTRIM(RTRIM(T1.[SM-ZIP])))=6 AND LEFT(LTRIM(T1.[SM-ZIP]),1)='0' THEN RIGHT(RTRIM(T1.[SM-ZIP]),5)
ELSE LTRIM(RTRIM(T1.[SM-ZIP]))
END
,@Phone =LTRIM(RTRIM(T1.[SM-PHONE]))
,@email ='email not yet available'
FROM dbo.SalesRep T1
INNER JOIN dbo.SalesRepCustomerZip T2 ON T1.[SM-NBR]=T2.[SM-NBR]
WHERE
CASE
WHEN LEN(LTRIM(RTRIM(T2.ZIP)))=6 AND LEN(LTRIM(RTRIM(@CustomerZip)))=6 THEN LTRIM(RTRIM(T2.ZIP))
WHEN LEN(LTRIM(RTRIM(T2.ZIP)))=6 AND LEFT(LTRIM(T2.ZIP),1)='0' THEN RIGHT(RTRIM(T2.ZIP),5)
ELSE LTRIM(RTRIM(T2.ZIP))
END
=@CustomerZip;
-- Return the information to the user
IF @CustomerZip IS NOT NULL AND @Name IS NOT NULL
BEGIN
INSERT @retSalesRep
SELECT @Name, @Address, @City, @State, @Zip, @Phone, @email;
END;
RETURN;
END;
GO
SQL Functions
