problem using a function for several tablenames
-
ok.. i have a function and it looks like this.. public static void test(ListBox lst,string tablename) { string myConn = "Server=(local);Integrated security=SSPI;database=halkdanis"; string str = "SELECT * FROM '"+tablename+"'"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); DataSet dt = new DataSet(); DataAdapter.Fill(dt,tablename); // here it says error DataTable dtb = dt.Tables[0]; foreach(DataRow dtr in dtb.Rows) { lst.Items.Add(dtr["username"]); } } as u can see, i want to get data from mssql, and put the usernames in a listbox and i want to use this function several times depends on tablenames but it gives error at: DataAdapter.Fill(dt,tablename) ; i guess its coz of `"` char or dunno what is it.. any help would be great.. thx. good coding.. god is a coder..
-
ok.. i have a function and it looks like this.. public static void test(ListBox lst,string tablename) { string myConn = "Server=(local);Integrated security=SSPI;database=halkdanis"; string str = "SELECT * FROM '"+tablename+"'"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); DataSet dt = new DataSet(); DataAdapter.Fill(dt,tablename); // here it says error DataTable dtb = dt.Tables[0]; foreach(DataRow dtr in dtb.Rows) { lst.Items.Add(dtr["username"]); } } as u can see, i want to get data from mssql, and put the usernames in a listbox and i want to use this function several times depends on tablenames but it gives error at: DataAdapter.Fill(dt,tablename) ; i guess its coz of `"` char or dunno what is it.. any help would be great.. thx. good coding.. god is a coder..
Don't enclose the table name in single quotes. Better yet, use parameters:
string str = "Select * from @table"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); dataAdapter.SelectCommand.Parameters.Add("@table", tablename); DataSet dt = new DataSet(); dataAdapter.SelectCommand.Connection.Open();//open the connection DataAdapter.Fill(dt,tablename);//fill the adapter ...Since you are just copying one colum value to a listbox, theis would be much more effieciently done with an SqlDataReader, and the select commnd should only ask for the one column:string str = "Select [username] from @table"; SqlConnection myConn + new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); try { while(reader.Read()) { lst.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection }This is much faster, and consumes much less working memory. It also transfers much less data 'over the wire' if the server is on a different box. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -
Don't enclose the table name in single quotes. Better yet, use parameters:
string str = "Select * from @table"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); dataAdapter.SelectCommand.Parameters.Add("@table", tablename); DataSet dt = new DataSet(); dataAdapter.SelectCommand.Connection.Open();//open the connection DataAdapter.Fill(dt,tablename);//fill the adapter ...Since you are just copying one colum value to a listbox, theis would be much more effieciently done with an SqlDataReader, and the select commnd should only ask for the one column:string str = "Select [username] from @table"; SqlConnection myConn + new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); try { while(reader.Read()) { lst.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection }This is much faster, and consumes much less working memory. It also transfers much less data 'over the wire' if the server is on a different box. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burkethis is still not working... string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); // here it sais error try { while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection } and i also tried myStyle with parameters.. string str = "Select * from @table"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); dataAdapter.SelectCommand.Parameters.Add("@table", tablename); gives the same error DataAdapter.Fill(dt,tablename); // error here..
-
this is still not working... string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); // here it sais error try { while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection } and i also tried myStyle with parameters.. string str = "Select * from @table"; SqlDataAdapter dataAdapter = new SqlDataAdapter(str,myConn); dataAdapter.SelectCommand.Parameters.Add("@table", tablename); gives the same error DataAdapter.Fill(dt,tablename); // error here..
What is the error? What are you passing in for tablename? Does the table exist? Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -- modified at 22:45 Sunday 30th October, 2005
-
What is the error? What are you passing in for tablename? Does the table exist? Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -- modified at 22:45 Sunday 30th October, 2005
public static void test(ListBox lst, string tablename) { string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); // error points here try { while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection } } An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. and i coulnt take take SqlDataReader reader = cmd.ExecuteReader(); in a try - catch block coz it gives `reader` couldnt find errors .. my usage of the function is.. defaultCheck.test(empList,"employeeinfo"); // empList = listBox , "employeeinfo" = tablename thx.
-
public static void test(ListBox lst, string tablename) { string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); // error points here try { while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } finally { reader.Close();//close reader myConn.Close();//close connection } } An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. and i coulnt take take SqlDataReader reader = cmd.ExecuteReader(); in a try - catch block coz it gives `reader` couldnt find errors .. my usage of the function is.. defaultCheck.test(empList,"employeeinfo"); // empList = listBox , "employeeinfo" = tablename thx.
Are you sure your Sql Server is running? "system error' sounds rather bad, like something fundamental is wrong. Can you open the table in Enterprise Manager? I don't understnd your comment about not being able to move the executereader() call into the try block --- are you saying that
public static void test(ListBox lst, string tablename) { string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); try { SqlDataReader reader = cmd.ExecuteReader(); // error points here while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } catch(SqlException e) { System.Diagnostics.Trace.WriteLine(e.message); } finally { reader.Close();//close reader myConn.Close();//close connection } }Doesn't throw an error? Also, I'm more accustomed to seeing "Initial Catalog=halkdanis;" in an Sql Server connection string, rather than "database=" "database" might work, but then again it might not.. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -
Are you sure your Sql Server is running? "system error' sounds rather bad, like something fundamental is wrong. Can you open the table in Enterprise Manager? I don't understnd your comment about not being able to move the executereader() call into the try block --- are you saying that
public static void test(ListBox lst, string tablename) { string str = "Select [username] from @table"; SqlConnection myConn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); SqlCommand cmd = new SqlCommand(str,myConn); cmd.Parameters.Add("@table",tablename); myConn.Open(); try { SqlDataReader reader = cmd.ExecuteReader(); // error points here while(reader.Read()) { lst.Items.Add(reader.GetString(0)); //since we only asked for one colume, just get string from col 0 } } catch(SqlException e) { System.Diagnostics.Trace.WriteLine(e.message); } finally { reader.Close();//close reader myConn.Close();//close connection } }Doesn't throw an error? Also, I'm more accustomed to seeing "Initial Catalog=halkdanis;" in an Sql Server connection string, rather than "database=" "database" might work, but then again it might not.. Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burkemy mssql server and everything seems fine, this is just a function of the system, i can add, delete, update records on my program right now and there is no problem, what i wanted to do is, i'm using this code 5 times in the program but each time i have to specify the tablename , so i had to use 5 functions only the tablenames are different. and instead of doing like that i wanted to use only one function and take the tablename as a string so i could use it.. try { SqlDataReader reader = cmd.ExecuteReader(); // error points here while(reader.Read()) ... when i do like this , it gives error like where the heck is the reader but SqlDataReader reader = cmd.ExecuteReader(); // error points here try { while(reader.Read()) ... no errors but when i wanted to use the function it gives sql error and points SqlDataReader reader = cmd.ExecuteReader(); thanks for your help but i guess i have to use 5 function grrr :)
-
my mssql server and everything seems fine, this is just a function of the system, i can add, delete, update records on my program right now and there is no problem, what i wanted to do is, i'm using this code 5 times in the program but each time i have to specify the tablename , so i had to use 5 functions only the tablenames are different. and instead of doing like that i wanted to use only one function and take the tablename as a string so i could use it.. try { SqlDataReader reader = cmd.ExecuteReader(); // error points here while(reader.Read()) ... when i do like this , it gives error like where the heck is the reader but SqlDataReader reader = cmd.ExecuteReader(); // error points here try { while(reader.Read()) ... no errors but when i wanted to use the function it gives sql error and points SqlDataReader reader = cmd.ExecuteReader(); thanks for your help but i guess i have to use 5 function grrr :)
Try it more youre original way. Instaed of a parameter, build up the SQL string like this: string str = "select [username] from [" + tablename + "]"; leave the rest of the code the same (just remove the add parameters part). the square brackets around username are important, since "username" is a keyword. Your original version had single quotes around tablename, which was definitely a problem. The square brackets should work. To fix the compile error with the reader inside the try block,
SqlDatareader reader = null; try { reader = cmd.ExeuteReader(); ...Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -- modified at 23:22 Sunday 30th October, 2005 -
Try it more youre original way. Instaed of a parameter, build up the SQL string like this: string str = "select [username] from [" + tablename + "]"; leave the rest of the code the same (just remove the add parameters part). the square brackets around username are important, since "username" is a keyword. Your original version had single quotes around tablename, which was definitely a problem. The square brackets should work. To fix the compile error with the reader inside the try block,
SqlDatareader reader = null; try { reader = cmd.ExeuteReader(); ...Absolute faith corrupts as absolutely as absolute power Eric Hoffer All that is necessary for the triumph of evil is that good men do nothing. Edmund Burke -- modified at 23:22 Sunday 30th October, 2005awesome !!! thank you so much.. it works ! ;)