Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.

--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

DataGrid updation problem !

Hello all
i am trying to update the database with the DataSet (Updated from the DataGrid) but it is giving the error as
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
the code is as follows

public DataSet bindControlToGrid(DataSet dataSet)
{
SqlConnection objConn= null;
SqlCommand objComm = null;
try
{
objConn = new SqlConnection(@."User ID=sa;Password=sa;database=ExtraNet;server=EAGLES\EAGLES");
SqlDataAdapter objAdapter=new SqlDataAdapter();
objAdapter.SelectCommand= new SqlCommand("SELECT sUserID,sUserName FROM tblUser WHERE dUserDeActivated IS NULL ORDER BY sUserName,nUsrSerialNo",objConn);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(objAdapter);
objConn.Open();
DataSet objdataSet= new DataSet("ko");
string d = (dataSet.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());
objAdapter.Fill(objdataSet);
objAdapter.Update(dataSet);
objAdapter.Dispose();
}
catch(Exception ex)
{
string s= ex.Message;
}
finally
{
//objComm.Dispose();
objConn.Close();
}
return dataSet;
}

kindly helpGot The Answere

sUserID was not declared as primary ker

Thanks all

datagrid

hey all

i have 2 tables
1. Location (locid, name)
2. Product (pid, locid, productname, ...etc)

right now i do a select * from product where locid ='locid', and a datagrid with colums
ProductId | ProductName | AvailableIn

how do i do a select * from Location where locid ='locid' and show Location Name in the datagrid?

ProductId | ProductName | AvailableIn
123456 | testing | Somewhere (instead of the locid)

thankstry this. I haven't tested this, but should give you a good indication of what is needed...

SELECT *, L.Name AS "AvailableIn'
FROM Product P
INNER JOIN Location L
ON P.locid=L.locid
WHERE P.locid='locid'

Hope that helps!
KJ
(The INNER JOIN will list all products that have a corresponding entry on the Location table. If that column was optional, you can use a LEFT JOIN)|||It should probably be

SELECTP.*, L.Name AS 'AvailableIn'......

KJ