|
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()
|
|
|
|
|
getdate() function returns current date & time
ex.
SELECT getdate()
returns current system date & time
INSERT INTO tblA (col1) VALUES (getdate()
inserts current system date & time into "col1" of "tblA"
Regards
KP
|
|
|
|
|
|
Hi
I'm using this code:
cmd = new MySqlCommand("SELECT * FROM @table ORDER BY size LIMIT 20", conn);
cmd.Parameters.Add("@table", table);
reader = cmd.ExecuteReader();
However, @table is not replaced with the variable table, and thus I get an exception that my SQL query is not correct. Can someone tell me what I'm doing wrong?
regards
modified 12-Sep-18 21:01pm.
|
|
|
|
|
|
Thanks 
Do I really need stored procedures for this task? I actually just wanted to avoid SQL injections, something like mysql_escape_string(table) does in PHP.
regards
modified 12-Sep-18 21:01pm.
|
|
|
|
|
Hope this is the right place - is anyone using SSIS here?
Let's say I have an input source which gives me a column of data that I'm interested in. Before inserting this data into my target, I need a couple of additional columns added to this data. One column is a sequence, 1, 2, 3, etc.. and the second column is a literal string. I'm pretty new to SSIS and I'm having some trouble getting off the ground.
Can anyone point me in the right direction?
Thanks!
Marcus
Marcus Spitzmiller
You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
|
|
|
|
|
Use a Derived Column task to add new columns to the data.
If I work out a simple method to generate a sequence, I'll let you know.
|
|
|
|
|
Thanks for the help!
Marcus Spitzmiller
You will never hear me say, "I listen to pretty much everything." That is for people who don't care what goes in their ears.
|
|
|
|
|
hi
i wanted to perform a search for records based on the date range specified by the user..
the input method is in mm/dd/yyyy in the webform
before going to the web from i just queried the table using the query builder and it constructed a query
SELECT patient.*
FROM patient
WHERE (Dt BETWEEN CONVERT(DATETIME, '2006-08-09 00:00:00', 102) AND CONVERT(DATETIME, '2006-09-09 00:00:00', 102))
now how do i use the same query to the webform... also how will i display the results in a gridview control..
also.. shud i create an stored procedure, by which i can make this possible
thx
Kunal
|
|
|
|
|
A stored proc is always a better idea. You can just pass a DateTime as a param to a stored proc
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 )
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
