Column insert help

Hi,

I have some values I want put into a table, but the values are from other sources and I dont know how to retrieve them..

I'llshow my code, and the bold is explaining what I want inserted and wherefrom. I'd apprechiate if someone could help me with syntax etc. Thereare 2 about getting value from another table and one about just puttingin straight forward text..:

command.CommandText ="INSERT INTO Messages (sendername,recievername,message,Date,subject)VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

Thanks alot if anyone can help me with those three things..

Jon

Pls help!

|||

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

Could you further explain that.

Thanks

|||

Hi, sorry not very well explained!

The bold writing means:

I have a talbe called 'Transactions' - and for every row in which Itemid = Itemid, the username is retrieved. So this could be 1 username, or many, depending on how many people have the same Itemid in their row. I guess the usernames would have to be separated by commas.

Thanks!

Jon

|||

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";

command.Parameters.Add("@.sendername", System.Web.HttpContext.Current.User.Identity.Name)

command.Parameters.Add("@.recievername",every value of column named Usersname of the Transactions table, WHERE Itemid=Itemid in the gridview on this page);

command.Parameters.Add("@.message",the value of items table - column 'paymentinstructions' WHERE Username=System.Web.HttpContext.Current.User.Identity.Name);

command.Parameters.Add("@.subject",some text: IMPORTANT - Payment Required);

command.Parameters.Add("@.date", DateTime.Now.ToString());

command.ExecuteNonQuery();

You are trying to do sub query's with your parameters. You cant do that. Also, I would think that you would want one record for each value. I would probably create a table with one record for each user, another Table (ex. Transactions with a UserId) with one record for each transactions and another table (ex. Items) with a TransactionId. If not you are going to have a hard time with your data. Then you could just simply do one select. Something like this:
SELECT ui.UserName, t.TransactionId, i.Item FROM UserId ui INNER JOIN Transactions t ON t.UserId = ui.UserID INNER JOIN Items i ON i.TransactionId = t.TransactionId WHERE ui.UserId = @.UserId

That would get you every record based on the userid with out having to do all that funky stuff. You dont really ever want to insert values(espcially with orders) by CSV's. Maintenance nightmare.

Does that make sense?

|||

Hi, I'll have to go through what you said and ask in more detail sorry.

What do you mean by sub query's?

What do you mean by one record for each value? Create a table for each value?

I will explain my scenario maybe it will help:

I have a message system within the site, and the method above is to send a 'bulk message' - that is sending the same message to many people.

My messages table has the columns @.sendername, @. recievername etc.. From this people check the messages through a formview which shoes the messages which reciever name is their username.

This mass message is sent by a user. He presses a button on a formview which contains an Itemid value. - then the button adds all the users in the transactions table which contain the same itemid value.

The second bold bit - this is to insert the value of the paymentinstructions (from 'items' table) of the user (who is sending the message).

Last bit - just universal text that gets inserted every time the same.

Hope this deeper explanation helps things, thanks for helping

Jon

|||

SO i would create a UserMessage(Or something like that) table that has a foreign key UserId in one table, and then create a second table called Messages which has a messageId (foreign key to UserMessage) and the other columns would be SenderName, ReceiverName, Message, PaymentInstructios etc...

Then you can get all messages by sending in one USerId and when you insert you can simply provide one UserId.

Make sense?

|||

Hi, before I try your method, can your hear this out -

I have a page with gridviews that retrieve the data that I want to insert - from 'SqlDataSource1' and 2 etc etc.

Can I just set the parameter to SqlDataSource1? Its on the same page..

I.e.:

command.CommandText = "INSERT INTO Messages (sendername,recievername,message,Date,subject) VALUES (@.sendername,@.recievername,@.message,@.date,@.subject)";
command.Parameters.AddWithValue("@.sendername", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.AddWithValue("@.recievername", SqlDataSource2);
command.Parameters.AddWithValue("@.message", SqlDataSource3);
command.Parameters.AddWithValue("@.subject", TextBox1.Text);
command.Parameters.AddWithValue("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

Thanks

Jon

|||

Yes, you can do it that way, but you are hitting the DB 3 times to retrieve the data you need. That is a sign of your tables not being normalized. I would think that you would want a one to many with Users and MessageId table and then a one to many table with MessageId table and Messages. This would allow you to only hit the db one time and retrieve the data you want.

|||

Hi thanks for your help. I think im nearly there so im going to post each individual error message that I have up, and see what people make of them!

Thanks again,

Jon

Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!

Column Encryption/Decryption in SQL 2000

Hello,
I m using MS SQL 2000 server. I want to encrypt a password column of
my table and retrieve it later by decrypting the same. How can I do so in sq
l
2000 without using any third party tool?
Thanks,
RbanBphYou can't (unless you write your own - technically that would be a 1st
party tool wouldn't it? :P).
*mike hodgson*
http://sqlnerd.blogspot.com
RbanBph wrote:

>Hello,
> I m using MS SQL 2000 server. I want to encrypt a password column o
f
>my table and retrieve it later by decrypting the same. How can I do so in s
ql
>2000 without using any third party tool?
>Thanks,
>RbanBph
>
>|||You can encrypt password column using Extended procedures.
You can search BOL and below URL.
http://www.codeproject.com/database/xp_md5.asp
Hong.
"RbanBph"?? ??? ??:

> Hello,
> I m using MS SQL 2000 server. I want to encrypt a password column
of
> my table and retrieve it later by decrypting the same. How can I do so in
sql
> 2000 without using any third party tool?
> Thanks,
> RbanBph
>