Nvarchar(max)
Dec 11, 2007Does anyone know the formula to calcuate the space occupied by an nvarchar(max) column for the purposes of sizing a table?
View 1 Replies
ADVERTISEMENT
The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.
Jan 10, 2008HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
Compare Nvarchar(10) With Nvarchar(1000)
Sep 4, 2007I had this question for quite a long time.
It seems the latter one don't take any extra storage space than the previous one.
As long as the real string length is less than 10.
Is that mean the latter one not cost anything?
I once heard the different is when they are in memory. But not sure of it.
Can anyone explain it and provide some official reference on it?
Thank.
Nvarchar(m)
Sep 2, 2007
Hi All,
The storage reserverd for nvarchar(m) is 2*m+ 2 extra bytes.
I understood each character requires because it supports unicode format.
Why extra two bytes are required?
Regards
Abdul
Nvarchar(100)
Jan 31, 2007Hello,
What is the maximum lenght when using nvarchar(MAX)?
If I want to save a really long text, should be this the data type to use?
I am using SQL 2005.
Thanks,
Miguel
Using Nvarchar(MAX) Instead Of Nvarchar(#)
Feb 19, 2008Is it bad form to use nvarchar(MAX) in place of column types with specific lengths like nvarchar(50)? Are there performance issues? Because to me (a novice), it appears that I would save space by using nvarchar(MAX) everywhere.
Same question applies to varbinary(MAX) as well.
Thanks
Jim
_______________
Jim Geurts
Personal: http://www.biasecurities.com
Work: http://propertycenteronline.com
NVARCHAR(MAX)
May 22, 2008
I have an SSIS pkg, that gets data from Lotusnotes db and loads into SQL Server, using ODBC Driver for Notessql driver . I have a comments, field, in Lotusnotes which has comments>8000 chars in lotusnotes. Hence I created my destn SQL Table with datatype NVARCHAR(MAX) so that it can uplaod the comments that are >8000 chars.
However, every time I run the ssis pkg , the destn table is bringing only 250 chars ONLY in the comments field , and truncates the rest of the chars.
And I see the reason is because, on my ODBC Serttings for Lotusnotes, There is a section for NOTESSQL options
where the "Max length of text fields" set to 254. hence it brings only 254 chars into SQL.
However, If I increase that option "Max length of text fields" to 8000 or some higher number, the sssis package errors out on the datareader source itself, err is"
"The output column 'comments' has a length that is not valid.
Pl advise how can I load all the comments which are more than 8000 chars from lotus notes into SQL, AND KEEPING THE ODBC settings, the "Max length of text fields" TO 254 Only.
Order By Nvarchar
Feb 2, 2007I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...
abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...
The issue I'm having is when the records are sorted, they are returned as...
abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)
However, I need the numeric component to be treated as a whole number and order in this fashion...
abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)
I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?
Nvarchar To Text
Aug 26, 2007Hello, I have a column in my table that is a nvarchar.The information that we need to store in this column has exceeded the limit.Can we simply change the datatype to 'text' ? Will there be any issues that we might experience? Thank you in advance.
View 1 Replies View RelatedNvarchar To Varchar
Aug 28, 2007i have used nvarchar as my datatype in sql server 2000 now
i have decided to change to varchar as i can increase the character length from 4000 to 8000
Do I Lose data if i change the datatype.
Nvarchar To Varchar
Jul 10, 2003I have a table using nvarchar(for what ever reason which beyond me why its a nvarchar...) that I would like to change to a varchar. There is no unicode in the fields so I don't have to worry about but I don't want to lose any text data. Will coverting the data type lose data?
Thanks
Converting Nvarchar To Int
Oct 28, 2004Hello all,
I'm trying to convert a nvarchar datatype to int
(ie:1234-56-78 to 12345678) . These values are primary keys in two tables. Both these tables have 3500 rows of this key type. I want to convert this to a int so I can make it a AutoNumber primary key so I can increment it. Is this possible? If so, how do I do it. Do I need to delete the dashes first some how? I fairly new to database adminstration, so any guidance will be greatly appreciated.
Thank You
VARCHAR Vs NVARCHAR
May 16, 2006So I have an existing table that looks like:
ID BIGINT
VAL VARCHAR(128)
I am converting this table to something that will be multi language compliant. My question is, I know that NVARCHAR's take double the space of a VARCHAR. Do I actually need to double the length of the VAL field to store the same amount of data or does the DB handle that?
Basically I want to store a 128 character NVARCHAR.. do I need to set my table up like this:
ID BIGINT
VAL NVARCHAR(256)
or
ID BIGINT
VAL NVARCHAR(128)
NText Vs NVarChar
Sep 27, 2005I am new to SQL Server and would like to hear opinions on pros and cons of using nText vs. nVarChar data type for following uses:
URLs (up to 260 bytes)
Addresses (50-300 bytes)
Descriptions and comments (50-2,000 bytes)
Memos (up to 8000 bytes)
TIA.
NText And NVarChar.
Feb 6, 2007Hello,
I need to save some news text in an SQL table. The text can be long.
1. Should I use nvarchar(MAX) or nText?
2. And what is the difference between nText and Text?
I am using SQL 2005.
Thanks,
Miguel
Ntext To Nvarchar
Feb 15, 2008Dear All,
i'm trying to convert the datatype from ntext to nvarchar.i'm getting error. is it not possible?
Vinod
Even you learn 1%, Learn it with 100% confidence.
Nvarchar(50) To Datetime
Feb 29, 2008Hi all, Please help.
I have created a new SQL dB and imported a table from Access to SQL.
my date columns in Access have been imported as nvarchar(50).
all dates are in the format dd/mm/yyyy.And there is no bad data
My problem is that in the table design of my new SQL Table, if I change the datatype from nvarchar(50) to datetime I get the error "Arithmetic overflow error converting expression to data type datetime"
I know this is telling me that there is a problem with the format of some of the dates but all my dates are defo dd/mm/yyyy ,none of the data has format mm/dd/yyyy ie a 13th month.I know this because I cut the data down to just 2 pieces of data.
can someone help me get the format into datetime please
Ray..
Nvarchar && Varchar
Feb 26, 2007Hi,I am new to MS SQL. When I create a column in a table, when shall Iuse nvarchar or varchar? Please help.Thanks,Mike
View 5 Replies View RelatedParse Nvarchar To Int?
Apr 4, 2008Is there a way to parse a string to int? this is my example code. quote_id is a string i want to be treated as an int here.
Code Snippet
ALTER PROCEDURE [dbo].[GetNextQuoteID]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT MAX(quote_id)+1 as id From Quote
END
Thanks
K
Convert Nvarchar To Int
Jul 24, 2007i have got a table
id name pagenumber(nvarchar)
1 gas pg:231-123
2 dff pg:323-123
i need to copy data from this table to another with page number as
id name pagenumber(int)
1 gas 231
2 dff 323
help me
How To Return Nvarchar(max) From CLR?
Jan 17, 2008 How to return nvarchar(max) from CLR?
----------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString Select_Description(int Obj_ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
string sql = @"Select description from tbl_detail";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return (SqlString)cmd.ExecuteScalar();
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}
}
}
};
----------------------
Code above work fine with any [description]< 4000 characters. Get an error if description field have values> 4000 characters.
Try to solve ==> Looking for the net they said change SqlString ==> SqlChars
Here my modify code
------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString Select_Description(int Obj_ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{
string sql = @"Select description from tbl_detail";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return (SqlChars)cmd.ExecuteScalar(); //<== is this correct syntax?
}
catch (Exception e)
{
throw e;
}
finally
{
conn.Close();
}
}
}
};
------------------------
the error come when compile
Error 1 Cannot implicitly convert type 'System.Data.SqlTypes.SqlChars' to 'System.Data.SqlTypes.SqlString'. An explicit conversion exists (are you missing a cast?)
Please teach me how to solve this problem. Thank you.
I'm newbie on CLR and .NET
Varchar && Nvarchar
Dec 18, 2006from the definition, i know that "n" means uni-code. but what is the exact advantage of having nchar or nvarchar over char or varchar?
View 1 Replies View RelatedWhat Is The ADO DataType For NVarchAR(max)
Mar 21, 2006I can't seem to get nvarchar(max) to work with ADO 2.8 using sql native client.
I am creating a stored procedure and every time i attempt to add a parameter to the command object of type nVarChar(max) I receive the error
"Parameter object is improperly defined"
here is the code to add the parameter
cmd.Parameters.Append cmd.createparameter(@piComments,adLongVarWChar,adparaminput,,me.comments)
adLongVarWChar is the ado data type i am using to map to the new nVarChar(max) but it does not appear to be working.
Is this supported in ADO? I am using the sql native client connection to connect to the database as follows.
pubStrConnectionString = "Provider=SQLNCLI;" _
& "Server=.sqlExpress;" _
& "Database=MyDBName;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
thanks
Collations And Nvarchar
Aug 22, 2007Should a programmer bother using nvarchar for a person's name (special chracters allowed) if I still use SQL_Latin1_General_CP1_CI_AS?
View 1 Replies View Related[MS Sql] Data Type Nvarchar
Jul 9, 2006Hi everybody,I'm using SQL Server Management Studio Express.I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!I couldn't find out where and how to fix that. If you have any idea :)Thanks a lot
View 5 Replies View RelatedConverting From Nvarchar To Int Into Sql Server
Jul 21, 2006Columns from database have values like 1,5 etc and I'm getting the error: Syntax error converting the nvarchar value '1,5' to a column of data type int. when trying to convert from nvarchar to int:
SELECT CONVERT(int, MyNVarChar_column) FROM MyTable
What I'm doing wrong? Thanks a lot
Invalid Syntax Near 'nvarchar'
Jul 26, 2006 Im trying to update and modify data in a grid view in my sql data base. I keep getting this error..What is the solution to this. Sorry, I have no programing experience. All help will be greatly appreciated. thank youjdslimIncorrect syntax near 'nvarchar'. Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +835 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +162 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +118 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840
Error With Using Nvarchar Datatype
Sep 10, 2007Hi, I have created a database using VWD to keep values of urls and have structured it as...
Prefix (http://, network name), address(www.name.com), and name (name of address), the address field has been defined as a nvarchar(MAX).
Most of the addresses updated into the address field work, except something like: www.java-scripts.net/javascripts/Image-Rollover-Script.phtml.
I get this error:
Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.Source Error:
Line 1176: if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
Line 1177: != System.Data.ConnectionState.Open)) {
Line 1178: this.Adapter.InsertCommand.Connection.Open();
Line 1179: }
Line 1180: try {
I can insert something like www.google.com into the addresses field without any errors. Any ideas why?If it is a nvarchar type it should be able to except all sorts of characters??
Nvarchar Is Incompatible With Image
Oct 6, 20071 file.SaveAs("F://images/" + file.GetName(), true);
this was successfully stored in folder
2 SqlCommand sqlcmd = new SqlCommand("insert into tblCategories (CatImage) values (@CatImage)", sqlcon);
sqlcmd.Parameters.Add("@CatImage", file.GetName ());
sqlcmd.ExecuteNonQuery();
I gave datatype for CatImage is image.
error:nvarchar is incompatible with image
so wat i have to do
please give me suggestions
regards
kishore
NVarchar And SelectedValue And InsertCommand
Dec 19, 2007Hi guys,I've got a problem inserting data into my db.I've created a NVARCHAR column and I'm using SelectedValue Parameters.I only have a problem in the INSERT mode.The UPDATE and DELETE are working fine.All the fields can be updated or deleted, but I can't insert new data inside my db.I've changed one column to NVARCHAR : "reference"I use NVARCHAR because I have some Arabic Fields (unicode) into my db.But I've copy-pasted everything about the SqlDataSource.10x a lot anyway ! ASP.NET using MS Visual Studio 2005 :.... <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" OldValuesParameterFormatString="original_{0}" OnDeleted="SqlDataSource2_Deleted" OnUpdated="SqlDataSource2_Updated" OnInserted="SqlDataSource2_Inserted" SelectCommand= "SELECT [reference], [ddf], [description], [quantity], [pru], [supname], [catname] FROM [Products] WHERE ([reference] = @reference)" InsertCommand="INSERT INTO [Products] ([reference], [ddf], [description], [quantity], [pru], [supname], [catname]) VALUES (@reference, @ddf, @description, @quantity, @pru, @supname, @catname)" UpdateCommand="UPDATE [Products] SET [ddf] = @ddf, [description] = @description, [quantity] = @quantity, [pru] = @pru, [supname] = @supname, [catname] = @catname WHERE [reference] = @original_reference" DeleteCommand="DELETE FROM [Products] WHERE [reference] = @original_reference"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="reference" PropertyName="SelectedValue" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="reference" Type="String" /> <asp:Parameter Name="ddf" Type="DateTime" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="quantity" Type="String" /> <asp:Parameter Name="pru" Type="Decimal" /> <asp:Parameter Name="supname" Type="String" /> <asp:Parameter Name="catname" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ddf" Type="DateTime" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="quantity" Type="String" /> <asp:Parameter Name="pru" Type="Decimal" /> <asp:Parameter Name="supname" Type="String" /> <asp:Parameter Name="catname" Type="String" /> <asp:Parameter Name="original_reference" Type="String" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="original_reference" Type="String" /> </DeleteParameters> </asp:SqlDataSource>SQL SERVER 2005 : Create Database FileUSE masterGOIF EXISTS(SELECT * FROM sysdatabases WHERE name='Products') DROP DATABASE ProductsGOCREATE DATABASE ProductsON ( NAME=Product, FILENAME = 'C:WebAppApp_DataProducts.mdf', SIZE=10 )GOUSE ProductsGOCREATE TABLE Categories ( catname VARCHAR(25) NOT NULL, PRIMARY KEY (catname) )GOCREATE TABLE Suppliers ( supname VARCHAR(25) NOT NULL, tel VARCHAR(50) , cell VARCHAR(50) , fax VARCHAR(50) , pob VARCHAR(25) , address VARCHAR(300) , nearby VARCHAR(100) , website VARCHAR(100) , email VARCHAR(100) , skypephone VARCHAR(100) , PRIMARY KEY (supname) )GOCREATE TABLE Products ( reference NVARCHAR(25) NOT NULL, ddf DATETIME NOT NULL, description VARCHAR(50) NOT NULL, quantity VARCHAR(10) NOT NULL, pru MONEY NOT NULL, supname VARCHAR(25) NOT NULL, catname VARCHAR(25) NOT NULL, pv MONEY NOT NULL, PRIMARY KEY(reference), FOREIGN KEY(catname) REFERENCES Categories(catname), FOREIGN KEY(supname) REFERENCES Suppliers(supname) )GO
View 3 Replies View RelatedUnwanted NVARCHAR Padding
Jan 22, 2004I've created an asp.net page that takes the content of text boxes and writes them to a sql table.
The problem is that when I examine the resulting data in the SQL database, I find that the fields written to have had padding added (up to the maximum size of the fields).
I was under the impression that fields of type NVARCHAR did not store padding (only the no of characters being stored).
I've checked it's not the text boxes on the aspx page by explicitly posting values instead of the boxes content and the same thing happens.
Help
example of function i'm using to post data:
Function AddNews(ByVal Category As String, ByVal ApplicRole As String, ByVal NewsType As String, ByVal Description As String, ByVal News As String, ByVal Hyperlink As String, ByVal Email As String, ByVal BirthDate As Date, ByVal KillDate As Date, ByVal Parent As String) As Integer
Dim connectionString As String = ConfigurationSettings.AppSettings("AuthentConnection")
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [News]([Category],[ApplicRole],[NewsType],[Description],[News],[Hyperlink],[Email],[BirthDate],[KillDate],[Parent]) VALUES (@Category,@ApplicRole,@NewsType,@Description,@News,@Hyperlink,@Email,@BirthDate,@KillDate,@Parent)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_Category As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Category.ParameterName = "@Category"
dbParam_Category.Value = Category
dbParam_Category.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Category)
Dim dbParam_ApplicRole As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_ApplicRole.ParameterName = "@ApplicRole"
dbParam_ApplicRole.Value = ApplicRole
dbParam_ApplicRole.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ApplicRole)
Dim dbParam_NewsType As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_NewsType.ParameterName = "@NewsType"
dbParam_NewsType.Value = NewsType
dbParam_NewsType.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_NewsType)
Dim dbParam_Description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Description.ParameterName = "@Description"
dbParam_Description.Value = Description
dbParam_Description.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Description)
Dim dbParam_News As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_News.ParameterName = "@News"
dbParam_News.Value = News
dbParam_News.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_News)
Dim dbParam_Hyperlink As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Hyperlink.ParameterName = "@Hyperlink"
dbParam_Hyperlink.Value = Hyperlink
dbParam_Hyperlink.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Hyperlink)
Dim dbParam_Email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Email.ParameterName = "@Email"
dbParam_Email.Value = Email
dbParam_Email.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Email)
Dim dbParam_BirthDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_BirthDate.ParameterName = "@BirthDate"
dbParam_BirthDate.Value = BirthDate
dbParam_BirthDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_BirthDate)
Dim dbParam_KillDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_KillDate.ParameterName = "@KillDate"
dbParam_KillDate.Value = KillDate
dbParam_KillDate.DbType = System.Data.DbType.Date
dbCommand.Parameters.Add(dbParam_KillDate)
Dim dbParam_Parent As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_Parent.ParameterName = "@Parent"
dbParam_Parent.Value = Parent
dbParam_Parent.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_Parent)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Nvarchar And Varchar Sizes....
Jun 19, 2004Hello again everyone....
I have another question for everyone....
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Thanks ahead for any help...
-Alec
Concatenation Uniqueidentifier Into Nvarchar
Apr 12, 2006where t.[PortalID] = '+ convert(nvarchar(36), @PortalID) +' where t.[PortalID] = '+ convert(nvarchar(36), @PortalID) +' Im trying to concate a uniqueidentifier into a BIG nvarchar(4000) string so i can execute it at the end (
exec sp_executesql @sql
)
But my problem is that i got an error saying that i can not do so or the first 5 digit of the uniqueidentifier object is not well formated.
any ideas ? thank you