Friday, March 9, 2012

Error with using nvarchar datatype

Hi, 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), andname (name of address), theaddress 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 'NETWORKNAME\ASPNET'.

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 'NETWORKNAME\ASPNET'.

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 likewww.google.com into theaddresses field without any errors. Any ideas why?
If it is a nvarchar type it should be able to except all sorts of characters??

The error itself has nothing to do with datatypes. It's a connection error which implies that you have invalid credentials in the connection string (it seems to run using integrated Windows authentication).

|||

Hello, I apologise, but i was checking something in the database when I was writing this post. After closing the connection and re-entering the field entry >www.java-scripts.net/javascripts/Image-Rollover-Script.phtml

Here is the error i have been getting:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

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.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 1095: this.Adapter.SelectCommand = this.CommandCollection[0];Line 1096: TasksDataSet.intranet_addressesDataTable dataTable = new TasksDataSet.intranet_addressesDataTable();Line 1097: this.Adapter.Fill(dataTable);Line 1098: return dataTable;Line 1099: }

Hope this makes sense. If I delete the submitted value from the database and re-enter something likewww.google.com, it works fine.
So why would a URL likewww.java-scripts.net/javascripts/Image-Rollover-Script.phtml cause the above error?

|||

Ah, the dreaded ConstraintException.. The error is in the DataTable, not in the query execution. You can get a better error message by catching the exception and sending it to this method:

public static string ConstraintExceptionToString(ConstraintException ce, DataTable dt) {return"Constraint Exception [" + ce.Message +"] in " + dt.TableName +" (" + dt.Rows.Count +" rows)\n" + DataTableErrorsToString(dt);}public static string DataTableErrorsToString(DataTable dt) {StringBuilder sb =new StringBuilder();for (int x=0; xif (dr.HasErrors) {sb.Append("\tError in row " + x +": " + dr.RowError +"\n");}}return sb.ToString();}
|||

Very interesting, where abouts would I place the above script?

|||

Oohh...don't say "script"... .NET developers will look strangely upon you...Smile

The methods are just helper methods that will retrieve the actual constraint error. For simplicity's sake we can add them to the same class as your data filling method.

this.Adapter.SelectCommand =this.CommandCollection[0];
TasksDataSet.intranet_addressesDataTable dataTable =new TasksDataSet.intranet_addressesDataTable();
try{this.Adapter.Fill(dataTable);
}
catch(ConstraintException ce)
{
throw new ConstraintException(ConstraintExceptionToString(ce,dataTable),ce);
}
return dataTable;
}

public static string ConstraintExceptionToString(ConstraintException ce, DataTable dt)
{
return"Constraint Exception [" + ce.Message +"] in " + dt.TableName +" (" + dt.Rows.Count +" rows)\n" +
DataTableErrorsToString(dt);
}

public static string DataTableErrorsToString(DataTable dt)
{
StringBuilder sb =new StringBuilder();
for (int x=0; x < dt.Rows.Count; x++)
{
DataRow dr = dt.Rows[x];
if (dr.HasErrors)
{
sb.Append("\tError in row " + x +": " + dr.RowError +"\n");
}
}
return sb.ToString();
}


|||

Cheers Gunteman, I'll have to remember that for my future post! Ahem, one more question.

I am working with Visual Web Developer, and the files I am working with are...

index.aspx (holds the gridview and dataset information), The TaskDataSet.xsd and the database itself.
In which of the files in my Visual Web Developer do I place the above method, or do I open a class in VWD to place it into?

I did have a try implementing the above before posting, but still not sure where it goes...

I appreciate any more help you can pass my way :-)

|||

You would probably be better off using nvarchar(2048) than nvarchar(max). As far as I am aware, there isn't a defined limit, but most browsers (IE, etc.) won't handle more than that, and some proxies/security scanners won't allow more than 1024.

|||

Hi, thanks for the reply. I have updated it from MAX to 2048, so thats very cool to know.

But it hasnt fixed my problem with inserting the following address into my database:www.java-scripts.net/javascripts/Image-Rollover-Script.phtml

|||

jamesstar:

I did have a try implementing the above before posting, but still not sure where it goes...

I appreciate any more help you can pass my way :-)

If you look closely you''ll see that I've just continued from the portion of your code that was visible in the error message.

|||

Ah yes, I see now... :-D

Hmm, that code comes from a temporary App_Code (something).cs file within the framework.
So I gather even if I changed that one file, it wouldnt be permanent, I will do some further research and see if I can locate the hard copy of that file.

I thought you might be able to create a CS file inside the App_Code folder that could be shared across all files.

Okay, chat soon.

|||

Okay, after visiting a few more forums and hands in head. I started reading through each line in the error code and noticed the dataset code had not updated itself.

It was still referencing each column in the table with nvarchar(MAX), even though I had updated the database.

So I deleted my current dataset file and re-inserted it back in... and now, wonderfully enough... my aspx page works!

Yahhh ~ Thank you!!

*** Don't give up hope, just keep going forward ;-)

No comments:

Post a Comment