|
Try
SQLstr="insert into books ({col1}, {col2}) values ('" & textBox1.Text & "', '" & textBox2.Text & "')";
(This is VB ... I'm not sure what language you're using ... the important thing is the {col1} and {col2} which are the names of the table colums that @textBox1 and @textBox2 are going to be inserted into)
I find that a good debug tip is to use Query Analyser to test the structure of my SQL statement before I insert it into code.
So I would try something like INSERT INTO books VALUES ('SQL Programming', 'Robert Vieira') and when that didn't work I'd try INSERT INTO books (title, author) VALUES ('SQL Programming', 'Robert Vieira')
|
|
|
|
|
Hi all,
I have 4 tables where in each table 3 coulmns are the primery key(Year,Month,ProductID) and it has notherextra different columns.
Now i wanna join all four related to primery key. and combine all columns.
Table 1
Year,Month,ProductID,Location,Sales
Year,Month,ProductID,Location,Returns
Year,Month,ProductID,Location,DuePayment
Year,Month,ProductID,Location,Profit
I wanna join and get Following
Location,Sales,Returns,DuePayments,Profit
Also i wanna group the table Location
And Filter it for Month=03
Please Help me
I need the SQL Select statement
Regards
Ruwandi
rkherath
|
|
|
|
|
It should be something like:
Select t1.location, t1.sales, t2.returns, t3.duepayment, t4.profit
from table1 t1
join table2 t2 on t1.year = t2.year and t1.month = t2.month and t1.productid = t2.productid and t1.location = t2.location
join table3 t3 on t1.year = t3.year and t1.month = t3.month and t1.productid = t3.productid and t1.location = t3.location
join table4 t4 on t1.year = t4.year and t1.month = t4.month and t1.productid = t4.productid and t1.location = t4.location
where t1.month = '03'
order by t1.location
It might be a good idea for you to get a sql book so you can learn the basics of write sql queries.
Ben
|
|
|
|
|
Hi Ben,
Thx for replying. I tried on the code and it didnt work out. Actually It doesnt show error but no records shown as the resul. I checked manually and there are records that should be displayed.
I have a doubd that , is it a problem that i am using year , month, Pro ID, Location as composite key(All are primary keys in both the tables)
if the case like this is there a different way to do this.
regards
Ruwandi
rkherath
|
|
|
|
|
As per what i understood hope this is what you are looking for ....
SELECT Year, Month, ProductID, Location, SUM(Sales), SUM(Returns),
SUM(DuePayments), SUM(Profit)
FROM (
SELECT Year,Month,ProductID,Location,Sales FROM TableA
UNION
SELECT Year,Month,ProductID,Location,Returns FROM TableB
UNION
SELECT Year,Month,ProductID,Location,DuePayment FROM TableC
UNION
SELECT Year,Month,ProductID,Location,Profit FROM TableD
)a
WHERE Month = 3
GROUP BY Year, Month, ProductID, Location
Regards
KP
|
|
|
|
|
Hi all,
I have created a dts package to upload data from a csv file in to a table in SQLServer. Now I want to do 2-things.
1. I dont want the same rows to be loaded in the table more than once, means the records must be unique.
2. I would like to display an error message in to a .Net UI when DTS package fails runnig, or when the same records are tried to be loaded.
Please tell me any idea how can I achieve this, because I am new to this DTS Package services. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
hello every one, my name SAN Vibol, now i just working with unicode with SQL server 2000, i can insert, show all record, but when i select (WHERE) sometime related my key word, sometime show other data. it dont follow my mine.
example: i have data ( my unicode language to english)
School
Water
We
You
Class
SQL:
select ItemName from Tbl_Test where ItemName=N'School'
Result
School
or
School
You
or Class
Water
School
================================================
i dont kown why it like this. bec' i need result school, why it show any data?
Please help me
SAN Vibol
SAN Vibol
|
|
|
|
|
i'm facing a problem connecting to mysql using C#
i'm using oledb and it gives me (MySQLProv' provider is not registered on the local machine)
i downloaded and installed ADO.NET Driver for MySQL (Connector/NET)
so does anybody know the problem
|
|
|
|
|
Hi,
You have downloaded MySQl connector, so use it instead of oledb. It's a native driver and fully managed .Net so more performant than oledb driver.
HTH.
Hayder Marzouk
|
|
|
|
|
thanks Hayder. but i think we need to add reference first to use the name space
|
|
|
|
|
Hi,
I am using C# and MSSQL to read a bunch of complex objects from a file and trying to insert them into a database. To simplify and point out my problem I'll use this structure:
Object:
Name
Array a
Table1:
ObjectID (Primary Key)
Name
Table2:
ObjectID (Foreign Key)
ArrayIndex
value of a @ ArrayIndex
What I can't figure out is how to update the database. I can load the objects into a dataset, but how do I get the primary key from Table1 to insert as a foreign key into Table2? I'm currently unable to update without that foreign key.
Thanks!
|
|
|
|
|
You can do this job
SELECT * FROM Table1
Store the result in a Combobox or DropDownList control and then use the SelectedValue of that control
|
|
|
|
|
If you have the data already then there shouldn't be an issue. If the keys don't exist yet then you would probably have to create them on the fly. I am guessing that you are perhaps using identity columns, so you don't know the key until after you have inserted. If this is what you are doing, an option is to write a stored procedure that returns the new identity in an output parameter so the next insert can use it. In the procedure after the insert you can use @@scope_Identity to get that value that you want to return. It is the identity value that was used for that insert.
Hope that helps.
Ben
|
|
|
|
|
Here is the logic I'm trying to use...and maybe I'm thinking about this all wrong.
Loop through Objects loaded from file (Loop1)
Add Object to Dataset (Table1)
Loop through Array a in Object (Loop2)
I need the key from the Object above at this point
Add values from Array a to Dataset (Table2)
Table1.Update
Table2.Update
What I was trying to avoid was updating the database each time through Loop1, in order to get back an a unique ID (or primary key), feeling all those updates may be quite a performance hit. Maybe there is no way around that though. Any thoughts?
Thanks,
Brandon
|
|
|
|
|
Can anybody please tell me the steps to include activeX scripting in a DTS package. I would be thankfull to them.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
So, I've got an open connection to my DB, I've got a dataSet, I've made my table and added it to the dataSet, I've got a SqlDataAdapter, constructed with a SELECT command string. I would like to use the SqlCommandBuilder to get the rest of the SQL commands in the DataAdapter, but I can't seem to find the correct order to do everything. I need to add the table to the dataset, initialize the adapter for the table, build the other commands for the adapter, and finally save everything back to the DB on disk. Below is my code, and I'm sure hoping someone can point me in the right direction. I know it seems long, but there are lots of comments and extra lines. I really need help on this, been working all hours for 2 days trying to figure this out.
<code>
private void CheckDDBTables()
{
DataTable table;
DataColumn column;
DirectoryInfo dir = new DirectoryInfo( Application.StartupPath + "\\forms\\" );
bool needNewtable = true;
CustomFormat frm = new CustomFormat();
SqlDataAdapter tempDA;
SqlCommandBuilder builder;
string selStr = ""; // SELECT "column_name" FROM "table_name"
DBConn.Open(); // a global SqlConnection, intialized with the correct connection string
// loop through all files in the directory
foreach( FileInfo fli in dir.GetFiles( "*.*" ) )
{
// for every form file, make a table, to make the DataAdapter
// load the file into the form, function also inits
LoadFile( "\\forms\\" + fli.Name, out frm );
// init the selStr
selStr = "SELECT ";
// make a new table for this file
table = new DataTable( frm.name.Substring( 0, frm.name.Length - 4 ) );
// identity index column
column = new DataColumn( "Listing", typeof( int ) );
column.AllowDBNull = false;
column.AutoIncrement = true;
column.Unique = true;
column.AutoIncrementSeed = 1000;
table.Columns.Add( column );
table.PrimaryKey = new DataColumn[] { table.Columns[ 0 ] };
// add column to selStr
selStr += column.ColumnName + ",";
// begin adding columns, for each item in frm
foreach( Item gc in frm.ItemList )
{
// new column
column = new DataColumn( gc.name, typeof( string ) );
// add the column to the table
table.Columns.Add( column );
// add column to selStr
selStr += column.ColumnName + ",";
} // end for each Item in frm.ItemList
// finish selStr
selStr = selStr.Substring( 0, selStr.Length - 1 ) + " FROM " + table.TableName;
// init the tempDA
tempDA = new SqlDataAdapter( selStr, DBConn );
// loop through all the tables in the DB
foreach( DataTable tbl in DBDataSet1.Tables )
{
if( tbl.TableName == fli.Name )
needNewtable = false;
}
// after searching the tables, if we need the table, it's made, just add it
if( needNewtable )
{
DBDataSet1.Tables.Add( table );
DBDataSet1.AcceptChanges(); // wasn't sure if this needs to be here, didn't seem to affect anything
}
// see if we can fill the dataset with anything
try
{
tempDA.Fill( DBDataSet1 );
}
catch( SqlException sqle )
{
// returning SqlException "Invalid object name '<name>'"
// table must not exist, so add it
DBDataSet1.Tables.Add( table );
}
// try to make the rest of the commands
// same error if i try to access the other commands
builder = new SqlCommandBuilder( tempDA );
// update and check below
DBDataSet1.AcceptChanges();
tempDA.Update( table );
// save in list for later
tableDA.Add( tempDA );
} // end loop through filenames
// and save the dataset
DBDataSet1.AcceptChanges();
}
</code>
A soft glow comes from the pit in the darkness.
The clicking noise become faster - and louder.
A wind begins to stir up from the pit, as the
creature flexes it's wings, preparing for flight.
You stare into the pit, and hear a voice say
in your mind, "If you survive the encounter,
declare it to the world."
The Code Demon Rises.
|
|
|
|
|
I have a problem like this:
when i restore my database backup file (ex: the file name is warehouse) , i recognized that a transaction log file is unusual (the file name after restore is: warehouse_log.LDF) because a size of this file is very big: 2.3 gigabytes.
Before i backup my database, this file (warehouse_log.LDF) is only 1 megabytes.
when i backup my database, the backup file is only 4 megabytes.
Who know what happend, please help me how to make this file to normal. Thankyou very much. Please contact with me at email:
aeriscute@yahoo.com
Hanh Aeriscute
|
|
|
|
|
your query is not clear to me. however ...
Transaction log file size can be reduced by
1. take backup of transaction log
2. shrink the transaction log
if required repeat both steps
Regards
KP
|
|
|
|
|
Hi all,
I have a target to write activeX script for a SQL-Server DTS package, so that, depending upon the retrieved column value it should retrieve another value(Id) from another table and Map that value with a destination column. If anybody is having idea to write this script pls. advice me or give me some code snippet if possible.
And
But that destination column doesnt exist, can I create it and map it or do I need to map it with the existing column only. That destination column is coming from a table, so pls. advice me if u have idea of it.
I would be thank full.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Something like this
// VBs Script on DTS
if DTSSource("Col008") <> "?" THEN
DTSDestination("ldh_date_frz") = DTSSource("Col008")
//
|
|
|
|
|
Thank you and still its not working fine, giving error as
Error code: 0
Error Source=Microsoft VBScript runtime error
Error Description: Type mismatch: 'DTSSource'
Error on Line 1.
Do I need to give any special connection to run activeX scripting. Please give me some Idea. Thank you.
Regards,
Aleem.
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
hi guys anyone know whats the date function is in sql statment?
example of it in access:
> Date()
i want it in sql pls any one know?
|
|
|
|
|
getdate() ?
There is nothing in SQL, I don't think, I think that's a SQL Server specific call.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
so is there anyway for me to set the today date to an dat feild automatically during insertion of the record?
|
|
|
|
|
Yes set the default value to getdate()
|
|
|
|
|