Post notes on development here, if you want to find it later

J

jxharding

Expert Member
Joined
May 4, 2006
Messages
1,629
Reaction score
3
e.g.
asp.net

Scenario : fill dataset in code-behind by passing a parameter variable in query string
Error : Must declare the scalar variable @Variable

Dim strSQL As String = "Select * from MyTable Where ID = @Variable "
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strSQL, con)
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = MyVariable

Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, con)
da.Fill(ds)



solution : add the paramater to the datadapter, not to the initial command
da.SelectCommand.Parameters.AddWithValue("@ID", MyVariable )
 
jxharding said:
e.g.
asp.net

Scenario : fill dataset in code-behind by passing a parameter variable in query string
Error : Must declare the scalar variable @Variable

Dim strSQL As String = "Select * from MyTable Where ID = @Variable "
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(strSQL, con)
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = MyVariable

Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, con)
da.Fill(ds)



solution : add the paramater to the datadapter, not to the initial command
da.SelectCommand.Parameters.AddWithValue("@ID", MyVariable )
Click to expand...

Very very bad programming practice use only stored procs m8 this kind of asp.net coding will allow hackers to compromise your website or intranet site.

Have a look at SENSPOST.
 
@fulmine
true, i use stored procs 95% of the time, but there is the odd occasion for an ad hoc query
anyway, senspost looks good, you work there?
 
Dont know if any of you will ever find this handy but i sure use it quite often.
I use this internally not sure how it will impact security when used on a public domain.

Write XML directly to filesystem from a SQL2005 SP

1.Create a new file called WriteToFile.vb in “c:\windows”
And paste the following vb.net code in it using notepad.

Code: 
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

Public Class SQLCLRIO
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub WriteToFile(ByVal content As String, _
                                  ByVal filename As String)

        Try
           File.WriteAllText(filename, content)

        Catch ex As Exception
            SqlContext.Pipe.Send("Error writing to file " & ex.Message)
        End Try

    End Sub

End Class

Then Compile/build a new dll file to be used with SQL2005 by running the following command in the command prompt.
(VB.NET does not need to be installed its done via the .Net Framework installation compiler.)
Code: 
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
vbc /target:library C:\windows\WriteToFile.vb

After the above command is done you should now have a WriteToFile.dll in ‘c:\windows\’

Then Reconfigure the SQL 2005 server by running the following query.
The Alter Authorization command is not needed if the master database and your database has the same
Owner user, Please take note this could cause logon problems for 3rd party applications especially if mixed mode
Authentication is used.

Code: 
ALTER AUTHORIZATION ON DATABASE::DATABASENAME TO sa 

exec sp_configure 'clr enabled',1
reconfigure
go
alter database DATABASENAME set trustworthy on
go
use DATABASENAME
go
create assembly WriteToFile from 'c:\Windows\WriteToFile.dll'
with permission_set = external_access
Then Run the following Query which will then create a new system StoredProc called (WriteToFile) which writes any string given to it
Directly to a given path.

create procedure dbo.writetofile
(
   @content nvarchar(max),
   @filename nvarchar(255)
)
as external name WriteToFile.SQLCLRIO.WriteToFile
And you are done.

USAGE

Code: 
declare @MySTR nvarchar(MAX)

set @MySTR = (
select * from TABLE where FIELDNAME = 'WHATEVER'

FOR XML PATH('XMLROOT')

)

exec writetofile @MySTR,'C:\test.XML'
 
Last edited:
^ I don't really know where your XML comes from in your final query. If you want to export it to XML you'd have to use the "FOR XML" clause at the end of your query. Also, XML files typically have the extension ".xml" and not ".txt". But that's just me nitpicking... ;)
 
Forgive my ignorance but I don't see the vulnerability in jxharding's initial query. He is parameterising his query... :confused:
 
FarligOpptreden said:
^ I don't really know where your XML comes from in your final query. If you want to export it to XML you'd have to use the "FOR XML" clause at the end of your query. Also, XML files typically have the extension ".xml" and not ".txt". But that's just me nitpicking... ;)
Click to expand...

Ye ye I know, I will fix it for your sake :-)

The idea is not to write an actual XML structure to file but to write a string to file.
The limitation is , you can only write a single returned value to file that's why i used the "Where clause"

So the above does actually work.

When SQL 2005 returns XML it returns the one query to a single string
Then that is written to file.

I will fix it in a while as i cant remember how to run a query and return header and detail into an XML format.

[EDIT] Its fixed and tested it ;-)
 
Last edited:
SBSP said:
Ye ye I know, I will fix it for your sake :-)

The idea is not to write an actual XML structure to file but to write a string to file.
The limitation is , you can only write a single returned value to file that's why i used the "Where clause"

So the above does actually work.

When SQL 2005 returns XML it returns the one query to a single string
Then that is written to file.

I will fix it in a while as i cant remember how to run a query and return header and detail into an XML format.
Click to expand...

Took the liberty of writing a sample for you:

Code: 
DECLARE @MY_XMLString nvarchar(max)

SET @MY_XMLString = 
(
    SELECT
        [Field1]
      , [Field2]
      , [Field3]
      , [Field4]
    FROM [TABLE]
    FOR XML PATH('ElementName'), ROOT('Elements')
)

EXEC writetofile 
    @content = @MY_XMLString
  , @filename = ’C:\TEST.txt’

Remember to stick to best practices when posting samples on a public site like this - people very easily pick up bad habits. Always, always, ALWAYS execute a stored procedure by NAMING the parameters and setting their values. It makes for backwards compatible code when changing the parameters to the procedure in the future. Also, "select *" will make your life a living hell when you decide to add a column or two to a table and you don't necessarily want them in a "select *" query in some stored procedure. ;)
 
SBSP said:
Thanks that also works
Click to expand...

Again just a little note on your query:

Code: 
select * from TABLE where FIELDNAME = 'WHATEVER'

FOR XML PATH('XMLROOT')

You don't want the PATH to refer to the ROOT element of the XML document, else you might end up with a bunch of "Employees" elements, instead of one root of "Employees" with child elements of "Employee". That's why you usually specify PATH('ElementName') and ROOT('RootElement'), which would in an example of a table of Employees be:

Code: 
SELECT
    ID
  , FirstName
  , LastName
  , Initials
FROM Employees
FOR XML PATH('Employee'), ROOT('Employees')

So you end up with:
Code: 
<Employees>
    <Employee>
        <ID>Value</ID>
        <FirstName>Value</FirstName>
        <LastName>Value</LastName>
        <Initials>Value</Initials>
    </Employee>
    <Employee>
        <ID>Value</ID>
        <FirstName>Value</FirstName>
        <LastName>Value</LastName>
        <Initials>Value</Initials>
    </Employee>
</Employees>
...instead of:
Code: 
<Employees>
    <ID>Value</ID>
    <FirstName>Value</FirstName>
    <LastName>Value</LastName>
    <Initials>Value</Initials>
</Employees>
<Employees>
    <ID>Value</ID>
    <FirstName>Value</FirstName>
    <LastName>Value</LastName>
    <Initials>Value</Initials>
</Employees>
 
Last edited:
But what if i do want it like that ? LOL just kidding thanks, I have never used this to write XML to disk only to write a value to file, I'm not a SQL developer more like a SQL self helper if i can call it that.

I write Crystal reports every now and again and sometimes i make use of custom views. So yes my SQL programming is very raw.
 
n/p ;) That's why we have forums like this, so people learn something new. Microsoft SQL Server is immensely powerful if you start using more advanced features like XML data types, XQuery and CLR. Those are the features few people delve into, not knowing how much easier they'll make life for them once they start using it.
 
Nope they audit our sites on a regular basis I deffo want to go on their course's but company doesnt wanna pay :(
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X