Hi
Using SQLserver 2000 I have to enter a huge data (nearly 8000 charcters) into a field, which data type is suitable for that and what is the method to store data.
You might need to use a VARCHAR(8000) or a text datatype; however, there is not enough information.
Is your data divisible into different definite pieces? Do you need to support unicode? Is your data numeric? binary? Is your data XML Is there additional data that needs to be stored with the 8000 characters?
// This code is called 10 times with a select * from where item="xxx"
P21Conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
if (myReader.Read()) {
thanks
Rodin your select or sproc, use ISNULL or COALESCE around the columns that have null values.
cs|||Thank you
I did that and now it works perferct
Rod
Hi All,
I need a solution for the following:
I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).
Now I need to transfer this field into a field with data type DATE.
I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?
Any hint for me is very
Thanks in Advance
ulrike
You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.
Code Snippet
setDATEFORMAT ymd
selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2
|||You could use CONVERT function with 112 style:
Code Snippet
declare @.dt varchar(20)
set @.dt ='20070507'
selectconvert(datetime,@.dt,112)
|||Hello again,
Thanks for your advise.
I tried to run the following code:
selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt
into WHUser.[tbl_target]
from WHUser.[tbl_source]
BUT it terminated with error:
“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.
What’s going wrong? Please give me some mor advise.
Thanks in advance,
ulrike
|||Hi ulrikeG,
SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.
Code Snippet
create table dbo.t1 (
c1 char(8) null,
c2 datetime null
)
go
insert into dbo.t1(c1) values('20070606')
go
select * from dbo.t1
go
update dbo.t1
set c2 = c1
go
select * from dbo.t1
go
drop table dbo.t1
go
AMB
|||
ulrikeG wrote:
“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.
Maybe there's bad data in your table.
Try running a query WHERE IsDate (string) = 0.
|||Change your code to:
Code Snippet
select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt
into WHUser.[tbl_target]
from WHUser.[tbl_source]
Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors
Hello,
I have a page with a detailsview where I can add articles (Generated by visual studio). Now the table contains a field (Autor) wich must contain the username of the Autor from the article. But when I run my page now I have to give it in manually ( in a textbox). I've searching for a way to bind the Profile Username with the insert Sql Query, @.Autor value.
I tought maybe I should insert the value of the Profile username in the textbox and put the textbox visibel on false.
But When i saw the component code I saw that Text= is already bound, so it's not possible to insert a value
<asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur")%>'>Here is whole the page code (line 23 is the textbox).
1<asp:FormView ID="FormView1" runat="server" DataKeyNames="id" DataSourceID="SqlDataSource1" AllowPaging="True" CellPadding="4" ForeColor="#333333" style="left: 30%; position: relative">2 <EditItemTemplate>3 <asp:Label ID="idLabel1" Visible="false" runat="server" Text='<%# Eval("id")%>'></asp:Label>4 <asp:TextBox ID="auteurTextBox" Visible="false" runat="server" Text='<%# Bind("auteur")%>'>5 </asp:TextBox>6 soort:7 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort")%>'>8 </asp:TextBox><br />9 titel:10 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel")%>'>11 </asp:TextBox><br />12 text:13 <asp:TextBox ID="textTextBox" Height="200px" TextMode="MultiLine" Rows="20" Width="260px" runat="server" Text='<%# Bind("text")%>'>14 </asp:TextBox><br />15 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"16 Text="Update">17 </asp:LinkButton>18 <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"19 Text="Cancel">20 </asp:LinkButton>21 </EditItemTemplate>22 <InsertItemTemplate>23 <asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur")%>'>24 </asp:TextBox><br />25 Soort:26 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort")%>'>27 </asp:TextBox><br />28 titel:29 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel")%>'>30 </asp:TextBox><br />31 text:32 <asp:TextBox ID="textTextBox" runat="server" Text='<%# Bind("text")%>'>33 </asp:TextBox><br />34 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"35 Text="Insert">36 </asp:LinkButton>37 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"38 Text="Cancel">39 </asp:LinkButton>40 </InsertItemTemplate>41 <ItemTemplate>42 <asp:Label ID="idLabel" Visible="false" runat="server" Text='<%# Eval("id")%>'></asp:Label><br />43 <asp:Label ID="auteurLabel" Visible="false" runat="server" Text='<%# Bind("auteur")%>'></asp:Label><br />44 soort:45 <asp:Label ID="soortLabel" runat="server" Text='<%# Bind("soort")%>'></asp:Label><br />46 titel:47 <asp:Label ID="titelLabel" runat="server" Text='<%# Bind("titel")%>'></asp:Label><br />48 text:49 <asp:Label ID="textLabel" runat="server" Text='<%# Bind("text")%>'></asp:Label><br />50 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"51 Text="Edit">52 </asp:LinkButton>53 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"54 Text="Delete">55 </asp:LinkButton>56 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"57 Text="New">58 </asp:LinkButton>59 </ItemTemplate>60 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />61 <EditRowStyle BackColor="#2461BF" />62 <RowStyle BackColor="#EFF3FB" />63 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />64 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />65 </asp:FormView>66 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"67 ConnectionString="<%$ ConnectionStrings<img src="http://pics.10026.com/?src=images/smilies/biggrinn.gif" border="0" alt="">atabankConnectie%>" DeleteCommand="DELETE FROM [artikel2] WHERE [id] = @.original_id AND [auteur] = @.original_auteur AND [soort] = @.original_soort AND [titel] = @.original_titel AND [text] = @.original_text"68 InsertCommand="INSERT INTO [artikel2] ([auteur], [soort], [titel], [text]) VALUES (@.auteur, @.soort, @.titel, @.text)"69 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [artikel2] WHERE [auteur] = @.auteur"70 UpdateCommand="UPDATE [artikel2] SET [auteur] = @.auteur, [soort] = @.soort, [titel] = @.titel, [text] = @.text WHERE [id] = @.original_id AND [auteur] = @.original_auteur AND [soort] = @.original_soort AND [titel] = @.original_titel AND [text] = @.original_text">71 <DeleteParameters>72 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">73 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">74 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">75 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">76 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">77 </DeleteParameters>78 <UpdateParameters>79 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">80 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">81 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">82 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">83 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">84 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">85 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">86 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">87 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">88 </UpdateParameters>89 <InsertParameters>90 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">91 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">92 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">93 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">94 </InsertParameters>95 </asp:SqlDataSource>96 </LoggedInTemplate>97 </asp:LoginView>98</asp:Content>99 Can somebody help me?Hi,
instead of doing that in the asp code, you could achieve the same goal in the code behind:
1. Removed the insert parameter 'auteur' for the asp code
2. add in the page load event the following code:
protected void Page_Load(object sender, EventArgs e)
{
SqlParameter param = new SqlParameter("auteur", SqlDbType.NVarChar);
param.Value = Profile.UserName;
SqlDataSource1.InsertParameters.Add(param);
}
It may need some adjustment to meet your needs but I believe you got the idea :)
Cheers,
Yani
|||Im having the exact same problem and Im a Newbie......i can't send the profile.username into the d.b. Could it be possible to elaborate a bit, my detailsview looks almost identical. I've spent two frustrating days and hope to have this figured out........... is there a way to put it into the sql insert statement?? im not the best coder by far!
Thanks!
|||i tried using your example but keep getting errors
SqlParameter
param =newSqlParameter("profile",SqlDbType.NVarChar);param.Value = Profile.UserName;
SqlDataSource1.InsertParameters.Add(param);
it keeps telling me that
Error 1 The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(System.Web.UI.WebControls.Parameter)' has some invalid arguments C:\Documents and Settings\Karl\My Documents\Visual Studio 2005\WebSites\WebSite_fitness\Members\diet_journal.aspx.cs 21 9 C:\...\WebSite_fitness\
Error 2 Argument '1': cannot convert from 'System.Data.SqlClient.SqlParameter' to 'string' C:\Documents and Settings\Karl\My Documents\Visual Studio 2005\WebSites\WebSite_fitness\Members\diet_journal.aspx.cs 21 45 C:\...\WebSite_fitness\
im totally helpless and frustrated....please help!!
|||The SelectParameters collection is not a collection of SqlParameter. Use another overload of the add function:
SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);
Cheers,
Yani
|||Thanks Yani, appreciate it... im going to try it when I get home. is it possible to use it in the asp detailsview? basically i bind a textbox during insert and have this problem...... either i can set the to text = bind("profile") or text= membership.get()username. The latter returns my username but wont insert it into my table...lol im quite frustrated and my experience is somewhat lacking..... i use the controls kinda out of the box.......
thanks again !
|||if maybe u can help with the asp aspect...... this is what my code looks like.... i cant get your page load event to work...... can u maybe walk me through step by step... im really having a rough time!
Thanks!!
<%
@.PageLanguage="C#"MasterPageFile="~/Members/MasterPage.master"AutoEventWireup="true"CodeFile="diet_journal.aspx.cs"Inherits="Members_diet_journal"Title="Untitled Page" %><
asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server"> Diet Journal<br/> <asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="meal_hist_id_pk"DataSourceID="SqlDataSource1"Height="50px"Style="position: static"Width="125px"DefaultMode="Insert"><Fields><asp:BoundFieldDataField="meal_hist_id_pk"HeaderText="meal_hist_id_pk"InsertVisible="False"ReadOnly="True"SortExpression="meal_hist_id_pk"/><asp:TemplateFieldHeaderText="date"SortExpression="date"><EditItemTemplate><asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("date") %>'></asp:TextBox></EditItemTemplate><InsertItemTemplate><asp:CalendarID="Calendar1"runat="server"SelectedDate='<%# Bind("date") %>'Style="position: static"></asp:Calendar></InsertItemTemplate><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Bind("date") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundFieldDataField="meal_des_fk"HeaderText="meal_des_fk"SortExpression="meal_des_fk"/><asp:TemplateFieldHeaderText="profile"SortExpression="profile"><EditItemTemplate><asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("profile") %>'></asp:TextBox></EditItemTemplate><InsertItemTemplate><asp:TextBoxID="TextBox1"runat="server"Text='<%# bind("profile") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:LabelID="Label1"runat="server"Text='<%# Bind("profile") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundFieldDataField="calories"HeaderText="calories"SortExpression="calories"/><asp:BoundFieldDataField="fat"HeaderText="fat"SortExpression="fat"/><asp:BoundFieldDataField="carbs"HeaderText="carbs"SortExpression="carbs"/><asp:BoundFieldDataField="protein"HeaderText="protein"SortExpression="protein"/><asp:BoundFieldDataField="fibre"HeaderText="fibre"SortExpression="fibre"/><asp:CommandFieldShowInsertButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\diet_journal.mdf;Integrated Security=True;User Instance=True"DeleteCommand="DELETE FROM [meal_history] WHERE [meal_hist_id_pk] = @.original_meal_hist_id_pk AND [date] = @.original_date AND [meal_des_fk] = @.original_meal_des_fk AND [profile] = @.original_profile AND [calories] = @.original_calories AND [fat] = @.original_fat AND [carbs] = @.original_carbs AND [protein] = @.original_protein AND [fibre] = @.original_fibre"InsertCommand="INSERT INTO [meal_history] ([date], [meal_des_fk], [profile], [calories], [fat], [carbs], [protein], [fibre]) VALUES (@.date, @.meal_des_fk, @.profile, @.calories, @.fat, @.carbs, @.protein, @.fibre)"OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient"SelectCommand="SELECT [meal_hist_id_pk], [date], [meal_des_fk], [profile], [calories], [fat], [carbs], [protein], [fibre] FROM [meal_history]"UpdateCommand="UPDATE [meal_history] SET [date] = @.date, [meal_des_fk] = @.meal_des_fk, [profile] = @.profile, [calories] = @.calories, [fat] = @.fat, [carbs] = @.carbs, [protein] = @.protein, [fibre] = @.fibre WHERE [meal_hist_id_pk] = @.original_meal_hist_id_pk AND [date] = @.original_date AND [meal_des_fk] = @.original_meal_des_fk AND [profile] = @.original_profile AND [calories] = @.original_calories AND [fat] = @.original_fat AND [carbs] = @.original_carbs AND [protein] = @.original_protein AND [fibre] = @.original_fibre"OnSelecting="SqlDataSource1_Selecting"><DeleteParameters><asp:ParameterName="original_meal_hist_id_pk"Type="Int32"/><asp:ParameterName="original_date"Type="DateTime"/><asp:ParameterName="original_meal_des_fk"Type="Int32"/><asp:ParameterName="original_profile"Type="String"/><asp:ParameterName="original_calories"Type="Int32"/><asp:ParameterName="original_fat"Type="Int32"/><asp:ParameterName="original_carbs"Type="Int32"/><asp:ParameterName="original_protein"Type="Int32"/><asp:ParameterName="original_fibre"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="date"Type="DateTime"/><asp:ParameterName="meal_des_fk"Type="Int32"/><asp:ParameterName="profile"Type="String"/><asp:ParameterName="calories"Type="Int32"/><asp:ParameterName="fat"Type="Int32"/><asp:ParameterName="carbs"Type="Int32"/><asp:ParameterName="protein"Type="Int32"/><asp:ParameterName="fibre"Type="Int32"/><asp:ParameterName="original_meal_hist_id_pk"Type="Int32"/><asp:ParameterName="original_date"Type="DateTime"/><asp:ParameterName="original_meal_des_fk"Type="Int32"/><asp:ParameterName="original_profile"Type="String"/><asp:ParameterName="original_calories"Type="Int32"/><asp:ParameterName="original_fat"Type="Int32"/><asp:ParameterName="original_carbs"Type="Int32"/><asp:ParameterName="original_protein"Type="Int32"/><asp:ParameterName="original_fibre"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="date"Type="DateTime"/><asp:ParameterName="meal_des_fk"Type="Int32"/><asp:ParameterName="profile"Type=String/><asp:ParameterName="calories"Type="Int32"/><asp:ParameterName="fat"Type="Int32"/><asp:ParameterName="carbs"Type="Int32"/><asp:ParameterName="protein"Type="Int32"/><asp:ParameterName="fibre"Type="Int32"/></InsertParameters></asp:SqlDataSource><br/>|||Hi,
when you add parameters from the code behind like this :
SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);
you need to remove the asp tag for that parameter from the aspx page:
<asp:ParameterName="profile"Type=String/>
From <InsertParameters> tag.
Cheers,
Yani
|||Thanks once again yani, I'll be trying that once i get home!! So if i understand correctly, i need to addSqlDataSource1.InsertParameters.Add("profile",Profile.UserName); inside of the page load event
and remove<asp:ParameterName="profile"Type=String/>....... from the aspx page. For the insert command and values, do i keep @.profile??
also, for my insert template for the textbox...... do i leave it as bind(profile) ?
Thanks a million for taking the time with me, its greatly appreciated...... I'll owe you at least a case of beer!
Karl
|||
Well,
let me try to explain a lil bit more.
So you have for the InsertParameters in the asp code(aspx) sth like:
<InsertParameters>
// some parameters
<asp:ParameterName="profile"Type=String/>
// some parameters
</InsertParameters>
When you compile the whole web site, the aspx pages are parsed and transformed into code, sth like
public class YourPageNameHere: Page
{
// some functions
//
SqlDataSource1.InsertParameters.Add(paramName, paramvalue);
}
but in your case
<asp:ParameterName="profile"Type=String/>
you have not specified the value here.
So it does not know what to put here.
So instead of doing that in the asp code.
you could do it in the code behind.
SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);
(in the page load function);
where Profile object is accessible.
For the insert command and values, do i keep @.profile??
also, for my insert template for the textbox...... do i leave it as bind(profile) ?
In the insert command - it should remain - since it is the pure sql statement. So keep it as is.
For the insert template...i think you should remove it... actually what are you trying to do there ?
to put as parameter @.profile - the name entered in the text box, or the Profile.UserName ?!
If it is the first you could do sth like:
<asp:ControlParameter ControlID="TextBox1" PropertyName="Text" Name="profile" Type="string" />
Where TextBox1 is the ID of the TextBox control that will be used for entering the profile name.
If this does not solve the problem , please clarify what are you trying to accomplish exactly.
Cheers,
Yani
|||
Awesome, your explanation really cleared things up.... thank you for being super patient and so clear on your explanations...... I'll try this as soon as i get home!.... I can't wait to get this up and running!!
Karl
|||
Hi again, I just tried what you had suggested.........
and keep getting this error
Cannot insert the value NULL into column 'profile', table 'C:\DOCUMENTS AND SETTINGS\KARL\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\WEBSITE_FITNESS\APP_DATA\DIET_JOURNAL.MDF.dbo.meal_history'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Im using your page load event like this :
protectedvoid Page_Load(object sender,EventArgs e)
{
newSqlParameter("profile",SqlDbType.NVarChar);SqlDataSource1.InsertParameters.Add(
"profile", Profile.UserName);
}
and my asp code looks like this:
<%
@.PageLanguage="C#"MasterPageFile="~/Members/MasterPage.master"AutoEventWireup="true"CodeFile="diet_journal.aspx.cs"Inherits="Members_diet_journal"Title="Untitled Page" %><
asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server"> Diet Journal<br/> <asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"DefaultMode="Insert"Height="50px"Style="position: static"Width="125px"><Fields><asp:TemplateFieldHeaderText="date"SortExpression="date"><EditItemTemplate><asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("date") %>'></asp:TextBox></EditItemTemplate><InsertItemTemplate><asp:CalendarID="Calendar1"runat="server"SelectedDate='<%# Bind("date") %>'Style="position: static"></asp:Calendar></InsertItemTemplate><ItemTemplate><asp:LabelID="Label1"runat="server"Text='<%# Bind("date") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundFieldDataField="profile"HeaderText="profile"SortExpression="profile"/><asp:CommandFieldShowInsertButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\diet_journal.mdf;Integrated Security=True;User Instance=True"InsertCommand="INSERT INTO meal_history(date, profile) VALUES (@.date, @.profile)"ProviderName="System.Data.SqlClient"SelectCommand="SELECT [date], [profile] FROM [meal_history]"></asp:SqlDataSource><br/></asp:Content>
To make it simple i just want to insert the date and profile.username............. I keep getting the null profile error though ...i dont know why it wont pass my value to the d.b
Im sorry if Im not getting it, I thought I was a little smarter than this but this problem is kicking my butt!!!
Karl
|||
SUCCESS!!! I got it to work, I used your explaination and I went about it a little bit differently
for the page load i used........
SqlDataSource1.InsertParameters.Add(
"profile",Membership.GetUser().UserName);
and it works.....I guess the profile.name wasn't what i needed, but I did remove the other insert parameter from the aspx page....."profile"
Thank you soooooo much ........I didnt realize thatI have so much to learn!
|||Hi Karl,
it's great you did it on your own :) but not just copying sth from someone that you don't understand.
We all study each day, so there is much left to be learnt ;)
Cheers,
Yani
|||
Very true Yani !!
Thanks again for all the patience and help, it is very much appreciated!
Have a great day,
Karl
I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).
eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.
Does anyone know the easy way to do this? / Syntax for the trigger?
Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:
Creating and Modifying FOREIGN KEY Constraints
|||I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)
My statement in('Yes','No','') does not work for my check constraint.
Does anyone know what my syntax would be?
|||A quick sample:
CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))
ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))
--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')
For mor information, you can refer to:?ALTER?TABLE?