C# Ado.net Sql query parameter query

Hi All, 

I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read.
So can anyone help as I'm really stuck on this one. The code is as follows :

public void DisplayUserInfo()
{

ArrayList UserIdArrayList = IdentifyUserID();
foreach(string ShowUserIDString in UserIdArrayList)
{
try
{
SqlConnection SqlConn = new SqlConnection(DBConnString);
SqlConn.Open();
Console.WriteLine("Connected to DB");
SqlDataAdapter SqlAD = new SqlDataAdapter();
SqlAD.SelectCommand = new SqlCommand("Select * from UserSystemSpecs where UserName ='+ShowUserIDString.ToString()+'",SqlConn);
DataSet ds = new DataSet();
SqlAD.Fill(ds);
ds.WriteXml(".\\ResultsXML.xml", XmlWriteMode.WriteSchema);
}

catch (Exception ex)
{
throw new Exception ("Error Connecting to DB. " + ex.Message);
}
//SqlConn.Close();


}

}


Thanks
Garry
0
glabana
8/14/2004 10:53:36 PM
asp.net.getting-started 91979 articles. 4 followers. Follow

4 Replies
1997 Views

Similar Articles

[PageSpeed] 39

OK Guys,

The following code within the Try{} block now has solved my problem. But the poblem now is that when I write to my XML file I'm only getting the last results in my loop. So is there any kind of Append method, i.e. so that each round trip in my loop the XML file is added to and not over written.
Thanks
Garry

try
{
SqlConnection SqlConn = new SqlConnection(DBConnString);
SqlConn.Open();
Console.WriteLine("Connected to DB");
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandText = ("SELECT * FROM UserSystemSpecs WHERE UserName = @UserID");
SqlCmd.Parameters.Add("@UserID", SqlDbType.VarChar, 50).Value = ShowUserIDString.ToString();
SqlDataAdapter SqlAD = new SqlDataAdapter();
SqlAD.SelectCommand = SqlCmd;
DataSet ds = new DataSet();
SqlAD.Fill(ds);
ds.WriteXml(".\\ResultsXML.xml", XmlWriteMode.WriteSchema);
SqlConn.Close();
}
0
glabana
8/15/2004 12:06:56 AM
Ha Ha!

The solution is defined below:
Have a separted class file that is called from the standard class in a console app:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Data.SqlTypes;
using System.Xml;

namespace UserSpecDataRequest
{
/// <summary>
/// Summary description for StoreData.
/// </summary>
public class StoreData
{
static string DBConnString = "data source=localhost;initial catalog=Harry;uid=sa;pwd=;";
// string FilePathString is the location of your file. Simply change the path within the ""
static string FilePathString =@"C:\Documents and Settings\Administrator\My Documents\Visual Studio Projects\UserSpecDataRequest\UserName.txt";


public StoreData()
{
//
// TODO: Add constructor logic here
//
}

/// <summary>
/// Method used to read in a text file and then obtain user Ids, which are separated by ","
/// </summary>
/// <param name="FilePathString">This parameter is the path of the txt file to be read in</param>
/// <returns>Returns an Array called UserIDArray that has all the values of the User IDs</returns>
public static ArrayList IdentifyUserID()
{
// Create an Array that hold the values of the words of the file
ArrayList UserIDArray = new ArrayList();
try
{
// Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (StreamReader sr = File.OpenText(FilePathString))
{
while(sr.Peek() > 0)
{
string[] Data = sr.ReadLine().Split(",".ToCharArray());
foreach(string dt in Data)
{
UserIDArray.Add(dt);
}
}// end while loop
}// end using statement

}// end try

catch (Exception e)
{
// Let the user know what went wrong.
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
return UserIDArray;
} // end IdentifyUserIds
public static ArrayList StoreUserInfo()
{
ArrayList UserIdArrayList = IdentifyUserID();
ArrayList UserDetailsArray = new ArrayList();
foreach(string ShowUserIDString in UserIdArrayList)
{
// Console.WriteLine(ShowUserIDString+DBConnString);
// Console.ReadLine();
//// Instansiate Sqlcommand object called Conn and pass in the Connection string DBConnString
try
{
SqlConnection SqlConn = new SqlConnection(DBConnString);
SqlConn.Open();
Console.WriteLine("Connected to DB");

SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandText = ("SELECT * FROM UserSystemSpecs WHERE UserName = @UserID");
SqlCmd.Parameters.Add("@UserID", SqlDbType.VarChar, 50).Value = ShowUserIDString.ToString();

SqlDataAdapter SqlAD = new SqlDataAdapter();
SqlAD.SelectCommand = SqlCmd;
DataSet ds = new DataSet();
SqlAD.Fill(ds);
string XmlOutputFileString = ds.GetXml();
// Console.WriteLine(XmlOutputFileString);
// Console.ReadLine();
// XmlTextWriter MyWriter = new XmlTextWriter("UserData.txt", null);
// MyWriter.WriteRaw(XmlOutputFileString
// );
// MyWriter.WriteString(XmlOutputFileString);
// MyWriter.Close();

UserDetailsArray.Add(XmlOutputFileString);
// using(StreamWriter sw = File.AppendText("UserData.txt"))
// {
// //File.AppendText();
// sw.WriteLine("!!!!!!!!!!!!!New User!!!!!!!!!!!!!!!!");
// sw.Write("");
// sw.WriteLine(XmlOutputFileString);
// sw.Close();
// }
// ds.WriteXml(".\\ResultsXML.xml", XmlWriteMode.WriteSchema);
SqlConn.Close();
}
catch (Exception ex)
{
throw new Exception ("Error Connecting to DB. " + ex.Message);
}
}
return UserDetailsArray;

}

public static void PrintValues( IEnumerable myList )
{
System.Collections.IEnumerator myEnumerator = myList.GetEnumerator();
while ( myEnumerator.MoveNext() )
Console.Write( "\t{0}", myEnumerator.Current );

foreach(string i in myList)
{

StreamWriter sw = File.AppendText("UserData.txt");

//File.AppendText();

sw.WriteLine("!!!!!!!!!!!!!New User!!!!!!!!!!!!!!!!");
sw.WriteLine("\t");
sw.WriteLine("\n");
sw.WriteLine(i);
sw.WriteLine("\t");
sw.WriteLine("\n");
sw.Close();
}


//Console.WriteLine();
//Console.ReadLine();

}
}
}

and here is the intial class file that calls the class above:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Data.SqlTypes;
using System.Xml;

namespace UserSpecDataRequest
{
/// <summary>
/// Class used to perform tasks:
/// read data from a text file that contains user names and query these names
/// against SQL server to retrieve User Setem Specification
/// </summary>
public class UserSystemSpec
{



/// <summary>
/// The main entry point for the application. I.e the main method in
/// C++ terminology
/// </summary>
[STAThread]


static void Main(string[] args)
{
// You have to use the @ or \\ as in C# and many other languages \ means newline i.e \n so @ or
// \\ is the workaround
// Instanciate the UserSystemSpec class defined above to access the methods where
// UserSpecDataRequest is the namespace.
UserSpecDataRequest.StoreData myStoreData = new StoreData();
ArrayList GetUserArray = StoreData.StoreUserInfo();
StoreData.PrintValues(GetUserArray);

//PrintValues(UserDetailsArray);


}
}
}
0
glabana
8/18/2004 12:50:11 PM
Hi glabana,

Good to see that you found a solution to your problem. Thanks for sharing your solution with the community! :-D
FYI, I added code tags to your last post. This makes your code blocks more readable. Please consider using them on future posts.

<code></code>

Cheers,
Jamie Kindred, CGA, MCSD
0
NetProfit
8/18/2004 12:58:45 PM
Hi NetProfit,

Will do so in the future I wasn't aware that I could do so.
Cheers
Garry
0
glabana
8/18/2004 1:29:11 PM
Reply:

Similar Artilces:

SQL Query String insert a null with ADO.NET
 I have an SQL Update query with four string variables that I get from a textBox.Text to pass to DB. When data is present, no problem, but when There is NO data to Update, I want to pass a null to the database. I tried:        string box1 = txtCustMachSN.Text;        if( box1 == "" || box1 == "0" ) { box1 = System.DBNull.Value; }       string box2......        string strCmd = "UPDATE Equip2 SET CustomerSerialNo='" + box1 + "&...

how to represent Membership.GetAllUsers(); as an sql query in ADO.net?
 How would I recreate Membership.GetAllUsers(); as a raw sql query? for example I found this online: If Membership is being used on the same site, just call Membership.GetUser : MembershipUser mu = Membership.GetUser("username"); Guid g = (Guid)mu.ProviderUserKey; From a SQL query standpoint - you can do the same thing with the following code in ADO.NET or in SQL in a sproc: select m.UserId from dbo.vw_aspnet_Applications a, dbo.vw_aspnet_Users u, dbo.vw_aspnet_MembershipUsers m where a.LoweredApplicationName = LOWER(@pApplication...

weird difference between t-sql and ado.net query results
Hi All: I have a really simple query that returns a recordset with a count for each row...if I run the query in an SSMS query, I get one set of results, but if I run the query in my code-behind, I get different results? I don't know how that could be possible (except I'm making an obvious mistake somewhere)? In SSMS I have: SELECT     s.radiation_dose, COUNT(s.radiation_dose) AS Expr1FROM         table_procedures AS s INNER JOIN               &nbs...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

ADO.NET Query
 Hi All,         Difference between SqlCommand and SqlCommandBuilder?         How many records can take clustured index in SQL Server?          Can we connect two datareader to same data source using single connection at same time?          How to find the given query is optimised one or not?         Why can’t we use Multiple inheritance and garbage collector in parallel in .NET? I th...

Query SQL DateTime column using ADO.NET Entities Framework
I have a SQL table called Slots with a Datetime column named Date.  I would like to query the db and return all rows where the date is equal to a date provided by the user on an asp.net form.  I have tried various ways to covert the datatime column, but always get an exception.  Any suggestions how to query a SQL datetime field where the date column is equal to a date specified by the user?    using (DBEntities1 myDB  new DBEntities1())   {      try        {  string querySt...

use VB.NET and C#.NET code in the same C#.NET project
All-- Here is a sample that is "off the beaten path", (at least for me). Is it possible, in an ASP.NET application, using the code-behind page building technique, to have both pages written in VB.NET and pages written C#.NET?At http://www.WebLogicArts.com/DemoList.aspx there is a sample that shows that, (contrary to popular belief), it IS possible to mix ASP.NET pages built with C#.NET with ASP.NET pages built with VB.NET in the same VS.NET 2003 project. Note that this is just a "fun" sample to see if it can be done and I do not recommend this practice as a "standard" way of develo...

.net + dynamic SQL-query
On our page we have texbox, dropdownlist, database and gridview for it.It DropDownList we have all names of columns from database (in our situation they are Item_Name, Item_Type,  City and Country). In database we have two more columns, but we do not need them in this task.And the task is to choose from DropDownList item/value and deliver it to SQL-query. We have done searching from database, but the column to search from is static, we want to make it dynamic, we want to choose from Dropdownlist column, from which we will search.We are making all in aspx, not in .cs code. We are not fam...

converting to vb.net from c#.net authorize.net
authorize.net offered me some sample code when I signed up with them the only problem is the sample code is in c#.net but my page that they type all of their credit card into is vb.net <code><%@ Import Namespace="System.Net" %> <%@ Import Namespace="System.IO" %> <script language="C#" runat="server"> void Page_Load(Object Src, EventArgs E) { myPage.Text = readHtmlPage("https://certification.authorize.net/gateway/transact.dll"); } private String readHtmlPage(string url) { ...

SQL query in a VB.net array
I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks! If you really need array as result you can use ExecuteReader on your Comand object. and next you can inserts result for each Reader.read command into array collection (reader returns array of values). But I this that will be better if you will just use datatable and dataadapter which is more flexible and faster and next you can grab data into your collection o...

getting sql query in c# code
Dear All I am a beginner and looking for some help. I have a database with just one column (some names). That is the primary key aswell. Because I want the names to be unique.I used a grid view control to display the data and included the insert functionality in the grid view by using some code and the part of the code that does the insert is  1 public static void Insert(Categories category) 2 { 3 string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 4 using (IDbConnection cn = new S...

How to get parameter in url ( by C# for .net)
  I would like to get parameter of ASP.NET script in URLexample : /localhost/example.asp?parameter1How can i to get this parameter ? can you give me a example? ( using C#) Thanks a lot   JC To get a querystring you can use the Request.Querystring property: Request.Querystring("parameter1")/Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog Actually in C# it would be://this takes request parameters only from the query string Request.QueryString["parameter1"]; //this one works for bot - form and query string Request["paramet...

How to query a query...
I'm working on converting an Access DB that I created into an ASP.NET application (and attempting to learn ASP at the same time...). One of my queries was very complex and required running a different query first, and then working off of that one. Any suggestions on the best way to do this in ASP? The following is a sample of code I'm using so you can see the type of data-binding I'm using. Thanks for the help! myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +"Data Source=" + Server.MapPath("/EquipmentCheckout.mdb")) strS...

Does .NET map calls for ADO to ADO.NET?
Hello,We are trying to troubleshoot some DB problems from COM objects that call ADO. I heard that when ADO is loaded inside of a COM object by a .NET app, that .NET intercepts the call and re-maps those calls over to ADO.NET. This seems untrue to me, given the nature of ADO.NET being a totally separate technology.Does anyone know for sure whether this does or does not occur?The problem we are seeing is actually almost identical in symptoms to this:http://support.microsoft.com/default.aspx/kb/230101"FIX: Data Queries Sporadically Return Empty Recordsets" Literally, this is what...

Web resources about - C# Ado.net Sql query parameter query - asp.net.getting-started

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google / Firefox Parameter - Flickr - Photo Sharing!
HS what? www.seroundtable.com/archives/017105.html

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 2/3/2016 6:10:42 PM