|
I'd recommend splitting this into two procedures, add and edit. The query processor will either have to build one query plan with redundant code, or, more likely, recompile the query plan every time you swap the value of @AddMode.
As for your specific problem, you can only parameterize values. You can't parameterize schema. If you have to, you've probably designed your database incorrectly. Tables are not objects. Creating and dropping tables are very expensive operations.
If you absolutely must do this, you should use string concatenation to form the schema part of the query, then use sp_executesql to execute it supplying the parameters, e.g.:
DECLARE @query varchar(2000)
SET @query = 'UPDATE ' + @GenTable +
' SET ' + @GenNameField + ' = @GenName WHERE ' +
@GenCodeField + ' = @GenCode'
EXEC sp_executesql @query,
N'@GenName varchar(100), @GenCode int',
@GenName, @GenCode
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Example:
Create procedure prg.ExamleProcedure
@tabloname varchar(50),
@data int
as
declare @stmt varchar(8000)
select @stmt = 'insert into ' + @tabloname + ' (column1, column2) ' +
'select ' + @data + ' , NULL'
Exec (@stmt)
You can solve your problem like this.
abbaskaya
|
|
|
|
|
Example:
Create procedure prg.ExamleProcedure
@tabloname varchar(50),
@data int
as
declare @stmt varchar(8000)
select @stmt = 'insert into ' + @tabloname + ' (column1, column2) ' +
'select ' + @data + ' , NULL'
Exec (@stmt)
You can solve your problem![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif) like this.
abbaskaya
|
|
|
|
|
Thanks Mr Abbaskaya and Mr Mike
I have 8 tables in my database which have the same structure and so i planned to do a general function so that i can make those 8 modules as a single functions and can use parameters to make it work.
I have tried the way you have explained above but i am getting a syntax error.Could you tell me exactly how to implement that with correct syntax so that i can check it here.
Thanks a lot for your help
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
If you have 8 tables with the same structure you should put them all in one table and drop the seven other ones. That would save your problem.
Wout Louwers
|
|
|
|
|
those 8 tables are same structured but due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables.
with regards
vimal
Help in need is the help indeed
|
|
|
|
|
What do you mean with 'due to simplicity'. If the use of the 8 tables was that simple you wouldn't have to ask this question.;)
due to simplicity and future oriented modifications and additions i am in a position to use 8 different tables.
You sound like a manager (PHB). Not like a programmer.
Wout Louwers
|
|
|
|
|
Unable to install Oracle 8i Release 3 (8.1.7) on Windows 2000 Professional . When I insert the installation CD..the Oracle Enterprise Edition - Autorun screen appears..then on clicking on the "Install/Deinstall Products" option ...some processing occurs..and then no further steps appears...According to what I have heard,
Oracle 8i Release 3 (8.1.7) gets easily installed on Windows 2000 Professional and has some problems with XP ,then where the problem lies...Can someone one help me out...Thanking you in advance.
|
|
|
|
|
Do you have a Pentium 4 processor? If so, see here[^], especially the post from Bart-Jan Keetels.
|
|
|
|
|
Thank u ....it worked!!
|
|
|
|
|
With ref. to ASP.NET web application:
suppose there are two textbox (web form controls) , one reset button(HTML control) on the screen.
when the page is loaded for the first time,I enter some values in two textboxes and press reset button.
Values from the text boxes gets cleared on clicking the reset button. If I perform the same activity again reset button doesn't work.
|
|
|
|
|
|
I have windows 2000 professional edition installed on my system.
When I try to open a new project in ASp .NET web applications or web services, I get the following error:
"Visual Studio .NET has detected that the specified webserver is not running ASP.NET version 1.1. You will be unable to run ASP.NET web applications or
services."
Note that Webserver is running propery on my PC.
How to resolve this error?
|
|
|
|
|
This refers to the records returned by the "For XML auto" in ADO.Net.
The first case is simply retirning results from a select statement from a
table returning some columns with for xml auto.
--------------------------------------------
[No erros, results returned]
Database2, connection2
SqlConnection1.Open()<br />
Dim SQLCmd As New SqlClient.SqlCommand<br />
SQLCmd.Connection = SqlConnection1<br />
SQLCmd.CommandType = CommandType.Text<br />
SQLCmd.CommandTimeout = 20<br />
SQLCmd.CommandText = "SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto"<br />
Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader()<br />
<br />
'myXmlReader.MoveToContent()<br />
'myXmlReader.MoveToElement()<br />
Response.Write("<table border='1'>")<br />
<br />
While myXmlReader.Read<br />
'Response.Write(myXmlReader.Item("CustomerID") & "<br>")<br />
Response.Write("<tr><td>" & myXmlReader.Item("CustomerID") & " " & myXmlReader.Item("CompanyName") & _<br />
myXmlReader.Item("City") & "" & myXmlReader.Item("PostalCode") & "</td></tr>")<br />
End While<br />
Response.Write("</table>")<br />
myXmlReader.Close()<br />
SqlConnection1.Close()<br />
[No errors, results returned]
-----------------------
The second case is, results expected to be returned from table joins using For xml auto. In this case no results were returned, no errors or exceptions, exactly same trace info as in the first case
[No errors, no output(empty table), trace info exactly same as the one with results returned]
Database1, connection string1
SqlConnection1.Open()<br />
Dim SQLCmd As New SqlClient.SqlCommand<br />
SQLCmd.Connection = SqlConnection1<br />
SQLCmd.CommandType = CommandType.Text<br />
SQLCmd.CommandTimeout = 20<br />
SQLCmd.CommandText = "SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto, ELEMENTS"<br />
'SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto<br />
'SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto<br />
Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader()<br />
Response.Write("<table border='1'>")<br />
<br />
While myXmlReader.Read<br />
'Response.Write(myXmlReader.Item("CustomerID") & "<br>")<br />
<br />
Response.Write("<tr><td>" & myXmlReader.Item("nums.NumId") & " " & myXmlReader.Item("nums.Nums") & _<br />
myXmlReader.Item("color.colr") & "</td></tr>")<br />
End While<br />
Response.Write("</table>")<br />
myXmlReader.Close()<br />
SqlConnection1.Close()<br />
[No errors, no output]
What could be different?
|
|
|
|
|
Possibly the join yeilds no matches. Try the query in Query analyzer (first as a normal query, - no forXml, then just without the ELEMENTS directive).
Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
|
|
|
|
|
That would be the first thing I would try. It does return resutls in Query Analyzer.
Thanks,
Happy New Year
Mysorian
|
|
|
|
|
how to make a case sensitive comparsion in sql ?
select * from users where Password = 'ahmed'
is the same
select * from users where Password = 'AhmEd'
how to make it case-sensitive ?
|
|
|
|
|
|
thanx for reply, but could u explain more ,
actually i'm not a proffessional with sql, so could u provide an example or just explain.
thanx in advance
|
|
|
|
|
Every time I run the code below, it tells me "No update permissions!"
<html>
<body>
<h2>Update Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.mapPath("chem.mdb"))
cid=Request.Form("sh*t")
response.write(Request.Form("test1"))
jon = 1
response.write(jon)
if Request.form("test1")<>"" then
sql="UPDATE NEWS SET "
sql=sql & "DATE='" & now() & "',"
sql=sql & "CONTENT='" & Request.Form("test1") & "'"
sql=sql & " WHERE ID='" & jon & "'"
on error resume next
conn.Execute sql, Recordsaffected
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & cid & " was updated!")
end if
end if
conn.close
%>
</body>
</html>
|
|
|
|
|
RomanD wrote:
Every time I run the code below, it tells me "No update permissions!"
I would guess that there is an error in the SQL statement.
I would also guess that err does not necessariliy indicate a permissions problem, but your code assumes that any error must be a permissions problem. You should find out what err actually is, and find out what that error number actually means.
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Could you update using this SQl from within MS Access?
|
|
|
|
|
Could you update using this SQl from within MS Access?
|
|
|
|
|
Here is my newest code:
sql="SELECT * FROM NEWS WHERE ID="&jon&";"
Set rsSrc = Server.CreateObject("ADODB.Recordset")
rsSrc.Open sql, oDBConn,0,3
if(not(rsSrc.EOF))then
rsSrc("DATE")= Now()
rsSrc("CONTENT")=Request.Form("test1")
rsSrc.update
end if
rsSrc.close
set rsSrc=nothing
Now, the error message I get is that my database/object is readonly. But I think that's a problem with my host.
|
|
|
|
|
I suspect that the user account that your ASP page is running under - typically IUSR_machinename - doesn't have NTFS write permissions to the directory that contains the .MDB file. Hence the database is opened read-only and you can't insert new rows or update or delete existing ones.
The account needs to be able to write to the directory, not just the file, as the Jet database engine needs to create an .ldb file if multiple connections access the file concurrently.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
