Showing posts with label sqlcommand. Show all posts
Showing posts with label sqlcommand. Show all posts

Friday, March 23, 2012

How to use SQLConnection in Script Task ?

Dear all,

I hava a Script Task to log an error if it occurs. I want to log the error into a table. I used sqlconnection object and sqlcommand object. This is my script inside the task :

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub

I thought my script was correct, but when I run the package there's an error, saying like this :
'object reference not set to an instance of an object'.
Where's the problem anyway ?
Thanks in advance,

Best regards,

Hery

Hi, though the code seems free of errors other than - varConnStr not declared

Please check the following things

1) Try Individually Running the Script Task, if it runs successfully, Package might have faied due to other reasons.

2) Else There might be some problem establishing connection in the Script, please add a messagebox just below conn.open

3) You might also add one more messagebox just below cmd.ExecuteNonQuery() to check or see the table if the new row has been inserted.

Many Thanks

Subhash Subramanyam

|||Hi Subhash,
thanks for the reply...
I've just found out the error, there's a silly mistake that I made by myself, in previous script I didn't put the Dts.ExecutionValue.ToString(), actually I did :-(
When I remove that line, the script worked successfully...sorry for this silly question..

Public Sub Main()
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim id As String = Dts.Variables("varUnitID").Value.ToString()
Dim file As String = Dts.Variables("varFile").Value.ToString()
Dim sqlIns As String
Try
conn = New SqlConnection(Dts.Variables("varConnStr").Value.ToString())
conn.Open()
sqlIns = "insert into APP_LOGFAILED values" + _
"(" + id + ",TARGET," + file + ";" + Dts.ExecutionValue.ToString() + ", getdate())"
cmd = New SqlCommand(sqlIns, conn)
cmd.ExecuteNonQuery()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
conn.Close()
conn = Nothing
End Sub


Best regards,

Hery
|||

Hi,

Please mark your thread as answered.

Thanks

Subhash Subramanyam

Sunday, February 19, 2012

How to use a variable in a SQLCommand?

Hi!

I need to know what can I do to use a variable in theWHEREcondition of the sqlcommand as I show you:

current_user = User.Identity.NameDim cmdAs New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = current_user", cn)
 
Obviously it doesn't work and I need your help. Thanks. 

Sorry this is not the question that I have, sorry.

The question is what can I do to use a variable(User.Identity.Name)in a SelectCommand that I use in a GridView, thanks again.

Something like that, but it doesn't work:

SelectCommand="SELECT id_fax, fecha,destino, propietario, contenido, estado, cola FROM fax WHERE(propietario = <%# User.Identity.Name %>)"

|||
try this, I think this is what you are looking for:
Dim cmdAs New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = "¤t_user, cn)
|||

Hi rubencdl,

You have to use String Concatination, the term String Concatination is use to combine two different strings. In your case you have two different string

first string containt the query

"SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = ' '"

and other contain the user name

current_user = User.Identity.Name

Now you have to combine these two string in a signle string, and for + operator is use for combination of two string

Dim cmdAs New SqlCommand("SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = ' '"

+ current_user + " ' " , cn)

Hope it works

Thank You,

Best Regards,

Muhammad Akhtar Shiekh

 
 
|||

Thanks to both of you.

But this is the answer for the first question, and I need to know the answer for the second, how can I do to use a variable in the SelectCommand of a GridView.

Thanks.

|||

Hi Dear,

I m really sorry dear, i can't understandt he meaning of "SelectCommand of a GridView."Confused

What do u mean by SelectCommand of a GridView? there is no Select Command Property in GridView...

can u please explain again what you really want? because string concatination will be same in each senario...

Thank You

Best Regards,

Muhammad Akhtar Shiekh

|||Sorry, is the SelectCommand of a SQLDataSourceEmbarrassed|||

rubencdl:

Sorry, is the SelectCommand of a SQLDataSourceEmbarrassed

try this in page_load event

SqlDataSource1.SelectCommand = "SELECT [id_usuario], [nombre], [apellidos], [telefono], [empresa] FROM [usuario] WHERE [id_usuario] = ' " + User.Identity.Name + " ' "