Substring Function Requires 3 Arguments

Aug 30, 2007

Substring ('(' + left(@phone,3) + ')') + substring(@phone,4,3) + '-' + substring(@phone,7,4) + 'x' + right(@phone,4) getting an error on this code help please.

View 2 Replies


ADVERTISEMENT

Argument Not Specified For Parameter 'arguments' Of Public Function Select(arguments As System.Web.DatasourceSelect Arguments As Collections Ienumerable

Mar 25, 2007

I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
 I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
 Function GetCategoryName(ByVal ID As Integer) As String          sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input          sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3          sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
 
Help I have not got much more hair to loose
Thanks Steve

View 5 Replies View Related

SQL Problem (The MAX Function Requires 1 Argument(s).)

Mar 9, 2008

Hey Guys,
I'm having problems with my SQL statement and Im wondering if anyone could help?  My code is below.
The Label6 is filled up using a previous SQL Statement, and when Ive had a fiddle around it is using the KnowledgeID It works in reading in the date, it just doesnt appear to want to work for the second statement as I am getting the error shown in the subject.
Thanks in advance =)
C# Codeprotected void Button2_Click(object sender, EventArgs e)
{KnowledgeID = Convert.ToInt32(Label5.Text);
string path = Server.MapPath(" ") + "\X_Drive\";FileLocation = path + Convert.ToString(FileUpload1.FileName);SqlConnection myConnection = new SqlConnection(myConnectionString);
 SqlCommand myCommand2 = new SqlCommand("select * from Knowledge WHERE KnowledgeID = '" + KnowledgeID + "'", myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand2.ExecuteReader();if (myReader.HasRows)
{while (myReader.Read())
{Add = myReader["DateAdded"].ToString();
}
myReader.Close();
}
else
{
myConnection.Close();
}
 SqlTransaction trans = myConnection.BeginTransaction();
{
try
{
 SqlCommand myCommand3 = new SqlCommand("Select ISNULL(MAX(Version,0)+1 FROM Archive WHERE KnowledgeID = "+KnowledgeID+"",myConnection);
myCommand3.Transaction=trans;int nextVersion =(int)myCommand3.ExecuteScalar();if (File != null)
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, Location, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@File,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}
else
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}myCommand3.Parameters.AddWithValue("@FixName", TextBox1.Text);
myCommand3.Parameters.AddWithValue("@Description",TextBox2.Text);myCommand3.Parameters.AddWithValue("@File",FileLocation);
myCommand3.Parameters.AddWithValue("@Add",Add);myCommand3.Parameters.AddWithValue("@AddDate",AddDate);myCommand3.Parameters.AddWithValue("@Versions",nextVersion);
myCommand3.ExecuteNonQuery();
trans.Commit();
}catch (Exception ex)
{
//TextBox2.Text = ex.Message;
trans.Rollback();
myConnection.Close();
}
//}

View 5 Replies View Related

Procedure Or Function '' Has Too Many Arguments Specified.

Feb 2, 2007

Im getting this error when looping through a gridview. Im trying to save each row into the database. The weird thing is that it saves the first row, but errors out on the second.If I only add one item to the gridview, it works. The second row is screwing it up somehow.  Any ideas?  ------------------------------------------------------------------------------------------------------------------------------------------------------------------Some of my code to get an idea of how im doing it:(This
is for a shopping cart. It displays the products in the shopping cart.
If the order is approved by the CC processing company, each product
entry is saved in the DB. The gridview is being populated by my
ShoppingCart Profile Object.)   SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["rdevie"].ConnectionString);
SqlCommand objCmd = new SqlCommand("InsertOrderDetails", objConn);
objCmd.CommandType = CommandType.StoredProcedure;

objConn.Open();

foreach (GridViewRow row in gvOrderDetails.Rows)
{
objCmd.Parameters.Add("@idOrder", SqlDbType.Int).Value = orderNum;
objCmd.Parameters.Add("@fldName", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblName")).Text;
objCmd.Parameters.Add("@fldUnitPrice", SqlDbType.Money).Value = ((Label)row.FindControl("lblUnitPrice")).Text;
objCmd.Parameters.Add("@fldQuantity", SqlDbType.Int).Value = Convert.ToInt32(((Label)row.FindControl("lblQuantity")).Text);
if (!String.IsNullOrEmpty(((Label)row.FindControl("lblSku")).Text))
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSku")).Text;
else
objCmd.Parameters.Add("@fldSku", SqlDbType.NVarChar).Value = DBNull.Value;
objCmd.Parameters.Add("@fldSize", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblSize")).Text;
objCmd.Parameters.Add("@fldLogo", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblLogo")).Text;
objCmd.Parameters.Add("@fldPlacement", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblPlacement")).Text;
objCmd.Parameters.Add("@fldColor", SqlDbType.NVarChar).Value = ((Label)row.FindControl("lblColor")).Text;

objCmd.ExecuteNonQuery();
}

objCmd.Dispose();
objConn.Close();
objConn.Dispose(); 

View 5 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Oct 3, 2007

Hi,I have a simple stored procedure that basically SELECT some data from database. I am calling this stored procedure using a sqldatasource. The first time I try it, it works perfectly fine. But I get the error message "Procedure or function has too many arguments specified" the second time I try to search for thing. This is the code that I have that called the stored procedure: Session("SearchItem") = txtSearch.Text.Trim SqlDataSource1.SelectCommand = "_MyStoredProcedure" SqlDataSource1.SelectParameters.Add("SearchItem", Session("SearchItem")) SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure Gridview1.DataSourceID = "SqlDataSource1" Gridview1.DataBind() The following is the code in the stored procedure: CREATE PROCEDURE [dbo].[_MyStoredProcedure] (    @SearchItem nvarchar(255) = NULL)ASSELECT Field1, Field2, Field3 FROM Registration WHERE((@SearchItem IS NULL) OR (Field1 LIKE '%' + @SearchItem + '%') OR (Field2 LIKE '%' + @SearchItem + '%'))GO Please help.Stan 

View 6 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Jan 19, 2006

I have two tables: Person (member_no, name) and Participant (participant, member_no) member_no is the primary key in Person.The stored procedure is:CREATE PROCEDURE dbo.InsertRecs@member_no char(10),@name char(10),@participant char(10)ASbegininsert into person (member_no, name)values (@member_no, @name)select @member_no = @@Identityinsert into participant (participant, member_no)values (@participant, @member_no)endGOWhen I run this via the DetailsView control and try to insert a record I always get "Procedure or function InsertRecs has too many arguments specified"If I remove the second table from the stored procedure it works fine.Does anyone have any ideas?

View 9 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Feb 18, 2006

Hello All,
I am having a very strange problem which I cannot figure out. I am using the ASP.NET 2.0 SqlDataSource control to insert a news item into a SQL Express database. When inserting the a message I receive the following error: Procedure or function has too many arguments specified
However according to me every thing adds up. Can anyone please help me figure out what is wrong? Here is the code:
SqlDataSource:    <asp:SqlDataSource ID="sqlNews" runat="server" ConnectionString="<%$ ConnectionStrings:FlevoOptiekDB %>"        SelectCommandType="StoredProcedure" SelectCommand="dbo.usp_News_ByID"        InsertCommandType="StoredProcedure" InsertCommand="dbo.usp_News_Insert"        UpdateCommandType="StoredProcedure" UpdateCommand="dbo.usp_News_Update"        DeleteCommandType="StoredProcedure" DeleteCommand="dbo.usp_News_Delete"         OldValuesParameterFormatString="{0}">        <SelectParameters>            <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" />        </SelectParameters>        <UpdateParameters>            <asp:Parameter Name="p_dtmPublished" Type="DateTime" />            <asp:Parameter Name="p_sTitle" />            <asp:Parameter Name="p_sDescription" />            <asp:Parameter Name="p_sStaticUrl" />            <asp:Parameter Name="p_iPhotoID" />            <asp:Parameter Name="p_iNewsID" />            <asp:Parameter Name="p_iAlbumID" />        </UpdateParameters>        <InsertParameters>            <asp:Parameter Name="p_dtmPublished" Type="DateTime" />            <asp:Parameter Name="p_sTitle" />            <asp:Parameter Name="p_sDescription" />            <asp:Parameter Name="p_sStaticUrl" />            <asp:Parameter Name="p_iPhotoID" />            <asp:Parameter Name="p_iAlbumID" />        </InsertParameters>        <DeleteParameters>            <asp:QueryStringParameter Name="p_iNewsID" QueryStringField="news_ID" />        </DeleteParameters>    </asp:SqlDataSource>Stored procedure:ALTER PROCEDURE dbo.usp_News_Insert( @p_sTitle VARCHAR(256), @p_sDescription TEXT, @p_sStaticUrl VARCHAR(256), @p_dtmPublished DATETIME, @p_iPhotoID INT, @p_iAlbumID INT)AS /* SET NOCOUNT ON */  INSERT INTO dbo.tbNews(news_Title, news_Description, news_StaticUrl, news_Published, photo_ID, album_ID) VALUES(@p_sTitle, @p_sDescription, @p_sStaticUrl, @p_dtmPublished, @p_iPhotoID, @p_iAlbumID)  RETURN
Thanks, Maurits

View 3 Replies View Related

Procedure Or Function Has Too Many Arguments Specified

Mar 7, 2006

I have tried to introduce information in  webform with sp and she leaves the following error

View 1 Replies View Related

Procedure Or Function InsertUpdateArtist_Tracks Has Too Many Arguments Specified.

Mar 22, 2007

Hi
While coding with ASP.NET 2.0 I came across this error "Procedure or function InsertUpdateArtist_Tracks has too many arguments specified."
Its quite frustrating because I dont know what arguments are being referred to because I've clearly assigned the correct parameters with their correct values to this Stored procedure :InsertUpdateArtist_Tracks
Please can some help me with this? is there something fundamentality wrong with my code (Below) or is this a know Microsoft stuff up?
or by the way I'm using SQL Server 2005 and ASP.NET 2.0
Below is my sample code:
.............. 
'Insert or Update Artist_tracks table
Response.Write("<br><b>Inserting or Updating Artist_tracks table</b>")
AT_Title = SearchArtistsResults(i).title
AT_Meta_ID = SearchArtistsResults(i).meta_id
AT_AA_ID = AA_ID
Dim ParamTitle As SqlParameter
Dim ParamMeta As SqlParameter
Dim ParamAA_ID As SqlParameter
dbCommand.CommandText = "InsertUpdateArtist_Tracks"
dbCommand.CommandType = Data.CommandType.StoredProcedure
dbCommand.Connection = conn
ParamTitle = dbCommand.Parameters.Add(New SqlParameter("@AT_Title", Data.SqlDbType.VarChar, 50))
ParamTitle.Value = AT_Title
ParamTitle.Direction = Data.ParameterDirection.Input
Response.Write("<br>Sent through this Title =" & AT_Title)
ParamMeta = dbCommand.Parameters.Add(New SqlParameter("@AT_Meta_ID", Data.SqlDbType.Int))
ParamMeta.Value = AT_Meta_ID
ParamMeta.Direction = Data.ParameterDirection.Input
Response.Write("<br>Sent through this Meta_id =" & AT_Meta_ID)
ParamAA_ID = dbCommand.Parameters.Add(New SqlParameter("@AT_AA_ID", Data.SqlDbType.Int))
ParamAA_ID.Value = AT_AA_ID
ParamAA_ID.Direction = Data.ParameterDirection.Input
Response.Write("<br>Sent through this AT_AA_ID =" & AT_AA_ID)
conn.Open()
dbCommand.ExecuteNonQuery()
conn.Close()
..................
 
Thanks in Advance

View 2 Replies View Related

Procedure Or Function DeleteSender Has Too Many Arguments Specified.

Sep 19, 2007

 I'm Deleting my record in the database using stored procedure and I'm always getting this error :Procedure or function DeleteSender has too many arguments specified. same as I insert record... here's my stored proc for delete:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[DeleteSender]@RecNo int ASDELETE FROM [Sender] WHERE [RecNo] = @RecNo  for insert:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InsertSender](@SenderID nvarchar(50), @SenderLastName nvarchar(50), @SenderFirstName nvarchar(50), @SenderAddress nvarchar(50), @SenderCity nvarchar(50), @SenderState nvarchar(25), @SenderZip nvarchar(10), @SenderTel nvarchar(25), @FS int, @TB int, @Status nvarchar(10), @EndBalance money)ASINSERT INTO [Sender] ([SenderID], [SenderLastName], [SenderFirstName], [SenderAddress], [SenderCity], [SenderState], [SenderZip], [SenderTel], [FS], [TB], [Status], [EndBalance]) VALUES (@SenderID, @SenderLastName, @SenderFirstName, @SenderAddress, @SenderCity, @SenderState, @SenderZip, @SenderTel, @FS, @TB, @Status, @EndBalance) aspx:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %><%@ Register src="FormControl.ascx" TagName="FormControl" TagPrefix="uc1" %><%@ Register Assembly="RadGrid.Net2" Namespace="Telerik.WebControls" TagPrefix="radG" %><%@ Register Assembly="RadComboBox.Net2" Namespace="Telerik.WebControls" TagPrefix="radC" %><%@ Register Assembly="RadInput.Net2" Namespace="Telerik.WebControls" TagPrefix="radI" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">        <title>Sender</title>    <link href="sender.css" rel="stylesheet" type="text/css" /></head><body>    <form id="form1" runat="server">    <div class="wrapper">    <div class="banner">        <radG:RadGrid ID="rgSender" runat="server" GridLines="None" DataSourceID="sqlDataSource1" EnableAJAX="True" ShowStatusBar="True"         AllowAutomaticInserts="True" AllowAutomaticUpdates="True" AllowAutomaticDeletes="True" Skin="3D" AutoGenerateColumns="False">            <MasterTableView CommandItemDisplay="Top" DataKeyNames="RecNo" DataSourceID="sqlDataSource1">                <ExpandCollapseColumn Visible="False">                    <HeaderStyle Width="19px" />                </ExpandCollapseColumn>                <RowIndicatorColumn Visible="False">                    <HeaderStyle Width="20px" />                </RowIndicatorColumn>                  <EditFormSettings EditFormType="Template">                                            <FormTemplate>                      <table border="0">                         <tr>                         <td> <asp:Label ID="Label1" runat="server" Text="Record No.:" Visible='<%# rgSender.EditIndexes.Count > 0 %>'></asp:Label></td>                         <td> <radI:RadTextBox ID="rtbRecNo" runat="server" Text='<%# Bind("RecNo") %>' Visible='<%# rgSender.EditIndexes.Count > 0 %>'>                            </radI:RadTextBox></td>                         <td>  <asp:Label ID="Label8" runat="server" Text="Zip Code:"></asp:Label></td>                         <td> <radI:RadTextBox ID="rtbZip" runat="server" Text='<%# Bind("SenderZip") %>'>                            </radI:RadTextBox></td>                         </tr>                                                  <tr>                         <td> <asp:Label ID="Label2" runat="server" Text="Sender ID:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbSenderID" runat="server" Text='<%# Bind("SenderID") %>'>                            </radI:RadTextBox></td>                         <td>  <asp:Label ID="Label9" runat="server" Text="Telephone #:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbTel" runat="server" Text='<%# Bind("SenderTel") %>'>                            </radI:RadTextBox></td>                         </tr>                                                  <tr>                         <td><asp:Label ID="Label3" runat="server" Text="First Name:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbFName" runat="server" Text='<%# Bind("SenderFirstName") %>'>                            </radI:RadTextBox></td>                         <td> <asp:Label ID="Label10" runat="server" Text="Total Free Sent:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbFS" runat="server" Text='<%# Bind("FS") %>'>                            </radI:RadTextBox></td>                         </tr>                                                  <tr>                         <td><asp:Label ID="Label4" runat="server" Text="Last Name:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbLName" runat="server" Text='<%# Bind("SenderLastName") %>'>                            </radI:RadTextBox></td>                         <td><asp:Label ID="Label11" runat="server" Text="Total Box Sent:"></asp:Label></td>                         <td> <radI:RadTextBox ID="rtbBS" runat="server" Text='<%# Bind("TB") %>'>                            </radI:RadTextBox></td>                         </tr>                                                  <tr>                         <td> <asp:Label ID="Label5" runat="server" Text="Address:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbAddress" runat="server" Text='<%# Bind("SenderAddress") %>'>                            </radI:RadTextBox></td>                         <td><asp:Label ID="Label12" runat="server" Text="Status:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbStatus" runat="server" Text='<%# Bind("Status") %>'>                            </radI:RadTextBox></td>                         </tr>                                                  <tr>                         <td><asp:Label ID="Label6" runat="server" Text="City:"> </asp:Label></td>                         <td> <radI:RadTextBox ID="rtbCity" runat="server" Text='<%# Bind("SenderCity") %>'>                            </radI:RadTextBox></td>                         <td><asp:Label ID="Label13" runat="server" Text="End Balance:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbEndBalance" runat="server" Text='<%# Bind("EndBalance") %>'>                            </radI:RadTextBox></td>                         </tr>                                                   <tr>                         <td> <asp:Label ID="Label7" runat="server" Text="State:"></asp:Label></td>                         <td><radI:RadTextBox ID="rtbState" runat="server" Text='<%# Bind("SenderState") %>'>                            </radI:RadTextBox> </td>                         <td>                                                   <asp:Button ID="btnUpdate" Text='<%# IIf(CType(Container,GridItem).OwnerTableView.IsItemInserted,"Insert","Update") %>'                                                                runat="server" CommandName='<%# IIf(CType(Container,GridItem).OwnerTableView.IsItemInserted,"PerformInsert","Update") %>'></asp:Button>                                                                &nbsp;                        <asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False" CommandName="Cancel"></asp:Button></td>                         </tr>                         </table>                    </FormTemplate>                </EditFormSettings>                <Columns>                    <radG:GridEditCommandColumn ButtonType="ImageButton"/>                                   <radG:GridTemplateColumn HeaderText="Sender ID" UniqueName="TCSenderID">                <ItemTemplate>                    <asp:Label ID="lblSenderID" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderID") %>'></asp:Label>                                    </ItemTemplate>              </radG:GridTemplateColumn>                             <radG:GridTemplateColumn HeaderText="First Name" UniqueName="TCSenderFName">                <ItemTemplate>                    <asp:Label ID="lblSenderFName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderFirstName") %>'></asp:Label>                </ItemTemplate>              </radG:GridTemplateColumn>                            <radG:GridTemplateColumn HeaderText="Last Name" UniqueName="TCSenderLName">                <ItemTemplate>                    <asp:Label ID="lblSenderLName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "SenderLastName") %>'></asp:Label>                </ItemTemplate>              </radG:GridTemplateColumn>               <radG:GridButtonColumn UniqueName="ButtonColumn" ConfirmText="Are you sure?" Text="Delete" CommandName="Delete" ButtonType="ImageButton" ImageUrl="RadControls/Grid/Skins/Default/Delete.gif" />              </Columns>                          </MasterTableView>        </radG:RadGrid></div>        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConString_TO_Cargo %>"            SelectCommandType="StoredProcedure"            SelectCommand="sp_SelectSender"             DeleteCommandType="StoredProcedure"            DeleteCommand="DeleteSender"             InserCommandType="StoredProcedure"            InsertCommand="InsertSender"             OldValuesParameterFormatString="original_{0}"             UpdateCommand="UPDATE [Sender] SET [SenderID] = @SenderID, [SenderLastName] = @SenderLastName, [SenderFirstName] = @SenderFirstName, [SenderAddress] = @SenderAddress, [SenderCity] = @SenderCity, [SenderState] = @SenderState, [SenderZip] = @SenderZip, [SenderTel] = @SenderTel, [FS] = @FS, [TB] = @TB, [Status] = @Status, [EndBalance] = @EndBalance WHERE [RecNo] = @original_RecNo AND [SenderID] = @original_SenderID AND [SenderLastName] = @original_SenderLastName AND [SenderFirstName] = @original_SenderFirstName AND [SenderAddress] = @original_SenderAddress AND [SenderCity] = @original_SenderCity AND [SenderState] = @original_SenderState AND [SenderZip] = @original_SenderZip AND [SenderTel] = @original_SenderTel AND [FS] = @original_FS AND [TB] = @original_TB AND [Status] = @original_Status AND [EndBalance] = @original_EndBalance">            <DeleteParameters>            <asp:Parameter Name="RecNo" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="SenderID" Type="String" />                <asp:Parameter Name="SenderLastName" Type="String" />                <asp:Parameter Name="SenderFirstName" Type="String" />                <asp:Parameter Name="SenderAddress" Type="String" />                <asp:Parameter Name="SenderCity" Type="String" />                <asp:Parameter Name="SenderState" Type="String" />                <asp:Parameter Name="SenderZip" Type="String" />                <asp:Parameter Name="SenderTel" Type="String" />                <asp:Parameter Name="FS" Type="Int32" />                <asp:Parameter Name="TB" Type="Int32" />                <asp:Parameter Name="Status" Type="String" />                <asp:Parameter Name="EndBalance" Type="Decimal" />                <asp:Parameter Name="original_RecNo" Type="Int32" />                <asp:Parameter Name="original_SenderID" Type="String" />                <asp:Parameter Name="original_SenderLastName" Type="String" />                <asp:Parameter Name="original_SenderFirstName" Type="String" />                <asp:Parameter Name="original_SenderAddress" Type="String" />                <asp:Parameter Name="original_SenderCity" Type="String" />                <asp:Parameter Name="original_SenderState" Type="String" />                <asp:Parameter Name="original_SenderZip" Type="String" />                <asp:Parameter Name="original_SenderTel" Type="String" />                <asp:Parameter Name="original_FS" Type="Int32" />                <asp:Parameter Name="original_TB" Type="Int32" />                <asp:Parameter Name="original_Status" Type="String" />                <asp:Parameter Name="original_EndBalance" Type="Decimal" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="SenderID" Type="String" />                <asp:Parameter Name="SenderLastName" Type="String" />                <asp:Parameter Name="SenderFirstName" Type="String" />                <asp:Parameter Name="SenderAddress" Type="String" />                <asp:Parameter Name="SenderCity" Type="String" />                <asp:Parameter Name="SenderState" Type="String" />                <asp:Parameter Name="SenderZip" Type="String" />                <asp:Parameter Name="SenderTel" Type="String" />                <asp:Parameter Name="FS" Type="Int32" />                <asp:Parameter Name="TB" Type="Int32" />                <asp:Parameter Name="Status" Type="String" />                <asp:Parameter Name="EndBalance" Type="Decimal" />            </InsertParameters>        </asp:SqlDataSource>        <br />        <radG:RadGrid ID="rgSenderDetails" runat="server" AutoGenerateColumns="False" GridLines="None">            <MasterTableView>                <ExpandCollapseColumn Visible="False">                    <HeaderStyle Width="19px" />                </ExpandCollapseColumn>                <RowIndicatorColumn Visible="False">                    <HeaderStyle Width="20px" />                </RowIndicatorColumn>            </MasterTableView>        </radG:RadGrid><br />        </div>    </form></body></html> hope you can help me guys/// stings... 

View 1 Replies View Related

Procedure Or Function SavePhotoHotelLang Has Too Many Arguments Specified.

Sep 26, 2007

Hi all I having suffering from a strange behavior when trying to update. I receive the following error :
 
Here is my code: Procedure or function SavePhotoHotelLang has too many arguments specified. 
 
<asp:SqlDataSource ID="SqlDataSourceHotelPhotoList" runat="server" ConnectionString="<%$ ConnectionStrings:smileDBConnectionString %>"SelectCommand="SELECT phl.PhotoHotelLangId, ph.smallfilepath, ph.photorank, pht.Type, phl.name, phl.description, ph.PhotoHotelId FROM datPhotoHotel ph
INNER JOIN datPhotoHotelType pht ON ph.PhotoHotelTypeId = pht.PhotoHotelTypeId
LEFT OUTER JOIN datPhotoHotelLang phl on ph.PhotoHotelId = phl.PhotoHotelId WHERE HotelId = @HotelId and ( LanguageId = @languageId or LanguageId is null)"
UpdateCommand="SavePhotoHotelLang" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="HotelId" QueryStringField="HotelId" Type="int32" />
<asp:ControlParameter Name="languageId" ControlID="ddlLanguageForGV" PropertyName="SelectedValue" Type="int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="name"/>
<asp:Parameter Name="Description" />
<asp:ControlParameter Name="languageId" ControlID="ddlLanguageForGV" PropertyName="SelectedValue"/>
<asp:Parameter Name="PhotoHotelLangId" />
<asp:Parameter Name="PhotoHotelId" />
</UpdateParameters>
</asp:SqlDataSource>
 <Grids:BulkEditGridView
ID="GvHotelPhotoList" runat="server"
DataSourceID="SqlDataSourceHotelPhotoList" AutoGenerateColumns="false" SaveButtonID="saveBtn"
DataKeyNames="PhotoHotelLangId, PhotoHotelId">
<Columns>
<asp:TemplateField>
<itemtemplate>
<asp:Image id="smallimage" runat="server" ImageUrl='<%# Eval("smallFilePath") %>'></asp:Image>
</itemtemplate>
<Edititemtemplate>
<asp:Image id="smallimageedit" runat="server" ImageUrl='<%# Eval("smallFilePath") %>'></asp:Image>
</Edititemtemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name"/>
<asp:BoundField DataField="Description" FooterText="Description"/>
<asp:BoundField DataField="photorank" HeaderText="Rank" ReadOnly="true"/>
<asp:BoundField DataField="Type" FooterText="Type" ReadOnly="true"/>
</Columns></Grids:BulkEditGridView>
">'>'> 
alter procedure SavePhotoHotelLang         @name varchar(100),                                        @Description varchar(500),                                        @languageId int,                                        @PhotoHotelLangId int,                                        @PhotoHotelId intasbegin
        if ( @PhotoHotelLangId is null )        begin
                        insert into dbo.datPhotoHotelLang (                        name,                        Description,                        languageId                        )                        values (@name,                        @Description,                        @languageId,                        @PhotoHotelId)        end        else        begin
                        update dbo.datPhotoHotelLang    set name = @name,                                                            Description = @Description                                                        where PhotoHotelLangId = @PhotoHotelLangId
        endend 

View 1 Replies View Related

Procedure Or Function StoredProcedure1 Has Too Many Arguments Specified

Dec 6, 2007

Hi,

I keep on getting the same error for my stored procedure:System.Data.SqlClient.SqlException: Procedure or function StoredProcedure1 has too many arguments specified.The procedure itself looks like this:@imie varchar(255),    @nazwisko varchar(255),    @email varchar(255),    @username varchar(255),    @password varchar(255),    @id_roli Int,    @PESEL varchar(11),    @ulica text,    @numer_domu int,    @numer_mieszkania int,    @kod_pocztowy varchar(7),    @miasto text,    @telefon text,    @id_kandydat int,    @id_kierunku int,    @id_stan int    ASBeginSet nocount on    Insert into Uzytkownik     (imie, nazwisko, email, username, password, id_roli, PESEL, ulica, numer_domu, numer_mieszkania, kod_pocztowy, miasto, telefon)    values (    @imie, @nazwisko, @email, @username, @password, @id_roli, @PESEL, @ulica, @numer_domu, @numer_mieszkania, @kod_pocztowy, @miasto, @telefon)        select @id_kandydat=@@IDENTITY        Insert into Kandydat_na_Kierunek     (id_kandydat, id_kierunku, id_stan)    Values    (@id_kandydat, @id_kierunku, @id_stan)    SET NOCOUNT OFF        ENDand the code that uses it:  SqlConnection con;               con = new SqlConnection("Data Source=ELF;Initial Catalog=logos;Integrated Security=True");        SqlCommand cmd = new SqlCommand("StoredProcedure1", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.Add(new SqlParameter("@imie", TextBox3.Text));        cmd.Parameters.Add(new SqlParameter("@nazwisko", TextBox4.Text));        cmd.Parameters.Add(new SqlParameter("@email", TextBox6.Text));        cmd.Parameters.Add(new SqlParameter("@username", Label2.Text));        cmd.Parameters.Add(new SqlParameter("@password", Label1.Text));        cmd.Parameters.Add(new SqlParameter("@id_roli", '1'));        cmd.Parameters.Add(new SqlParameter("@PESEL", TextBox5.Text));        cmd.Parameters.Add(new SqlParameter("@ulica", TextBox8.Text));        cmd.Parameters.Add(new SqlParameter("@numer_domu", TextBox9.Text));        cmd.Parameters.Add(new SqlParameter("@numer_mieszkania", TextBox10.Text));        cmd.Parameters.Add(new SqlParameter("@kod_pocztowy", TextBox11.Text));        cmd.Parameters.Add(new SqlParameter("@miasto", TextBox12.Text));        cmd.Parameters.Add(new SqlParameter("@telefon", TextBox13.Text));        if (DropDownList1.SelectedValue == "1")        {        }        else        {            cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList1.SelectedValue));            cmd.Parameters.Add(new SqlParameter("@id_stan", '1'));        }        if (DropDownList2.SelectedValue == "1")        {        }        else        {            cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList2.SelectedValue));            cmd.Parameters.Add(new SqlParameter("@id_stan", '1'));        }        if (DropDownList3.SelectedValue == "1")        {        }        else        {            cmd.Parameters.Add(new SqlParameter("@id_kierunku", DropDownList3.SelectedValue));            cmd.Parameters.Add(new SqlParameter("@id_stan", '1'));        }                con.Open();        cmd.ExecuteNonQuery();        con.Close();Could somebody explain what is wrong with this? I dpn't really unserstand the meaning of error. Any help will be apreaciated.Regards,N. 

View 7 Replies View Related

HELP!!! - Procedure Or Function SpInsertNewProduct Has Too Many Arguments Specified

Mar 3, 2008

Hi All,
 I'm getting this annoying message when I try to call my Sp on an insert. I've executed the SP straight from SQL Server 2005 Express and it works fine. but I think I have a problem how I'm calling it.
 The SP-------------------------------------------------------------------------
ALTER PROCEDURE dbo.spInsertNewProduct(
@productName nvarchar(50),
@productSummary text,
@productDesc text,
@productPhoto int,
@productFeaturelist text,
@productTestimonials text,
@productFile1 int,
@productFile1_Title nvarchar(50),
@productFile2 int,
@productFile1_Title nvarchar(50),
@NewProductID int OUTPUT)AS
-- inserts new product
INSERT INTO [tbl_products] ([productName], [productSummary], [productDesc], [productPhoto], [productFeaturelist], [productTestimonials], [productFile1], [productFile1_Title], [productFile2], [productFile2_Title])
VALUES (@productName, @productSummary, @productDesc, @productPhoto, @productFeaturelist, @productTestimonials, @productFile1, @productFile1_Title, @productFile2, @productFile2_Title);
-- Read the just-inserted productID into @NewProductID
SET @NewProductID = SCOPE_IDENTITY();
-------------------------------------------------------------------------
The DataSource-------------------------------------------------------------------------
<asp:SqlDataSource ID="dsproduct" runat="server" ConnectionString="<%$ ConnectionStrings:myConn %>"            InsertCommand="spInsertNewProduct" InsertCommandType="StoredProcedure">            <InsertParameters>                <asp:Parameter Name="productName" Type="String" />                <asp:Parameter Name="productSummary" Type="String" />                <asp:Parameter Name="productDesc" Type="String" />                <asp:Parameter Name="productPhoto" Type="String" />                <asp:Parameter Name="productFeaturelist" Type="String" />                <asp:Parameter Name="productTestimonials" Type="String" />                <asp:Parameter Name="productFile1" Type="String" />                <asp:Parameter Name="productFile1_Title" Type="String" />                <asp:Parameter Name="productFile2" Type="String" />                <asp:Parameter Name="productFile2_Title" Type="String" />                <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />            </InsertParameters>        </asp:SqlDataSource>-------------------------------------------------------------------------
 In code behind I grab params from front end dropdowns:-------------------------------------------------------------------------
    Sub InsertProduct(ByVal Src As Object, ByVal Args As DetailsViewInsertEventArgs)
        dsProduct.InsertParameters("productPhoto").DefaultValue = CType(dvEditProduct.FindControl("ddlProdImage"), DropDownList).SelectedValue
        dsProduct.InsertParameters("productFile1").DefaultValue = CType(dvEditProduct.FindControl("ddlProdFile1"), DropDownList).SelectedValue
        dsProduct.InsertParameters("productFile2").DefaultValue = CType(dvEditProduct.FindControl("ddlProdFile2"), DropDownList).SelectedValue
        lblStatus.Text = "Product has been added to the database"    End Sub-------------------------------------------------------------------------
I can't see how in the VB front end file how there can be too many params? Any help is greatly appreciated!

View 5 Replies View Related

Procedure Or Function &<stored_procedure_name&> Has Too Many Arguments Specified.

Mar 24, 2008

Thanks in advance,
I am getting the "Procedure or function JobsDb_Development_Update has too many arguments specified." error.  Below is the sp and the vwd generated sqldatasource, any suggestions?
Thanks
Nick
 
Update Stored Procedure: 
ALTER PROCEDURE [dbo].[JobsDb_Development_Update]
@sDevelopmentArea varchar(255), @sDevelopmentGoals text, @sDevelopmentPlans text, @sCurrentStatus text, @sFutureState text, @sDetermineFactor text, @bMentorRequested bit,@sMentorList text, @dtCheckPointDate datetime, @dtDateUpdated datetime, @iPlanPKID int
AS
UPDATE JobsDb_Development
SET DevelopmentArea = @sDevelopmentArea, DevelopmentGoals = @sDevelopmentGoals, DevelopmentPlans = @sDevelopmentPlans, CurrentStatus = @sCurrentStatus,
FutureState = @sFutureState, DetermineFactor = @sDetermineFactor, MentorRequested = @bMentorRequested, MentorList = @sMentorList, CheckPointDate = @dtCheckPointDate,
DateUpdated = @dtDateUpdated
WHERE (PlanPKID = @iPlanPKID)
 SQL Data Source:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyProviderConnectionString %>"
DeleteCommand="JobsDb_Development_Delete" DeleteCommandType="StoredProcedure"
InsertCommand="JobsDb_Development_Insert" InsertCommandType="StoredProcedure"
SelectCommand="JobsDb_Development_SelectOne"
SelectCommandType="StoredProcedure" UpdateCommand="JobsDb_Development_Update"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtUserName" Name="sUserName"
PropertyName="Text" Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="iPlanPKID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="sDevelopmentGoals" Type="String" />
<asp:Parameter Name="sDevelopmentPlans" Type="String" />
<asp:Parameter Name="sCurrentStatus" Type="String" />
<asp:Parameter Name="sFutureState" Type="String" />
<asp:Parameter Name="sDetermineFactor" Type="String" />
<asp:Parameter Name="bMentorRequested" Type="Boolean" />
<asp:Parameter Name="sMentorList" Type="String" />
<asp:Parameter Name="dtCheckPointDate" Type="DateTime" />
<asp:Parameter Name="dtDateUpdated" Type="DateTime" />
<asp:Parameter Name="iPlanPKID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="sUserName" Type="String" />
<asp:Parameter Name="sDevelopmentArea" Type="String" />
<asp:Parameter Name="sDevelopmentGoals" Type="String" />
<asp:Parameter Name="sDevelopmentPlans" Type="String" />
<asp:Parameter Name="sCurrentStatus" Type="String" />
<asp:Parameter Name="sFutureState" Type="String" />
<asp:Parameter Name="sDetermineFactor" Type="String" />
<asp:Parameter Name="bMentorRequested" Type="Boolean" />
<asp:Parameter Name="sMentorList" Type="String" />
<asp:Parameter Name="dtCheckPointDate" Type="DateTime" />
<asp:Parameter Name="dtDateAdded" Type="DateTime" />
<asp:Parameter Name="dtDateUpdated" Type="DateTime" />
<asp:Parameter Direction="InputOutput" Name="iPlanPKID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>

View 9 Replies View Related

Procedure Or Function Sppaintinsert Has Too Many Arguments Specified, Please Help

Apr 10, 2008

Hi,I cant see why this will not work, when i run the page and try and perform a function it clicks me out and underlines
SQLCommand.ExecuteNonQuery() with this error Procedure or function sppaintinsert has too many arguments specified
Code below:
Imports System.IO
Imports System.Data
Imports System.Data.SqlClientPartial Class admin
Inherits System.Web.UI.PageProtected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Dim strFileName As String
Dim strFilePath As String
Dim strFolder As String
Dim strPicTitle As StringDim SQLConnection As Data.SqlClient.SqlConnectionDim SQLCommand As Data.SqlClient.SqlCommand
 
strFolder = "C:Documents and SettingsRoss HintonMy DocumentsVisual Studio 2005WebSitesjessicawebsitePictures"
strFileName = oFile.PostedFile.FileName
strFileName = Path.GetFileName(strFileName)
strPicTitle = TextBox1.Text
If (Not Directory.Exists(strFolder)) Then
Directory.CreateDirectory(strFolder)
End If
strFilePath = strFolder & strFileName
If File.Exists(strFilePath) Then
lblUploadResult.Text = strFileName & " already exists on the server!"
ElseSQLConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|picturesdb.mdf;Integrated Security=True;User Instance=True")SQLCommand = New SqlCommand
SQLCommand.CommandType = CommandType.StoredProcedure
If DropDownList.SelectedItem.Value = 0 Then
SQLCommand.CommandText = "paintingpictures"
Else
SQLCommand.CommandText = "spdrawinsert"
End If
SQLCommand.Connection = SQLConnection
SQLConnection.Open()
SQLCommand.Parameters.AddWithValue("@image", "Pictures" + strFileName) ' sp parameter nameSQLCommand.Parameters.AddWithValue("@Title", strPicTitle)
SQLCommand.ExecuteNonQuery()
oFile.PostedFile.SaveAs(strFilePath)
lblUploadResult.Text = strFileName & " has been successfully uploaded."
End If
frmConfirmation.Visible = TrueEnd Sub
End ClassALTER PROCEDURE paintingpictures
@image VARCHAR(50)
AS
INSERT INTO pictable([image])
VALUES (@image)

View 7 Replies View Related

Procedure Or Function ImportLand Has Too Many Arguments Specified

Sep 16, 2004

Can anybody help me with this error? I am not even sure where to start looking.

i am looping through an arrayList that populates the SqlParameters. I open the database outside the loop, close it outside the loop and execute myCommand.ExecuteNonQuery(); inside the loop for every loop.

The error I am getting is:

System.Data.SqlClient.SqlException: Procedure or function ImportData has too many arguments specified.

Thanks!

View 1 Replies View Related

Error: Procedure Or Function Has Too Many Arguments Specified

Dec 9, 2005

Hi,

I've found that I'm not the first one to get the error

"Procedure or function x has too many arguments specified"

while working with Stored Procedures (which is no surprise at all). But
all suggested solutions didn't help, maybe this is because I
misunderstood the whole concept. The situation is: On my page there is
a FormView control including the EditItemTemplate. The database
contains a Stored Procedure called UpdatePersonByID which is working
fine as long as executed in Visual Web Developer. Here's the procedure
code:

ALTER PROCEDURE dbo.UpdatePersonByID
    (
        @LastName varchar(50),
        @FirstName varchar(50),
        @Phone varchar(50),
        @PhonePrivate varchar(50),
        @PhoneMobile varchar(50),
        @Email varchar(50)
    )
AS
    UPDATE
        tblPersons
        SET
            PersonLastName = @LastName,
            PersonFirstName = @FirstName,
            PersonPhone = @Phone,
            PersonPhonePrivate = @PhonePrivate,
            PersonPhoneMobile = @PhoneMobile,
            PersonEmail = @Email
        WHERE
            PersonLastName = @LastName
    RETURN

This is not exactly what it will finally have to do, of course the
WHERE-clause later will contain an ID comparison. But since I tried to
break down my code as much as possible I changed it to what you see
right now. Here's the aspx-source (the red stuff is what I think is
important):

<%@ Page
    Language="VB"
    MasterPageFile="fb10.master"
    AutoEventWireup="false"
    CodeFile="codebehind/staff.aspx.vb"
    Inherits="staff"
    meta:ResourceKey="PageResource"
%>
<%@ MasterType VirtualPath ="~/fb10.master"  %>
<asp:Content
    ID="ContentStaff"
    ContentPlaceHolderID="cphContentMain"
    Runat="Server">
   
    <h3 id="hdStaff" runat="server" meta:resourcekey="Staff" />

    <!-- DataSource srcStaff collects all persons from staff table in database. -->
    <asp:SqlDataSource
        ID="srcStaff"
        runat="server"
        ConflictDetection="CompareAllValues"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommandType="StoredProcedure"
        SelectCommand="SelectPersonNames"
    />
    <asp:DropDownList
        ID="ddlStaff"
        runat="server"
        DataSourceID="srcStaff"
        DataTextField="CompleteName"
        DataValueField="PersonID"
        AutoPostBack="True">
    </asp:DropDownList>

    <!-- DataSource srcPerson gets person selected in DropDownList "ddlStaff". -->
    <asp:SqlDataSource
        ID="srcPerson"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="System.Data.SqlClient"
        SelectCommand="SelectPersonByID"
        SelectCommandType="StoredProcedure"
        UpdateCommand="UpdatePersonByID"
        UpdateCommandType="StoredProcedure"
        OldValuesParameterFormatString="{0}" >
        <SelectParameters>
            <asp:ControlParameter
                ControlID="ddlStaff"
                DefaultValue="1"
                Name="ID"
                PropertyName="SelectedValue"
                Type="Int32" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="LastName" Type="String" />
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="Phone" Type="String" />
            <asp:Parameter Name="PhonePrivate" Type="String" />
            <asp:Parameter Name="PhoneMobile" Type="String" />
            <asp:Parameter Name="Email" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>

    <!--  DataSource srcBuildings gets all buildings from building table in database. -->
    <asp:SqlDataSource
        ID="srcBuildings"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="System.Data.SqlClient"
        SelectCommand="SelectBuildings"
        SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
   
    <asp:FormView
        ID="fvStaff"
        runat="server"
        DataSourceID="srcPerson">
        <EditItemTemplate>
            <!--
DataSource srcRooms gets all rooms from room table in database. -->
            <asp:SqlDataSource
                ID="srcRooms"
                runat="server"
   
           
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                ProviderName="System.Data.SqlClient"
                SelectCommand="SelectRoomsByBuildingID"
                SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:ControlParameter
           
           
ControlID="ddlBuildings"
           
           
DefaultValue="1"
           
           
Name="ID"
           
           
PropertyName="SelectedValue"
           
           
Type="Int32"
                    />
                </SelectParameters>
            </asp:SqlDataSource>

            <asp:Label
cssClass="lblIdentifier" ID="lblLastName" runat="server"
meta:resourcekey="LastName" />
           
<asp:TextBox cssClass="staff" ID="LastName" runat="server"
Text='<%# Bind("PersonLastName")
%>'></asp:TextBox><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblFirstName" runat="server"
meta:resourcekey="FirstName" />
           
<asp:TextBox cssClass="staff" ID="FirstName" runat="server"
Text='<%# Bind("PersonFirstName")
%>'></asp:TextBox><br />
           
            <asp:Label
CssClass="lblIdentifier" ID="lblBuilding" runat="server"
meta:resourcekey="Building"></asp:Label>
            <asp:DropDownList
                ID="ddlBuildings"
                cssClass="small"
                runat="server"
                DataSourceID="srcBuildings"
                DataTextField="BuildingAbbreviation"
                DataValueField="BuildingID"
                AutoPostBack="True"
                SelectedValue='<%# Bind("RoomBuildingID") %>'
           
   
OnSelectedIndexChanged="ddlBuildings_SelectedIndexChanged">
            </asp:DropDownList><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblRoom" runat="server"
meta:resourcekey="Room" />
            <asp:DropDownList
                ID="ddlRooms"
                cssClass="small"
                runat="server"
                DataSourceID="srcRooms"
                DataTextField="RoomName"
                DataValueField="RoomID"
                AutoPostBack="true"
           
    SelectedValue='<%# Bind("PersonRoomID") %>'>
            </asp:DropDownList><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblPhone" runat="server"
meta:resourcekey="Phone" />
           
<asp:TextBox cssClass="staff" ID="Phone" runat="server" Text='<%#
Bind("PersonPhone") %>'></asp:TextBox><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblPhonePrivate" runat="server"
meta:resourcekey="Private" />
           
<asp:TextBox cssClass="staff" ID="PhonePrivate" runat="server"
Text='<%# Bind("PersonPhonePrivate")
%>'></asp:TextBox><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblPhoneMobile" runat="server"
meta:resourcekey="Mobile" />
           
<asp:TextBox cssClass="staff" ID="PhoneMobile" runat="server"
Text='<%# Bind("PersonPhoneMobile")
%>'></asp:TextBox><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblEmail" runat="server"
meta:resourcekey="Email" />
           
<asp:TextBox cssClass="staff" ID="Email" runat="server" Text='<%#
Bind("PersonEmail") %>'></asp:TextBox><br />

           
<asp:LinkButton cssClass="lnkButton" ID="UpdateCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="<%$resources:translations,
Cancel%>"></asp:LinkButton>
           
<asp:LinkButton cssClass="lnkButton" ID="UpdateButton"
runat="server" CausesValidation="True" CommandName="Update"
Text="<%$resources:translations,
Update%>"></asp:LinkButton>
        </EditItemTemplate>
        <InsertItemTemplate>
            <asp:Label
cssClass="lblIdentifier" ID="lblLastName" runat="server"
meta:resourcekey="LastName" />
           
<asp:TextBox ID="PersonLastNameTextBox"
runat="server"></asp:TextBox><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblFirstName" runat="server"
meta:resourcekey="FirstName" />
           
<asp:TextBox ID="PersonFirstNameTextBox"
runat="server"></asp:TextBox><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblBuilding" runat="server"
meta:resourcekey="Building" />
           
<asp:TextBox ID="BuildingAbbreviationTextBox"
runat="server"></asp:TextBox><br />
           
            <asp:Label
cssClass="lblIdentifier" ID="lblRoom" runat="server"
meta:resourcekey="Room" />
           
<asp:TextBox ID="RoomNameTextBox"
runat="server"></asp:TextBox><br />
           
           
<asp:LinkButton cssClass="lnkButton" ID="InsertCancelButton"
runat="server" CausesValidation="False" CommandName="Cancel"
Text="<%$resources:translations,
Cancel%>"></asp:LinkButton>
           
<asp:LinkButton cssClass="lnkButton" ID="InsertButton"
runat="server" CausesValidation="True" CommandName="Insert"
Text="<%$resources:translations,
Insert%>"></asp:LinkButton>
        </InsertItemTemplate>
        <ItemTemplate>
            <asp:Label
cssClass="lblIdentifier" ID="lblLastName" runat="server"
meta:resourcekey="LastName" />
            <asp:Label
cssClass="lblValue" ID="PersonLastNameLabel" runat="server"
Text='<%# Bind("PersonLastName") %>'></asp:Label><br
/>

            <asp:Label
cssClass="lblIdentifier" ID="lblFirstName" runat="server"
meta:resourcekey="FirstName" />
            <asp:Label
cssClass="lblValue" ID="PersonFirstNameLabel" runat="server"
Text='<%# Bind("PersonFirstName") %>'></asp:Label><br
/>

            <asp:Label
cssClass="lblIdentifier" ID="lblBuilding" runat="server"
meta:resourcekey="Building" />
            <asp:Label
cssClass="lblValue" ID="BuildingAbbreviationLabel" runat="server"
Text='<%# Bind("BuildingAbbreviation")
%>'></asp:Label><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblRoom" runat="server"
meta:resourcekey="Room" />
            <asp:Label
cssClass="lblValue" ID="RoomLabel" runat="server" Text='<%#
Bind("RoomName") %>'></asp:Label><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblPhone" runat="server"
meta:resourcekey="Phone" />
            <asp:Label
cssClass="lblValue" ID="PhoneLabel" runat="server" Text='<%#
Bind("PersonPhone") %>'></asp:Label><br />

            <asp:Label
cssClass="lblIdentifier" ID="lplPhonePrivate" runat="server"
meta:resourcekey="Private" />
            <asp:Label
cssClass="lblValue" ID="PhonePrivateLabel" runat="server" Text='<%#
Bind("PersonPhonePrivate") %>'></asp:Label><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblPhoneMobile" runat="server"
meta:resourcekey="Mobile" />
            <asp:Label
cssClass="lblValue" ID="PhoneMobileLabel" runat="server" Text='<%#
Bind("PersonPhoneMobile") %>'></asp:Label><br />

            <asp:Label
cssClass="lblIdentifier" ID="lblEmail" runat="server"
meta:resourcekey="Email" />
            <asp:Label
cssClass="lblValue" ID="EmailLabel" runat="server" Text='<%#
Bind("PersonEmail") %>'></asp:Label><br />
           
           
<asp:LinkButton cssClass="lnkButton" ID="DeleteButton"
runat="server" CausesValidation="False" CommandName="Delete"
Text="<%$resources:translations,
Delete%>"></asp:LinkButton>
           
<asp:LinkButton cssClass="lnkButton" ID="EditButton" runat="server"
CausesValidation="False" CommandName="Edit"
Text="<%$resources:translations,
Edit%>"></asp:LinkButton>
        </ItemTemplate>
    </asp:FormView>
</asp:Content>


And then once again a totally different question: Is there a way to
post the highlighted aspx or vb code into this forum and keep the
colors? i think I've seen that in some posts but didn't wanna do it
manually.

Thanks once again for trying to help,

Toob

View 1 Replies View Related

Procedure Or Function Recoms Has Too Many Arguments Specified

Jun 13, 2005

my sql procedure is like below

select * from tbl where ID in (@IDs)

for example @IDs is "11,23,44,55,66,77,88,99,223,223,445,666"
So it has alot of arguments.sometimes it can be too long

that's the reason to get error message

What must I do ?

View 10 Replies View Related

An Insufficient Number Of Arguments To Function....

Mar 27, 2008

Hi all

I have select statement which is using Function and I am passing arguments to the query


select * from funactin_name(@a,@b,@c)

It is giving an error saying is that "An insufficient number of arguments to function..."

some body help how to find out required arguments to the function by using QA.

View 4 Replies View Related

Procedure Or Function Sp_MSupdatesysmergearticles Has Too Many Arguments Specified.

Jan 4, 2007

Hi,

I've been on this one all day - and now tearing my hair out. I have a large SQL2005 database (migrated from SQL2000) which I'm trying to replicate using a VB6 front end.

The application and database concerned have been working with no problems using merge replication for the last 5 years - however, I now need to port to SQL 2005.

In the VB6 app, I'm using the SQLMerge9 control to handle the replication side of things. The code I'm using is exactly as per the code which works under SQL 2000, however, I'm getting an error message "Procedure or function sp_MSupdatesysmergearticles has too many arguments specified." when I try to initialize the subscription.

I've seen one other item on the web with this problem, and this was someone trying to replicate a 2005 client with a 2000 server. I'm using SQL2005 both ends with merge replication -the replication is set up for SQL2005 clients only, and the database (which incidentally is being created on the subscriber correctly) is in SQL2005 compatibility mode.

Any help would be gratefully appreciated - I haven't got much hair left now.

The code I'm using is as follows:

 

On Error GoTo Err_Handler
 
    Set SQLMergeCtl = New SQLMerge
    Dim strSQL As String
    Dim cnn As ADODB.Connection

      ' add subscriber
    strSQL = "EXEC sp_dropsubscriber '[subscriber name]'"
    Set cnn = New ADODB.Connection
    cnn.Open [connection string]

    strSQL = "EXEC sp_addsubscriber '[machine name]', 0 , '[user name]', '[password]', @security_mode = 0"
    On Error Resume Next
    cnn.Execute strSQL
   
    If Err <> 0 Then
        MsgBox Error
    End If
   
    strSQL = "sp_dropmergesubscription @publication = '[publication name], @subscriber = '[machine name]', @subscriber_db = '[subscriber database name]', @subscription_type = 'pull'"
    cnn.Execute strSQL
    If Err <> 0 Then
        MsgBox Error
    End If
   
   
    On Error GoTo Err_Handler
    cnn.Close
    Set cnn = Nothing

    SQLMergeCtl.Publisher = [publisher name]
    
    SQLMergeCtl.PublisherSecurityMode = DB_AUTHENTICATION
    SQLMergeCtl.PublisherDatabase = [database name] 
    SQLMergeCtl.Publication = [publication name]
    SQLMergeCtl.PublisherLogin = [user name] 
    SQLMergeCtl.PublisherPassword = [password]
    
    SQLMergeCtl.DistributorSecurityMode = DB_AUTHENTICATION
    SQLMergeCtl.Distributor = [distributor name - actually same as the publisher]
    SQLMergeCtl.DistributorLogin = [user name]
    SQLMergeCtl.DistributorPassword =[password]

    
    SQLMergeCtl.Subscriber = [local machine name]
    SQLMergeCtl.SubscriberSecurityMode = DB_AUTHENTICATION
    SQLMergeCtl.SubscriberLogin = [local user name]
    SQLMergeCtl.SubscriberPassword =[local password]
    SQLMergeCtl.SubscriberDatabase = [local database name]
    SQLMergeCtl.SubscriptionType = PULL
    SQLMergeCtl.ProfileName =[profile name]

 
    On Error Resume Next
    SQLMergeCtl.AddSubscription CREATE_DATABASE, NONE
    If Err <> 0 Then
        <--snip-->
    End If
        
    On Error GoTo Err_Handler
    SQLMergeCtl.Initialize    
    'Run
    SQLMergeCtl.Run
    'Terminate
    SQLMergeCtl.Terminate    

   <--snip-->
          code here to add merge agent removed for brevity - it's not getting this far anyway
   <--snip>

 

Exit_Handler:
     Exit Sub
Err_Handler:
    MsgBox Error
      Resume Exit_Handler

View 5 Replies View Related

Procedure Or Function Sp_AddDealer Has Too Many Arguments Specified (error Via SqlDataSource)

Apr 9, 2007

Procedure or function sp_AddDealer has too many arguments specified.
I am experiencing the 'too many arguments specified' error.  I am running on SQL 2005.  The Parameters lists on SQL server (when I view a dropdown under the sp name) shows a 'returns integer'  (but without the @ the signifies a parameter).I have looked around the forums and haven't seen quite this flavor of this error.  My forehead is sore from beating it against the wall... any clue would be appreciated!
The error occurs when I click the 'new' link button, enter some data and then click the update link button after ... BOOM - Procedure or function sp_AddDealer has too many arguments specified.
Thanks!!
Chip Kigar
Here is the stored Procedure:

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[sp_AddDealer]  @sCarcareSystem varchar(100), @sDealerName varchar(50), @sDealerPhysAddrStreet varchar(200), @sDealerPhysAddrCity varchar(100), @sDealerPhysAddrState varchar(10), @sDealerPhysAddrZip varchar(20), @nReturnCode bigint output, @nReturnId bigint output AS
 SET NOCOUNT ON  DECLARE @m_nCt bigint  SET @nReturnCode = 0 SET @nReturnId = 0
 -- VALIDATE IF (@nReturnCode = 0) BEGIN  SELECT   @m_nCt = COUNT(tblDealers.[_DealerId])   FROM    tblDealers    WHERE   [Dealer Name] = @sDealerName   IF (@m_nCt >0)  BEGIN   SET @nReturnCode = -2000 --'Error for exsiting Dealer'   SET @nReturnId = 0  END END
 -- PROCESS IF (@nReturnCode = 0) BEGIN  SET @nReturnCode = -2 --' Error getting new record id'  DECLARE @m_nNewRecId bigint  SET @m_nNewRecId = 0  EXEC sp_GetNewRecId @m_nNewRecId output  IF (@m_nNewRecId > 0)  BEGIN   SET @nReturnCode = -1 --'Error adding Dealer'   INSERT INTO     tblDealers    (    [_DealerId],    [Carcare System],    [Dealer Name],    [Dealer Phys Addr Street],    [Dealer Phys Addr City],    [Dealer Phys Addr State],    [Dealer Phys Addr Zip]    )    VALUES    (    @m_nNewRecId,    @sCarcareSystem,    @sDealerName,    @sDealerPhysAddrStreet,    @sDealerPhysAddrCity,    @sDealerPhysAddrState,    @sDealerPhysAddrZip    )    SET @nReturnCode = 0 --'Success'   SET @nReturnId = @m_nNewRecId
   END  END
 
Here is the SQLDataSource.  I plugged the ID parameter, so I got a schema back, but no data.


    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"        InsertCommand="sp_AddDealer" InsertCommandType="StoredProcedure"         SelectCommand="sp_GetDealerByDealerId"   SelectCommandType="StoredProcedure">        <SelectParameters>            <asp:Parameter DefaultValue="2" Name="nDealerId" Type="Int64" />            <asp:Parameter DefaultValue="" Direction="Output" Name="nReturnCode" Type="Int64" />            <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />        </SelectParameters>        <InsertParameters>            <asp:Parameter Name="sCarcareSystem" Type="String" />            <asp:Parameter Name="sDealerName" Type="String" />            <asp:Parameter Name="sDealerPhysAddrStreet" Type="String" />            <asp:Parameter Name="sDealerPhysAddrCity" Type="String" />            <asp:Parameter Name="sDealerPhysAddrState" Type="String" />            <asp:Parameter Name="sDealerPhysAddrZip" Type="String" />            <asp:Parameter Direction="InputOutput" Name="nReturnCode" Type="Int64" />            <asp:Parameter Direction="InputOutput" Name="nReturnId" Type="Int64" />        </InsertParameters>    </asp:SqlDataSource>
Here is the Formview:

    <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">        <EditItemTemplate>            _DealerId:            <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:TextBox><br />            Carcare System:            <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:TextBox><br />            Dealer Name:            <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:TextBox><br />            Address Street:            <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:TextBox><br />            Address City:            <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:TextBox><br />            Address State:            <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:TextBox><br />            Address Zip:            <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:TextBox><br />            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"                Text="Update">            </asp:LinkButton>            <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                Text="Cancel">            </asp:LinkButton>        </EditItemTemplate>        <InsertItemTemplate>            _DealerId:            <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:TextBox><br />            Carcare System:            <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:TextBox><br />            Dealer Name:            <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:TextBox><br />            Address Street:            <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:TextBox><br />            Address City:            <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:TextBox><br />            Address State:            <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:TextBox><br />            Address Zip:            <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:TextBox><br />            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                Text="Insert">            </asp:LinkButton>            <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                Text="Cancel">            </asp:LinkButton>        </InsertItemTemplate>        <ItemTemplate>            _DealerId:            <asp:Label ID="_DealerIdLabel" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:Label><br />            Carcare System:            <asp:Label ID="Carcare_SystemLabel" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:Label><br />            Dealer Name:            <asp:Label ID="Dealer_NameLabel" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:Label><br />            Address Street:            <asp:Label ID="Address_StreetLabel" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:Label><br />            Address City:            <asp:Label ID="Address_CityLabel" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:Label><br />            Address State:            <asp:Label ID="Address_StateLabel" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:Label><br />            Address Zip:            <asp:Label ID="Address_ZipLabel" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:Label><br />            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                Text="New">            </asp:LinkButton>        </ItemTemplate>    </asp:FormView>

View 4 Replies View Related

Substring Function

Jun 20, 2008

it's early in the morning (well it is here anyway) and i need a shot of logic...

in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning

quick sample of what the data in this column may look like...


declare @t table (col varchar(30))
insert into @t
select '2' union all
select '[5] [4]' union all
select ' [12] [1]'


so i need to get...


col
-------
2
5
12


EDIT-
OK, so i get this to work but only if there is actually square brackets


declare @t table (col varchar(30))
insert into @t
--select '2'union all
select '[5] [4]'union all
select ' [12] [1]'

select col
,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1)
from @t


Em

View 20 Replies View Related

'Query Failed For The Datatset Reportsource.Procedure Of Function Has Too Many Arguments Specified'

Jan 9, 2008



I get an error like 'Query failed for the datatset Reportsource.Procedure of function has too many arguments specified.'
What could be the reason for this error??
please help me

View 1 Replies View Related

Substring Function Error.....

Jul 29, 2003

select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 0)
from msdb.sysjobs.name
name
-------------------- ----------- ----------- -----------
Job_4927_2_7Sun 30 9 4927_2_7S
Job_250144_2_6Sat 34 11 250144_2_6S
Job_30197_2_1Mon 32 10 30197_2_1M

but when I use following - 3)
select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 3)
from msdb.sysjobs.name

I get the result I want (last column):
Job_4927_2_7Sun 30 9 4927_2
Job_250144_2_6Sat 34 11 250144_2
Job_30197_2_1Mon 32 10 30197_2

but also with an error:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

thanks
David

View 3 Replies View Related

What Is The Max Length Of Substring Function.

Mar 16, 2001

hi thanks for your help

update vendor_invoice
set de_ftpdownload= (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice)
where vendor_invoice_id =931

This get me this error.... so what is the max allowable length of substring function.

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.
Thanks
Al

View 1 Replies View Related

SQL 2000 - Substring Function

Jun 20, 2004

Hello,

I want to select the characters before "-" from Column A.
For example:
12345-HSC
222-ABC

I'd would like to select it as:
12345
222

Please Help!!!!!!!!!!!!!!!!!!!!! :confused:

View 3 Replies View Related

Problem In Substring Function

Oct 11, 2005

Harshi writes "Hello SQL Team!!

I have a problem in Substring function. There is a Text data type field in my Table and it has large no of characters. I want to get the whole text as the result. The problem is its just giving me 300 characters from a row, as the result.

My statement as follows;

//============================================================

SELECT a.L2Topic, a.TopicIndex, a.L1Topic,
Substring(b.Des, 1, 900), b.PriceGroup, b.Price,
b.L1TopicIndex, b.Qty, b.OtherPrice,
c.Customer, c.Address, c.Date, c.ChassyNo,
c.VehicleNo FROM wshpTopicIndex a

LEFT JOIN wshpEstimateDetails b ON (a.EstimateID = b.EstimateID)
INNER JOIN wshpEstimateIndex c ON (b.EstimateID = c.EstimateID)

WHERE (b.RandomNo = a.TopicIndex) AND (a.EstimateID = 54)

GROUP BY a.L2Topic, Substring(b.Des, 1, 900), c.Customer, a.TopicIndex, a.L1Topic, b.Qty,
b.PriceGroup, b.Price, b.L1TopicIndex, b.OtherPrice, c.Address, c.Date, c.ChassyNo, c.VehicleNo

ORDER BY b.PriceGroup, b.L1TopicIndex

//===============================================================

Can some one please help me to get rid of this problem??

Thank you,
Harshi"

View 1 Replies View Related

Substring Function Can't Figure Out

Mar 5, 2008

I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:

CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville'
CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'

I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.

View 10 Replies View Related

Join Using A Substring Function

Jul 30, 2007



Hi All,
I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.

Thanks

View 7 Replies View Related

Is There A Function Like SUBSTRING But For Integers?

Apr 14, 2006

I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?

View 5 Replies View Related

Serious Perfomance Problem Using UDFs As Function Arguments. (I Suspect A Bug, Show Me I'm Wrong!)

Nov 29, 2007


(From an exchange originally posted on SQLServer.com, which wasn't resolved...)

To return views tailored to the user, I have a simple users table that holds user IDs, view names, parameter names, and parameter values that are fetched based on SUSER_SNAME(). The UDF is called MyParam, and takes as string arguments, the name of the view in use, and a parameter name. (The view the user sees is really a call to a corresponding table returning UDF, which accepts some parameters corresponding to the user.)

But the performance is very dependent on the nature of the function call. Here are two samples and the numbers reported by (my first use of) the performance monitor:
Call to table returning UDF, using local variables:

declare @orgauth varchar(50)
set @orgauth = dbo.MyParam('DeptAwards', 'OrgAuth')
declare @since datetime
set @since = DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
select * from deptAwardsfn(@orgauth,@since)

[187 CPU, 16103 Reads, 187 Duration]


Call to same table returning UDF, using scalar UDFs in parameters:

SELECT *
from deptAwardsFn (
dbo.MyParam('DeptAwards', 'OrgAuth')
,DATEADD(DAY,-1 * dbo.MyParam('DeptAwards', 'DaysAgo'),CURRENT_TIMESTAMP)
)
[20625 CPU, 1709010 Reads, 20632 Duration]
(My BOL documentation claims the CPU is in milliseconds and the Duration is in microseconds -- which I question.) Regardless of the unit of measure, it takes a whole bunch longer in the second case.

My only guess is that T-SQL is deciding that the parameter values (returned by dbo.MyParam) are nondeterministic, and continually reevaluates them somehow or other. (What ever happened to call by value?)

Can anyone shed some light on this strange (to me) behavior?


----- (and later, from me)---
(I have since discovered that the reference to CURRENT_TIMESTAMP in the function argument is the cause, but I suspect that is an error -- it should only capture the value of CURRENT_TIMESTAMP once, when making the function call IMHO.)

View 8 Replies View Related

Performance Issue Using Left Or Substring Function

Oct 18, 2007

Hi,

I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.

select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))

The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.

The Table1 containt approx. 6000 row and Table2 containt only 210 rows

The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".

When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.

When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 secondes!!!!

This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.

It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!


Anyone have an idea why?



Mathieu Desbiens

View 1 Replies View Related

Substring And Patindex Having An User Defined Function

Oct 30, 2007

Hi All,
Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters? Something like this:




Code Block
Select SUBSTRING(Select * from DB.dbo.Function('Parameter'), PATINDEX('%\%', Select * from DB.dbo.Function('Parameter'), 8)



Mannu.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved