|
I currently have this stored procedure:
<br />
@id as bigint,<br />
<br />
AS<br />
SELECT Meetings.meetinguser, Companies.name, Meetings.meetingdate <br />
FROM Companies INNER JOIN Meetings <br />
ON (Companies.id = Meetings.company) <br />
WHERE (Meetings.complete = 0)<br />
FOR XML AUTO, ELEMENTS<br />
Meetings.meetinguser is an ID number that also appears in the User table.
User.UserName is the name that coresponds to that ID.
At the moment this returns:
UserID CompanyName Date
I would liek it to return:
UserName CompanyName Date.
I was not sure about using more than one join in a single query...
Can any one, please, help ?
Thanks
-- modified at 5:07 Thursday 20th July, 2006
|
|
|
|
|
SELECT User.UserName, Companies.name, Meetings.meetingdate
FROM Companies, Meetings, User
WHERE Companies.id = Meetings.company AND User.id = Meetings.meetinguser
AND Meetings.complete = 0
ought to do it.
Steve S
Developer for hire
|
|
|
|
|
Simply add
INNER JOIN User
ON MeetingsUser.UserID = User.UserID after the existing ON clause and before the WHERE clause. You can have as many join clauses as you need.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Brilliant thanks guys 
|
|
|
|
|
Anyone know how to set the lenght of the date???
Like
I have a product information here that i bought it from 1st Jan 2003, then the access will automatic set it warranty valid until 1st Jan 2004 if the user select 1 year warranty, if 2 year then 2005.
how do i set those queries??? anyone try b4?
i try to set the "actual date +365" for 1 year but it will come out as number , not the date.
|
|
|
|
|
campbells wrote: I have a product information here that i bought it from 1st Jan 2003, then the access will automatic set it warranty valid until 1st Jan 2004 if the user select 1 year warranty, if 2 year then 2005.
how do i set those queries??? anyone try b4?
i try to set the "actual date +365" for 1 year but it will come out as number , not the date.
Read up on the DateAdd function.
DateAdd[^]
exp DateAdd("yyyy",1,Date())
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i need to copy data from table to another table do i used this statement
(
insert into table1
select category_id ,category_name,category_description
from categories
)
this error appear
(
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
)
what is the soulation
ma_refay
|
|
|
|
|
Or your select statement is out of order with the column names. Depending on the difference you will have to case fields in the select statement to match the column definitions in the result table.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi,
This problem occurs when you trying to insert to field a string that exceeds fields length. The only solution I could find was to set a bigger field length.
Check the length of the datatype of table in which you are inserting...
May be the length of the data type from the table whose contents you are inserting is greater than that of the table in which insertion is made.
Try out...
Good Luck!
|
|
|
|
|
Is there a way to have the progress bar increment for each record that will be imported?
When I d0 a select * (or whatever), does it bring the records in one-by-one where a progress bar can be set up?
Thanks,
RABB17
|
|
|
|
|
Rabbit17 wrote: When I d0 a select * (or whatever), does it bring the records in one-by-one where a progress bar can be set up?
No, I would use some type of animated gif (similar to the one that windows uses when empting the “recycle bin”) display it while your importing upon completion hide it.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
I was hoping for something a little more accurate. Anyone have any ideas?
|
|
|
|
|
Rabbit17 wrote: I was hoping for something a little more accurate.
I have no idea what language you’re using; MySQL is a database server not a programming language. What was so inaccurate about my suggestion? 
Rabbit17 wrote: Anyone have any ideas?
Sure I have lots of ideas, a few even pertaining to the topic at hand. 
When selecting information from a database, you don’t know how many rows will be returned, which for all practical purposes you will have no idea how long it’s going to take that operation to complete.
I have used things like the gears turning, progress bar (that bounces back and forth quickly) to the jack in box they all work on the same principal. Start playing your animation before beginning the complex operation and hide it when finished.
If you peruse through this site there are is a whole host of different animations to choose from.
vbaccelerator[^]
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i have sql server database
i need to copy data from existing table to an existing table
the select into statement create another new table i want to copy the datet from table to table that already exists in the database
|
|
|
|
|
INSERT INTO Foo
SELECT FIELD1, FIELD2, FIELD2 FROM bar
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
i have sql server database
i need sql statement to copy all data from one table to another table
|
|
|
|
|
SELECT FIELD1, FIELD2, FIELD2
FROM table1
INTO table2
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I keep getting an exception when I call the DataAdapter Update method.
I have been trying to figure out what is causing the null reference
exception for this code for what seems like forever:
private void DoInserts(OdbcDataAdapter odbcDataAdapter, string
tableName)
{
DataTable dataTableChanged =
dsTapes.Tables[tableName].GetChanges(DataRowState.Added);
if ((dataTableChanged != null) &&
(dataTableChanged.Rows.Count > 0))
{
// Open the connection if its not already open.
if (odbcConnection.State !=
System.Data.ConnectionState.Open)
{
odbcConnection.Open();
}
//Create a new transaction.
odbcDataAdapter.InsertCommand.Transaction =
odbcConnection.BeginTransaction();
try
{
//Submit the changes.
odbcDataAdapter.Update(dsTapes,
dataTableChanged.TableName.ToString());
//Commit the changes and close the connection.
odbcDataAdapter.InsertCommand.Transaction.Commit();
}
catch (Exception ex)
{
odbcDataAdapter.InsertCommand.Transaction.Rollback();
throw (ex);
}
}
}
The exception information has not been helpful. All it says is: "Object
reference not set to an instance of an object."
I don't know if this will help, but the Insert command looks like this:
tapeLogInsert = odbcConnection.CreateCommand();
tapeLogInsert.CommandText =
"INSERT INTO " + tapeLogODBCName.Trim()
+ " (NBR, Backup_Name, BakDate, ScrDate, Location, "
+ "Tape_No, Tape_Set, Usage, Comment, UseDate) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
tapeLogInsert.Parameters.Add(new OdbcParameter("NBR",
OdbcType.Int));
tapeLogInsert.Parameters["NBR"].SourceColumn = "nbr";
tapeLogInsert.Parameters.Add("Backup_Name", OdbcType.Char, 17,
"backup_name");
tapeLogInsert.Parameters.Add(new OdbcParameter("BakDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["BakDate"].SourceColumn = "bakdate";
tapeLogInsert.Parameters.Add(new OdbcParameter("ScrDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["ScrDate"].SourceColumn = "scrdate";
tapeLogInsert.Parameters.Add("Location", OdbcType.Char, 1,
"location");
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_No",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_No"].SourceColumn = "tape_no";
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_Set",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_Set"].SourceColumn = "tape_set";
tapeLogInsert.Parameters.Add(new OdbcParameter("Usage",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Usage"].SourceColumn = "usage";
tapeLogInsert.Parameters.Add("Comment", OdbcType.VarChar, 50,
"comment");
tapeLogInsert.Parameters.Add(new OdbcParameter("UseDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["UseDate"].SourceColumn = "usedate";
tapeLogAdapter.InsertCommand = tapeLogInsert;
Does any one have any idea what is going on here or how to go about
finding out? Any suggestions would be appreciated: I don't even know
how to figure out which reference is null!
|
|
|
|
|
When using c#, I've found I have to re-reference the commands when using CreateCommand.
Try:
odbcCommand tapeLogInsert = odbcConnection.CreateCommand();
instead of:
tapeLogInsert = odbcConnection.CreateCommand();
even if you lead with:
odbcCommand tapeLogInsert = new odbcCommand();
tapeLogInsert = odbcConnection.CreateCommand();
Hope that helps
RABB17
|
|
|
|
|
I am tring to create a table in a mdb file, but the maximum number of columns i can add is 40. When i try to add the same number (or move) of columns from the access interface, there is no problem.
Can you tell me if it is something i overlooked.
The connection string to the mdb file is : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<filepath>
I am using C#, on .NET Framework 1.1 and the connection class is OleDbConnection
|
|
|
|
|
So, what is the error you get? What is the SQL you are using?
|
|
|
|
|
I'm using the next sequence of SQL:
CREATE TABLE Table_1 ( ID char(50) PRIMARY KEY);
After the creation of the table i'm using a for cycle to add as many columns in the table as necesary. (max. 200)
The query run in the for cycle is: ALTER TABLE Table_1 ADD column_i char(50); - where "i" is the counter
I get the following error when i add more then 40 columns:
System.Data.OleDb.OleDbException: Record is too large.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
|
|
|
|
|
You are exceeding a published limit for Microsoft Access:
Access XP allows a maximum of 2000 characters per record in a Table.
Access 2003 allows 4000, but depending on your connection string,
you may be using the access xp engine version even thought the application version
you have is 2003, which would explain why you can interactively create
up to 80 fields of Char(50) data. See this[^] for a list of the limitations.
BTW, a table with 200 50 char fields does not look like a very good design...
|
|
|
|
|
I know is not very good, but it was only a test. The type of the fields will be changed when i start codding seriously.
10x for the link.
|
|
|
|
|
Hi,
recently I needed a tool to run SQL scripts against a Microsoft Access database. I found OleDbCmd on Sourceforge.net (http://sourceforge.net/projects/oledbcmd/[^] which did the trick.
However it had a number of shortcomings, so I extended it somewhat and have released my updates through sourceforge.net as version 1.0. These usage notes should explain the extent of the updated application:
----------------------------------------------------------------------------------------------------------------
Purpose:
This program allows you to execute SQL commands against an OleDB Database
- eg a Microsoft Access database, an Excel spreadsheet, etc. This can be a
powerful adjunct to LGX Report (see www.freereporting.com) as it allows
database creation to be scripted rather than manually creating a database
with Microsoft Access. Another useful free tool to load data into your
database is Microsoft's LogParser 2.2 - search their site for the latest
download location for this tool.
Usage:
OleDBCmd [/help] [/ver] [/connect=<oledb connect="" string=""> | /file=filename] ...
[/create] [/script=<sql script="" filename=""> | /cmd="sql command" ...
[/quiet] [/CSV] /nohead] [/starComments]
Parameters:
/help Print the usage details and exit.
/ver Print the version and license details.
/connect Specifies the OleDB connection string for the database, eg
'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb'
/file Specifies a file name, the provider will be set to
Microsoft.Jet.OLEDB.4.0. Both .mdb and .xls files are
handled.
/create The program will attempt to create the database.
/script Specifies a script file for the SQL commands to be
read from. Note, commands may span multiple lines and
must be terminated with a semi-colon. Remark lines must
start with -- following any leading whitespace.
/cmd Specifies a single SQL command to execute.
Note, if neither the /script nor /cmd arguments are given
commands are read from the console - comment processing
is not performed.
/quiet Suppresses command echo and status messages. Only SQL
output is written to the console.
/CSV SQL results are output in CSV format rather than the default
TSV format.
/nohead Excludes the header row from the results.
/starComments Allows embedded and multi-line comments using /* ... */ format.
----------------------------------------------------------------------------------------------------------------
As noted above, I found this useful in conjuction with the free web reporting tool - LGX Report (http://www.freereporting.com/[^]) as I can then design databases using Enterprise Architect from http://www.sparxsystems.com.au/[^] and generate the script to create the database and run it with OleDbCmd.
Hope this is useful to others out there looking to leverage free and low-cost tools to build solutions quickly.
Regards,
Ian.
Ian Hogan - Consultant
THINKronicity Pty Ltd
http://THINKronicity.com.au/[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
