Datatype nvarchar(max) not accessible

Hi,

trying to input

create table T (c1 nvarchar(max));

in MS SQL Server Manangement Studio Express results in an error :

Fehler beim Analysieren der Abfrage. [ Token line number = 1,Token line offset = 30,Token in error = max ]

create table T (c1 nvarchar(4000));

is processed w/o errors.

I′ve installed SQL Server 2005 Express Ed. SP2

Microsoft SQL Server Management Studio Express 9.00.3042.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft .NET Framework 2.0.50727.42

Betriebssystem 5.1.2600

Any ideas?

Thanks in advance

Werner

This command should work without problems, and did on my computer. Could you check the error log to see if there is more informaiton about the failure there?

Mike

|||

Hi,

thanks for your reply. I think I found the reason.

Being a newcomer I accidently selected "SQL Server Everywhere" and generated a compact sdf-DB. This type of server doesn′t support nvarchar(max) (which is not mentioned in any documentation available in the net).

Setting up a new db (conversion of the sdf to mdf format is not possible (?)) fixed the problem.

btw, I can imagine using large datafields even on a PDA, so what′s the reason for not supporting this datatype (I do see a parallel to those guys of TI in the late 70s who invented the datetime format for PCs, saving 2 byte...)

Thanks anyway for your fast reaction and your help offered

Werner

Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am
having a hard time trying to figure out which datatype to use in SQL to
store this value. I have tried using datetime, char, nchar, text and
nothing seems to work. Anyone have any ideas? Thanks!

Regards, :)

Christopher BowenDo you mean that you want to store a time without a date? This isn't
possible in MSSQL, since there is only a single datetime data type.

http://www.aspfaq.com/show.asp?id=2206
http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Use a DATETIME or SMALLDATETIME column.

INSERT INTO YourTable (dt_col) VALUES (CURRENT_TIMESTAMP)

--
David Portas
SQL Server MVP
--|||Christopher,

the function call Hour(Now) will return the current hour, which is an
integer. I would expect this information to be of little use. However,
if you want to store this in an SQL-Server database, then a column of
type tinyint would suffice.

HTH,
Gert-Jan

c_bowen@.earthlink.net wrote:
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen|||It would probably be helpful to see your code and it's not 100% clear what
you're trying to do. I'm guessing hour(now) is a .NET function? Does it
simply return the number of the current hour? That would be some sort of
INT, which would be a datatype mismatch with the datatypes you say you've
tried. Your note mentioned "insert the date and time", which wouldn't
simply be the current hour, anyway.

When I want to insert the date and time, I usually use SQL Server's
getdate() function to supply the value. A T-SQL example would be:

create table foo (col1 char(10),col2 datetime)
go
insert foo values ('abc',getdate())
go
select * from foo
go

[results]
(1 row(s) affected)

col1 col2
---- ----------------
abc 2005-02-25 15:16:38.367

(1 row(s) affected)

Use the datetime datatype if you can. In my experience, using character or
numeric types for storing dates and times usually ends up in grief.

By the way, I usually set things up so that SQL Server is responsible for
supplying the time, rather than the application. That way, if the various
workstation's or web server's clocks are a little bit off, the time values
of rows inserted/updated will still be synchronized across your
applications.

<c_bowen@.earthlink.net> wrote in message
news:1109141117.808195.36560@.l41g2000cwc.googlegro ups.com...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
> having a hard time trying to figure out which datatype to use in SQL to
> store this value. I have tried using datetime, char, nchar, text and
> nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowensql

DatasourceView named query creation problem

Hi to all,
I have a problem within the editor of Named Query in Visual Studio.
Whenever I try to add a named query to a Datasourceview I receive the common "Object Reference not seto to an instance of an object". In the PC Visual studio have the SP1 installed, and the Client Components of Sql Server are installed and patched to the Sp2.
I tried to debug the IDE of visual studio using another instance of VS attached to the first but... nothing! What can I do to add a named query to my datasource view ?
Thanks in advance!
Marco

When these kind of strange problems appear I usually do a total reinstall of the software.

Is the data source SQL Server or another database?

You can also tell us about the O/S and version and other software installed on your computer/workstation.

HTH

Thomas Ivarsson

|||Oh... I would be happy if I can NOT DO a total reinstall of the software!
The Datasource is Sql Server (the same datasouce of other table in the Datasourceview), OS is WinXp sp2 continuosly update by windows update. I have a lot of software installed on my computer... (VS 6.0, VS2003, VS2005, VSS, MSOff2007, Firefox, MSSQL Express, Client Tools of SQL Server, MsnMessenger, Dameware remote control, Nero...)
I think it's a problem with the dll that create the dialogue to make the named query, although I don't know wich dll it is!
|||This problem has also appeared on my workstation (it used to work OK).
I am Windows XP, SP2, SQL 2005 SP2, VS2005 SP1.
I've also compared the contents of:
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge
with
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge
and they match.
I'm not sure what caused the problem to start happening - I suspect an automatic update may be the culprit.
I've also uninstalled/reinstalled & reapplied SP2 for SQL Server 2005 to no effect.
The error dialog does not have a "Details>>" button, so I cant get any further details on the issue.
Note that the error occurs before the "Add New Neamed Query" dialog box appears - when I click OK on the error the dialog box appears for a microsecond and then disappears.

Any help appreciated!
PK

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

I am moving this to the SQL Server Security forum.|||

From where you are connecting (trying) to this new database?

Is it from Reporting services or any other application?

|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

I am moving this to the SQL Server Security forum.|||

From where you are connecting (trying) to this new database?

Is it from Reporting services or any other application?

|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

Hi Hanel O,

Try posting this query to the MSDN SQL Server Data Access Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1&bcsi_scan_A9C07E6225287F19=z5cf+RkCipiHryeey+QUUQYAAAAmrYMB&bcsi_scan_filename=ShowForum.aspx

Also here is the MSDN SQL Server Forum main page, where you will find many SQL forums dedicated to different aspects of Microsoft SQL Server:
http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

Also try looking in SQL Server Books Online, which comes as part of the SQL Server install package.

Hope this helps,

Frank

Datasource error after deployment

reports run fine in studio. When I deploy the reports that reference a 2k
box, I get an error saying I'm unablt to connect to the data source unless we
promt for name and password and pass them as windows credentials. How do I
use the user's windows credentials?Once I granted access to nt authority\anonymous logon, my problems
disappeared. Is this an IIS setting?
"Jeff Ericson" wrote:
> reports run fine in studio. When I deploy the reports that reference a 2k
> box, I get an error saying I'm unablt to connect to the data source unless we
> promt for name and password and pass them as windows credentials. How do I
> use the user's windows credentials?

Datasets and SQL Injection

I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?

Hey,

Welcome to the forum. The key is, do you have any dataset queries that take user input and passes it to a SQL query directly? Then you may want to beware if that is true. I like the use of SPs because SQL Server will cache them for increased performance.

In addition, you should be able to use @.Parameters within a SQL query, and pass in the values as parameters, which is safer than embedding the response directly into the SQL query.

|||

wendycarpenter@.polk-county.net:

I am wondering if there is any 'built-in' functions in the C files for sql injection prevention?

Use parameterized queries!

http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

I am using parameterized queries - I've included a sample below. SQL Injection prevention has been "in the air" a lot lately and I guess I want to make sure that I am doing what I need to do to keep my customers' data safe.

INSERT INTO [Case] ([cseYear], [cseNumber], [cseStatus], [csePartI], [csePartII], [cseDestruction])

VALUES (@.cseYear, @.cseNumber, @.cseStatus, @.csePartI, @.csePartII, @.cseDestruction);
select SCOPE_IDENTITY()

|||

I appreciate the link. Maybe I wasn't specific enough. I am using the built in dataset (.xsd), so I am not creating any sql statement within my code - I strictly call the method within the datatable of the table adapter within the dataset. Most of the work is done within the auto generated code within Visual Studio 2005.

|||

Hey,

OK, which used the parameter approach I believe. That should be OK; I've seen several projects where Adapters were used even with secure info. Note that table adapters can also use stored procedures in case you want to be sure.

sql

Dataset CommandType: TableDirect (Error)

In Visual Studio Report Designer data tab, whenever I try to create a new
dataset with the CommandType "TableDirect" I get the following message:
"An error has occurred while setting the Command Type property of the data
extension command. CommandType.TableDirect is not supported by the .Net
SqlClient Data Provider."
when I click OK to the dataset. I wonder why Visual Studio.NET would contain
a CommandType that is not supported. Any ideas what is causing this?
MalikSee
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommandTypeClassTopic.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:uiM%23UDybEHA.1000@.TK2MSFTNGP12.phx.gbl...
> In Visual Studio Report Designer data tab, whenever I try to create a new
> dataset with the CommandType "TableDirect" I get the following message:
> "An error has occurred while setting the Command Type property of the data
> extension command. CommandType.TableDirect is not supported by the .Net
> SqlClient Data Provider."
> when I click OK to the dataset. I wonder why Visual Studio.NET would
contain
> a CommandType that is not supported. Any ideas what is causing this?
> Malik
>sql

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

DataRow in a CLR Stored Procedure

I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
created a Database project where I've written some simple CLR Stored
Procedures. I can deploy and call the simple CLR Stored Procedures from my
host WinForm application. This all works great.
I'd now like to write a CLR Stored Procedure with a parameter of type
System.Data.DataRow. Doing this compiles just fine, but when I attempt to
deploy, I get the following error:
Cannot find data type DataRow.
Any suggestions on what I might do to get a CLR Stored Procedure with a
DataRow parameter to compile AND deploy?
Thanks,
--
Randyexamnotes <randy1200@.newsgroups.nospam> wrote in
news:4A6B0CA3-733E-4E43-AA94-CD9D98B295C2@.microsoft.com:

> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
>
Can not be done. Your CLR procedures can only have params of types that are
T-SQL compatible.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||randy1200 (randy1200@.newsgroups.nospam) writes:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
You can't do that. A CLR stored procedure can only take parameters
that maps to types used in SQL Server, and DataRow is not such a type.
Keep in mind that even if the stored procedure is implemented in the CLR,
there is still is a CREATE PROCEDURE statement which looks just like
the CREATE PROCEDURE statement for a T-SQL procedure as far as the
parameter list is concerned.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could serialize the DataRow and use a VarChar or VarBinary parameter.
Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
"Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML Dat
a
Serialization." You could use an XML representation, however there is a
particularly interesting example of serializing/deserializing a DataTable an
d
the DataRows it contains using an efficient custom binary representation on
pages 424-428. The sample code is in C# and there isn't much code needed.
Hope this helps.
Jack Whitney
"randy1200" wrote:

> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
> Thanks,
> --
> Randy|||I have been playing around with Dino Esposito's example of custom binary
serialization of a DataTable and the DataRows it contains. (This is the
example that I mentioned in my earlier posting on this thread.) It is very
nicely implemented. The code to serialize a DataTable to a file and
deserialize the file back to a DataTable, including a worker class definitio
n
and comments is 78 lines of code. Dino provides a sample Windows applicatio
n
that connects to a Northwind database. You enter SQL in a textbox like
"SELECT * FROM [Order Details]" and the application gets the data from the
database into a DataTable, serializes the DataTable to a file, deserializes
the file back to a DataTable, and renders the DataTable in a DataGrid. He
actually serializes the DataTable using two methods and compares the output
for size efficiency. The two methods are ordinary .NET framework binary
serialization and his custom binary serialization. The custom binary
serialization produces much smaller output. The code was written for .NET
1.1, but I just converted it to .NET 2.0 and accessed SQL Server 2005 with n
o
hitches. If you are interested in pursuing this, you should definitely take
a look at this sample.
Note that to serialize just a DataRow, you would need to serialize some
properties of the containing DataTable object, minimally the column names an
d
types, in addition to the values in the DataRow. Dino's example demonstrate
s
this.
Hope this helps.
Jack Whitney
"Jack Whitney" wrote:
> You could serialize the DataRow and use a VarChar or VarBinary parameter.
> Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
> "Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML D
ata
> Serialization." You could use an XML representation, however there is a
> particularly interesting example of serializing/deserializing a DataTable
and
> the DataRows it contains using an efficient custom binary representation o
n
> pages 424-428. The sample code is in C# and there isn't much code needed.
> --
> Hope this helps.
> Jack Whitney
>
> "randy1200" wrote:
>

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

DATABASEPROPERTYEX Error but not using this function...

Hi Everyone,
I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0 database
as the datasource. My SQL statement isn't anything crazy, just a Left Outer
Join between two tables (see below), but when I go into Preview mode on the
report I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'MyDatasource'.
'DATABASEPROPERTYEX' is not a recognized function name.
I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
suggestions?
Datasource: MS SQL 7.0
Report created on VS.Net 2003
To be published on MS SQL 2005 Reporting Server
And here is my SQL statement:
SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
PAYHIST.trans
FROM MASTER LEFT OUTER JOIN
PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
ORDER BY MASTER.asset_no, PAYHIST.post_date
I used a wizard to create this report, so does VS.Net 2003 do something
wierd in the background that MS SQL 7.0 might not like? Just curious ...
Thanks --
AlexHi. Mistake in my last post, I initially said I was using Visual Studio.Net
2005, but it's actually 2003. I corrected this at the bottom of the
message, but forgot to at the top. So to correct, this is being done under
VS.Net 2003 as opposed to 2005.
Sorry 'bout that .. Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
> database as the datasource. My SQL statement isn't anything crazy, just a
> Left Outer Join between two tables (see below), but when I go into Preview
> mode on the report I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'MyDatasource'.
> 'DATABASEPROPERTYEX' is not a recognized function name.
> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
> suggestions?
> Datasource: MS SQL 7.0
> Report created on VS.Net 2003
> To be published on MS SQL 2005 Reporting Server
> And here is my SQL statement:
> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
> PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
> PAYHIST.trans
> FROM MASTER LEFT OUTER JOIN
> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
> ORDER BY MASTER.asset_no, PAYHIST.post_date
> I used a wizard to create this report, so does VS.Net 2003 do something
> wierd in the background that MS SQL 7.0 might not like? Just curious ...
> Thanks --
> Alex
>
>|||Solution Found...
After searching more in Google Groups I found an older post with the fix.
Here it is for anyone who might run across this issue in the future:
You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
Take care -- Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23$GDP9R2HHA.140@.TK2MSFTNGP02.phx.gbl...
> Hi. Mistake in my last post, I initially said I was using Visual
> Studio.Net 2005, but it's actually 2003. I corrected this at the bottom
> of the message, but forgot to at the top. So to correct, this is being
> done under VS.Net 2003 as opposed to 2005.
> Sorry 'bout that .. Alex
> "Alex" <samalex@.gmail.com> wrote in message
> news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Hi Everyone,
>> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
>> database as the datasource. My SQL statement isn't anything crazy, just
>> a Left Outer Join between two tables (see below), but when I go into
>> Preview mode on the report I get the following error:
>> An error has occurred during report processing.
>> Query execution failed for data set 'MyDatasource'.
>> 'DATABASEPROPERTYEX' is not a recognized function name.
>> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
>> suggestions?
>> Datasource: MS SQL 7.0
>> Report created on VS.Net 2003
>> To be published on MS SQL 2005 Reporting Server
>> And here is my SQL statement:
>> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
>> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
>> PAYHIST.interest, PAYHIST.late_fee,
>> PAYHIST.suspense, PAYHIST.trans
>> FROM MASTER LEFT OUTER JOIN
>> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
>> ORDER BY MASTER.asset_no, PAYHIST.post_date
>> I used a wizard to create this report, so does VS.Net 2003 do something
>> wierd in the background that MS SQL 7.0 might not like? Just curious ...
>> Thanks --
>> Alex
>>
>

database?

I am trying to built a web database using the visual studio web developer 2005 express. client will be able to access the database in the intranet server. I found out that i can built a database using the web developer itself so what is the different from built database using the sql server? so where should i built my database in?I would suggest that you put the SQL Server database on your client's SQL Server. I have no experience with Visual Studio Web Developer Express but, if it's like Pro, there's a "App_Data" folder that allows you to create SQL Server databases (.mdf). You must have SQL Server 2005 express to do this but it's highly unlikely that your client will use SQL Server 2005 Express as their Production copy.