SQL example of how to modify datavalue and add a leading zero.

First view the record your seeking to change. In this case I am looking for number store with 6 digits.

Select * from Coursework_Validation
where len(Outside_Institution_ID) = 6

Since the leading zero was chopped off on import (my mistake)...I am adding it back.

Update Coursework_Validation set Outside_Institution_ID = '0' + Outside_Institution_ID
where len(Outside_Institution_ID) = 6

Example in sql of how to list out duplicates.

SELECT        Institution_Name, Institution_City, Institution_State, Institution_Country, COUNT(*) AS TotalCount
FROM            Institution_Lookup
GROUP BY Institution_Name, Institution_City, Institution_State, Institution_Country
HAVING        (COUNT(*) > 1)
ORDER BY Institution_Name

Example of routine in sql that if a column(s) value(s) exists and then return the key.

IF EXISTS (SELECT Coursework_ID From Coursework_Validation Where Outside_Institution_ID = @Outside_Institution_ID and Outside_CourseName=@Outside_CourseName and Outside_Subject=@Outside_Subject and Outside_CourseNum=@Outside_CourseNum)
BEGIN
SET @id= (SELECT Coursework_ID From Coursework_Validation Where Outside_Institution_ID = @Outside_Institution_ID and Outside_CourseName=@Outside_CourseName and Outside_Subject=@Outside_Subject and Outside_CourseNum=@Outside_CourseNum)
END
ELSE
BEGIN
SET @id= -9;
END
return @id

Example of a sql routine that counts and return the number of times an id appears on sql

DECLARE @AA INT
SET @AA=(SELECT COUNT(*) as TheCount From Coursework_Validation Where Outside_Institution_ID = @Outside_Institution_ID )
return @AA

How to read a text file, with asp.net and then execute a string comapre out of SQl database

private void codecompare(string state_in)
        {
            string fileName = HttpContext.Current.Request.MapPath(HttpContext.Current.Request.ApplicationPath) + @"\institutioncodes.txt";

            if (!string.IsNullOrEmpty(fileName))
            {
                string lineOfText = "";
                var filestream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
                var file = new System.IO.StreamReader(filestream, System.Text.Encoding.UTF8, true, 128);

                string institution_name = "";
                string institution_city = "";
                string institution_state = "";

                string fice = "";
                string ipedsunit = "";
                string actcode = "";

                string colleage_id = "";
                string name = "";
                string city = "";

                int i = 0;
                int count = 0;

                string database = GetRecord(state_in);
                string[] sqlrows = database.Split('~');

                while ((lineOfText = file.ReadLine()) != null) //Looping each line of file
                {
                    if (i != 0)
                    {
                        string[] datacolumn = lineOfText.Split('|');

                        institution_name = datacolumn[0];
                        institution_city = datacolumn[3];
                        institution_state = datacolumn[4];

                        fice = datacolumn[7];
                        ipedsunit = datacolumn[8];
                        actcode = datacolumn[9];
                      
                        if (institution_state == state_in)
                        {
                           
                            //if (institution_city != "")
                            //{

                                if (database != "")
                                {
                                    
                                    foreach (string row in sqlrows)
                                    {

                                        string[] school_row = row.Split('|');

                                        colleage_id = school_row[0]; //Colleague ID
                                        name = school_row[1].ToUpper(); //School Name
                                        city = school_row[2].ToUpper(); //City
                                                                               //string state = school_row[4]; //State

                                       // if (city == institution_city)
                                        //{
                                            if (name == institution_name)
                                            {
                                                //Here's what to do if we have a match
                                                Response.Write(colleage_id);
                                                Response.Write("|");
                                                Response.Write(name);
                                                Response.Write("|");
                                                Response.Write(city);
                                                Response.Write("|");
                                                Response.Write(institution_state);
                                                Response.Write("|");
                                                Response.Write(fice);
                                                Response.Write("|");
                                                Response.Write(ipedsunit);
                                                Response.Write("|");
                                                Response.Write(actcode);
                                                Response.Write("|");
                                                Response.Write("<br />");
                                              
                                                count++;

                                            }
                                        //} //bottom of if city
                                    } //end foreach
                                }
                           // }//Blank City
                        }

                    } //end if

                    i++;

                    institution_name = "";
                    institution_city = "";
                    institution_state = "";

                    fice = "";
                    ipedsunit = "";
                    actcode = "";

                    colleage_id = "";
                    name = "";
                    city = "";

                } //end while

                file.Close();
                string results = "Of the total " + i.ToString() + " Records, There were a total of " + count.ToString() + " matches found.";
                Response.Write(results);
            }
        } //end function



        public string GetRecord(string institution_state) //Getting record info from database
        {

            string strSQL = "";
            string output = "";

            string ConnectString = ConfigurationManager.ConnectionStrings["ICTVIEWS"].ConnectionString;
            SqlConnection cn = new SqlConnection(ConnectString);
            cn.Open();

            strSQL = "SELECT [Colleague_Institution_ID], [Institution_Name], [Institution_City], [Institution_State] FROM Institution_Lookup ";
            strSQL += "WHERE Institution_State=@Institution_State";

            SqlCommand cmd = new SqlCommand(strSQL, cn);

            SqlParameter Institution_State = new SqlParameter();
            Institution_State.ParameterName = "@Institution_State";
            Institution_State.Value = institution_state;
            cmd.Parameters.Add(Institution_State);

            SqlDataReader rdr = cmd.ExecuteReader();

            if (rdr.HasRows == true)
            {
                int k = 0;
                while (rdr.Read())
                {
                    if (k != 0)
                    {
                        output += "~";
                    }
                    output += rdr["Colleague_Institution_ID"].ToString();
                    output += "|";
                    output += rdr["Institution_Name"].ToString();
                    output += "|";
                    output += rdr["Institution_City"].ToString();
                    //output += "|";
                    //output += rdr["Institution_State"].ToString();
                    k++;

                }
                rdr.Close();
                cn.Close();
            }
            else
            {
                rdr.Close();
                cn.Close();
            } //rdr.HasRows

            return output;

        } //GetRecord

    }
}

How to search delimnted text files with asp.net

Well, I had to look for a way to read in a delimented text file and separate the parts so I could work with the different data elements... here's what i got.


 private void codesearch()
        {
            string fileName = HttpContext.Current.Request.MapPath(HttpContext.Current.Request.ApplicationPath) + @"\rawdata2.txt";

            if (!string.IsNullOrEmpty(fileName))
            {
                string lineOfText = "";
                var filestream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
                var file = new System.IO.StreamReader(filestream, System.Text.Encoding.UTF8, true, 128);
                                         
                int i = 0;

                while ((lineOfText = file.ReadLine()) != null)
                {
                    string[] datacolumn = lineOfText.Split('|');

                    foreach (string data in datacolumn)
                    {
                       

                        i++;
                    }
              
                }

                file.Close();
            }

Example of : In SQL do s Bulk Import from CSV file.

Before you start your import of a CSV file into a SQL database, make sure the data in the CSV file and the Tables Columns are in the same order. Otherwise you'll get some interesting mismatching of information.

The SQL command you need is....

BULK INSERT TableName
FROM 'C:\Filename.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

One possible way to Bulk upload with a catch block.

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT TableName
FROM 'C:\Path\Filename.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    ROWS_PER_BATCH = 10000, 
    TABLOCK
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH