Click here to Skip to main content
15,897,968 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi everyone...once again..im seeking for help in this forum...

this is my code..

string[] content = System.IO.File.ReadAllLines(e.FullPath);
                       foreach (string line in content)
                       {
                           string[] arr;
                           arr = line.Split(','); //splitting the line which was read by the stream reader object
                           DataRow row = table.NewRow();
                           row["L_DateTime"] = DateTime.Parse(arr[0].Substring(0, 4) + "-" + arr[0].Substring(4, 2) + "-" + arr[0].Substring(6));
                           row["L_Direction"] = arr[1];
                           row["L_CardID"] = arr[2];
                           row["L_GateID"] = Convert.ToInt32(arr[3]);
                           table.Rows.Add(row);
                           i2++;
                       }

                       //Connect to DB
                       string conString = ConfigurationManager.ConnectionStrings["Data Source=HEIZ-PC\\SQLEXPRESSR2;Initial Catalog=attendent;User ID=sa;Password=gemini"].ConnectionString;
                       using (SqlConnection con = new SqlConnection(conString))
                       {
                           con.Open();
                           try
                           {
                               //Execute the command to make a temp table
                               SqlCommand cmd = new SqlCommand(tmpTable, con);
                               cmd.ExecuteNonQuery();

                               //BulkCopy the data in the DataTable to the temp table
                               using (SqlBulkCopy bulk = new SqlBulkCopy(con))
                               {
                                   bulk.DestinationTableName = "#templogDetail";
                                   bulk.WriteToServer(table);
                               }

                               //Now use the merge command to upsert from the temp table to the production table
                               string mergeSql = "merge into logDetail as Target using #templogDetail as Source on" +
                                               "Target.L_DateTime=Source.L_DateTime, Target.L_Direction=Source.L_Direction," +
                                                   "Target.L_CardID=Source.L_CardID, when matched then update set Target.L_GateID=Source.L_GateID," +
                                                       "when not matched then insert (L_DateTime,L_Direction,L_CardID,L_GateID)" +
                                                           "values (Source.L_DateTime,Source.L_Direction,Source.L_CardID,Source.L_GateID);";

                               cmd.CommandText = mergeSql;
                               cmd.ExecuteNonQuery();

                               //Clean up the temp table
                               cmd.CommandText = "drop table #templogDetail";
                               cmd.ExecuteNonQuery();
                           }


my debugger state that the error comes from this line..
string conString = ConfigurationManager.ConnectionStrings["Data Source=HEIZ-PC\\SQLEXPRESSR2;Initial Catalog=attendent;User ID=sa;Password=gemini"].ConnectionString;


what is wrong with my connection string people?? real headache,...
Posted

It looks there is NOT an entry "Data Source..." in your ConnectionStrings collection (is there possibly a typo in the string itself)?
BTW Do you really think it is a good idea to post db passwords on a public forum?
 
Share this answer
 
v2
Comments
beh7606 23-Dec-11 14:06pm    
Thanks pallini...i've learned a lesson tonight..haaha..im a totaly dummy!
CPallini 23-Dec-11 14:48pm    
You are welcome :-)
Try this:
add below line in your configuration file:

XML
<connectionStrings>
    <add name="dbconn" connectionString="Data Source=HEIZ-PC\\SQLEXPRESSR2;Initial Catalog=attendent;User ID=sa;Password=gemini"; providerName="System.Data.SqlClient"/>
  </connectionStrings>



and then write this to access this connectionstring in your code:

C#
string conString=ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;


hope it helps :)
 
Share this answer
 
v2
Comments
beh7606 23-Dec-11 14:06pm    
thanks for your advise (and everyone here)..but after i did exactly as your suggestion it appear a lot other problem comes...

e.g : Instance failure at system.data.sqlclient.TdsParser.Connect(Server info..blablabla...)

what it said actually??
Hi
ConfigurationManager.ConnectionStrings() method will read connection string from the app.config file. you cannot directly pass connection string as parameter in the ConfigurationManager.ConnectionStrings() method. First you have to add connection string in app.config file and then need to use that connectionstring name in ConfigurationManager.ConnectionStrings() method parameter

App.Config :

<configuration>
  
  <connectionstrings>
	  <add name="MyConnectionString" providername="System.Data.SqlClient">
            connectionString="Data Source=localhost;Initial Catalog=MyDB; Integrated Security=true" />
  </add></connectionstrings>
  
</configuration>


Now Use this connectionstring in code as

string conString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;


Hope this will help :)

Thanks
Vinod
 
Share this answer
 
Comments
beh7606 23-Dec-11 14:10pm    
thanks...but im still stuck at other error...like the one i posted above...instance failure in system.data.sqlclient..blabla..how come?
use only this and make sure that connection string is exactly correct.
C#
string conString = "Data Source=HEIZ-PC\\SQLEXPRESSR2;Initial Catalog=attendent;User ID=sa;Password=gemini"
 
Share this answer
 
Comments
beh7606 23-Dec-11 14:20pm    
thanks...but it said that "cannot open attendent requested by the login. The login fail"

"Login fail for user sa at system.data.sqlclient"..and blabla...many2 more..guide pls...
perhaps you don't have access to HEIZ-PC\\SQLEXPRESSR2 machine.check this blog http://msdn.microsoft.com/en-us/library/ms178371.aspx[^]
--NDK
 
Share this answer
 
Comments
beh7606 23-Dec-11 22:52pm    
i think it' not...bcos i used exactly the same connection string for my other projects..and its work...
hi everybody!

sorry for taking your time...im really sorry..

i just figured it out...the error simply just because..typo error in connection string... it should be attandent not attendent..

thanks everyone...a real lesson from you guys!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900