|
|
Hi All,
I have two tables like Casual_Employee_Details, Permanent_Employee_Table and the columns in both tables are same.
I have a stored procedure as follows
CREATE PROCEDURE getEmpId
(
@Eid int,
@EName varchar(50)
)
AS
insert into Casual_Employee_Details(n_EmpId,c_EmpName)values(@EmpId,@EmpName)
GO
Now i want to pass table name also dynamically how can it be done.
help me out with code if possible..
So that if if i execute it should as follows
Execute dbo.getEmpId 'tableName',Eid,'Ename'
Thanks,
Ashok
ashok
|
|
|
|
|
you have to concatenate the whole sql string to a variable like
set @sql = 'insert into ' + QUOTENAME(@tablename)
+ ' (n_EmpId,c_EmpName)values('
+ convert(varchar(50), @EmpId)
+ ',' + QUOTENAME(@EmpName, '''') + ')'
exec (@sql)
However it's not typically a good idea to use dynamic sql this way. It may lead to several problems.
Modification, added QUOTENAME to prevent injections.
|
|
|
|
|
Mika Wendelius wrote: However it's not typically a good idea to use dynamic sql this way
It is posslble to do some verification before you start the concatenation. For instance. you can look up INFORMATION_SCHEMA.TABLES to ensure that the a table with the given name exists. If it doesn't you can exit the sproc.
You can also pass parameters to dynamic SQL by using sp_executesql[^] instead of EXEC. That way you are not concatenating potentially dangerous data into the SQL String, you're passing it as a parameter as normal.
|
|
|
|
|
You're right, there are numerous ways to perform checks and prevent injections etc. but since I wasn't sure why the OP wanted to use dynamic sql for the insert (it didn't quite make sense since for example columns were hardcoded) I thought it would be wise if he thinks about it once more.
If dynamic handling for the table name is still required, at least it's thoroughly considered.
I just noticed that I forgot to use QUOTENAME. Think I should add it to the answer. Another way is to use sp_executesql as you mentioned.
|
|
|
|
|
Hi all,
Can anyone explain other possible mechanisms to handle this scenario in detail.
Any references could be helpful..
thanks,
Ashok
ashok
|
|
|
|
|
ashok_rgm wrote: Can anyone explain other possible mechanisms to handle this scenario in detail
As Colin and I wrote you can use either sp_executesql or exec to execute a dynamic sql statement. Both will do as long as you prevent yourself from sql injections.
In your original post you described that you want to pass the table name as a parameter and use it in your sql statement. However your column names were hardcoded in the statement so it makes me wonder, why do you want to change the table name. First impression is that you have several tables with the same structure. If that's correct then again, why?
|
|
|
|
|
My issue is something like this
i have a table Master_Info with columns(RecId(PK),Emp_Name,DOB) where RecId is primary key and increments by 1 for each entry.
RecId Emp_Name DOB
1 abc 01/01/2009
2 xyz 02/02/2009
Now for each Employee I have separate table to track hi daily details Employee_Details_(RecId) with columns (Working_Date,No_of_Hours)
So for user "abc" the table name will be Employee_Details_1,for "xyz" it will be Employee_Details_2
Now I want to insert the records for particular user in Employee_Details_(RecId) table using the stored procedure.
What are the suggested ways to do this task?
Thanks,
Ashok
ashok
|
|
|
|
|
ashok_rgm wrote: Now for each Employee I have separate table to track hi daily details Employee_Details
Is there really a very good reason to do it this way?
Normally you would have a table structures like:
Master_Info
- RecId (primary key)
- Emp_Name
- DOB
Employee_Details
- ID (primary key)
- RecId (foreign key to Master_Info)
- Working_Date
- No_of_Hours
So in Employee detail you store details for all employees and the RecId column tells which employee is referenced.
|
|
|
|
|
I am running 64-bit Vista Ultimate.
1. Do 32-bit applications require 32-bit ODBC drivers, and 64-bit applications require 64-bit ODBC drivers?
2. How can I administer the 32-bit ODBC drivers on my system? Whenever I run the ODBC Administrator applet, it only shows me the 64-bit drivers.
Thanks
|
|
|
|
|
Richard Andrew x64 wrote: 1. Do 32-bit applications require 32-bit ODBC drivers, and 64-bit applications require 64-bit ODBC drivers?
In case of ODBC drivers, 32-bit require 32-bit odbc drivers only and same for 64 bit.
Richard Andrew x64 wrote: 2. How can I administer the 32-bit ODBC drivers on my system?
You need to figure it out manually
|
|
|
|
|
I have a stored procedure , that imports the data to sql server 2005, For that I send the XML
to database, there I extract the data and insert/update it in one query.
I am operating the same thing at multi user level and starts multi users start the import
for the central localized database, there I am watching the execution in sql profiler,
and now my problem is
The Audit logout/login takes lot of time to execute,
Suppose two users are simultaneously doing the import process, if the first user reaches the
database first, there after the completion of the import for the first user,
still the request of first user does not complete , it
remains there for several minutes until audit logs out.
and due to this the second user keep waiting for the import execution.
please provide suggestion for this performance issue.
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Is there some reason why you suspect that audit log is the rason for waiting? If both of the users are adding data to the same table, I would guess that the second session is waiting for exclusive locks to release from the first session. Have you used current activity window in Management Studio to see what the second session waits?
|
|
|
|
|
I'm a beginner C#/ASP.NET programmer (long time C/C++ in Unix programmer) having problems with storing an image on my webform to sql then retrieving it. Problem is, I've got the image on my webform along with other information in textboxes that need to be stored in the database. All the textbox info as well as two images on the webform (the images are signatures captured from signature pads) need to be stored then later recalled to bring up a print preview of the information that was earlier saved. All my textbox info is being stored and recalled properly, but I'm having issues with the images of the signatures. There's no real file for the image to upload via a dialog box, just the captured image. Can anyone help me out?
Thanks
|
|
|
|
|
You can store the image data in byte[] and use SqlParameter with type VarBinary. Although it's not exactly what you ask, you could look at the sample project in the article http://www.codeproject.com/KB/database/SqlFileStream.aspx[^]. SqlFileStream isn't included in SQL Server 2005, but in the example project one of the variations is normal SqlParameter usage.
|
|
|
|
|
I figured I'd need to convert the image data to byte[] from what I've been finding when looking for a solution, but I'm not sure how to go about that in C#. Like I said, I'm a newbie at this .NET stuff. The signature images I need to store to (and then later retrieve again from) SQL are System.Web.UI.WebControls.Image. Also, my database table already has a row entry, so storing the images are going to be an update into that row, rather than an insert.
|
|
|
|
|
I'm not very familiar with web controls, but I take it you set the imageurl to point to a file? If yes, then you could just read that file using File.ReadAllBytes.
roachae wrote: my database table already has a row entry, so storing the images are going to be an update into that row, rather than an insert
If you mean the example in the article which inserted a record, you would simply use an UPDATE statement instead of an INSERT statement. Something like:
UPDATE SomeTable
SET ImageColumn = @Image
WHERE KeyColumn = @Key
|
|
|
|
|
The only file I have is of a default blank image. Like I said, the images are of signatures captured from a signature pad, so there's no real file there until someone signs the signature pad and the image is placed within the webform. But even my default blank image will not store properly. How would I use File.ReadAllBytes? I'm not finding that anywhere.
As for using UPDATE in place of INSERT, I got that much figured out as most of my other data is being saved by UPDATE. I've got a bit of experience working in PHP and MySQL, but this is the first project I've worked on in C#/ASP/SQL Server. And I've never dealt with images before.
|
|
|
|
|
roachae wrote: Like I said, the images are of signatures captured from a signature pad, so there's no real file there until someone signs the signature pad and the image is placed within the webform
Where does the image data come from in this situation? I didn't find any property in Image control that contains the actual image data, only the Url property. However, as I said, I'm not very familiar with Asp.Net controls.
roachae wrote: How would I use File.ReadAllBytes? I'm not finding that anywhere
File.ReadAllBytes Method[^]
If I remember correctly I used that method in the example project in the article. Have a look at the DbOperations class and method StoreFileUsingSqlParameter. That should contain almost everything you need except that you have to use UPDATE.
|
|
|
|
|
|
I tried this, but the only file I have to work with is a default blank image file saved on a server. When I try your suggestion, I keep getting a URI is not supported error. What I need to do is store captured images of signatures captured from signature pads that are placed in the webform. Then once they are stored in SQL, I need to be able to retrieve them as well to recall all the info from the webform that was previously stored.
|
|
|
|
|
Hello all,
We are planning a change in our main database (customers, products...).
Now that we are preparing that it has fallen into my hands to decide the best method in order to store the articles/products into the database.
NOTICE: the Id field limit is 20 alphanumeric characters.
My main worry here is that I strongly believe that the references from different sellers can be identical. (<-- what do you think on this topic?).
I have thought on some solutions:
1. Set a number (like an autonumeric) for each product:
This means that each time the secretaries will need to add a new product they will need to search for it before trying to add it.
This seems to me that is cumbersome, it can't fail in theory, but it is too much work and this leads to human failure.
2. Write a code for the seller and the reference of the seller itself:
00 | 0123456789012345678 (the first two of them could be someething like 00 to ZZ in order to tell fr
fom where we buy the product and the other 12 can be the reference.
The secretaries must remember the "provider/seller code" before entering anything.
We can filter by seller easily. We loose two characters from the 20 we have.
3. Write a 0 at the beggining and the reference of the product.
As I told before, I believe that the references can be duplicated easily...
the first character would allow me to introduce from 0 to Z references with the same code. In that way, in the special case that a repeated reference would appear we could control it a small number of times...
What would you do?
Thank you in advance...
|
|
|
|
|
Just checking that I understood correctly. You have a seller identification (code etc) and the product identification. The product Id is given so it has to be accepted as-is. The products don't have any structure (at least in your system). And the question was partly about duplicates and also about avoiding errors. And the main usage target was in data input. Correct me something was wrong.
I'll just jump to a suggestion (assuming that the previous was correct). Based on the info you provided, I would start with three tables (you didn't mention the database so I'll use SQL Server terminology):
Seller
- Seller# int, identity, primary key, not null
(surrogate key)
- Name varchar, unique key, not null
(Seller name if needed)
- Id int, unique key, not null
(Id to show along with the product id)
...
Product
- Product# int, identity, primary key, not null
(surrogate key)
- Seller# int, foreign key, unique key (with Id), not null
(Reference to seller)
- Id varchar, unique key (with Seller#), not null
(Id to show along with the seller id)
...
So if you have seller XYZ (with visible Id 05) which has product 123456 and product 98765 you would have records like:
Seller# Name Id
------- ---- --
1 XYZ 05
Product# Seller# Id
-------- ------- ---------
1 1 123456
2 1 98765
Optionally if the product Id between sellers is the same, one table could be added so that the seller and the product is stored only once and if you buy the same product from two sellers it would be handled by a link table. It would have only Product# and Seller# and the product table wouldn't vave Seller# anymore.
Also the Id in seller may not be necessary unless for example it is printed to a paper etc. and used visually since those tables don't use the seller id for identification. It's just an optional key element.
Was this answering to your question at all?
|
|
|
|
|
Yes, more or less it answers my question... (* explanation below).
My question "intended" to ask about the possibility to find a repeated code between two different manufacturers. (Something I feel that is more than probable).
And I was trying to ask about the method used in order to avoid that.
I can't do anything else than modifying the way the user introduces the code as I'm not the programmer of that program... it simply is the program our accountants use.
This is why I've given 3 possible scenarios (the ones that I thought about).
(*) You proposed a method that include the seller ID so I suspect that you also feel that the codes can be repeated in different sellers.
Thank you...
|
|
|
|
|
Okay, when writing the answer I figured it isn't necessarily what you asked. Yes I think it's quite possible to have duplicate product codes it's necessary to identify the seller somehow.
I think that the solution leckey described would be a good one. No duplicates and optionally additional information how the record came into the system.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
