Iteration within Stored Procedure

A Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.

So, two questions
1. What is the form of an iterative loop in a Stored Procedure?
2. How do you call a Stored Procedure from inside another?

Thanks, td

1. while

2. just use exec your_stored_procecure_name

|||

Thanks KH. I'm afraid, though, I am very new & your help is a bit too cryptic for me. Sorry but I require a bit more.

In the interim I had figured out the EXEC (I presume this is an abbreviation of EXECUTE) but I have trouble with the parameters. The SP goes something like this:

...AS
@.RequestID BigInt
.....
EXEC InsertReviewer @.RequestID,@.RequestID,@.RequestDate,GETDATE()

The problem is that the SP doing the calling has a parameter of @.Request. So does the called SP. I want to pass the ID through with other parameters.

The WHILE, is it used with a BEGIN/END eg
WHILE @.NumReviewers<5
BEGIN

END

Thanks very much for your patience

td

|||Is this what you trying to do ?
declare @.NumReviewers int
-- Initialize the @.NumReviewers
select @.NumReviewers = 1
WHILE @.NumReviewers <= 5
BEGIN
-- execute your SP and pass in the @.NumReviewers
exec your_sp_name @.NumReviewers
-- increment the @.NumReviewers
select @.NumReviewers = @.NumReviewers + 1
END

it will not show the data from the DB

is have this code, and i know that i have a record with the ID=1 but it will not show the data from the record..

<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server"><asp:FormView ID="form1" runat="server" DataSourceID="SqlDataSource1"></asp:FormView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStrAccess%>" SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = ?)" ProviderName="<%$ ConnectionStrings:ConnStrAccess.ProviderName%>"> <SelectParameters> <asp:SessionParameter DefaultValue="1" Name="SiteMainID" Type="Int32" /> </SelectParameters></asp:SqlDataSource></asp:Content>

Why can't it show the record !??

Hello:

A few things are not right here:

1. YourSelectCommand should be:SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = @.MainID)" Yours (the ? is not forSqlDataSource);

2. The SessionParameter should look like:

<asp:SessionParameter DefaultValue="1" Name="MainID" SessionField="yourSessionMainIDValue" Type="Int32" />;

3.Your FormView should include at lease the <ItemTemplate> section to show your data.

Hope you can get your Access table work here.

|||Thx now it's works

It takes a long time to insert the first record each time when the program start

I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.

The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?

I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.

cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()

I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:

1. Start the PPC Program

2. Load DB into dataset

3. Create a ONE new record in dataset

4. Fill back to DB

When I take this four steps everytime, the filling time is almost 1s or even more!

Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)

However, when I give up the dataset and using the following code:

cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount

I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!

Last example does not include looking for updated rows in the DataSet and generating appropriate commands. Since it does less, it's faster.

sql

Issue inserting null value into a formview/gridview control

Hi,

My formview or gridview control stops updating or deleting a record once the record has a null value.

I have table tblTest with the following

pkID int NOT NULL **IDENTITY COLUMN**
string1 varchar(30)
string2 varchar(30)

I then create a SqlDataSource with the statement:

Select * From [tblTest]

I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.

I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.

So, if I had a record in my FormView like:

string1: foo
string2: bar

I can update and delete normally. And when I update to:

string1: foo
string2:

the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.

What am I doing wrong? Should i be binding to a different object?

Regards,

Chris

Do you set your DataKeyNames for the formview? ( I thought you did). Anyway, here is a working copy and you may find your isseue by yourself.

<asp:FormView ID="fv1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id"
AllowPaging="true" >

<EditItemTemplate>

col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></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
col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></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
id:

<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>'></asp:Label><br /
col1:

<asp:Label ID="col1_Label" runat="server" Text='<%# Bind("col1") %>'></asp:Label><br />
col2:
<asp:Label ID="col2_Label" runat="server" Text='<%# Bind("col2") %>'></asp:Label><br /
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete"
</asp:LinkButton
<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit"
</asp:LinkButton
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New"

Text="New"
</asp:LinkButton
</ItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn1 %>"
DeleteCommand="DELETE FROM [test1] WHERE [id] = @.id"
InsertCommand="INSERT INTO [test1] ( [col1], [col2]) VALUES ( @.col1, @.col2)"
SelectCommand="SELECT [id], [col1], [col2] FROM [test1]"
UpdateCommand="UPDATE [radios] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.id">
<DeleteParameters>
<asp:Parameter Name="id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

Ok, our SqlDataSources differ somewhat. Was your auto-generated, or did you create it manually? The following code was generated for me:

============================================================================

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2" InsertCommand="INSERT INTO [tblTest] ([col1], [col2]) VALUES (@.col1, @.col2)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [tblTest]" UpdateCommand="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2"> <DeleteParameters> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> </InsertParameters> </asp:SqlDataSource>

=============================================================================================

 Specifically, my update and delete statements have different, more complicated WHERE clauses. When I change the WHERE clause so that it only uses the ID, the formview control works as it should:

 DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id"

UpdateCommand
="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id"

Am I generating my statements incorrectly? It's bizarre that the SqlDataSource auto-generated command statements choke once they start working with null fields...

Regards,

Chris

|||

Hello,

Both of the codes are generated by the IDE, but you checked the "Use Optimistic Concurrency" to get the more complicated where clause. You may need find more information to use this. If you don't check this one, you will have the same code as I have.

HTH.

|||

That did it, thanks so much!

Regards,

Chris

|||The wizard portion that generates SQL Strings for optimistic queries has that bug. You can still use the wizard to generate the basic query, but you'll need to go in and modify it if any of the fields can be null.

Issue about full-text index

Hi: all,

I have created a full text index on a column(column1) in a table(table1).

There is one record in the table1. And for example, the column1 stores "Documents".

Table1.Column1:"Documents"

When I run the query:

select * from Table1 where contains(Column1,'Documents')

There is 1 one row be selected.

But when I run the query:

select * from Table1 where contains(Column1,'cum')

There is no row be selected.

So the contains can only search the whole word,right?

Can I search a part of the whole word?

Just like the result of : select * from Table1 where Column1 like '%cum%'?

Thanks,

Tracy

You can search the part of the word but only with Prefixed wild card...

select * from Table1 where contains(Column1,'"Doc*"')|||

So sql server full-text index doesn't support to search any part of a word, right?