hi, i need help on SQL, thanks

I'm doing a shopping cart using SQL Express and Visual Studio Web Developer on C#, ASP.NET

I recieved error when adding a order:

The variable name '@.oid' has already been declared. Variable names must be unique within a query batch or stored procedure.

The codes are:
comm = new SqlCommand("SELECT IDENT_CURRENT('Orders') as NewOrderID ");

comm.Connection = conn;
comm.Transaction = myTrans;

OrderID = Convert.ToInt32(comm.ExecuteScalar());

foreach (CartItem i in o.ItemList)
{
comm.CommandText = "INSERT INTO OrderDetail(OrderID, ProductID,Quantity, UnitPrice)VALUES (@.oid, @.pid, @.qty, @.price)";

comm.Parameters.AddWithValue("@.oid", OrderID);
comm.Parameters.AddWithValue("@.pid", i.ProductID);
comm.Parameters.AddWithValue("@.qty", i.Quantity);
comm.Parameters.AddWithValue("@.price", i.UnitPrice);
comm.Connection = conn;
comm.Transaction = myTrans;
comm.ExecuteNonQuery();
}

It seems that i can't add records into database with multiple loop.

Thanks in advance.

Well you want to only call once the insert for multiple items, that will optimize the code a little bit more.

Use TableAdapters, On the project right click and select Add New Item, select a DataSet and create a method to add multiple items!

|||

Try to clear parameters after each insert.

Add this to the end of your code block:

...........

comm.ExecuteNonQuery();

comm.Parameters.Clear();

}

|||

Thanksalbertpascual,

I'm not sure about using a TableAdapter, but does declaring the parameters outside the loop works?

I tried adding a dataset but with my limited 1month knowledge, I don't know how to complete the wizard or codes for the dataset.

if changing my existing codes works, it will be great.

thanks again,

|||

Hey, thanks alot Limno!!

but adding this sweet and simple "comm.Parameters.Clear();" it works..

thanks!!

HELP: MFC ODBC SQL Server problems

I got a problem where in SQL Server a table has a column of type REAL & size
(precision) 4. It's meant to store double types for C++ variable values.
In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
translates to wrong numbers. Such as:
1.1E+10 => 10000000512
1.00001E+10 => 10000001024
1.01E+10 => 10099999744
1.0000001E+10 => 10000100352
Strange huh? Has anybody experienced this before? Does anyone know what is
wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.6
to SP2. Still no success.You should not use "Real" to handle a big number like "1E+10" since real
data type does not have enough precisions to do the job. If you change
the data type to "Float", you will have better chance to get the number
right. Or if you want to store "Exact" numbers, you should use "decimal"
as your data type.
Charles Zhang
http://www.speedydb.com
SpeedyDB ADO.NET is the fastest, most secure, and most flexible ADO.NET
Provider over Wide Area Netword (WAN).
Andrew Wan wrote:
> I got a problem where in SQL Server a table has a column of type REAL & si
ze
> (precision) 4. It's meant to store double types for C++ variable values.
> In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
> MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
> translates to wrong numbers. Such as:
> 1.1E+10 => 10000000512
> 1.00001E+10 => 10000001024
> 1.01E+10 => 10099999744
> 1.0000001E+10 => 10000100352
> Strange huh? Has anybody experienced this before? Does anyone know what is
> wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.
6
> to SP2. Still no success.
>
>

HELP: How do I pass variables from Control Flow to Data Flow

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

smcneese wrote:

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

You can use the Derived Column Component to put variable values into the pipeline.

-Jamie

|||Use a multicast and an OLE DB Command to perform the update on the original table.|||

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

|||

smcneese wrote:

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

You can't use Derived Column Component as a source adapter so in this case you'll have to use a script component.

I would question as to whether this is really what you want to do though. Executing a data-flow just to process one row of data with one column in it seems like the proverbial sledgehammer to crack a nut. What are you trying to achieve exactly?

-Jamie

|||

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

|||

smcneese wrote:

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

I don't really see a problem updating the table while it is being written to. Modern RDBMSs are supposed to be handle multiple concurrent connections to the same resource - that's kinda the point.

However, you say that when a record is written to the table in SQL Server 2005 you need to write it to the Access application. That is inherently a real-time operation and SSIS is a tool for batch operations. Given that 10000foot view of the problem I don't think SSIS is what you want. If you do NOT need to write to the access application at the same time as the record arrives then this becomes more of a batch operation and SSIS will be more suitable.

-Jamie

|||

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

|||

smcneese wrote:

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

Yes, that is the way to do it. And jwelch replied on this thread telling you exactly how.

-Jamie

|||

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

|||

smcneese wrote:

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

Good news. A happy customer for a change

Help: about charindex function doesnt work with variable

Hello to all,

I hope that somebody can help me.

I have written a sql query to search Partner. I have a wtcomValidRelationships Table. There are two Fields(IDMember(type: Int) andRelationshipIDs(type: varchar(1000))in this table.

Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088.

declare @.IDMint;

declare @.IDOchar(100);

set @.IDM= 3418;

set @.IDO='4088';

select B.IDMember

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex(cast(B.IDMemberaschar(100)),A.RelationshipIDS)> 0

andcharindex(@.IDO,B.RelationshipIDs)> 0

Using this query i get nothing.

I try to use constant incharindex and i get result.

declare @.IDMint;

declare @.IDOchar(100);

set @.IDM= 3418;

set @.IDO='4088';

select B.IDMember

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMandcharindex('3422',A.RelationshipIDS)> 0

andcharindex('4088',B.RelationshipIDs)> 0

So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?

Thanks

Best Regards

Pinsha

Hi Shasha,

See the earlier post and question you have asked about the same issue and use the PATINDEX i have updated the code. Just see the earlier question reply.