Showing posts with label sqldatasource. Show all posts
Showing posts with label sqldatasource. Show all posts

Friday, March 30, 2012

How to value parameters for sqldatasource

I 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>

You've set the value of an InsertParameter, but you are performing a Select, not an Insert. Anyway, the SqlDataSource offers a number of different parameter types. One of them is an <asp:SessionParameter>. Use that instead of the generic<asp:Parameter> that you are currently using. You can reconfigure the SqlDataSource to generate it automatically. Click the WHERE button when applying the Select Statement, and in the Source dropdown, choose Session.

|||

Mike,
Thanks for the information. I went back into the configure the sqldatasrouce, but if you chose to use a stored procedure, the button for selecting "Where" is grayed out. I assumed that since the code was generated from vb and from the stored procedure I selected, vb would know the type of parameter it had to generate.


Regards,
Tom

|||

Mike,

I went back into the code and used the "SelectParameter" type and all worked well. Thanks for pointing me in the right direction.

Tom

Monday, March 26, 2012

how to use update command at sqldatasouce

hello all..,

i have problem in update sqldatasource,

my code like that:

me.sqldatasource.updateparameter(index).defaultvalue=value

me.sqldatasource.update()

it can not update data, why?

but if i use insert or delete like:

me.sqldatasource.insertparameter(index).defaultvalue=value

me.sqldatasource.insert()

me.sqldatasource.deleteparameter(index).defaultvalue=value

me.sqldatasource.delete()

it can work for insert and delete data...

can anyone give me update command code in sqldatasouce?

plsss...

thx...

Here's your example:

ASPX

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="1161930.aspx.vb" Inherits="MyPosts_DataAccess_1161930" %><!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>Untitled Page</title></head><body> <form id="form1" runat="server"><asp:label id="lblStatus" runat="server" /><br /><br />ShipperID:<asp:literal id="litShipperID" runat="server"></asp:literal><br />CompanyName:<asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnUpdate" runat="server" text="Update" /><br /><asp:sqldatasource id="sdsShippers" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"selectcommand="SELECT * FROM [Shippers] WHERE [ShipperID] = @.ShipperID" updatecommand="UPDATE Shippers SET CompanyName = @.CompanyName, Phone = @.Phone WHERE (ShipperID = @.ShipperID)"><selectparameters><asp:querystringparameter defaultvalue="1" name="ShipperID" querystringfield="ShipperID" /></selectparameters><updateparameters><asp:parameter name="CompanyName" /><asp:parameter name="Phone" /><asp:querystringparameter defaultvalue="1" name="ShipperID" querystringfield="ShipperID" /></updateparameters></asp:sqldatasource> </form></body></html>

CODE-BEHIND

Imports System.DataImports System.Web.UIImports System.Web.UI.WebControlsPartialClass MyPosts_DataAccess_1161930Inherits System.Web.UI.PagePrivate Sub SetData()Dim drvAs DataRowView =CType(sdsShippers.Select(DataSourceSelectArguments.Empty), DataView)(0)litShipperID.Text = drv("ShipperID").ToStringtxtCompanyName.Text = drv("CompanyName").ToStringtxtPhone.Text = drv("Phone").ToStringEnd SubProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf Not Me.IsPostBackThenMe.SetData()End IfEnd SubProtected Sub btnUpdate_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnUpdate.ClicksdsShippers.Update()Me.SetData()End SubProtected Sub sdsShippers_Updating(ByVal senderAs Object,ByVal eAs SqlDataSourceCommandEventArgs)Handles sdsShippers.Updatinge.Command.Parameters("@.CompanyName").Value = txtCompanyName.Texte.Command.Parameters("@.Phone").Value = txtPhone.TextEnd SubProtected Sub sdsShippers_Updated(ByVal senderAs Object,ByVal eAs SqlDataSourceStatusEventArgs)Handles sdsShippers.UpdatedIf e.ExceptionIs Nothing ThenlblStatus.Text ="Record successfully updated!"End IfEnd SubEnd Class
|||

ok, thx ec...

your methods is right...

it can update..

i have try it, u use update command

<asp:querystringparameterdefaultvalue="1" name="ShipperID" querystringfield="ShipperID" />
so the update in sqldatasource can work....
u  make defaultvalue="1", it mean only for update data where shipperid=1..
 but i want to update every data that i choose.. i try changedefaultvalue={0}.but it can not work..
how can i do?
thx.. 
|||

Modify the Parameter within the SqlDataSource.Selecting event as you do for the SqlDataSource.Updating event.

Protected Sub sdsShippers_Selecting(ByVal senderAs Object,ByVal eAs SqlDataSourceSelectingEventArgs)Handles sdsShippers.Selectinge.Command.Parameters("@.ShipperID").Value ="Whatever you want here"End Sub
|||

helo ec...

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
Dim shipperId As String = GridView1.Rows(0).Cells(0).Text
e.Command.Parameters("@.shipper_id").Value = shipperId
End Sub

this is error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

it means error index in row or cell, but my index look right...

i display in gridview like that:

shipperid name status

1 andy sendbutton checkbox

2 budy sendbutton checkbox

my code like that:

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand

if e.commandname="send" then

me.sqldatasource1.update()

end if

end sub

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
Dim name As String = GridView1.Rows(0).Cells(1).Text
e.Command.Parameters("@.name").Value = name
e.Command.Parameters("@.status").Value = "true"

end sub

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
Dim shipperid As String = GridView1.Rows(0).Cells(0).Text
e.Command.Parameters("@.shipperid").Value = shipperid
End Sub

pls...

thx...

|||

I'm confused, it looks like the SqlDataSource1 control is already associated to GridView1 as the DataSourceID. Is this correct?

|||

i have make the new page, but it same error...

it like can not use in selecting event...

i have many try it..

pls... ec...

thx...

|||

I gotta see your code before I can do anything.

|||

okay, this is my full code in one page...

test3.aspx

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="test3.aspx.vb" Inherits="test3" %
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:GAMESConnectionString %>"
DeleteCommand="DELETE FROM [Shippers] WHERE [shipperId] = @.shipperId" InsertCommand="INSERT INTO [Shippers] ([shipperId], [CompanyName], [status]) VALUES (@.shipperId, @.CompanyName, @.status)"
SelectCommand="SELECT * FROM [Shippers]" UpdateCommand="UPDATE [Shippers] SET [CompanyName] = @.CompanyName, [status] = @.status WHERE [shipperId] = @.shipperId">
<DeleteParameters>
<asp:Parameter Name="shipperId" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:querystringparameter name="shipperid" querystringfield="shipperid" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="shipperId" Type="String" />
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="status" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="shipperId"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="shipperId" HeaderText="shipperId" ReadOnly="True" SortExpression="shipperId" />
<asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
<asp:ButtonField CommandName="send" Text="sendbutton" />
<asp:TemplateField HeaderText="status" SortExpression="status">
<EditItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("status") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("status") %>' Enabled="false" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

</div>
</form>
</body>
</html>

test3.aspx.vb

Partial Class test3
Inherits System.Web.UI.Page

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
If e.CommandName = "send" Then

Me.SqlDataSource1.Update()
End If
End Sub

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
'Dim shipper As String = Me.GridView1.Rows(0).Cells(0).Text
'e.Command.Parameters("@.shipperid").Value = shipper
End Sub

Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
Dim company As String = Me.GridView1.Rows(0).Cells(1).Text
e.Command.Parameters("@.companyname").Value = company
e.Command.Parameters("@.status").Value = "true"
End Sub

End Class

thx...

|||

Ok, your selecting all the records from your table in the select command so there's no need to even use the SqlDataSource.Selecting event so get rid of it. As far as your update goes, change the QueryStringParameter to Parameter so that it is automatcially populated by the DataKeys collection, and remove your SqlDataSource.Updating event as these parameters will automatically be populated by the BoundFields you have declared.

|||

ok.. many thx ec..

i have put commandname=update in sendbutton... it can work..

yes... ok, i have many learn from you since i join in this forums..

the problem that u resolve for me...:

1.formview is ok

2.gridview is ok

3.store procedure is ok

4.searching many word?

the next problem is searching, but i will post new thread in other time...

ok, i want sleeping...

thx....

|||

hello ec... my code like in the top on this post...

pls.. ec.. or i give it again..

this is my code:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="test3.aspx.vb" Inherits="test3" %
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:GAMESConnectionString %>"
DeleteCommand="DELETE FROM [Shippers] WHERE [shipperId] = @.shipperId" InsertCommand="INSERT INTO [Shippers] ([shipperId], [CompanyName], [status]) VALUES (@.shipperId, @.CompanyName, @.status)"
SelectCommand="SELECT * FROM [Shippers]" UpdateCommand="UPDATE [Shippers] SET [CompanyName] = @.CompanyName, [status] = @.status WHERE [shipperId] = @.shipperId ">
<DeleteParameters>
<asp:Parameter Name="shipperId" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="status" Type="String" />
<asp:parameter name="shipperid" type="string" />
</UpdateParameters>

<InsertParameters>
<asp:Parameter Name="shipperId" Type="String" />
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="status" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="shipperId"
DataSourceID="SqlDataSource1" Visible="False">
<Columns>
<asp:BoundField DataField="shipperId" HeaderText="shipperId" ReadOnly="True" SortExpression="shipperId" />
<asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" />
<asp:ButtonField CommandName="update" Text="sendbutton" />
<asp:TemplateField HeaderText="status" SortExpression="status">
<EditItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("status") %>' />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("status") %>' Enabled="false" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

</div>
</form>
</body>
</html>

pls.. thx...

Friday, March 23, 2012

How to use SQLDataSource

Hello guys i am a bit confused, because i cannot do a simple select using the SQLDataSource from ASP.NET 2.0

here is how was doing after droping the SQLDatAdapter using the VS2003

Example

SqlDataAdapter1.SelectCommand="select * from users"

SQLDataAdapter1.Fill(Datasource1,"users")

TextBox1.DataBind()

my question is how to do it in VS2005 since that i dont see theDataAdapter anywhere, do i have to do by hand like declaring everything??

thank you in advance!!

Hi,

Because we are no longer have MSTray in the webform designer, we won't support SQLDataAdapter/DataSet on the designer. However, you can still use the old binding syntax with sqlDataAdapter/DataSet in the code.
For more information about new feature SQLDataSource, please seehttp://www.asp.net/Tutorials/quickstart.aspx

Hope that helps,

Lan

sql

Monday, March 19, 2012

How To Use Return Value from SqlDataSource

I have the following code in my page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

<SelectParameters><asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/><asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/></SelectParameters></asp:SqlDataSource>

I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?

Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you -have to work with is what's above.

Hi,

Add OnSelected="SqlHolidayDateRange_Selected" to SqlDataSource then you can get return value using

Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

Hope this helps.

|||

Like this:

voidSqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);

//add your code.......

}

|||I'm sorry, but I'mreally new at this. Neither of your answers make much sense to me. Where do I add those things? In the page, in the codebehind, or what?|||

Doesn't matter.

Add OnSelected="SqlHolidayDateRange_Selected" in your page:

<asp:SqlDataSourceID="SqlHolidayDateRange"runat="server"ConnectionString="(OurConnectionString)"

SelectCommand="CountHolidays"SelectCommandType="StoredProcedure">

OnSelected="SqlHolidayDateRange_Selected"

<SelectParameters>

<asp:ControlParameterControlID="WeekEndingDatePicker"Name="EndDate"PropertyName="SelectedDate"/>

<asp:ParameterDirection="ReturnValue"Name="RowCount"Type="Int32"/>

</SelectParameters>

</asp:SqlDataSource>

And add the following to you code-behinde:

void SqlHolidayDateRange_Selected(object sender, SqlDataSourceStatusEventArgs e) {int RowCount = Convert.ToInt32(e.Command.Parameters["@.RowCount"].Value);//add your code.......}

Then you can display RowCount .

You can take a look atSqlDataSource in QuickStart Tutorials.

Wednesday, March 7, 2012

How to Use Delete Parameters

Hey gang I am trying to use the delete feature of a sqldatasource and having issues with the delete feature. My code is below the issue is if I just call the delete and don't have a paramter in my delete statement then it deletes the records in my table. However when I add a parameter to my delete query (@.maID) and try to set it then it doesn't delete the record. What am I doing wrong? The e.CommandArgument is passing the correct record ID (which is an Integer)....can someone please help?

PS I also tried without the @. symbol before my parameter and no luck either.

DELETE FROM tempMbrAccounts WHERE (maID = @.maID) is my delete query

Public Sub OnDeleteButtonClick(ByVal senderAs Object,ByVal eAs GridViewCommandEventArgs)Handles gvEnterAcct.RowCommandDim RecIDAs Integer If e.CommandName ="Delete"Then RecID = e.CommandArgument dsInsert.DeleteParameters.Add("@.maID", RecID) dsInsert.Delete() tempAcctTable()End If End Sub
If maID is your table's primary key, SetDataKeyNames="maID" for your gridview. You should be able to use the default DelectCommand of SqlDataSource without adding extra code.|||

I should add that I an not using the standard delete button that is implemented in a GridView. I am using an imagebutton.

I do have the datakey set to maID but that isn't working either.

|||

By using an ImageButton in a TemplateField, you can still use the standard way to do delete. Here is a sample which is working . ID is the Primary Key of the test table.

<div>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="ID"DataSourceID="SqlDataSource1"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ID"HeaderText="ID" ReadOnly="True"SortExpression="ID"/><asp:BoundFieldDataField="myCol"HeaderText="myCol"SortExpression="myCol"/><asp:TemplateField><ItemTemplate><asp:ImageButtonid="DeleteImgBtn"CommandName="Delete"ImageUrl="="~/images/delete.gif"runat="server"/></ItemTemplate>

</

asp:TemplateField>

</Columns></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"DeleteCommand="DELETE FROM [test] WHERE [ID] = @.ID"SelectCommand="SELECT [ID], [myCol] FROM [test]"><DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters></asp:SqlDataSource></div>

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499