|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
I have a table which has 1 Cr records .Now i have a query which has joins on 3 tables includung the one that has the large number of records.The select fields remain the same but the fields in where condition changes as per user selection.I have created a view when there is no where condition and this works fast.But now what do i do when there is a where condition.This query takes 9 min.I have already applied indexes
|
|
|
|
|
Hello,
CREATE PROCEDURE dbo.spFindProduct
@searchText varchar(50)
AS
SELECT * FROM Product
WHERE productName LIKE '@searchText%'
GO
When the user enters a letter in the textbox the stored procedure will find all the words that begin with that letter. When they type a second letter the stored procedure will find all the words that begin with those 2 letters etc. All rows that are matched are displayed in datagrid. This is the same as one of those applications that find the closest word for what you are looking for.
My problem is that the above stored procedure does not return anything, even if the right letter has been entered.
The parameter is being passed correctly: WHERE productName = @searchText
That works and fine if l am looking for the exact word.
WHERE productName = 'F%' that works ok and finds all the rows where product name begins with a F.
But the above stored procedure does not work. Can anyone tell me how to correc this.
Many thanks in advance,
Steve
|
|
|
|
|
That is because you have asked it to search for the literal string '@searchText%' so it will only match product names that start with @searchText because you enclosed it in quotes.
What you need to do is something like this
DECLARE @search varchar(51)
SET @search = @searchText + '%'
SELECT * FROM Product
WHERE productName LIKE @search
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
If Not (Session("searchstring") Is Nothing) Then
Session.Remove("searchstring")
End If
str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
|
|
|
|
|
Breddy wrote: str1 = "select uid, firstname+' '+middlename+' '+lastname as name,login,email,Case When Status = 1 Then '../admin/img/img_status_active.gif' When status=0 Then '../admin/img/img_status_inactive.gif' End status from tb_users where 1=1"
If TextBox1.Text <> "" Then
str1 = str1 & " and last_name like '" & TextBox1.Text & "%'"
End If
I'm not sure what this post is supposed to demonstrate. It does not appear to have much to do with the original poster's query. It looks like it is some code you are using some place else that is vaguely applicable, but you have not provided an explanation so it is difficult to see where it is supposed to be helpful.
More importantly, your solution contains some pretty horrible security holes that you should patch. You may want to read the article SQL Injection Attacks and Tips on how to prevent them[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hi guys..
my problem is like this..
i have a query which is :
string query = "select count(*) AS [username] from [" + tablename + "] WHERE username = '"+username+"'";
and my method is :
public void checkIfUserExists(string username,string tablename)
as u can see, i want to check how many user exists with this username in the database, i want to execute the query, and take the result inside an int
( e.g. int result )
if the result is > 0 , then i won`t allow user to choose that username
else user can register..
how can i do this ? it was easy doing that at php but i cant do this in c# because i can`t understand ado.net very well ( datasets, datagrids,etc..)
any help would be great!
thx!
good coding !
|
|
|
|
|
The way you build your SQL string is susceptable to a SQL Injection attack you may wish to read SQL Injection attacks and tips on how to prevent them[^]
You don't say what database you are using so I'll assume SQL Server 2000
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
int result = (int)cmd.ExecuteScalar();
conn.Close();
This is a very basic example and does not take into account error conditions. You have to provide the connection string (as I know nothing about your database or the security you've set up) and the query (which you have above - although I do recommend securing it as shown in the article I've linked to)
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm sure this has been asked 1,000 times already so sorry for this but...
Can 2005 & 2000 co-habitate on a server? If it can then is it a good idea?
E=mc2 -> BOOM
|
|
|
|
|
Albert Einstien wrote: Can 2005 & 2000 co-habitate on a server?
Yes. I am have both happilly running right now.
Albert Einstien wrote: If it can then is it a good idea?
Since they both play nice together I don't see why it would be a bad idea. Some things to be aware of:
* Enterprise Manager cannot see SQL Server 2005, but the new SQL Server Management Studio can see SQL Server 2000.
* Query Analyser from SQL Server 2000 can connect to SQL Server 2005 instances.
* Having both SQL Server 2000 and 2005 on one machine is just like having multiple instances of a SQL Server running. Each instance will launch its own service and run as a separate process.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
We discovered that you can't edit a multi-statement table-valued function in SQL Server 2000 from SQL Server 2005 Management Studio. My bug report[^] (I've just found two duplicates submitted before mine which didn't show up when I searched originally).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I have two dataBase with different username and passWord ,
dataBase1 : UserName =u1 , Password=pass1
dataBase2 : UserName =u2 , Password=pass2
I have a Procedure on First dataBase , on this procedure I use a table of Second dataBase , But When I run the Application ,it make error that "u1" don't have access to dataBase2.
How can I use tables of dataBase2 in procedures of dataBase1?
Thanks.
|
|
|
|
|
Well, if you are working on SQL Server then you can operate across databases, even across different servers (if you link the servers)
Anyways you can use the full name of the table when you use two different databases like this:
SELECT t1.myColumn AS table1Column, t2.myColumn AS table2 column
FROM database1.dbo.myTable as t1
INNER JOIN database2.dbo.myTable as t2 ON t1.id = t2.id
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Oops sorry, you were talking about stored procedures.
Well, if you have a stored procedure on database1 you can use a table on database2 like this
CREATE PROCEDURE dbo.myStoredProc
AS
SELECT * FROM database2.dbo.myTable
GO
When you use a table name you can qualify it fully like this
[server].[database].[schema].[table name]
You can obviously miss the bits that are not needed. For example, in normal circumstanced you would only give the table name because everything is on the same server, in the same database, in the same schema.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I have a int ID Field set to auto-increment. When does it run out of room? What's the highest number it can go to?
E=mc2 -> BOOM
|
|
|
|
|
IIRC: (2*10^31)-1 = 2147483647
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks. I'm a developer and know some SQL but not the mechanics of SQL Server. I leave that to the experts. Can I change my Auto-Increment field to numeric and if so does that buy me anything?
E=mc2 -> BOOM
|
|
|
|
|
Suppose DataSet myDataSet has a DataTable myDataTable. Do the follwing:
myDataSet.Tables.Remove(myDataTable);
bool tf=myDataSet.HasChanges(); //tf will be false
DataSet tbl=myDataSet.GetChanges(); //tbl will be null
I understand the datatable is removed, thus all the DataRow are removed from the dataset. So dataset does not detect any change and does not get any changes. But what is the common way to track the fact that a whole table is removed from the dataset? Thanks.
webx10
|
|
|
|
|
i am using vs2002 and want to enter data in a sql server 2000 database through vb interface. i am able to enter data if the vb application and sql server both are on same node but i want to enter data to database on other node running sql server. computers are networked under same workgroup and there is no domain controller.
i used the following code for connection.
Dim constudent As SqlConnection
Dim cmdadd As SqlCommand
Dim conn As String
conn = "server=.; UID=sa;PWD=secret;database=student"
constudent = New SqlConnection(conn)
cmdadd = New SqlCommand("insert into student values(@name,@roll,@stream)", constudent)
cmdadd.Parameters.Add("@name", TextBox1.Text)
cmdadd.Parameters.Add("@roll", Val(TextBox2.Text))
cmdadd.Parameters.Add("@stream", TextBox3.Text)
Try
constudent.Open()
cmdadd.ExecuteNonQuery()
constudent.Close()
MessageBox.Show("Data Added", "added", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
Catch ex As Exception
MessageBox.Show("error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2, MessageBoxOptions.DefaultDesktopOnly)
End Try
may any one hlp plz to modify the connection string or tell me how to establish such connection
|
|
|
|
|
server=TheMachineName
or
server=TheMachineName\TheSQLInstanceName
Oh... And logging in as sa through your application is a huge security risk - Don't do it or you could get 0wn3d.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks Colin Angus Mackay for reply
but it is not working
I even tried giving port no 1433 wit the ip address of machine but no success.
As there is no domain controller and all the systems are under same workgroup, if your solution works under these circumstances or not????
Reply.........
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
