PAGE HEADER: sqldatasource

Showing posts with label sqldatasource. Show all posts

Friday, March 30, 2012

Problem with SqlDataSource.Insert() using MasterPage

Hi,

because I have had problems with transporting of data from TextBoxes to SQL database in my application using SqlDataSource.Insert(), I tried to analyze it using Microsoft's sample code located here:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx

Everything worked well untill I have used masterpage. Since this moment my database table started receive only NULL values - the same problem, which I have in my application. Despite all data values were correct before calling SqlDataSource.Insert() method. Is it some bug or something else (my bug Smile ) and how can I resolve it? I'd like to use all advantages of masterpages and datasources together.

Here are non-working codes of spoken sample:

shelter3.aspx

<%@.PageLanguage="VB"MasterPageFile="~/MasterPage3.master"AutoEventWireup="false"CodeFile="shelter3.aspx.vb"Inherits="shelter3"Title="Untitled Page" %>
<asp:ContentID="Content1"ContentPlaceHolderID="Obsah"runat="Server">
<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"
DataTextField="nazev"DataValueField="code"/>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:intranet %>"
SelectCommand="SELECT nazev,code FROM smenky"InsertCommand="INSERT INTO smenky (nazev,code) VALUES (@.nazev,@.code)">
<InsertParameters>
<asp:FormParameterName="nazev"FormField="NazevBox"/>
<asp:FormParameterName="code"FormField="CodeBox"/>
</InsertParameters>
</asp:SqlDataSource>
<p>
<asp:TextBoxID="NazevBox"runat="server"/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="NazevBox"
Display="Static"ErrorMessage="Please enter a company name."/>
<p>
<asp:TextBoxID="CodeBox"runat="server"/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="CodeBox"
Display="Static"ErrorMessage="Please enter a phone number."/>
<p>
<asp:ButtonID="Button1"runat="server"Text="Insert New Shipper"/>
</asp:Content>

shelter3.aspx.vb

PartialClass shelter3
Inherits System.Web.UI.Page
ProtectedSub InsertShipper(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click
SqlDataSource1.Insert()
EndSub
EndClass
masterpage3.master

<%@.MasterLanguage="VB"CodeFile="MasterPage3.master.vb"Inherits="MasterPage3" %>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title>Untitled Page</title>
</head>
<body>
<formid="form1"runat="server">
<div>
<asp:contentplaceholderid="Obsah"runat="server">
</asp:contentplaceholder>
</div>
</form>
</body>
</html>

masterpage3.master.vb

PartialClass MasterPage3
Inherits System.Web.UI.MasterPage
EndClass

Thanks for any idea

I reproduced your issue, not sure what's causing the problem. However we can use asp:ControlParameter in this case:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

SelectCommand="SELECT id,name FROM test_MasterSqlDataSource" InsertCommand="insert into test_MasterSqlDataSource select @.id,@.name">

<asp:ControlParameter Name="name" PropertyName="Text" ControlID="CodeBox" />

</InsertParameters>

</asp:SqlDataSource>

I've tested and found the values can be inserted into database properly.

Problem with SqlDataSource using sub-query and date as parameters

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.

In my SqlDataSource, I setup the SQL like this.

select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)

Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appended

and exists (select 1 from detailTable where pid = masterTable.id and productNo = @.productNo)

The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.

Any suggestion on tackling this problem? Thanks!

hi,

U can avoid this by diffarent options...By above information I can explain like this

1 use UNION

2 Use Primary Key in your every Subquery Query followed by the Search.

3 Use Joins with valid Key Elements.etc

or send the required result columnes and the Table Design

bye

murthy

|||

Hi Murthy,

1. use UNION

I don't know how should I use UNION in master-detail structure. Please give more detail.

2. The primary key is already used in the where clause of the subquery. The column pid means the primary key ID in master table.

3. Use Join

The use of join is not desirable. I have to group the records back together afterward. I just want to search the master table but use detail record as criteria. If I join them without group, the master record will repeat themselves in the result.

Actually, I'm strange about the performance difference by using ADO.NET and SQL Server management studio.

|||

Hi,

Do this Use "#' table with one primary key ID as the Column column in all the condictions

and finally join all the Table with the key Elements..

Ex:

select <Key>,<Search column 1 > into #TableA from <Master table> where <Condition>

select <Key>,<Search column 2 > into #TableB from <Master table> where <Condition>

.....

....

and Finally

select <Search Column1>,<Search Column2>.<Search Column3>,... from #TableA, #TableB, #TableC...where #TableA.Key=#TableB.Key,#TableA.Key=#TableC.key etc

drop all temp table

your result is ready now...

|||

This approach seems making the simple request into a complex one.

Wednesday, March 7, 2012

Problem with querystringparameter

Hi, I'm having problems with the querystring parameter in a SQLDataSource, I think the SelectCommand is not getting the value of the Querystringparameter, here is the code:

<asp:SqlDataSourceID="SqlDataSource1"

runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"

SelectCommand="SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'">

I've tried many things like...

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' & @.nombre & '%'"

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE '%' + @.nombre + '%'"

And nothing work, where's the problem?, I send the value of the SelectCommand of the DataSource and the @.name is not replaced by any value.

Use

"SELECT cedula,nombre,direccion FROM clientes WHERE nombre LIKE @.nombre"

and set the value of @.nombre as

@.nombre = '%' + yourValue + '%'

|||The third option should work.

Friday, March 30, 2012

Problem with SqlDataSource.Insert() using MasterPage

Problem with SqlDataSource using sub-query and date as parameters

Wednesday, March 7, 2012

Problem with querystringparameter

PAGE HEADER

Blog Archive

About Me