|
At my company, we have a SQL database that contains information about employees, clients, and other such administrative information. I'm developing an application that requires its own SQL database. One of the main tables has several fields that need to 'point' to valid records in the administration database, such as ClientID, for example.
How can I go about doing this? Can I add this somehow to my relationship diagram?
Thanks in advance.
Kyosa Jamie Nordmeyer - Cho Dan
Portland, Oregon, USA
|
|
|
|
|
MS SQL Server doesn't support creating relations to external datasources.
Though you are able to JOIN to external tables in a query.
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
Figures. Maybe that add that ability in Yukon. From a user standpoint, it seems like an obvious thing to include (my company can't be the first to want seperate databases for seprate needs), but who knows how tricky it is to program from Microsoft's side.
Oh well. Here's hoping for Yukon...
Kyosa Jamie Nordmeyer - Cho Dan
Portland, Oregon, USA
|
|
|
|
|
I am having trouble storing the date in UK format.
I have an aspx page with a DateTime Validator control - works ok.
If the Date Text field on the page is entered in US format: mm/dd/yyy my INSERT works fine. However iif the format is UK: dd/mm/yyyy, the INSERT fails.
So i got to thinking i should be formatting the date myself after it passes through the Validator.
i tried the code below:
dt = Convert.ToDateTime(txtBirth.Text);
output = dt.ToString("d",DateTimeFormatInfo.InvariantInfo);
values.Add("'" + output + "'");
Is this the correct approach? (full listing shown below)
If i do: //values.Add("'" + txtBirth.Text + "'");
and the Date is in US format it works fine, but fails for UK.
your help would be greatly appreciated...
Carl
Full Listing + stuff i've tried:
SqlConnection con;
string sql;
SqlCommand cmd;
StringBuilder sb = new StringBuilder();
ArrayList values = new ArrayList();
sb.Append("INSERT INTO [Users] ");
sb.Append("(UserID,Login,Password,FirstName,LastName,PhoneNumber,Email,Address,");
sb.Append(" MobileNumber, DateOfBirth,IsAdministrator) ");
sb.Append("VALUES ('{0}', '{1}', '{2}', '{3}','{4}','{5}','{6}','{7}','{8}',{9},{10})");
values.Add(Guid.NewGuid().ToString() );
values.Add(txtUser.Text);
values.Add(txtPwd.Text);
values.Add(txtFName.Text);
values.Add(txtLName.Text);
values.Add(txtPhone.Text);
values.Add(txtEmail.Text);
//values.Add(0);
if(txtBirth.Text != string.Empty)
{
//test vars
string output="No Output";
DateTime dt;
dt = Convert.ToDateTime(txtBirth.Text);
output = dt.ToString("d",DateTimeFormatInfo.InvariantInfo);
values.Add("'" + output + "'");
//Test Blocks
//block 1
//DateTime dtNow = DateTime.Now;
//values.Add("'" + dtNow + "'");
//block 2
//values.Add("'" + txtBirth.Text + "'");
//block 3
//values.Add("'27/11/1999'");
//block 4
//storing the date & time local to the client
//DateTime dt;
//dt = DateTime.Now;
//string output = dt.ToString("G",DateTimeFormatInfo.CurrentInfo);
//values.Add("'" + dt + "'");
//block 5
//dt = Convert.ToDateTime(txtBirth.Text);
//output = dt.ToString("d",DateTimeFormatInfo.InvariantInfo);
//values.Add("'" + output + "'");
//block 6
//values.Add("'" + txtBirth.Text + "'");
//test stuff
Label1.Text = output;
}
else
values.Add("Null");
|
|
|
|
|
I suggest either using DateTime.Parse, or using separate entry fields for day, month and year.
|
|
|
|
|
I suggest use Globalization Classes in .Net
using System.Globalization<br />
using System.Threading<br />
in page load
<br />
string slang = Request.UserLanguages[0];<br />
Thread.CurrentThread.CurrentCulture = new CultureInfo(slang);<br />
With this code according to user preferences you should be able to save datetime properly. If user is american it expect american style, if it is turkish then it expects turkish style. Request.UserLanguages[0] gives you a
string in this format
en-UK or en-US
first two chars is about language
second two chars is about culture.
You can check user culture and call different function according to that also.
Education is no substitute for intelligence. That elusive quality is defined only in part by puzzle-solving ability. It is in the creation of new puzzles reflecting what your senses report that you round out the definition. Frank Herbert
|
|
|
|
|
I suggest storing the date as a double. It is the way a computer stores it anyhow. You can't make a mistake when the user changes regional settings e.g. dd/mm/yyyy i.s.o. yyyy-mm-dd
Use a double and format it later for display purposes.
Grtz, Guus
|
|
|
|
|
Fun situation: We have been asked to convert an Access application to ASP.NET. The chap who wrote it eloped with the source code and so all we have is the database and compiled front-end.
The Access application has SQL 6.5 as it's back-end with just Access as the front-end (an ADE file).
We are running SQL 2000 and the database copies over fine from SQL 6.5.
The problem comes when we want to try and run this ADE file on our network as opposed to the clients. With some help from David Wulff (thanks mate!) I have managed to HEX edit the connection string. It was a stroke of luck that our named pipe was the same length as the original. So that is changed.
But I have no clue about SQL named pipes. How do I make our SQL 2000 box accept this connection type?
Here is the pipe: \\BGCTNT01\PIPE\SQL\QUERY
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
Einstein says...
|
|
|
|
|
In Enterprise Manager, right-click your server, choose Properties. At the bottom of the General tab, click Network Configuration. Find 'Named Pipes' in the Disabled Protocols list, select it, then click Enable. OK out of all the boxes.
You need to stop and restart the server for the change to take effect.
|
|
|
|
|
hi there
Is there any way (method or a built-in procedure in sql) to find out which databases are included in a specific ms sql server.I know the ip address of the ms sql server and i want to write a method in C# to list the available databases.Can you give me an example code?
thank you in advance
fotis
greece
|
|
|
|
|
just write a select statement to get a list of all the databases located in a server
make sure to connect to the 'master' database
select * from sysdatabases
|
|
|
|
|
I have the following code:
<br />
<br />
try<br />
{<br />
string ConnString;<br />
ConnString="Provider=SQLOLEDB;";<br />
ConnString+="data source=127.0.0.1;";<br />
ConnString+="initial catalog=AutoConvert;";<br />
ConnString+="password=password;";<br />
ConnString+="persist security info=False;";<br />
ConnString+="user id=sa;";<br />
OleDbConnection dbConn = new OleDbConnection(ConnString);<br />
dbConn.Open();<br />
<br />
FileStream fs = File.Open("c:\\temp\\output.dat",FileMode.Open);<br />
BinaryReader BR = new BinaryReader(fs);<br />
byte [] BLOB=BR.ReadBytes(Convert.ToInt32(fs.Length));<br />
DataBase.InsertBLOB(dbConn,"Documents","BatchName","1234567",true,"Document",BLOB);<br />
dbConn.Close();<br />
<br />
}<br />
catch(Exception Err)<br />
{<br />
Console.WriteLine(Err.Message);<br />
}<br />
<br />
<br />
<br />
<br />
static public void InsertBLOB(OleDbConnection db,string Table,string LookUpField,string LookUpData,bool IsLookUpString,string BLOBField,byte [] BLOB)<br />
{<br />
try<br />
{<br />
string Command;<br />
if(IsLookUpString)<br />
Command= String.Format("UPDATE {0} SET {1}=@{1} WHERE({2}='{3}')",<br />
Table,BLOBField,LookUpField,LookUpData);<br />
else<br />
Command= String.Format("UPDATE {0} SET {1}=@{1} WHERE({2}={3})",<br />
Table,BLOBField,LookUpField,LookUpData);<br />
OleDbCommand addEmp = new OleDbCommand(Command, db); <br />
addEmp.Parameters.Add("@"+BLOBField,BLOB);<br />
addEmp.ExecuteNonQuery();<br />
}<br />
catch(Exception Err)<br />
{<br />
throw new Exception(Err.Message);<br />
}<br />
}
addEmp.ExecuteNonQuery throws an exception witht he message:
"You must define @Document"
I copied the code almost directly from MSDN, but I can't get it to work...Any ideas ?
Oh and in my AutoConvert Catalog I have the table Documents with an image field of length 16 called Document.
|
|
|
|
|
From the OleDbCommand.CommandText documentation:
"The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL Statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used."
If you're talking to SQL Server (7.0 or 2000), use the SqlConnection and SqlCommand classes instead. Otherwise, replace the named parameter in the command text with a ?.
|
|
|
|
|
Thanks for the info, I was still wondering how it was done with OleDb...thanks.
|
|
|
|
|
Hi-
I'm a real beginner, so be gentle with me.
I'm trying to write a store procedure that basically takes value from Database A and insert it into Database B. Can someone give me an example of how to do this please?
Thanks! 
|
|
|
|
|
You need to do two things.
1. Make sure the user the stored procedure is using has access to the other database
2. In your TSQL statement, reference the other database and owner
Here is an example:
Two databases, Bob and Bill. In Bill there is a UserGroup table owned by DBO that I want to insert a record into, from Bob.
So INSERT INTO [Bill].[dbo].[UserGroups] ([Title], [Description]) VALUES ('title', 'description')
The only difference being the [Bill].[dbo] bit.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
Einstein says...
|
|
|
|
|
There seems to be a problem filter records from a dataset by date when the date spans a year.
This produces the expected results
string strFilter = "date >= '1/1/2003' AND date <= '12/31/2003'; <br />
DataRow[] rows = ds.Tables[0].Select(strFilter);
This does not
string strFilter = "date >= '1/1/2003' AND date <= '1/1/2004'; <br />
DataRow[] rows = ds.Tables[0].Select(strFilter);
Anyone know what is going on here or how to work around it?
|
|
|
|
|
I see what is going on now. The dates being compared as strings (dah!! ). I needed to write some code to convert and compare the dates as dates.
|
|
|
|
|
I'm using the followng code to access an Oracle database procedure. All works well until the ExecuteNonQuery() member is called. It throws an exception with the Oracle error message of "ORA-01008: not all variables bound".
try
{
m_ReturnRate = -1.0;
m_FXConn = new OracleConnection();
m_FXConn.ConnectionString = "Server=XXXXXX;User ID=XXXXXX; Password=XXXXXX";
m_FXCommand = m_FXConn.CreateCommand();
m_FXCommand.CommandType = CommandType.StoredProcedure;
m_FXCommand.CommandText = "get_currency_forward_rate( :TRADECURRENCY, :SETTLECURRENCY, :VALDATE, :SETTLEDATE, :STATUS, :CALCRATE )";
m_parmTradeCurrency = m_FXCommand.CreateParameter();
m_parmTradeCurrency.ParameterName = "TRADECURRENCY";
m_parmTradeCurrency.OracleType = OracleType.VarChar;
m_parmTradeCurrency.Direction = ParameterDirection.Input;
m_parmSettlementCurrency = m_FXCommand.CreateParameter();
m_parmSettlementCurrency.ParameterName = "SETTLECURRENCY";
m_parmSettlementCurrency.OracleType = OracleType.VarChar;
m_parmSettlementCurrency.Direction = ParameterDirection.Input;
m_parmValuationDate = m_FXCommand.CreateParameter();
m_parmValuationDate.ParameterName = "VALDATE";
m_parmValuationDate.OracleType = OracleType.DateTime;
m_parmValuationDate.Direction = ParameterDirection.Input;
m_parmSettlementDate = m_FXCommand.CreateParameter();
m_parmSettlementDate.ParameterName = "SETTLEDATE";
m_parmSettlementDate.OracleType = OracleType.DateTime;
m_parmSettlementDate.Direction = ParameterDirection.Input;
m_parmStatus = m_FXCommand.CreateParameter();
m_parmStatus.ParameterName = "STATUS";
m_parmStatus.OracleType = OracleType.Int32;
m_parmStatus.Direction = ParameterDirection.Output;
m_parmCalculatedRate = m_FXCommand.CreateParameter();
m_parmCalculatedRate.ParameterName = "CALCRATE";
m_parmCalculatedRate.OracleType = OracleType.Double;
m_parmCalculatedRate.Direction = ParameterDirection.Output;
m_FXConn.Open();
Console.WriteLine( "OPENED" );
m_FXCommand.Prepare();
Console.WriteLine( "PREPARED" );
}
...
...
...
try
{
m_parmTradeCurrency.Value = CurrencyTrade;
m_parmSettlementCurrency.Value = CurrencySettle;
m_parmValuationDate.Value = ValuationDate;
m_parmSettlementDate.Value = SettlementDate;
m_parmCalculatedRate.Value = -1.0;
m_parmStatus.Value = -1;
m_FXCommand.ExecuteNonQuery();
Console.WriteLine( "EXECUTED" );
if ( m_parmStatus.Value.Equals(0) )
{
m_ReturnRate = (double)m_parmCalculatedRate.Value;
}
}
The first try block is in a constructor that works fine. The second try block is in a method that is passed values to assign to the parameters. Have I forgot to call some Bind method that I can't locate or is there some simpler answer to what's going wrong? Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Christopher Duncan
I can't help getting older, but I refuse to grow up. Roger Wright
I've been meaning to change my sig. Thanks! Alvaro Mendez
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
|
|
|
|
|
Turns out that I made an assumption that is incorrect. It seems that just cause you have an OracleCommand object create an OracleParameter object for you (instead of using new OracleParameter), it doesn't add the parameter to its parameter collection. For some reason, you still have to do that. So I've added the following
m_FXCommand.Parameters.Add(m_parmTradeCurrency);
m_FXCommand.Parameters.Add(m_parmSettlementCurrency);
m_FXCommand.Parameters.Add(m_parmValuationDate);
m_FXCommand.Parameters.Add(m_parmSettlementDate);
m_FXCommand.Parameters.Add(m_parmStatus);
m_FXCommand.Parameters.Add(m_parmCalculatedRate);
and things seem to be working fine.
Chris Meech
It's much easier to get rich telling people what they want to hear. Christopher Duncan
I can't help getting older, but I refuse to grow up. Roger Wright
I've been meaning to change my sig. Thanks! Alvaro Mendez
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
|
|
|
|
|
Dear Friends,
I have a SQL Server 2000 database named test. The database has 2 files: 01 data file (test_Data.mdf) and 01 log file (test_Log.ldf). But the log file has been lost. Please help me on how to recover the database from the data file only.
Thank in advance.
|
|
|
|
|
I am doing some research and would like to know if any fellow DBAs out there or programmers support Best MS500 ERP at your organization.
Could you tell me :
1) Can you code it in a standard language such as VB or C?
2) How well do you like supporting it
3) Anything you can tell me in your experience supporting it which may be helpful. Good or Bad
|
|
|
|
|
I am a DBA doing some research and would like to know if any fellow DBAs/programmers out there or programmers support Syspro ERP at your organization.
Could you tell me
1) Can you code it in a standard language such as VB or C?
2) How well do you like supporting it
3) Anything you can tell me in your experience supporting it which may be helpful. Good or Bad
|
|
|
|
|
i've been developing a program for a customer for recipe editing. I am new to .NET dataaccess and am having a problem. I open the DBF file, export it to an xml file so i can edit it (cause i know how to do that). Then when i'm done i want to import it back into the database. I have this working except for it duplicates every record in the table. Any help would be greatly appreciated.
Thanks for your time,
Jamie Lindemann,
Lindemann Electric Services Inc.
|
|
|
|
|
Could use some more information.
Do you want to Update or Insert the information that you are sending back?
Do you have a check that would tell you if a file exists so that it does not get re-inserted?
How is the XML string constructed that you are sending back?
Could you post some of you procedure?
Thanks
Will
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
