Whn I use a UniqueIdentifier field as a parameter for SQLDataSource the parameter type defaults as an object. I just want to use the string value of the field.
How do I do this?
Thanks.
I have a table that contains articles (as in, newspaper articles, blogarticles, whatever). I need to use a column of type uniqueidentifierbecause one of the requirements is that I be able to write the articlesout to XML or import them from XML, and references (as in, "for moreinfo read this: 2323-232-90934" have to still work after the export andimport).So as a minimum, the table is going to look like this:CREATE TABLE Articles (ArticleID uniqueidentifier,PublishDate datetime,Title nvarchar (50)ArticleContent ntext)GOALTER TABLE Articles ADDCONSTRAINT PK_ArticlesPRIMARY KEY NONCLUSTERED (ArticleID)WITH FILLFACTOR = 100GOAs you can see, I'm not going to use a clustered index on a column oftype UniqueIdentifier. I got that much from this newsgroup and fromwebsites on sql server performance tuning.Two questions. 1: I will obviously need to list recent articles. I'llneed to do: select top 10 ArticleID, PublishDate, Title from Articlesorder by PublishDate descWill there be any problem with an index on a datetime field to makethat query faster?CREATE UNIQUE CLUSTERED INDEX IX_Articles_PublishDateON Articles (PublishDate DESC)WITH FILLFACTOR = 100GOQuestion 2: Is there anything else that I can do here that I'm missing?Should I maybe also have a auto-increment field and put the clusteredindex on it instead?Thanks in advancechris
View 4 Replies View RelatedWhat is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 Replies View RelatedI have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields?
Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView)
When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
Dim strStatus As String
Dim strGroup As String
Dim strLicense As String = dtgPhysician.DataKeys(e.Item.ItemIndex)
Dim strUpdateStatus As String
Dim strUpdateGroup As String
Dim strName As String
Dim strDate As String
Dim strSubject As String
Dim blPDA As Boolean
Dim strPDA As Byte
strStatus = CType(e.Item.FindControl("lstStatus"), DropDownList).SelectedItem.Text
strGroup = CType(e.Item.FindControl("txtGroup"), TextBox).Text
strName = e.Item.Cells(1).Text.Trim
strDate = CType(e.Item.FindControl("txtDate"), TextBox).Text
strSubject = CType(e.Item.FindControl("dpTraining"), DropDownList).SelectedItem.Text
blPDA = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked
strPDA = Convert.ToString(blPDA)
strUpdateStatus = "UPDATE DoctorMaster SET Status = @p_Status , PGroup=@p_Group , @p_PDA wHERE (PLIC= @p_License);"
' ... Make a SQL call to update the database ...
'UpdatePhysician
Dim strConn As String
strConn = ConfigurationSettings.AppSettings("ConString")
If strConn = String.Empty Then
myConn = New SqlConnection()
Else
myConn = New SqlConnection(strConn)
End If
myConn.Open()
Dim daPhysician As New SqlDataAdapter()
Dim comUpdate As New SqlCommand()
comUpdate.Connection = myConn
daPhysician.UpdateCommand = comUpdate
comUpdate.CommandType = CommandType.Text
comUpdate.CommandText = strUpdateStatus
'Return the DataGrid to its pre-editing state
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@p_Status", System.Data.SqlDbType.Char, 3, "Status")).Value = strStatus
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@p_Group", System.Data.SqlDbType.NVarChar, 15, "PGroup")).Value = strGroup
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@p_License", System.Data.SqlDbType.NVarChar, 15, "PLIC")).Value = strLicense
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked.ToString
comUpdate.ExecuteNonQuery()
comUpdate.Dispose()
Thanks.
It kept givimg me data format error.
Is
it possible to specify different parameters for the
select/update/delete stored procedures used by a sqldatasource? When I
'configure the datasource' it lists the different stored procedures for
each command, but when it comes to specifying the parameters it only
lets me do so for the select command. Is there another way to do this
for the other commands?
I have tried doing it manually in the aspx file (using the
DeleteParemeters etc), but I don't know how to reference a specific
cell in the selected row of the gridview for the controlparameter). Any
thoughts?
Hello I need help withsetting parameters for SqlDataSource
I have a simple program. I want display date from database on MS SQLSERVER from the table USERS only for current sing on user select by his login.
I save into this variable login current user: string @LOGIN = Context.User.Identity.Name;
I have already done with this way without SqlDataSource:
string login = Context.User.Identity.Name;
SqlConnection conn1 = new SqlConnection("server=CR\SQLEXPRESS;database=myData;integrated security=SSPI");
conn1.Open();
SqlCommand cmd1 = new SqlCommand(" SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN", conn1);
cmd1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50);
cmd1.Parameters["@LOGIN"].Value = login;
1.Parameters.Add("@LOGIN", SqlDbType.NVarChar, 50);
cmd1.Parameters["@LOGIN"].Value = login;
Now I don't know how to do with SqlDataSource, what I have to set in SqlDataSource1 yet
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
ProviderName="<%$ ConnectionStrings:myConnectionString.ProviderName %>" SelectCommand="SELECT IN_OUT.TIME_START, IN_OUT.TIME_END, FROM IN_OUT INNER JOIN USER ON USER.USER_ID=IN_OUT.RC_USER_ID where USER.LOGIN=@LOGIN">
</asp:SqlDataSource>
Does anybody knows how to use parameters.add with SqlDataSource?
View 4 Replies View RelatedI set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID") <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FDConn %>" SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="DeptID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <radG:RadGrid ID="RadGrid1" runat="server"> </radG:RadGrid>
View 3 Replies View RelatedI need to do some select & update from VB code behind a web page. Using VS2005, ASP.NET.
To me, the most logical approach would be to use a SqlDataSource. I can select the connection string, predefine select, insert and update queries and call the select(), and other commands from this control.
I need to use parameters in the queries but I cannot connect parameters straight to controls, I need to do it from VB. But nowhere can I find how to set the parameter values from code. I can find all kind of examples from VS2003 or using SqlCommand, but it should be possible from this control as well, but the help documentation is very poor in this respect.
Please provide me with some example.
Kind regards
Hi,How do I set the parameters of an SqlDataSource programatically?I have tried the following...dsDraftBudgetPI.SelectParameters.Add(new Parameter("@person_id", TypeCode.Int32, pID.ToString()));But that didn't work. I already have the parameter defined at design time so I don't need to create one I just want to set its value and then bind it to a ListBox to display the result.Thanks,Scott
View 3 Replies View RelatedHello!
I am pulling my hair out over this latest problem. I'm trying to do a simple SQL SELECT statement by taking values from textboxes and inserting them into the WHERE part of the statement. I'm using MySQL and have to use ? prefix for all inserts, that I have working by the way. Now for some reason I cannot get the values from the text boxes to replace the prefixes! Here is my code:
private void submitLogin(object source, EventArgs e) {
if (isPassword(password.Text.ToString()) && isEmail(username.Text.ToString()))
{
ValidateCustomer.SelectParameters["c_email"].DefaultValue = "fish";
ValidateCustomer.SelectParameters["c_password"].DefaultValue = "cakes";
test_label.Text = ValidateCustomer.SelectCommand.ToString();
}
else {
test_label.Text = "Username or Password Incorrect";
}
}
<asp:SqlDataSource runat="server"
ID="ValidateCustomer"
ProviderName="System.Data.Odbc"
ConnectionString ="<%$ connectionStrings:MySQLDatabase %>"
SelectCommand ="SELECT * FROM customer_details WHERE (Customer_Email = ? AND Customer_Password = ?)">
<selectparameters>
<asp:parameter name="c_email" type="String" />
<asp:parameter name="c_password" type="String" />
</selectparameters>
</asp:SqlDataSource>
I have omitted obvious code. The test label, when updated, shows the SQL statement but with the ?. I also use controlparameters that took the values directly from the textboxes but again this did not work. I'm really stuck with this one. Your help is much needed!
Cheers
Dan
Hi all,I have a stored procedure which sets an output variable. How could I access this after calling an SqlDataSource.Select()? ThanksChris
View 9 Replies View RelatedI'm try to use a SqlDataSource control for binding data (stored procedure) to a GridView control. But I only get the error message below. What am I doing wrong? ----------- Error message ----------------------Procedure or function 'StatisticCustomers' expects parameter '@From', which
was not supplied. ------------- Customer.aspx -------------------- From <asp:TextBox id="txtDateFrom" CssClass="datebox" runat="server"></asp:TextBox> To <asp:TextBox id="txtDateTo" CssClass="datebox" runat="server"></asp:TextBox> Min: <asp:TextBox id="txtMinimumOrders" CssClass="" runat="server"></asp:TextBox> <asp:GridView ID="grdvwCustomers" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="Column1" HeaderText="Column1" ReadOnly="True" SortExpression="Column1" /> <asp:BoundField DataField="Items" HeaderText="Items" ReadOnly="True" SortExpression="Items" /> <asp:BoundField DataField="Column2" HeaderText="Column2" ReadOnly="True" SortExpression="Column2" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" SelectCommand="StatisticCustomers" SelectCommandType="StoredProcedure" runat="server"> <SelectParameters> <asp:ControlParameter ControlID="txtDateFrom" Name="dtFrom" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtDateTo" Name="dtTo" PropertyName="Text" Type="DateTime" /> <asp:ControlParameter ControlID="txtMinimumOrders" Name="iMinimumOrders" PropertyName="Text" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> ALTER PROCEDURE [dbo].[StatisticCustomers] (@From datetime, @To datetime, @MinimumOrders int )AS SELECT Orders.UserId, 'name', COUNT(*) AS Items, SUM(OrderDetail.Subtotal) FROM Orders INNER JOIN OrderDetail ON Orders.OrderId = OrderDetail.OrderId WHERE DateCreated > @From AND DateCreated < @To GROUP BY Orders.UserId ORDER BY Items DESC Regards, Sigurd
Can you access the values within sqldatasource parameters
programmatically? I have tried to reference them in the following
ways without any luck.
-SqlDataSource_Contacts.UpdateParameters("original_user_id")
-SqlDataSource_Contacts.UpdateParameters("original_user_id").ToString
-SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue
-SqlDataSource_Contacts.UpdateParameters("original_user_id").DefaultValue.ToString
-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id")
-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").ToString
-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue
-SqlDataSource_Contacts.UpdateParameters.Item("original_user_id").DefaultValue.ToString
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted.
What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])
Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?
Thank you very much for your help.
HI, SQL newbie here... less than a month. I am modifying a report from our MRP vendor. We are allowed to do this. They discourage modifying the underlying SQL for upgrade and support reasons. I need to pull commission data to the report in order to determine proper logo/address/etc information on the page. The report is about 1000 pages long, each page may have different logo, etc. So, I created a stored procedure in SQL to retrieve the data when passed a customer ID. Tests fine. When I attempt to call this stored procedure from reporting services, it tells me that I cannot use a Field (or report item) as a parameter. I cant believe they would limit reports this way. Is there any way to circumvent this limitation? Any alternate recommendations?
Thanks in advance!
Ray
I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast. And then firing off the update command that contains that parameter from a button. But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes. All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list. But it doesn't even change anything. It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc. It just won't change the data from the page when I try to.
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!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>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Any help is much appreciated.
If I have a lot of dropdowns of similar type but only differ in one field, is there a way to set parameters so that the dropdownlist can specify what the parameter values are?
For example dropdowns: expenseaccount, incomeaccount, assetaccount
SqlDataSource:
SelectCommand="select accountid,accountname from accounts, accounttypest where accounts.accounttypeid=acounttypes.accounttypeid and accounttypes.accounttypename=? order by accountname"
<SelectParameters>
<asp:Parameter Name="accountTypeName" Type="string" />
</SelectParameters>
-------
I would like expenseaccount to be able to specify "EXPENSE", incomeaccount to specify "INCOME", etc...
Or is sqldatasource reusage bad practice?
(damn vs2k5 colors)
I have a sqldatasource in a wizard. When I click the finishbutton in the wizard I want to insert some values (from textboxes, dropdownlists in my wizard) into a mysql-database. For this I use parameters, I add some values in the design mode and some in codebehind. The parameters in codebehind are added perfectly but the other parameters in designmode (name, email) just inserts null. Why? My datasource: <asp:SqlDataSource
ID="dsInsert"
InsertCommand="INSERT INTO tbltest (name, email, city) VALUES (?name, ?email, ?city)"
runat="server"
ConnectionString="<%$ ConnectionStrings:conn %>"
ProviderName="MySql.Data.MySqlClient">
<InsertParameters>
<asp:FormParameter Name="?name" ConvertEmptyStringToNull="true" Type="string" FormField="name" />
<asp:FormParameter Name="?email" ConvertEmptyStringToNull="true" Type="string" FormField="email" />
</InsertParameters>
</asp:SqlDataSource> The codebehind: protected void finishButtonClick(object sender, EventArgs e) { dsInsert.InsertParameters.Add("?city", TypeCode.Int32, city.SelectedValue); dsInsert.Insert(); }
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
View 8 Replies View Relatedhow do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>
I have an SQLDataSource. The SQL is
SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records.
e.Command.Parameters("@Printed1").Value = Nothing 'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False
What am I doing wrong???
Thanks
Craig
CancelSelectOnNullParameterHi,Suppose I have a SQLDataSource with a
SelectCommand="SELECT * FROM STOCKS WHERE USER=@USER ORDER BY STOCK_NAME"
and @USER is set as a session parameter. No problem till this part, but in some postbacks, I wish to add a new WHERE criteria to my SQL, so I do:DSSQL.SelectParameters.Add("@STOCK_NAME", "ABC123");
DSSQL.SelectCommand = DSgvStoklar.SelectCommand.Replace(" ORDER BY ", " AND STOCK_NAME LIKE @STOCK_NAME+'%' ORDER BY ");
gridViewDS.DataBind();
and wish that my SelectCommand is modified and a new parameter is added.
The SQL is modified, however -I guess- new parameter is not added, so I get "Must declare the scalar variable "@STOCK_NAME" error.
How can I solve, any ideas?
I want to know how to set parameters for Update Stored Procedure in SQLDataSource
I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false" ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>" SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" > <SelectParameters> <asp:Parameter Name="fromDate" /> <asp:Parameter Name="toDate" /> <asp:Parameter Name="label" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server" SelectCommand="select label_id, label_name from dl_label order by label_name asc" ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>"> </asp:SqlDataSource> <div> <table> <tr> <td>Start Date: </td> <td> <asp:TextBox ID="FromDate" Runat="server" Width="70"/> <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>End Date: </td> <td> <asp:TextBox id="ToDate" Runat="server" Width="70" /> <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>Label: </td> <td> <asp:DropDownList ID="LabelList" Runat="server" DataSourceID="SqlDataSourceLabels" DataTextField="label_name" DataValueField="label_name"> </asp:DropDownList> </td> </tr> <tr> <td colspan="2" align="right"> <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" /> </td> </tr> </table> <p> <asp:GridView ID="GridViewSales" runat="server" DataSourceID="SqlDataSourceSales" ShowFooter="True" AllowSorting="True" AutoGenerateColumns="False" OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display."> <Columns> <asp:BoundField DataField="cat_no" HeaderText="Cat No" /> <asp:BoundField DataField="artist" HeaderText="Artist" /> <asp:BoundField DataField="title" HeaderText="Title" /> <asp:BoundField DataField="remix" HeaderText="Remix" /> <asp:BoundField DataField="qty" HeaderText="Sold" /> <asp:boundfield datafield="commission" HtmlEncode="False" dataformatstring="{0:F2}" headertext="Commission"> <ItemStyle HorizontalAlign="Right" /> </asp:boundfield> </Columns> </asp:GridView> </p> </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{ int totalSold; decimal totalCommssion; protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e) { // add column totals to gridview if (e.Row.RowType == DataControlRowType.DataRow) { totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty"); totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission"); } // display the totals else if (e.Row.RowType == DataControlRowType.Footer) { e.Row.Cells[0].Text = "<b>Total</b>"; e.Row.Cells[4].Text = totalSold.ToString(); e.Row.Cells[5].Text = totalCommssion.ToString("f2"); } } protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { // *** This method does not work *** e.Command.Parameters[0].Value = FromDate.Text; e.Command.Parameters[1].Value = ToDate.Text; e.Command.Parameters[2].Value = LabelList.Text; // *** This method works! *** e.Command.Parameters[0].Value = "02/01/2007"; e.Command.Parameters[1].Value = "02/01/2008"; e.Command.Parameters[2].Value = "Fat!"; } protected void SubmitButton_OnClick(object sender, EventArgs e) { SqlDataSourceSales.Select(DataSourceSelectArguments.Empty); }} Many thanks Simon
View 3 Replies View RelatedI’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
hi,
i am using a sqldatasource with a formview that has three stored procedures associated with it. when i update a record in my formview, i need to increase two parameters that are displayed as labels in the formview by their value plus one.
i can't figure out how to increase the value in the stored procedure, or using the codebehind, or with some script in the html. any solution works for me. any suggestions?
if i can figure out how to increment one of my fields, i can use the same logic for the other. below is an example of what i am trying to do.
in my formview i have three fields: Count, Rate, Price. Count is a label, Rate and Price are editable textboxes. once i commit the update for the values of Rate and Price, i need the value of Count to increase by 1, and then have that value updated in the data base along with the new values for Rate and Price.
is there a way to do this in the codebehind, or in my stored procedure?
i am using Asp.Net 2.0, vs2005, and sql server.
any help is greatly appreciated. thank you.
Hi,
I'm having an issue trying to send parameters to a SQLDatasource object. I"m creating it in code (and I need it to stay there) I have determined that the stored procs called with no parameters work fine. I start having problems when I have any parameters. Interestingly, it doesn't crash. It just fails to run (the onselected event never fires).
dsUser is a protected SQLDatasource property
repMain is a repeater on the aspx sheet
lstUser is a stored proc with one varchar(10) parameter called cSort
Here is my current code that will result in no called to StatementCompleted and no data. Pull cSort parameter from the proc and it works just fine. Obviously I'm setting it wrong.
string cSelect = "dbo.lstUser";
dsUser = new SqlDataSource();
dsUser .ConnectionString = GetConStr();
dsUser .SelectCommand = cSelect;
dsUser .SelectParameters.Add( "cSort", TypeCode.String, "" );
dsUser .Selected += StatementCompleted;
repMain.DataSource = dsUser ;
TIA,
Nick Hustak
Hi,
I'm having serious issues trying to refresh a schema in a SQLDatasource. It is hooked to a stored procedure that takes two varchar(39) parameters. The default parameters in this case are '%'. Note I am working in the designer.
If I set it up as a stored proc, I can't even get the 'test query' to run in the builder.
If I set it up as a select statement, ala 'exec <procname> @p1, @p2', the 'test query' will run.
In neither case will hitting refresh schema work. It returns 'Invalid length parameter passed to the substring function'
The stored proc is nothing special, simply returning a select based upon the parameters.
Thanks,
Nick H
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues.
The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key:
ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY()
This works fine when run from Sql Server Management Studio and @NewID has the correct value.
My data access code is roughly as follows:
dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try
The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value.
Has anyone tried to do this and can tell me what I am doing wrong?
Jon