By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

SqlParameter and Update problem

P: n/a
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
Nov 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 17 '05 #2

P: n/a
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> 撰寫於郵件新聞: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

Nov 17 '05 #3

P: n/a
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>
撰寫於郵件新聞: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


Nov 17 '05 #4

P: n/a
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>
撰寫於郵件新聞: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


Nov 17 '05 #5

P: n/a
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))
Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.