Showing posts with label fill. Show all posts
Showing posts with label fill. Show all posts

Wednesday, March 21, 2012

Image field in Trigger

Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ?ThanksIf you are using SQL Server 2005 you can use the VARBINARY(MAX) type. if you are on a version prior to SQL Server 2k5 you can try to code your statement set based, eliminating the need for interim variables.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi , i'm using Sql Server 2000 . . . How can i eliminate the need for interim variables ?I've tryed to do this :UPDATA table1 SET img1 = (SELECT img2 FROM table2 where ...) where ...But the message is that i can use image field in trigger ...|||Hi,

did you have a look on:

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

"In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views. "

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Monday, March 19, 2012

Im tryiong to fill one list from another

I have a databound dropdownlist that returns an integer (listID) I have a listbox that should return all the people on a membership list that aren't on the committee list with the ListID returned by the dropdownlist. The listbos code is:

<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ListID" DataValueField="ListID"></asp:ListBox>

SqlDataSource2 code is:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mainshipsystemsConnectionString %>" >
</asp:SqlDataSource>

When the user clicks on the button for the dropdownlist to make their list selection I have the following in the code behind:

Protected Sub BtnSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSelect.Click
'make visible panel containing code to move members
Dim sListID As Integer = DDLGetList.Text
SqlDataSource2.SelectCommand = "SELECT MemberID, First, Last, Prefix, Suffix FROM Mainship.CE_Members WHERE (MemberID NOT IN (SELECT MemberID FROM Mainship.CE_Emails WHERE (ListID = 1)))"
End Sub

I get the following error:

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ListID'.

I have listID = 1 in the above example for testing purposes.

Diane

The select command does not return a value for ListId. You need to change the select command to something like this

SELECT MemberID,ListID, First, Last, Prefix, Suffix FROM Mainship.CE_Members WHERE|||

ListID isn't a field in CE_Members, so it isn't in the first part of the select statement. It's in the second part, as it's a field of CE_Emails

Protected Sub BtnSelect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSelect.Click
'make visible panel containing code to move members
Dim sListID As Integer = DDLGetList.Text
SqlDataSource2.SelectCommand = "SELECT MemberID, First, Last, Prefix,Suffix FROM Mainship.CE_Members WHERE (MemberID NOT IN(SELECTMemberID FROM Mainship.CE_Emails WHERE (ListID = 1)))"
End Sub

Diane

|||

Diane,

<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="ListID" DataValueField="ListID"></asp:ListBox>

The listbox expects a ListID to be returned.

SELECT MemberID, First, Last, Prefix, Suffix FROM Mainship.CE_Members

The first part of the query defines the fields to be return. ListID is not one of them so ListBox1 will have an error because it can not find ListID

|||

I feel like an idiot.

What you were saying made no sense for a good reason - ListID is only used as a filter for the member list. But I did indeed have the box returning listID. What I want it to return is member name. Of course it didn't work. In my defense I plead the looming tax deadline.

Thank you Ken. It's working now.

Diane