how to assign ConnectionString to a SqlDataSource within the namespace?

hi

i am trying to assignConnectionString to aSqlDataSource within thenamespace but i am getting this error

plz advise

Object reference not set to an instance of an object.

namespace myNameSpace
{

public partial class NRP : System.Web.UI.Page
{

SqlDataSource sds1;

protected void Page_PreInit(object sender, EventArgs e)
{
sds1= new SqlDataSource();
sds1.ID = "sds1";
this.Controls.Add(sds1);

sds1.ConnectionString = ConfigurationManager.ConnectionStrings["my_conn_str"].ToString();

}
}
}

namespace myNameSpace
{

public partial class NRP : System.Web.UI.Page
{


protected void Page_PreInit(object sender, EventArgs e)
{
SqlDataSource sds1= new SqlDataSource();
sds1.ID = "sds1";
this.Controls.Add(sds1);

sds1.ConnectionString = ConfigurationManager.ConnectionStrings["my_conn_str"].ToString();

}
}
}

How to assign a value to a parameter?

Hello everyone,

i have the parameter in my stored procedure that i am using as a sqldatasource.

Now in one of the events, i need to assign a value to the parameter. How can i do that?

Microsoft is changing the syntax so often, all solutions i found on this forum just don't work anymore, like:

SqlDataSource1.SelectParameters[

"@.CompareInteger"].value="1"

OR

SqlDataSource1.SelectParameters["@.CompareInteger"].DefaultValue="1"

I guess the SelectParameter - became 'ReadOnly'..But how to assign value to a parameter now?!?

Thanks for any help

They aren't changing the syntax.. Hasn't ever changed, but those methods you mentioned above only work in certain circumstances, because they are "hacks".

What you want to do is capture the SqlDataSource1_Selecting event. From within that event, you have access to the underlying command (and parameters collection). Your code from within that event would looks something like:

e.Parameters["@.CompareInteger"].Value="1;

Or

e.parameters("@.compareinteger").value="1" if you are using VB.NET

|||

it does not work, Motley. It does not work.

for SqlDataSource1_Selecting event, e does not have such an option - e.parameters - check it for yourself...:(

So, the question still remains - HOW TO ASSIGN VALUE TO A PARAMETER?

|||IS there any way to assign the value to a parameter?!?!? in any event procedure?|||

If it's not e.parameters, then it's one of the following:

e.SelectCommand.Parameters

or

e.Command.Parameters

|||

thank you, Motley ! Will remember it now.

How to Add ControlParameter to SqlDataSource at Runtime?

Hi!

My question is exactly the subject.

My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)

1Dim SQLDSAs SqlDataSource =New SqlDataSource()23 SQLDS.ID ="CustomerDataSource"4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString5 SQLDS.SelectCommand ="select customerid,companyname,contactname,country from customers"6 SQLDS.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.customerid,@.companyname,@.contactname,@.country)"7 SQLDS.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.customerid"8 SQLDS.DeleteCommand ="delete from customers where customerid=@.customerid"910 SQLDS.UpdateParameters.Add(New Parameter("companyname"))11 SQLDS.UpdateParameters.Add(New Parameter("contactname"))12 SQLDS.UpdateParameters.Add(New Parameter("country"))13 SQLDS.UpdateParameters.Add(New Parameter("customerid"))1415 Page.Controls.Add(SQLDS)1617If Not Page.IsPostBackThen18 GridView1.DataKeyNames =New String() {"customerid"}19 GridView1.DataSourceID = SQLDS.ID2021' ... and so on

The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.

How can I synchronize the DetailsView?

I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this:

 
1<asp:SqlDataSource ID="SqlDataSource1" runat="server"2 ConnectionString="<%$ ConnectionStrings:Pubs%>"3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">4 <SelectParameters>5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />6 </SelectParameters>7</asp:SqlDataSource>8
 

Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?

Thanks!

Here is how to add it in code:

ControlParameter cp = new ControlParameter("state", "DropDownList1", "SelectedValue");

SqlDataSource1.SelectParameters.Add(cp);

|||cant get it to work.|||

using Northwind database.

i just want to synchronize the DetailsView to show the record selected in the GridView.

thanks!

the code of the web form:

ASPX.VB

1Imports SqlHelper2Imports System.Data3Imports System.Data.SqlClient45PartialClass Customers6Inherits System.Web.UI.Page78910Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load1112Dim SQLDS_GVAs SqlDataSource =New SqlDataSource()13Dim SQLDS_DVAs SqlDataSource =New SqlDataSource()1415' GridView16 SQLDS_GV.ID ="CustomerDataSource_GV"17 SQLDS_GV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString18 SQLDS_GV.SelectCommand ="select customerid,companyname,contactname,country from customers"19 SQLDS_GV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"20 SQLDS_GV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where custid=@.custid"21 SQLDS_GV.DeleteCommand ="delete from customers where customerid=@.custid"2223 SQLDS_GV.UpdateParameters.Add(New Parameter("companyname"))24 SQLDS_GV.UpdateParameters.Add(New Parameter("contactname"))25 SQLDS_GV.UpdateParameters.Add(New Parameter("country"))26 SQLDS_GV.UpdateParameters.Add(New Parameter("custid"))2728'DetailsView29 SQLDS_DV.ID ="CustomerDataSource_DV"30 SQLDS_DV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString31 SQLDS_DV.SelectCommand ="select customerid,companyname,contactname,country from customers"32 SQLDS_DV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"33 SQLDS_DV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.custid"34 SQLDS_DV.DeleteCommand ="delete from customers where customerid=@.custid"3536 SQLDS_DV.UpdateParameters.Add(New Parameter("companyname"))37 SQLDS_DV.UpdateParameters.Add(New Parameter("contactname"))38 SQLDS_DV.UpdateParameters.Add(New Parameter("country"))39 SQLDS_DV.UpdateParameters.Add(New Parameter("custid"))4041'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")4243 Page.Controls.Add(SQLDS_GV)44 Page.Controls.Add(SQLDS_DV)4546If Not Page.IsPostBackThen4748 GridView1.DataKeyNames =New String() {"customerid"}49 GridView1.DataSourceID = SQLDS_GV.ID5051' CRIAR AS COLUNAS MANUALMENTE52 GridView1.AutoGenerateColumns =False53 GridView1.AllowPaging =True54 GridView1.PageSize = 105556Dim bf1As BoundField =New BoundField()57Dim bf2As BoundField =New BoundField()58Dim bf3As BoundField =New BoundField()59Dim bf4As BoundField =New BoundField()6061 bf1.HeaderText ="ID"62 bf1.DataField ="customerid"63 bf1.ReadOnly =True64 bf1.SortExpression ="customerid"6566 bf2.HeaderText ="Empresa"67 bf2.DataField ="companyname"68 bf2.SortExpression ="companyname"6970 bf3.HeaderText ="Contato"71 bf3.DataField ="contactname"72 bf3.SortExpression ="contactname"7374 bf4.HeaderText ="País"75 bf4.DataField ="country"76 bf4.SortExpression ="country"7778Dim cfAs CommandField =New CommandField()79 cf.ButtonType = ButtonType.Button80 cf.ShowCancelButton =True81 cf.ShowEditButton =True82 cf.ShowSelectButton =True83 cf.ShowDeleteButton =True8485 cf.CancelText ="Cancelar"86 cf.EditText ="Alterar"87 cf.DeleteText ="Excluir"88 cf.UpdateText ="Gravar"89 cf.SelectText ="Selecionar"9091 GridView1.Columns.Add(cf)92 GridView1.Columns.Add(bf1)93 GridView1.Columns.Add(bf2)94 GridView1.Columns.Add(bf3)95 GridView1.Columns.Add(bf4)96'GridView1.Columns.Add(bf5)9798 ' ****************************99100 'Dim cp As ControlParameter = New ControlParameter("customerid", "GridView1", "SelectedValue")101 'SQLDS_DV.SelectParameters.Add(cp)102 'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")103104105 DetailsView1.DataKeyNames =New String() {"customerid"}106 DetailsView1.DataSourceID = SQLDS_DV.ID107108 DetailsView1.AutoGenerateRows =False109110 Dim bf1_DVAs BoundField =New BoundField()111Dim bf2_DVAs BoundField =New BoundField()112Dim bf3_DVAs BoundField =New BoundField()113Dim bf4_DVAs BoundField =New BoundField()114115 bf1_DV.HeaderText ="ID"116 bf1_DV.DataField ="customerid"117 bf1_DV.ReadOnly =True118 bf1_DV.SortExpression ="customerid"119120 bf2_DV.HeaderText ="Empresa"121 bf2_DV.DataField ="companyname"122 bf2_DV.SortExpression ="companyname"123124 bf3_DV.HeaderText ="Contato"125 bf3_DV.DataField ="contactname"126 bf3_DV.SortExpression ="contactname"127128 bf4_DV.HeaderText ="País"129 bf4_DV.DataField ="country"130 bf4_DV.SortExpression ="country"131132Dim cf_DVAs CommandField =New CommandField()133 cf_DV.ButtonType = ButtonType.Button134 cf_DV.ShowCancelButton =True135 cf_DV.ShowEditButton =True136 cf_DV.ShowSelectButton =True137 cf_DV.ShowDeleteButton =True138139 cf_DV.CancelText ="Cancelar"140 cf_DV.EditText ="Alterar"141 cf_DV.DeleteText ="Excluir"142 cf_DV.UpdateText ="Gravar"143 cf_DV.SelectText ="Selecionar"144145 DetailsView1.Fields.Add(bf1_DV)146 DetailsView1.Fields.Add(bf2_DV)147 DetailsView1.Fields.Add(bf3_DV)148 DetailsView1.Fields.Add(bf4_DV)149 DetailsView1.Fields.Add(cf_DV)150151'SQLDS_DV.SelectParameters.Add("customerid", TypeCode.String, "GridView1.SelectedValue")152153Dim cpAs ControlParameter =New ControlParameter("custid", TypeCode.String,"GridView1", GridView1.SelectedValue.ToString())154 SQLDS_DV.SelectParameters.Add(cp)155156 SQLDS_GV.DataBind()157 SQLDS_DV.DataBind()158End If159 End Sub160161 Protected Sub GridView1_SelectedIndexChanged(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles GridView1.SelectedIndexChanged162'DetailsView1.DataBind()163 'DetailsView1.DataKey = GridView1.SelectedDataKey164End Sub165166End Class167

ASPX 

1<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Customers.aspx.vb" Inherits="Customers" %>23<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">45<html xmlns="http://www.w3.org/1999/xhtml" >6<head runat="server">7 <title>Untitled Page</title>8</head>9<body>10 <form id="form1" runat="server">11 <div>12 <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True" AutoGenerateColumns="False">13 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />14 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />15 <EditRowStyle BackColor="#999999" />16 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />17 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />18 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />19 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />20 </asp:GridView>21 <br />22 <asp:DetailsView ID="DetailsView1" runat="server" CellPadding="4" ForeColor="#333333"23 GridLines="None" Height="50px" Width="125px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" DefaultMode="Edit">24 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />25 <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />26 <EditRowStyle BackColor="#999999" />27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />30 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />31 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />32 </asp:DetailsView>3334 </div>35 </form>36</body>37</html>38

How to access underlaying DataView of SQLDataSouce in its Selected event

I need access to the underlying DataView associated with a declaratively defined SQLDataSource. Ideally, I need this access in the SQLDataSource'sSelected event. I know that I can use the SQLDataSouce.Select()method to return the underlying DataView object. But, there is a GridView control bound to the datasource so I don't want to explicity call Select() since it will end up being called again implicity by the gridview itself. I have seen that this can be done with an ObjectDataSouce as follows:

void odsDataSouce_Selected(object sender, ObjectDataSourceStatusEventArgs e) {
DataView oDV = (DataView)e.ReturnValue;
}

Is there any way to accomplish the same thing with a SQLDataSouce? It seems hard to believe that the query results are not exposed to the Selected event.

Thanks!

have a look through my posts to see how to get the underlying data table, and this all rows/columns|||

sbyard,

I saw your post about how to use a sqldatasource programmatically but that still involves using the select() method. In my situation a gridview is bound to the datasource so the select is implicit. I need access to the data in an event such as the selected event.

Thanks

|||

You cannot get to the underlying data the way that you want to. However...

By the time the selected event is raised, the grid will be populated, so you can iterate the rows and columns.

If you want to process the data BEFORE it is bound, then you wil have to create your own data table and fill this manually, process the data, then bind it. This is what we were mostly doing in ASP.NET 1.1 anyway.

|||Thanks for your help.

How to Access SqlDataSource Fields from Code?

I 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 myDataSourceAs 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.ReadThen
If Convert.IsDBNull(myDataSource("CusAgentPhoto"))Then
ImageAgent.ImageUrl ="/photos/nophoto.gif"
Else
ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString
EndIf
IfConvert.IsDBNull(myDataSource("CusCompanyLogo"))Then
ImageCompany.ImageUrl="/photos/nophoto.gif"
Else
ImageCompany.ImageUrl=OfficeImagePath & myDataSource("CusCompanyLogo").ToString
EndIf
End If

What would be the correct way to get at the DataSet fields that contain the filenames?

Programmatically accessing values from datasource controls:http://www.mikesdotnetting.com/Article.aspx?ArticleID=45

Also, How to conditionally show an image:http://www.mikesdotnetting.com/Article.aspx?ArticleID=18

|||

That was the info I needed. Excellent reference!