Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

datatype casting in derived column

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

Sub-expressions and literals in a derived column expression are always DT_WSTR, but you can cast the expression result to DT_STR by wrapping the whole expression with a cast:

(DT_STR,<length>,<codepage>)("Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO)

That should eliminate the need for the data convert transform after the derived column.

However, the error you listed looks like it was coming from the derived column, and I'm not sure why that would be the case if PROP_NO is of type DT_WSTR...

|||thanks for trying, but i'm still getting the same error msg, anyhow, i create a script component and concatenate the strings together, not the solution i wanted but it should work for now

DataTable and stored Procedure insertion

hi,

How can I insert a DataTable as a whole into the Database (into an existing table) using a stored procedure?

can I just send the DataTable as a parameter to the procedure?

how can I do it?

thanx

Hello,

You need to iterate through the Rows (DataRows) of that DataTable and to insert them one by one to the database.

HTH
Regads

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
>
|||Thanks for the quick response. It was exactly what I needed to know. I have
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since this
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first, if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>
|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
|||I was confused and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, but
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the select
> clause directly. You may also want to add some more complex logic to adjust
> the date time based on the timezone if you expect more than one timezone to
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

Dataset rows?

Hello Team

i want to insert more than one row to the dataset before update the sqladapter for ex i want to insert rows for orderlines then i send them all to sql by updating adapter

is it done by javascript ? because when i press the button a postback hapend then it clears the dataset so the new row clears the old one

any idea Thanks lot

Hi,

this probably relates to that the DataSet instance isn't preserved over postbacks? E.g you have a DataSet to which (a DataTable in it) you add the row when Button is clicked, but since DataSet isn't using anything as store to survive over postbacks, it gets recreated. E.g standard member variables do not surive postbacks in ASP.NET, they need to use something to keep them alive (ViewState, Session, cache etc)

DataSet and Insert method

hi,

i created a query to insert a row in DataSet in Visual Studio 2005. i gave the method name to the query i created. as i understood it returns '1' if successful or '0' if not.

is it possible to get the ID or the row instead?

what did it say,or whats the eror code if there is?|||

there is no error. the point is i would like to get the id of the row that i insert instead of default int value.

|||Hi,

are you using a SQL 2k5? There is a new ouput clause in the syntax, where you can get values back. Look in the BOL for more information about that, or raise a hand if you need further assistance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

i am using SQLExpress...

sorry should have mentioned earlier.

|||

Hi,

ok then go the OUTPUT way (described in the BOL)

INSERT INTO Sometable (Columnlisthere....)
OUTPUT INSERTED.*
VALUES ...Values here...

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens

DataSet - Inserted row ID

I have a dataset that uses generated stored procedures to do its select, insert, update, delete.

I am inserting a row to that dataset, and after the update, using the ID of newly created row.

This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0)

Any idea what happened / what I have to do to fix this?

Thnx!

Hi,

The SQL Server uses SELECT SCOPE_IDENTITY() to get the last ID of the table. I'd like to know if you're using this to update your data.

Also, could you let me know what is the newly added trigger doing. It might be preventing the scope identity from returning.

Datareader insted of dataset Sored Procedure

i m writing a stored procudrue to update my data that is onthertable.and i pass the parameter in my vb code,when i pass the data thatis insert only first record of data but second record insert the eroorwill come is data reader is colsed. now insted of data reade i have touse data set how can i use that and update my data is ontehrtable.?below i written my vb.net2005 code.

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
' con.Open()
' Dim ggrnid As String
' Dim acceptqty As String
' Dim itemid As String

' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox)
' ggrnid = grnid.Text

' Dim sWhere As String = grnid.Text
' If (Not String.IsNullOrEmpty(sWhere)) Then
' For Each s As String In sWhere '
' 'Dim iRowIndex As Integer = Convert.ToInt32(s)
' Dim sqldtr As SqlDataReader
' sqlcmd = New SqlCommand
' sqlcmd.Connection = con
' sqlcmd.CommandType = CommandType.Text
' sqlcmd.CommandText = "select acceptqty,itemid fromgrndetail where grnid='" & Trim(ggrnid) & "'"

' datacommand = CommandType.StoredProcedure
' 'datacommand("aaceptqtygrn", con)

' Dim cmd As New SqlCommand("aaceptqtygrn", con)

' sqldtr = sqlcmd.ExecuteReader()

' 'dataset = datacommand.
' 'sqldtr = sqlcmd.ExecuteScalar

' If sqldtr.HasRows = True Then
' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While
' 'sqldtr.Close()
' 'cmd.ExecuteNonQuery()
' 'Next sqldtr.HasRows
' End If
' Next s
' sqldtr.Close()
' con.Close()
' End If
' End If
Catch ex As Exception

MsgBox(ex.Message)

End Try

prajapatiamit2003:

' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While

It looks like you are closing your data reader before exiting you while loop. And you probably do this because your stored proc wont execute while you have the data reader open.

You can either ececute the stored procedure ona different connection, or you can store the results of the reader in some kind of list/array/collection close the reader and then iterate through the collection to execute the sproc.

Because the datareader maintains an open connection to the database you cannot use that connection while the reader is open.

|||But how can i store in array of my data!

DataBinding with Insert query problem

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 add

SqlDataSource1.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

Databinding question

Hi,

I have a page created within VS 2005 which uses a detailsView with a SQLDataSource which has insert, edit and delete items allowed with it.

The problem is that if I delete a record I dont want to refresh the page as I want to set a label value to say item deleted. The problem then though is to select the item to delete I have a drop down which populates the details view on index change, but if I delete the item I cannot do a databind when its complete because it just binds to the existing dataset and does not do a fresh call on the database.

Is there a command I can run to refresh the dataset on click of the delete button?

Thanks

I think I would do databinding in a sub that does nothing else but the databinding. I normally do this in a sub called something like sub bindcontrols() or something like that. Then when I need to refresh the data I can just call that sub.|||

Thanks for your reply.

The problem is the databinding is handled by VS and I dont think I have a choice of where it runs.

|||try the following after deleting detailsView.databind() it should refresh the content Hope this helps

Databind for default value in INSERT

Hey guys, how can I databind a default value for my textbox in the <InsertItemTemplate>
I tried an Eval but it obviously didnt't work. The data source has a Selectcommand with ID that I thought I could use in the insertitem, but i guess not.

<asp:FormView ID="FormView2" DataSourceID="SqlDataSource1" runat="server">
<InsertItemTemplate>
Test<br />
<asp:TextBox ID="abc" runat="server" Text='<%# Eval("ID") %>' />
</InsertItemTemplate>
</asp:FormView
How can I do this?
thanks

Hi,

first try Bind instead of Eval in this case. If it's really a default value that you don't want the clients to see it's better to use theInserting eventhandler of the SqlDataSource control.

Grz, Kris.

|||

Actually what I want in this case is to have a default value (like PREFIX in this case) + a databind. I could do it in codebind with .select() and assign find the value from the database and assign "thisIDtextbox" that value"

however, since I've already used the value in the ItemTemplate I thought maybe there is a way to just apply that value in a simple way.

As I said, now I do a sqldatasource.select() procedure and find the value to assign the textbox with. Can I do this in a better way??

<asp:FormViewID="FormView1"runat="server"DataSourceID="SqlDataSource1">

<ItemTemplate>

ThisID:

<asp:LabelID="ReceiverIDLabel"runat="server"Text='<%# Bind("ThisID") %>'></asp:Label><br/></ItemTemplate>

<InsertItemTemplate>

ThisID:

<asp:TextBoxID="ThisIDTextBox"runat="server"Text='PREFIX<%# Bind("ThisID") %>'></asp:TextBox><br/><asp:LinkButtonID="InsertButton"runat="server"CausesValidation="True"CommandName="Insert"Text="Insert"/></InsertItemTemplate>

</asp:FormView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:database %>"SelectCommand="SELECT [ThisID] FROM [db1]"/>

Database Users and Triggers

I created a trigger on a table T1 for insert and update. The trigger
selects values from table T2, which happens to be in a different
database. It seems I have to create users in that other database for
every user who updates or inserts records into T1. Is there any way
around this, using views or stored procedures, in SQL Server 2000?
Thanks
TimoHi Timo,
I don't think you'll be able to do what you want.
Trigger is running in security context of a user who fired it. As so, select
statement on T2 table is run as that user and user must have proper
permissions.
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Timo" <timo@.org.org> wrote in message
news:eBj$lRp9FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I created a trigger on a table T1 for insert and update. The trigger
>selects values from table T2, which happens to be in a different database.
>It seems I have to create users in that other database for every user who
>updates or inserts records into T1. Is there any way around this, using
>views or stored procedures, in SQL Server 2000?
> Thanks
> Timo|||Hi,
please refer article FYI :
http://www.netdscure.co.in/Articles/AuditDML.htm
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Timo" wrote:

> I created a trigger on a table T1 for insert and update. The trigger
> selects values from table T2, which happens to be in a different
> database. It seems I have to create users in that other database for
> every user who updates or inserts records into T1. Is there any way
> around this, using views or stored procedures, in SQL Server 2000?
> Thanks
> Timo
>

Database user permission

Add a new user to a database and assign permission only to 2 tables in this database for select/update/insert/delete.

the user role is Public.

But when you login in this database with SQL Query Analyzer using this user account, in Object Browser (left side), this user can see all info same as dbo, such as table name, column, data types,... although this user can not access the data for others tables.

How to limit the this user view in Object Browser and only see the tables that the user have proper permission on?

With SQL Server 2000, you could NOT limit the user. They can 'see' all objects in the database.

With SQL Server 2005, the user can only see the objects in the schema(s) they have permissions for.

Are you using SQL 2000?

|||Thanks for reply. Yes, I use SQL Server 2000|||

Unfortunately, with SQL 2000, all users can see all database objects with the client tools.

There is nothing you can do about it -except upgrade to SQL 2005. With SQL 2005, you can keep things private and undisclosed.

Database update and insert problem

Hi,

I have 3 short(ish) questions, If someone could help I'd be very grateful..

This is the situation - I have a formview which contains a button, which on clicking should insert a row into one table, and update a column value of another. I will provide the code at the end.

1. On clicking, I get the error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.

it doesnt say where, but the stack trace is:

[SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1089
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +643
 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
 System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1151
 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +429
 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +88
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +86
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886

2. When updating, at the moment it will update with a new quantity provided in the text box, but id like the quantity in the textbox subtracted from the current column value - how would this be done?

3. For inserting, i was wondering if i need a command in the button_click to activate the insert command, similar to the one i have one for the update. I have tried but i cant seem to get the syntax right...

Code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = @.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@.User)"+
"INSERT INTO Transactions (Itemid)VALUES (@.productID)"+
"INSERT INTO Transactions (itemname)VALUES (@.Itemsname)"+
"INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+
"INSERT INTO Transactions (Qty)VALUES (@.qty)"+
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", TextBox1.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );

}

Huge thanks for anyone who can help shed some light!!

Thanks again,

Jon

1. Debug your application and see the query which is being executed by the SqlDataSource's update event. There might be a space missing in the query. The error is self explanatory - there is syntax error. Try to run the query in the query analyzer.

2.

' After successful updationdim qty asInteger =Integer(QuantityTB.TextBox)dim available asInteger =Integer(AvailableTB.TextBox)' Update the available text boxAvailableTB.TextBox = available - qty

3.

if e.CommandName ="insert" then SqlDataSource1.Insert()end if

|||

Hi, thanks for your response!

1. I had tried debugging but it didn't detect any errors or warnings.. is this what you mean or am i missing the point?

2. I meant to update the column in the database table - it currently updates with just the number that is written in the textbox, but id like it to take away the number written in the textbox from the number that is in their already - do you mean use this code to create a textbox, then update the table with this new value?

3. Is that VB? Do you have it in C# if it is? Or if it isn't, where shall I put it? it doesn't like the syntax in button2_click...

Thanks alot for your help!!!

Cheers,

Jon

|||

1. When I meant debug, put a breakpoint after the query has been formed and now check the value of the query. Copy this to the query analyzer and see if it runs fine. If it does then your query is fine, problem lies somewhere else. If it does not, watch out the error thrown by query analyzer. In your original post, you had written that you were getting error message. Did that error disappear?

2. When you insert the ordered quantity, I thought you would deduct the quantity from the stock. I would suggest, whenever you want to insert a ordered product, then it should be like one transaction.

a. Check if the ordered quantity is not bigger than the available stock.

b. Only add the record, if the above condition is true. If the transaction went through OK, rebind the grid, so that it will show new values.

c. Rebinding of the gridview is good practice, because there can be more than a single user attempting to fill out the order.

No, you do not need to create a new textbox. I was just showing how to update the UI.

3. It is in VB, but simple to convert to C#. It ideally would be in the command event handler of the Insert button.

void InsertCommandBtn_Command(Object sender, CommandEventArgs e) {if (e.CommandName =="insert") { SqlDataSource1.Insert(); }}

|||

Hi cheers for help.

Your code seems to work fine. Im getting a different error message now, and need help updating the table - Ill put it to the forum.

Thanks again!

Jon

database trigger question

Hi

I am trying to setup a trigger on a database where the trigger fires off a store proc when there is an insert. For some reason, its working on a database in Dev and not on a database in QA.

the trigger is on an insert to a table, the trigger looks something like this

create trigger XXX

after Insert

SET XACT_ABORT OFF -- this so that when the proc attached to the trigger fails, insert it anyway

exec updatesomething

if @.@.error <> 0

exec createAudit

In dev, the row is inserted, but in QA the row is not. I did a trace, both have the SQL:BatchCompleted event of the insert sql statement, but in QA environment, the trace does not have the sql statement after exec updatesomething. it just stops at exec updatesomething.

I check the database settings to make sure there were the same, looks like they are, I do not know how to find out what is causing it to work in 1 database and not the other

thanks

Pauli

A few things to check for -

Make sure that the trigger exists and is enabled -- select objectproperty(object_id('dbo.XXX'), 'ExecIsTriggerDisabled') -- should return 0.

Your description above seems to imply that the trigger actually fired in the QA environment. If so, then it could be that there was an error causing the 'exec createAudit' to be skipped. You can check for this by looking for error events in the trace output.

Hope that helps you track it down.

DataBase Trigger

Hi All,
I want to create a comon insert trigger which should be applicable to all the tables in a database
i.e,
the trigger should be fired every time when we try to insert a record in any one of the tables in that database
i don't to achieve this using Stored Procedures
Thanks and Best Regards
Jothi Magesh
> I want to create a comon insert trigger which should be applicable to all
> the tables in a database
There is no such thing in current versions of SQL Server. There are tools
that can help you though.
http://www.aspfaq.com/search.asp?q=lumigent