Data Access and ObjectDataSource Control

Re: how to apply paramatized queeries with this code

Mikesdotnetting — Tue, 07 Jul 2009 22:07:17 GMT

Ez416:
The problem is, the parameter in the stored procedure should be the same with the parameter declared in .net

I don't follow what you are saying....

You are passing in 2 arrays - one with the parameter names and one with the matching values. Yes, the parameter names that you pass in to the array should match the ones in the stored procedure that you are passing in too.

Re: how to apply paramatized queeries with this code

Mikesdotnetting — Tue, 07 Jul 2009 22:04:26 GMT

Ez416:
I've tried converting it to VB

Good. You have discovered codechanger.com. I normally just change:

System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)

to:

i = i + 1

Run the code to see if it works. That way you will find out if it is correct, but at first glance, I suspect it is.

Re: how to apply paramatized queeries with this code

Ez416 — Tue, 07 Jul 2009 09:29:54 GMT

The problem is, the parameter in the stored procedure should be the same with the parameter declared in .net

Re: how to apply paramatized queeries with this code

Ez416 — Tue, 07 Jul 2009 08:10:19 GMT

I've tried converting it to VB

from

{
    using (SqlConnection conn = new SqlConnection(connstring))
    {
        SqlCommand cmd = new SqlCommand(proc, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        for (int i = 0; i < Params.Length; i++)
        {
            cmd.Parameters.AddWithValue(Params[i], Values[i]);
        }
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Public Shared Sub ExecNonQuery(proc As String, Params As String(), Values As [Object]())
	Using conn As New SqlConnection(connstring)
		Dim cmd As New SqlCommand(proc, conn)
		cmd.CommandType = CommandType.StoredProcedure
		Dim i As Integer = 0
		While i < Params.Length
			cmd.Parameters.AddWithValue(Params(i), Values(i))
			System.Math.Max(System.Threading.Interlocked.Increment(i),i - 1)
		End While
		conn.Open()
		cmd.ExecuteNonQuery()
	End Using
End Sub

-------------------------------------

From

string sql = DeleteAllExpired; // name of procedure
string[] p = new string{"id"};
object[] o = new object{TextBox1.Text};
ExecNonQuery(sql, p, o);

Dim sql As String = DeleteAllExpired
' name of procedure
Dim p As String() = New String()
Dim o As Object() = New Object()
ExecNonQuery(sql, p, o)

Are these correct?

Re: how to apply paramatized queeries with this code

Mikesdotnetting — Mon, 06 Jul 2009 12:16:53 GMT

Here's an example:

public static void ExecNonQuery(string proc, string[] Params, Object[] Values)
{
    using (SqlConnection conn = new SqlConnection(connstring))
    {
        SqlCommand cmd = new SqlCommand(proc, conn);
        cmd.CommandType = CommandType.StoredProcedure;
        for (int i = 0; i < Params.Length; i++)
        {
            cmd.Parameters.AddWithValue(Params[i], Values[i]);
        }
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

This one causes an ExecuteNonQuery() command to be executed. You just feed it a procedure name, an array of strings for the parameter names and an array of objects that provide the values to the parameters.

Eg:

string sql = DeleteAllExpired; // name of procedure
string[] p = new string{"id"};
object[] o = new object{TextBox1.Text};
ExecNonQuery(sql, p, o);

Re: how to apply paramatized queeries with this code

Ez416 — Mon, 06 Jul 2009 07:00:04 GMT

but i wonder if how can I make my parameters flexible. in a way for example the stored proc that will be use has 4 parameters, since my code(class) has only designed for 3 parameters only. how can I accomplished this?

Re: how to apply paramatized queeries with this code

Ez416 — Mon, 06 Jul 2009 06:53:51 GMT

ok got it

if modified my class

.CommandType = CommandType.StoredProcedure
.CommandTimeout = 4000
.Parameters.AddWithValue("QType", P1)
.Parameters.AddWithValue("Desc1", P2)
.Parameters.AddWithValue("Desc2", P3)

Re: how to apply paramatized queeries with this code

RichardD — Fri, 03 Jul 2009 16:11:11 GMT

The problem is that you are creating a 'Text' command rather than a 'StoredProcedure' command. If you set cu.CommandType to CommandType.StoredProcedure and change the query to "spt_PhysiciansModule", the query will work.

Alternatively, if you can't change the command type, you need to specify the parameters in the query:

EXEC spt_PhysiciansModule @QType = @QType, @Desc1 = @Desc1, @Desc2 = @Desc2

The names need to be specified twice so that SQL matches the parameters by name; if you just specified "EXEC spt_PhysicialsModule @QType, @Desc1, @Desc2", the parameters would be matched by position, which may not be correct.

Re: how to apply paramatized queeries with this code

hans_v — Fri, 03 Jul 2009 15:51:26 GMT

cshark:
Command parameters in .net are case sensitive

I don't think it is .NET that is case sensitive. I think the sensitive Collation setting of the SQL Server requires the parameters to be case sensitive....

Re: how to apply paramatized queeries with this code

cshark — Fri, 03 Jul 2009 08:30:34 GMT

Command parameters in .net are case sensitive. @QType is different from @Qtype

Re: how to apply paramatized queeries with this code

Ez416 — Fri, 03 Jul 2009 08:27:12 GMT

whats the difference?

mine is

cu.Parameters.AddWithValue("@QType", "A")

Re: how to apply paramatized queeries with this code

cshark — Fri, 03 Jul 2009 08:16:27 GMT

You are adding the parameter as @QType in .net where as the parameter in sql is @Qtype.

try the below line.

cu.Parameters.AddWithValue("@Qtype", "A")

Re: how to apply paramatized queeries with this code

Ez416 — Fri, 03 Jul 2009 08:03:04 GMT

Dim sQuery1 As String = "spt_PhysiciansModule"

Dim cu As New SqlCommand(sQuery1, LibConn)

Dim cr As System.Data.SqlClient.SqlDataReader = cu.ExecuteReader()

cu.CommandType = CommandType.StoredProcedure

cu.Parameters.AddWithValue("@QType", "A")

cu.Parameters.AddWithValue("@Desc1", "")

cu.Parameters.AddWithValue("@Desc2", "")

Error Msg: Procedure or Function 'spt_PhysiciansModule' expects parameter '@Qtype', which was not supplied.

Re: how to apply paramatized queeries with this code

Ez416 — Fri, 03 Jul 2009 07:41:35 GMT

It doesnt work with the code above.

Re: how to apply paramatized queeries with this code

mudassarkhan — Fri, 03 Jul 2009 07:13:57 GMT

You can read here for parameterized queries

http://www.aspsnippets.com/post/2009/02/09/Parameterized-Queries-ADONet.aspx