Blue_boy, Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.

Hi, I would get a random record using the following:

SELECT TOP 5 (*) from tbl_organisations AS randomOrgs

However my issue is with how i would then catch these 5 records and insert them into the other table as a single record with the SP inputs.

Hi, I need to submit data from a form to an SQL2005 database via a stored procedure. The difficult part is that i also need to get 5 random records from a secondary table and insert these as part of the inserted record in table 1. My structure is akin to this:

Tbl_Campaigns (table to be inserted to)

Key | field_1 | field_2 | field_3 | field_4 | field_5 etc

Tbl_Organisations (table to get the 5 random records from)

Key | field_1

I need to get 5 unique / random records from 'tbl_Organisations', catch them and insert them into a single record along with the data input to the stored procedure. I understand arrays arent an option in SQL2005(?). So how do i catch these records and then insert them as a single record along with the SP Inputs? Any help would be greatly appreciated as i am trying my best to get to grips with the complexities of SQL Thanks.

modified on Tuesday, July 7, 2009 6:04 AM

Hi, I'm in need of a stored procedure which takes 5 input values and then stores them in 'Table A'. This is simple enough. However i also need to get data from a 2nd table 'Table B', return 5 random records(1 column only) and then input these records into 'Table A' along with the other data mentioned above. (Making a single record / row of 10 bits of information) Hopefully this makes sense. I know how to create the select statement for the above, but how do i then capture this data within the stored procedure and use it in the insert statement? Thanks! :doh:

Hi still someway off what im asking help with but thanks anyway.

Himanshu!!!!! Yet again you are a genius and an amazingly helpful guy. (i just hope this works now :laugh: :laugh: ) Thanks!

Vuyiswa, Thanks but unfortunately i need to do this in the codebehind before uploading the DataTable to a SQL2005 server since i need to do some columnmapping via SQLBulkCopy. So i really need a solution which is able to work in the c# codebehind and programmatically perform the changes to the DataTable. Thanks.

That doesnt solve the problem unfortunately. As explained i need to take the content of the cell in row1 and make that the column name. This is because i dont know the content of row1 nor do i know how many columns there are, since the info is from an uploaded source. Likewise i dont have a gridview, i have a datatable as the work is being done in the codebehind. Thanks anyway.

Hi, I have a populated DataTable of vaying columns and rows. I need to somehow take the data of the 1st row and make this the column name of the column in which it is found eg; Column1 | Column2 | Column3 name | date | email mike | 26 June | me@you.com dave | 05 Apr | him@wibble.co.uk should read as: name | date | email mike | 26 June | me@you.com dave | 05 Apr | him@wibble.co.uk Any suggestions? Many Thanks. :^)

I managed to come up with a very similar solution to this but a little simpler since i only need to add a column and populate it, rather than adding new rows. Ill post the details later. Thanks again for all the help everyone!

Himanshu, I see this creates a data table, which isnt what i really wanted to do. Thanks for the suggestion, but how do i then extend this code to perform the SQLBulkCopy over to the server???? Thanks.

the force strong in you is

Hi, I am using the following code to take data from a csv file then adding it to an SQL database using SQLBulkCopy. I need to add an additional column of data to each record transfered. This extra column is present in the SQL2005 database but not the CSV file, and relates to the users unique ID. At present i have this:

            using (OleDbCommand dcmd = new OleDbCommand("SELECT \* FROM " + strUPloadFile + "", dconn))
            {
                try
                {
                    dconn.Open();

                    using (OleDbDataReader dreader = dcmd.ExecuteReader())
                    {

                        // Bulk Copy to SQL Server
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(WebConfigurationManager.ConnectionStrings\["######"\].ConnectionString))
                        {
                            //bulkCopy.ColumnMappings.Add("adds\_ABMId", 45);
                            bulkCopy.ColumnMappings.Add("adds\_Email", "E-mail Address");
                            bulkCopy.ColumnMappings.Add("adds\_RecipientForename", "First Name");
                            bulkCopy.ColumnMappings.Add("adds\_RecipientSurname", "Last Name");

                            bulkCopy.DestinationTableName = "tbl\_AddressBookAddresses";

                            // Set the BatchSize.
                            bulkCopy.BatchSize = 500;
                            try
                            {//perform copy
                                bulkCopy.WriteToServer(dreader);
                            }
                            catch (Exception ex)
                            {//error
                                throw(ex);
                            }
                        }

                    }
                }
                finally
                {
                    dconn.Close();
                }
            }
        }

I could really do with some help on this. Thanks guys! :confused::confused::confused::confused::confused:

modified on Tuesday, June 16, 2009 6:49 AM

Hi, I need to import data from a CSV file to an SQL table i have used tohe following in the past to import an excel file but wondered if/how i can adapt it to import from a CSV. Also, it would be a great help if you could show me how to convert it to use column mapping.

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\qs_upload.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

    // Create Connection to Excel Workbook
    using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
    {
        OleDbCommand command = new OleDbCommand("Select \* FROM \[quality\_standard$\]", connection);
        //OleDbCommand command = new OleDbCommand("Select qs\_Id, qs\_Organisation, Convert(varchar,qs\_Start,111), qs\_End FROM \[quality\_standard$\]", connection);

        connection.Open();

        // Create DbDataReader to Data Worksheet
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = "Data Source=wibblewobble.com;Initial Catalog=comfound;Persist Security Info=True;User ID=##########;Password=##########";

            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "tbl\_QualityStandard";
                bulkCopy.WriteToServer(dr);
            }
        }
    }

Thanks.

THANKS ROB!!!!!!! Its actually quite easy when you know how, isnt it. :laugh:

Thats how it was suggested to me by someone else on here. That part of it works. Is it more efficient to use SET rather than the SELECT method i have used? Thanks.

SO i figured out that i need to place the 'BEGIN' & 'END' blocks within the IF statement. Any suggestions on optimising the procedure?

Rob, Ive started to develop things a bit more now towards where i need to be going. However im now having trouble with something a bit more complex, please take a look at the new thread: <a href="http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx"> http://www.codeproject.com/Messages/3078712/Stored-Procedure-with-complex-ish-IF-ELSE.aspx [^] Thanks!

Hi, Thanks to a massively helpful ROb ive manged to learn a little about conditional statements in Stored Procedures. However i have now created the following SP and it gives an error when compiling. Incorrect syntax near the keyword 'ELSE'

CREATE PROCEDURE web.createDefaultAddressBook
(
@tmp_custID bigint,
@tmp_Description varchar(500),
@tmp_NoRecipients bigint,
@tmp_Email varhar(100),
@tmp_Forename varchar(100),
@tmp_Surname varchar(100)
)
AS
BEGIN
declare @key bigint
declare @newSignUpID bigint
--determine id of record if exists
SELECT @key = addMaster_Key FROM tbl_AddressBookMaster WHERE addMaster_UserId = @tmp_custID AND addMaster_Title = 'Default'

   IF @key IS null
   --address book doesnt exist just so create default
   
   INSERT INTO tbl\_AddressBookMaster (\[addMaster\_UserId\], \[addMaster\_Title\], \[addMaster\_Description\], \[addMAster\_NoRecipients\])
   VALUES (@tmp\_custID, 'Default', @tmp\_Description, 0)
   SELECT @newSignUpID = SCOPE\_IDENTITY()
   
  --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@newSignUpID, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

   ELSE
   --default already exists
   
   --add addresses to second table
   INSERT INTO tbl\_AddressBookAddresses (\[adds\_ABMId\], \[adds\_Email\], \[adds\_RecipientForename\], \[adds\_RecipientSurname\])
   VALUES (@key, @tmp\_Email, @tmp\_Forename, @tmp\_Surname)

END

What am i doing wrong? Is there a more efficient way to do this considering i will be using it in code which will loop through a huge array and pass new records into the SP? Effective but simple is probably best at this stage though. Thanks in advance.

@@rowcount was purely to show that the 'Else' condition was being fired. Thanks for all the help, really appreciate it.