.Net, ADO.NET, ASP.NET, C#, Database, DataSet, DataTable, DB Connection Example, Programming, SQLDataAdapter, VB.NET, Web Development

ASP.NET Example – Populating a DataSet Using SQLDataAdapter Fill DataSet

In the example functions below I’m illustrating how to set up a basic query to a stored procedure in your database that takes a single string parameter. The examples involve using the SQLDataAdapter object to Fill a DataSet. The resulting DataTable can then be queried as desired. Specifically:

  1. The first example shows how to read the first row of the results after your Datatable has been populated. I provide the same example in VB.NET and C#.
  2. The second example shows how to loop through the results if you anticipate multiple returned rows. As before,  I provide the same example in VB.NET and C#.

As part of this code, remember to include the correct .NET Namespaces. If you are writing your code inline (in either VB.NET or C#), use:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

and if you are writing your code in codebehind, use:

VB Codebehind:

Imports System.Data
Imports System.Data.SqlClient

C# Codebehind:

using System.Data;
using System.Data.SqlClient;

Also, navigate here if you are interested in further articles I have written about ADO in .NET.

Continue reading “ASP.NET Example – Populating a DataSet Using SQLDataAdapter Fill DataSet”

.Net, ADO.NET, ASP.NET, Error Handling, Programming, SQL, XML

InvalidCastException – Failed to convert parameter value from a XmlDocument to a String

InvalidCastException was unhandled by user code

If you are working with XML data types in your .NET and SQL Server code, then you will most likely encounter this error message at some point.

Specifically, this error is related to improperly passing an XML value as a SQLParameter to your database.

Since most of the work one does with XML in .NET is done using the XmlDocument object, it is certainly surprising when Visual Studio complains when one tries to save the XML object as an XmlDocument to the database (say to a stored procedure or directly to a database table).

The bottom line is that you must adjust your  XmlDocument object  to a format compatible with the SqlDbType.XML accepted by your SqlParameters.

There are a few ways that you can use to pass your XML object to your database:

Continue reading “InvalidCastException – Failed to convert parameter value from a XmlDocument to a String”

.Net, ADO.NET, ASP.NET, Database, Programming, Security, SQL Server 2000, SQL Server 2005, SQL Server 2008, VB.NET, Web Development

ASP.NET Choosing Parameters.Add or Parameters.AddWithValue

If you are adding parameters to a SqlCommand object then you can use either Parameters.Add or Parameters.AddWithValue. The Parameters.Add syntax requires you to explicitly specify the DataType of the parameter you are passing, while the Parameters.AddWithValue syntax implicitly attempts to convert the parameters you pass in.

When choosing which method you want to use, you should be aware that although there is no difference in the functionality of either method, but Microsoft added Parameters.AddWithValue because overloads for Parameters.Add resulted in possible confusion of which specific overload was being used. I do find myself using Parameters.AddWithValue for its convenience and clarity, but the fact that you are leaving the datatype for the compiler to implicitly convert is not a good thing.

Continue reading “ASP.NET Choosing Parameters.Add or Parameters.AddWithValue”