Problem With Insert Using SQLDataSource

I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error:

========== Error ===========

Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query.

============================

The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows:

======== Code ============

Dim guidMemberidAs Guid =CType(user.ProviderUserKey, Guid)

Session("currmember") = guidMemberid

....

Session("currinterest") =CType(item.FindControl("CheckBox1"), CheckBox).Text

==========================

Any ideas of what I am doing wrong here?

========= SQLDataSource ==========

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:GoodSamSiteDB %>"

DeleteCommand="DELETE FROM [gs_MemberInterests] WHERE [memberid] = @.memberid"

InsertCommand="INSERT INTO [gs_MemberInterests] ([memberid], [Interest]) VALUES (@.memberid, @.Interest)"

SelectCommand="SELECT memberid, Interest FROM gs_MemberInterests WHERE (memberid = @.memberid) AND (Interest = @.interest)">

<DeleteParameters>

<asp:ParameterName="memberid"Type="Object"/>

</DeleteParameters>

<SelectParameters>

<asp:SessionParameterName="memberid"SessionField="currmember"Type="Object"/>

<asp:SessionParameterName="interest"SessionField="currinterest"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="memberid"Type="Object"/>

<asp:ParameterName="Interest"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

=============================

Type="Object"|||

Motley wrote:

Type="Object"

Thanks. I assume you are referring to the fact that the "Type='Object'" has to be removed. I just found a reference to that bug. That cleared the problem.

sql

problem with information_schema.Tables

I believe I am missing some columns from information_schema.Tables view. Is
this fixable? Where do the information_schema views reside? Is it possible
to copy one of these views from another DB? I looked in master, nothing.
What can I do?
Thank,
RichRich wrote:
> I believe I am missing some columns from information_schema.Tables
> view. Is this fixable? Where do the information_schema views
> reside? Is it possible to copy one of these views from another DB?
> I looked in master, nothing. What can I do?
> Thank,
> Rich
What columns are missing?
David Gugick - SQL Server MVP
Quest Software

Problem with ID update after deleting rows

Hi,

I hope some of you can help me with that

I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.

USE [tracking_db]

GO

/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spMaxId]

@.maxId integer output

as

SELECT @.maxId= MAX(id) FROM FILES_TABLE

Once i get the maxID value i simply concat max id and string like:

string.Concat(max_id.ToString(), file_name);

where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"

Two problems occur!!!

Problem nr 1.

Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"

Problem nr 2.

If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!

Hope some of you has any idea what to do

Muris

i think this table ID coulumn is Identity column. In that case identity value has to be reset using DBCC Checkident command to reset the value. Otherwise remove the Identity column and change the insert logic.

select COLUMNPROPERTY (object_id('yourtablename'),'yourcolumnname' , 'IsIdentity')

if the above statement returns 1 it means its a identity column. So you should use DBCC Checkident command to reset the identity.

Madhu

|||

Hi Madhu,

You are right. My ID column is an identity column and it is also autoincremental. I wil now try to solve problem using your idea.

Muris

|||

Hi Madhu,

I find your answer logical and helpful, but I have difficulties to implement it.

Where should I put this DBCC Checkident to reset the identity. I use a stored procedure to find the max value of my identity column.

Simply, where to add the statement your suggested?

Muris

|||

Execute Mahdu's suggestion one time only -in a empty query window.

DBCC CHECKIDENT ( 'YourTable', RESEED )

|||

Suppose u have 1000 rows in the table and you are deleting rows where ID>900 . ie 900 to 1000 - 100 rows. After deletion when you insert what i understand is you should have the ID as 900 but you will get it as 1001. If this is the case. After deletion you need to reset the indentity. in this particular scenario

Delete from yourtablename where id>900 -- It will delete 100 rows

DBCC CHECKIDENT ( 'YourTable', 900) -- REset the value to 900 , if you have not run this

statement your next id will be 1001. You can

do this in sp also. read about this BOL

Insert the new value

Madhu

|||

HI,

I was away from my computer for several days so my reply is a bit late. Anyway I solved the problem with ID update using your suggestions with small modification.

Typing DBCC CHECKIDENT('YourTable', new value of ID) does not help unless you do not specify command RESEED so following statement is fully functional:

DBCC CHECKIDENT('YourTable', RESEED,new value of ID) .

In terms of deleting all rows from your table you should write

DBCC CHECKIDENT('YourTable',RESEED,1) .

The number 1 will be the ID of the newly inserted row in your previously cleaned table.

Thank you all for your help!

Regards

Muris

Problem with formula input with SQL Server Management Studio Express

Hi all,

I'm creating a database using SQL Server Management Studio Express and have a problem. I've got 4 columns: Surface, Rent, MonthlyIncome and AnnualIncome. Surface and Rent are inputed by user, MonthlyIncome is straight calculation Rent*Surface using Computed Column Formula. But, when I want to calculate AnnualIncome SQL SMSE doesn't allow me to input formula like this MonthlyIncome*12. Where I can read about limitations in formula field?

TIA.

Przemek

hi,

computed columns can not include other computed columns in their definition..

http://msdn2.microsoft.com/en-us/library/ms191250.aspx

but, as the definition of your AnnualIncom column is not that heavy ( ) you can define it on the base columns without problems as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.t1 (

Id int NOT NULL PRIMARY KEY,

Surface int NOT NULL DEFAULT 0,

Rent decimal(18,4) NOT NULL DEFAULT 0,

MonthlyIncome AS (Surface * Rent),

AnnualIncome AS ((Surface * Rent) * 12 )

);

GO

INSERT INTO dbo.t1 VALUES ( 1 , 10, 1.8 );

SELECT * FROM dbo.t1;

GO

DROP TABLE dbo.t1;

--<-

Id Surface Rent MonthlyIncome AnnualIncome

-- -- --

1 10 1.8000 18.0000 216.0000

regards|||Hi Andrea, thank you very much for help. I shoul read msdn more carefully.

Przemek

Problem with expression window in derived column

Hi,

i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.

let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.

Can any one asssit in this regard how to achive it? and is it possible to use IF and CASE statement in this dervived column tranformation?

Sreenivas

Select the Add New column option, call it ColB or whatever, then use an expression like this -

ColA == 0 ? "GOOD" : "BAD"

This uses the conditional operator, as documented in Books Online - http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37(SQL.90).aspx

Logicaly it reads like this -

If ColA Equals 0 Then

Return "GOOD"

Else

Return "BAD"

End If

Problem with Dataset.Merge

Hello guyz,
I am using SQL server and I want to merge two datasets. I have two tables which are similar in structure-(same number of columns). I want to display these two tables in one datagrid. I have 5 rows in the first table and 3 on the other. So in the datagrid it should have 8 rows. Part of my code is below…

ConnectToDatabaseServer()
Dim querystr As String = ("Select username, password from PublicAccounts")
Dim da As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds1 As DataSet = new DataSet()
da.Fill(ds1, "firsttable")

querystr = ("Select username, password from EmployeeAccounts")
Dim da2 As SqlDataAdapter = new SqlDataAdapter(querystr, myConnection)
Dim ds2 As DataSet = new DataSet()
da2.Fill(ds2, "secondtable")

ds1.Merge(ds2,False, System.Data.MissingSchemaAction.Add)
MergeGrid.DataSource = ds1
MergeGrid.DataBind()
myConnection.Close()

This code only displays the contents of ds1 (5 rows).
My table structure are as follows:
PublicAccounts (username Char(50), password char(50), pid integer primary key)
EmployeeAccounts (username Char(50), password char(50), eid integer primary key)

I really would appreciate any help.
Thank you..couple of ideas. nothing jumps out at me immediately. try setting preservechanges to true (the second parameter of the ds1.Merge) just for grins.

also, try wrapping the merge in a try-catch block and see if any errors are being thrown. that might help narrow it down.

cs

Problem with dataset

My data from this dataset works until I get to a column that has no value in it then it starts not showing the value in the three columns after the no value column. This is my code to handle the no value column.

if ((myDataset.Tables[0].Rows[i][j].ToString()) == "")

I'm not sure of the C# syntax, but try:

if ((myDataset.Tables[0].Rows[i][j] is DBNull)

|||

It ended up that because of a javascript issue it was entering a bunch of spaces in that column and therefore the column wasn't null or "" empty and I couldn't check the length cause of the spaces. So I replaced " " with "" (2 spaces with 0 spaces) then checked if length is less than 2. It worked perfect.