SqlParameter and Update problem

[Jason Huang]

Hi,

The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is
working for update,
but SqlParameter myPM =new SqlParameter
("@Address",SqlDbType.NVarChar,90,txtAddress.Te xt) is not working.
The Address column is DataType NVarChar 90, no problem.
Any idea?
Jason

[Brandon Driesen]

That is because that constructor is not used for passing in the Value of the
parameter. It is used to pass in the name of the sourceColumn which would be
used when the Update method of the SqlDataAdapter is called (and assuming the
command object which holds this parameter is participating in the update
process)

// constructor
public SqlParameter(string parameterName, SqlDbType dbType, int size, string
sourceColumn)
{

}

you are actually adding in the name of the sourceColumn and NOT the value...

the constructor accepts the value of the parameter in 2 forms:

// #1
public SqlParameter(string parameterName, object value) { .. }

// #2
public SqlParameter(string parameterName, SqlDbType dbType, int size,
ParameterDirection direction, bool isNullable, byte precision, byte scale,
string sourceColumn, DataRowVersion sourceVersion, object value) { .. }
Thus:

SqlParameter parameter = new SqlParameter("@Address",SqlDbType.NVarChar,90);
parameter.Value = txtAddress.Text;

or if you are referring to the actual command object:

command.Parameters.Add("@Address",SqlDbType.NVarCh ar,90).Value =
txtAddress.Text;

Regards,

Brandon
"Jason Huang" wrote:

Hi,

The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is
working for update,
but SqlParameter myPM =new SqlParameter
("@Address",SqlDbType.NVarChar,90,txtAddress.Te xt) is not working.
The Address column is DataType NVarChar 90, no problem.
Any idea?
Jason

[Jason Huang]

Thanks Brandon!
I made up an UpdatByParameter function.
Would you give me some advice to improve my UpdateByParameter function?
strUpdate ="UPDATE ContactAddr SET Serial=@Serial, ";
strUpdate +="ZipCode =@ZipCode, Address=@Address ";
strUpdate +="WHERE CustNo=@CustNo AND Serial=@Serial ";
SqlParameter [] myPM=new SqlParameter[4];
myPM[0] = new SqlParameter("@Serial", SqlDbType.SmallInt, 2);
myPM[1] = new SqlParameter("@ZipCode", SqlDbType.Char, 10);
myPM[2] = new SqlParameter("@Address", SqlDbType.NVarChar, 90);
myPM[3] = new SqlParameter("@CustNo", SqlDbType.NVarChar, 8);
myPM[0].Value=txtSerial.Text;
myPM[1].Value=txtZipCode.Text;
myPM[2].Value=txtAddress.Text;
myPM[3].Value=txtCustNo.Text;
this.UpdatByParameter(strUpdate, myPM, 1);

public void UpdatByParameter(string strSQL, SqlParameter [] GoodParameter,
int i)
{
mConn=new SqlConnection(gstrConn);//new connection need to be here first
mConn.Open();
mComm=new SqlCommand(strSQL,mConn);
for (int j=0; j < i; j ++)
{
mComm.Parameters.Add(GoodParameter [j]); //2005.10.19
}
mTrans = mConn.BeginTransaction();
mComm.Transaction=mTrans;
try
{
mComm.ExecuteNonQuery();
mTrans.Commit();
}
catch(SqlException e)
{
mTrans.Rollback();
}
mConn.Close();
}//end UpdateByParameter

"Brandon Driesen" <Brandon Dr*****@discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:13********************************* *@microsoft.com...

That is because that constructor is not used for passing in the Value of
the
parameter. It is used to pass in the name of the sourceColumn which would
be
used when the Update method of the SqlDataAdapter is called (and assuming
the
command object which holds this parameter is participating in the update
process)

// constructor
public SqlParameter(string parameterName, SqlDbType dbType, int size,
string
sourceColumn)
{

}

you are actually adding in the name of the sourceColumn and NOT the
value...

the constructor accepts the value of the parameter in 2 forms:

// #1
public SqlParameter(string parameterName, object value) { .. }

// #2
public SqlParameter(string parameterName, SqlDbType dbType, int size,
ParameterDirection direction, bool isNullable, byte precision, byte scale,
string sourceColumn, DataRowVersion sourceVersion, object value) { .. }
Thus:

SqlParameter parameter = new
SqlParameter("@Address",SqlDbType.NVarChar,90);
parameter.Value = txtAddress.Text;

or if you are referring to the actual command object:

command.Parameters.Add("@Address",SqlDbType.NVarCh ar,90).Value =
txtAddress.Text;

Regards,

Brandon
"Jason Huang" wrote:

Hi,

The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is
working for update,
but SqlParameter myPM =new SqlParameter
("@Address",SqlDbType.NVarChar,90,txtAddress.Te xt) is not working.
The Address column is DataType NVarChar 90, no problem.
Any idea?
Jason

[Brandon Driesen]

assuming this is a collection you create:

MicrosoftSqlParameterCollection.cs
===========================
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Microsoft.NewsGroups
{

public class MicrosoftSqlParameterCollection : CollectionBase
{

private static bool IsValidType(object value)
{
return (value != null && value is SqlParameter);
}

protected override void OnInsert(int index, object value)
{
if (! IsValidType(value))
throw new InvalidTypeException("Invalid Type");
else
base.OnInsert (index, value);

}

protected override void OnRemove(int index, object value)
{
if (! IsValidType(value))
throw new InvalidTypeException("Invalid Type");
else
base.OnRemove (index, value);

}

protected override void OnSet(int index, object oldValue, object newValue)
{
if (! IsValidType(oldValue) || ! IsValidType(newValue))
throw new InvalidTypeException("Invalid Type");
else
base.OnSet (index, oldValue, newValue);
}

public SqlParameter Add(string parameterName, SqlDbType dbType, object
value)
{
SqlParameter parameter = new SqlParameter(parameterName,dbType);
parameter.Value = value;
Add(parameter);
return parameter;
}

public SqlParameter Add(string parameterName, SqlDbType dbType, int size,
object value)
{
SqlParameter parameter = new SqlParameter(parameterName,dbType,size);
parameter.Value = value;
Add(parameter);
return parameter;
}

public void Add(SqlParameter parameter)
{
this.InnerList.Add(parameter);
}

public void Remove(string parameterName)
{
SqlParameter parameter = FindByParameterName(parameterName);
if (parameter != null)
this.InnerList.Remove(parameter);
}

public SqlParameter this [int index]
{
get { return (SqlParameter)this.InnerList[index]; }
}

public SqlParameter this [string parameterName]
{
get { return FindByParameterName(parameterName); }
}

private SqlParameter FindByParameterName(string value)
{
foreach(SqlParameter item in this.InnerList)
{
if (string.Compare(item.ParameterName,value,true) == 0)
return item;
}
return null;
}

public SqlParameter [] ToArray()
{
return (SqlParameter [])this.InnerList.ToArray(typeof(SqlParameter));
}

}

public class InvalidTypeException : Exception
{
public InvalidTypeException(string message) : base(message) {}
public InvalidTypeException(string message,Exception innerException) :
base(message,innerException) {}
public InvalidTypeException() : base() {}
}
}

The code
======

public void UpdateByParameter(string sql, SqlParameter [] parameters)
{
UpdateByParameter(sql,parameters);
}

public void UpdateByParameter(string sql, MicrosoftSqlParameterCollection
parameters)
{
UpdateByParameter(sql,parameters);
}

private void InternalUpdateByParameter(string sql, ICollection parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql,connection))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
command.Transaction = transaction;
if (parameters != null)
{
foreach(SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
}
"Jason Huang" <Ja************@hotmail.com> wrote in message
news:uu**************@TK2MSFTNGP15.phx.gbl...

Thanks Brandon!
I made up an UpdatByParameter function.
Would you give me some advice to improve my UpdateByParameter function?
strUpdate ="UPDATE ContactAddr SET Serial=@Serial, ";
strUpdate +="ZipCode =@ZipCode, Address=@Address ";
strUpdate +="WHERE CustNo=@CustNo AND Serial=@Serial ";
SqlParameter [] myPM=new SqlParameter[4];
myPM[0] = new SqlParameter("@Serial", SqlDbType.SmallInt, 2);
myPM[1] = new SqlParameter("@ZipCode", SqlDbType.Char, 10);
myPM[2] = new SqlParameter("@Address", SqlDbType.NVarChar, 90);
myPM[3] = new SqlParameter("@CustNo", SqlDbType.NVarChar, 8);
myPM[0].Value=txtSerial.Text;
myPM[1].Value=txtZipCode.Text;
myPM[2].Value=txtAddress.Text;
myPM[3].Value=txtCustNo.Text;
this.UpdatByParameter(strUpdate, myPM, 1);

public void UpdatByParameter(string strSQL, SqlParameter []
GoodParameter, int i)
{
mConn=new SqlConnection(gstrConn);//new connection need to be here first
mConn.Open();
mComm=new SqlCommand(strSQL,mConn);
for (int j=0; j < i; j ++)
{
mComm.Parameters.Add(GoodParameter [j]); //2005.10.19
}
mTrans = mConn.BeginTransaction();
mComm.Transaction=mTrans;
try
{
mComm.ExecuteNonQuery();
mTrans.Commit();
}
catch(SqlException e)
{
mTrans.Rollback();
}
mConn.Close();
}//end UpdateByParameter

"Brandon Driesen" <Brandon Dr*****@discussions.microsoft.com>
¼¶¼g©ó¶l¥ó·s»D:13********************************* *@microsoft.com...

That is because that constructor is not used for passing in the Value of
the
parameter. It is used to pass in the name of the sourceColumn which would
be
used when the Update method of the SqlDataAdapter is called (and assuming
the
command object which holds this parameter is participating in the update
process)

// constructor
public SqlParameter(string parameterName, SqlDbType dbType, int size,
string
sourceColumn)
{

}

you are actually adding in the name of the sourceColumn and NOT the
value...

the constructor accepts the value of the parameter in 2 forms:

// #1
public SqlParameter(string parameterName, object value) { .. }

// #2
public SqlParameter(string parameterName, SqlDbType dbType, int size,
ParameterDirection direction, bool isNullable, byte precision, byte
scale,
string sourceColumn, DataRowVersion sourceVersion, object value) { .. }
Thus:

SqlParameter parameter = new
SqlParameter("@Address",SqlDbType.NVarChar,90);
parameter.Value = txtAddress.Text;

or if you are referring to the actual command object:

command.Parameters.Add("@Address",SqlDbType.NVarCh ar,90).Value =
txtAddress.Text;

Regards,

Brandon
"Jason Huang" wrote:

Hi,

The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is
working for update,
but SqlParameter myPM =new SqlParameter
("@Address",SqlDbType.NVarChar,90,txtAddress.Te xt) is not working.
The Address column is DataType NVarChar 90, no problem.
Any idea?
Jason

[Brandon Driesen]

typo
====
public void UpdateByParameter(string sql, SqlParameter [] parameters)
{
InternalUpdateByParameter(sql,parameters);
}

public void UpdateByParameter(string sql, MicrosoftSqlParameterCollection
parameters)
{
InternalUpdateByParameter(sql,parameters);
}

private void InternalUpdateByParameter(string sql, ICollection parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(sql,connection))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
command.Transaction = transaction;
if (parameters != null)
{
foreach(SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
}
and thus,
StringBuilder builder = new StringBuilder();
builder.Append(@"UPDATE ContactAddr SET Serial=@Serial, ");
builder.Append(@"ZipCode =@ZipCode, Address=@Address ");
builder.Append(@"WHERE CustNo=@CustNo AND Serial=@Serial ");
MicrosoftSqlParameterCollection parameters = new
MicrosoftSqlParameterCollection();
// icky though as you are passing a string to be converted to a small
integer
// but I am just showing the example
parameters.Add("@Serial", SqlDbType.SmallInt, 2,txtSerial.Text);
parameters.Add("@ZipCode", SqlDbType.Char, 10,txtAddress.Text);
UpdateByParameter(builder.ToString(),parameters);

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:uu**************@TK2MSFTNGP15.phx.gbl...

Thanks Brandon!
I made up an UpdatByParameter function.
Would you give me some advice to improve my UpdateByParameter function?
strUpdate ="UPDATE ContactAddr SET Serial=@Serial, ";
strUpdate +="ZipCode =@ZipCode, Address=@Address ";
strUpdate +="WHERE CustNo=@CustNo AND Serial=@Serial ";
SqlParameter [] myPM=new SqlParameter[4];
myPM[0] = new SqlParameter("@Serial", SqlDbType.SmallInt, 2);
myPM[1] = new SqlParameter("@ZipCode", SqlDbType.Char, 10);
myPM[2] = new SqlParameter("@Address", SqlDbType.NVarChar, 90);
myPM[3] = new SqlParameter("@CustNo", SqlDbType.NVarChar, 8);
myPM[0].Value=txtSerial.Text;
myPM[1].Value=txtZipCode.Text;
myPM[2].Value=txtAddress.Text;
myPM[3].Value=txtCustNo.Text;
this.UpdatByParameter(strUpdate, myPM, 1);

public void UpdatByParameter(string strSQL, SqlParameter []
GoodParameter, int i)
{
mConn=new SqlConnection(gstrConn);//new connection need to be here first
mConn.Open();
mComm=new SqlCommand(strSQL,mConn);
for (int j=0; j < i; j ++)
{
mComm.Parameters.Add(GoodParameter [j]); //2005.10.19
}
mTrans = mConn.BeginTransaction();
mComm.Transaction=mTrans;
try
{
mComm.ExecuteNonQuery();
mTrans.Commit();
}
catch(SqlException e)
{
mTrans.Rollback();
}
mConn.Close();
}//end UpdateByParameter

"Brandon Driesen" <Brandon Dr*****@discussions.microsoft.com>
¼¶¼g©ó¶l¥ó·s»D:13********************************* *@microsoft.com...

That is because that constructor is not used for passing in the Value of
the
parameter. It is used to pass in the name of the sourceColumn which would
be
used when the Update method of the SqlDataAdapter is called (and assuming
the
command object which holds this parameter is participating in the update
process)

// constructor
public SqlParameter(string parameterName, SqlDbType dbType, int size,
string
sourceColumn)
{

}

you are actually adding in the name of the sourceColumn and NOT the
value...

the constructor accepts the value of the parameter in 2 forms:

// #1
public SqlParameter(string parameterName, object value) { .. }

// #2
public SqlParameter(string parameterName, SqlDbType dbType, int size,
ParameterDirection direction, bool isNullable, byte precision, byte
scale,
string sourceColumn, DataRowVersion sourceVersion, object value) { .. }
Thus:

SqlParameter parameter = new
SqlParameter("@Address",SqlDbType.NVarChar,90);
parameter.Value = txtAddress.Text;

or if you are referring to the actual command object:

command.Parameters.Add("@Address",SqlDbType.NVarCh ar,90).Value =
txtAddress.Text;

Regards,

Brandon
"Jason Huang" wrote:

Hi,

The SqlParameter myPM =new SqlParameter("@Address", txtAddress.Text) is
working for update,
but SqlParameter myPM =new SqlParameter
("@Address",SqlDbType.NVarChar,90,txtAddress.Te xt) is not working.
The Address column is DataType NVarChar 90, no problem.
Any idea?
Jason

[Jason Huang]

Thanks Brandon.
I noticed that you have "using" inside the InternalUpdateByParameter.
How can we have an "using" in a function? Isn't the "using" just for in the
starting of a program?

private void InternalUpdateByParameter(string sql, ICollection parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))