|
I dont have SQL server, just an XML file on my hard drive.
How do i use Server Explorer to build aconnection to that XML file?
I can, so far, load the file into a dataset using:
DataSet MDS = new DataSet();
MDS.ReadXml("C:\\Students.xml");
But this doesnt allow for manipulaiton e.g: SELECT, DELETE, INSERT INTO, etc....
Does it??
I've had a bash conecting to an Access database and mangaed to manipulate it and do every thing i need to really.... But i need to be able to do this with an XML file and the informaiton in : Developing Windows Based Applictions seems a bit thin with regards connecting to and altering XML files.
Thanks
|
|
|
|
|
It's not a server, why would server explorer connect to it ? Why do you need to put it into a dataset, instead of an XmlDocument ? I have no idea how far the Xml support goes in a DataSet, but I know you can perform selects on a datatable, at least.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Can you give me an example of how i would perform a select on a DataSet please?
The only methods i have read in the book involve using the SqlConnection
i.e
string MyCom ="SELECT * WHERE id = 001"
SqlCommand MySqlCom = SQLCONNECTION.CreateCommand();
MySqlCom.CommanText = MyCom;
etc...
As you can see doing that requires you to refference the SqlConnection. So having no connection means i couldnt select in this way (and its the only way i have used up to now).
Also the Access files i have been using are not servers either but you can ad them through server explorer ??
|
|
|
|
|
JDUK wrote:
Also the Access files i have been using are not servers either but you can ad them through server explorer ??
Yes, because they are in fact a database.
The DataView has a RowFilter which basically operates like a select. I thought there was another object which held a table and took a select, but I could be thinking of that one.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Hi
I'm creating insert command object with command builder and then adding "; SELECT SCOPE_IDENTITY() as primary_key_field" formula to CommandText property of this object. I'm also setting its UpdateRowSource property to FirstReturnedRecord.
After calling update method of data adapter I do not get new
primary key value.
I did the same without using command builder (I've created insert command object in code "manually" and it's identical to the one which is created by the command builder) and everything seems to work fine. Does someone have any idea why retrieving autoincrement value doesn't work when I use CommandBuilder?
Thanks for any clues
Maciek
ps. If you need to see a code example, just let me know.
|
|
|
|
|
I have the myfile.mdb in sub-folder database, sometime I want to delete it but after I do the following (correct location), it is still there:
<br />
set fs=Server.CreateObject("Scripting.FileSystemObject") <br />
<br />
set f = fs.GetFile("\database\myfile.mdb")<br />
f.Delete[True]<br />
<br />
set f=nothing<br />
set fs=nothing<br />
Does anyone know how to do it differently?
Thanks
|
|
|
|
|
Are there any open connections to the database (does the .ldb file exist)?
If the database is in use, you won;t be able to delete it.
What does Delete return?
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
Can anyone please help me on the error handling?
This code below is belongs to SQL.
I'm doing in Access.
Catch ex As Exception
if ex.Number = 2627 <---- ex.Number is not support in system.data.OleDb.
Any solution?
Please help.
Try
myCommand.ExecuteNonQuery()
Message.Style("color") = "DarkBlue"
Message.InnerHtml = "Record is created successfully."
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try
|
|
|
|
|
The exception class SqlException exposes a property called Number . The OleDbException class does not. If you're using OleDb against an Access database, you'd want to determine how the "primary key" error is reflected in OleDbException - take a look at OleDbException in the SDK documentation.
OleDbException does provide an ErrorCode property and an Errors collection - both may be useful for this purpose. I don't know what ErrorCode is returned on a "primary key already exists" error, but you could find out easily enough catching the OleDbException , setting your Message.InnerHtml value to the ErrorCode , and running a test where you try to insert a duplicate record.
|
|
|
|
|
hi,
How could i save a image save into a sqlserver image field to hard drive?
Thanks
|
|
|
|
|
|
|
Hi can anyone pleez assist me, i am trying to sort my db results by first letter... ie a, b, c etc how would i go about this using SQL. i want to be able to select a letter and have it order my all results by that chosen letter.
Thanks very very much
Butch
|
|
|
|
|
SELECT foo FROM bar ORDER BY SUBSTRING(foo,1,1)
|
|
|
|
|
thanks very much pbpb this has proved to be very useful.
thanks again
|
|
|
|
|
how can i copy the diagrams from a db to another?
(backup/restore is not an option since the a few differences between them)
//Roger
|
|
|
|
|
Here you go:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125
|
|
|
|
|
I am trying to write a small app to map out sql server logins that are no longer mapped to any databases. simple enuff. got the algorithm worked out. however having problem with ado.net returning varbinary value to compare against.
First i create a reader (a) with the following query: "select name,sid from master.dbo.syslogins"
Then I create a reader (b) with the following query: "select name from master.dbo.sysdatabases"
Everything okay so far.
Now I want to loop through each login and check to see if the sid is mapped to any user in the databases.
So I create a query as such:
cString = select count(*) from " + b["name"].tostring() + ".dbo.sysusers where sid = " + a["sid"].tostring();
then next statement is to setup a sqlcommand and executeacaler the cString query.
I go through with the debugger and check to see what the value of cString becomes and everytime it is translated to:
"select count(*) from Broadband.dbo.sysusers where sid = System.Byte[]"
this generates a sql exception error. I can put single quotes around it but it still looks like:
"select count(*) from Broadband.dbo.sysusers where sid = 'System.Byte[]'"
this does not generate a sql exception but neither does it work. no sid values equals the string System.Byte[]
Need help.
cpritt
|
|
|
|
|
If the variable 'a' is a SqlDataReader (or any data reader, for that matter), you have a whole slough of methods to convert the data. Try a.GetString(1) to get a string value from a["sid"], or try a.GetInt32(1) to get an int value.
|
|
|
|
|
variable "a" is a SqlDataReader. I have tried the other options like a.GetSqlBinary(1) and everything still is being interpreted as System.Byte[].
what i mean is that the c# text:
cString = "Select whatever where sid = " + a.GetSqlBinary(1)
is sent to sql server as "Select whatever where sid = System.Byte[]"
whereas a SqlException is generated.
what i want is:
"Select whatever where sid = 0x27FA32BE455789"
not sure how to get this
|
|
|
|
|
"SELECT " + whatever + " WHERE sid=0x" + a.GetString(1);
When you build a query to execute with a SqlCommand, its just a string. Don't call GetSqlBinary, because its going to return the native type for binary data: byte[]. Call GetString....the SqlDataReader should properly convert from the native Sql datatype to a string, and then you just append that string to the rest of the query.
you'll end up with a SELECT statement thats something like:
"SELECT col1, col2, col3 FROM table WHERE sid=0x27FA32BE455789"
If that select statement executes fine if you run it directly on the database, it should run fine through a SqlCommand. Its just a matter of retrieving the data from the resultset in the way you want it. Just because something is an int in the resultset doesn't mean you have to call GetInt().
|
|
|
|
|
Thanks for the input and patience to respond to my questions. I tried the GetString(1) and it generated a exception for illegal typecast. Not sure where to go from here. Just in case we are missing each other in the interpretation of what i am trying to do in the code, here is the code in question. Pretty short and simple. I have walked through this step by step and where known sid's are in the sysusers table the executescalar count is still 0 when it should be 1. Of course that could be where my understanding is causing me to blieve the code is not working:
private void GetLoginsNotMapped()
{
int count;
bool sidFound;
string user;
SqlTransaction my_SqlTransaction;
SqlCommand my_Command;
SqlDataReader my_Reader;
SqlConnection my_Connection;
SqlCommand m_Command;
SqlDataReader m_Reader;
SqlConnection m_Connect;
SqlCommand my_SqlCommand;
SqlCommand m_SqlCommand = new SqlCommand();
SqlConnection t_Connection;
try
{
m_Connect = ConnectToServer();
my_Connection = ConnectToServer();
t_Connection = ConnectToServer();
if (sqlSuccess == true)
{
cString = "Select name, sid from master.dbo.syslogins";
my_Command = new SqlCommand(cString,my_Connection);
my_Reader = my_Command.ExecuteReader();
m_SqlCommand.Connection = t_Connection;
while (my_Reader.Read())
{
sidFound = false;
user = my_Reader["name"].ToString(); //Used only for troublshooting
cString = "Select name from master.dbo.sysdatabases";
m_Command = new SqlCommand(cString,m_Connect);
m_Reader = m_Command.ExecuteReader();
while (m_Reader.Read())
{
cString = "Select count(*) from " + m_Reader["name"].ToString() + ".dbo.sysusers ";
cString = cString + "where sid = " + my_Reader.GetString(1) + "";
m_SqlCommand.CommandText = cString;
try
{
count = (int) m_SqlCommand.ExecuteScalar();
if (count > 0 ) sidFound = true;
}
catch (SqlException e)
{
logErrorMessage(serverName,e.Message);
}
}
m_Reader.Close();
m_Command.Dispose();
if (sidFound == false)
{
try
{
my_SqlTransaction = mySqlConnection.BeginTransaction();
my_SqlCommand = mySqlConnection.CreateCommand();
my_SqlCommand.Transaction = my_SqlTransaction;
my_SqlCommand.CommandText = "Insert into tblNotMapped (" +
"serverName, loginName) Values ('" + serverName +
"', '" + my_Reader["name"].ToString() + "') ";
my_SqlCommand.ExecuteNonQuery();
my_SqlTransaction.Commit();
my_SqlTransaction.Dispose();
}
catch (SqlException ex)
{
logErrorMessage(serverName,ex.Message);
}
}
}
my_Reader.Close();
my_Command.Dispose();
m_SqlCommand.Dispose();
}
}
catch (SqlException err)
{
logErrorMessage(serverName,err.Message);
}
}
Thanks ahead of time for any tips can be sent my way.
/cp
|
|
|
|
|
Well, lets see what we can do here. It seems its not possible to have ADO.NET automatically cast a varbinary to a string. So, what we need to do is find a way to manually cast it ourselvs.
I think there are several ways of doing this. So, after a few minutes research, here are the best options I came up with.
Try using my_Reader.GetSqlBinary(). From this SqlBinary object, call sqlBinary.ToString() to convert the value to a string. I'm not certain if it will convert byte by byte or if it will just do something similar to calling Encoding.ASCII.GetString(byte[]).
If calling ToString doesn't work right, you could try calling sqlBinary.ToSqlGuid(), then call sqlGuid.ToString(). You should only do this if the binary data being returned is 128 bits, or 16 bytes, long.
If the data is not 16 bytes long, and calling GetSqlBinary doesn't result in useful data, I'd manually build a string from your byte array. Something like:
byte[] binary = my_Reader.GetSqlBinary().Value;
string binStr = "";
for (int i=0; i
|
|
|
|
|
Whooo Hwoooooo!!!!!!!!!!
You duh MAN!!!!!!!!
that worked. I used the byte[] array and looped through each byte in the array.
I'm rockin and rollin now.
thanks a whole lot. saved my butt and allowed me to complete a project on time.
|
|
|
|
|
I'm glad I could be of service.
|
|
|
|