problem with Sqlparameters :(
-
hi guys.. can anyone tell me what might be the error at the following code ? public void getCounterValue(string countername,string tablename,string email) { SqlConnection conn = null; SqlDataReader reader = null; int result = 0; try { conn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); conn.Open(); SqlCommand cmd = new SqlCommand("select @ctr from @table where @mail = @mail", conn); SqlParameter param = new SqlParameter(); param.ParameterName = "@ctr"; param.Value = countername; SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@table"; param2.Value = tablename; SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@mail"; param3.Value = email; cmd.Parameters.Add(param); cmd.Parameters.Add(param2); cmd.Parameters.Add(param3); reader = cmd.ExecuteReader(); while(reader.Read()) { result = Convert.ToInt32(reader[0]); } } catch(Exception x) { MessageBox.Show(x.ToString()); } finally { reader.Close(); conn.Close(); } } it throws the exception @table doesn`t have a value, but it has the value tablename from the method construction.. any thougts ? thx ! good coding !
-
hi guys.. can anyone tell me what might be the error at the following code ? public void getCounterValue(string countername,string tablename,string email) { SqlConnection conn = null; SqlDataReader reader = null; int result = 0; try { conn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis"); conn.Open(); SqlCommand cmd = new SqlCommand("select @ctr from @table where @mail = @mail", conn); SqlParameter param = new SqlParameter(); param.ParameterName = "@ctr"; param.Value = countername; SqlParameter param2 = new SqlParameter(); param2.ParameterName = "@table"; param2.Value = tablename; SqlParameter param3 = new SqlParameter(); param3.ParameterName = "@mail"; param3.Value = email; cmd.Parameters.Add(param); cmd.Parameters.Add(param2); cmd.Parameters.Add(param3); reader = cmd.ExecuteReader(); while(reader.Read()) { result = Convert.ToInt32(reader[0]); } } catch(Exception x) { MessageBox.Show(x.ToString()); } finally { reader.Close(); conn.Close(); } } it throws the exception @table doesn`t have a value, but it has the value tablename from the method construction.. any thougts ? thx ! good coding !
-
How can you pass the table name to a stored procedure?
-
How can you pass the table name to a stored procedure?
TheJudeDude wrote:
How can you pass the table name to a stored procedure?
Previously Colin Angus Mackay wrote:
You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
TheJudeDude wrote:
How can you pass the table name to a stored procedure?
Previously Colin Angus Mackay wrote:
You cannot use a parameter in place of a table name (or any object name, eg. Stored procedure, function, etc.)
ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Gotcha...use command text w/ defined function with the name passed as a parameter. Could you answer ( I sure you can)..will you answer this?? I am storing a variable to smalldatetime in a table. The parameter is defined as: cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString(); When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that? Thanx in advance!
-
Gotcha...use command text w/ defined function with the name passed as a parameter. Could you answer ( I sure you can)..will you answer this?? I am storing a variable to smalldatetime in a table. The parameter is defined as: cmdUpdateTime.Parameters.Add("@TimeIn",SqlDbType.SmallDateTime).Value = DateTime.Now.ToShortTimeString(); When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that? Thanx in advance!
TheJudeDude wrote:
When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Because it must. Actually the order of the elements (month, day, hours, minutes, etc.) are not important, that is just the user interface. Internally it is stored the same way regardless of the culture. The type is designed for storing a date with a time to a resolution of one minute. Therefore the date part must be filled with something. If I remember correctly it will be the 1st January 1900. There is no specific time only type in SQL Server, so you will need to work around the addition of the date. Finally, you may find that posting a new thread when you have a new question will be seen by more people and you will get a quicker response if I'm not around. Posting on a thread that started last November will only be seen by the person whose post you responded to (assuming they have email notification turned on). Hopefully you'll find that helpful in getting a good response sooner. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
TheJudeDude wrote:
When I look in the debugger, that parameter has the value "HH:mm AM/PM", but when in the database after the command is run, it stores it as "MM/DD/YY HH:mm:ss AM/PM". Why is that?
Because it must. Actually the order of the elements (month, day, hours, minutes, etc.) are not important, that is just the user interface. Internally it is stored the same way regardless of the culture. The type is designed for storing a date with a time to a resolution of one minute. Therefore the date part must be filled with something. If I remember correctly it will be the 1st January 1900. There is no specific time only type in SQL Server, so you will need to work around the addition of the date. Finally, you may find that posting a new thread when you have a new question will be seen by more people and you will get a quicker response if I'm not around. Posting on a thread that started last November will only be seen by the person whose post you responded to (assuming they have email notification turned on). Hopefully you'll find that helpful in getting a good response sooner. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Thanx for the info! If you get this, I have another delima ;) I am storing a decimal value in the table that is defined as a decimal, but it is only storing the integer part. In the debugger I would have something like 8.234534, but it stores 8.The definition for the column is decimal 5 allow nulls pricision 4. The code to store the value is: cmdSetHours.CommandText = "UPDATE " + strName + " SET Hours = " + decTotalHours + " WHERE EmpID = " + m_intEmployeeID; Thanx again!