|
I want to create a stored procedure where I can select with a 'where in ' and a list of integers against the integer-typed primary key.
For example:
PR_GetSomething @ID='0,1'
Only that doesn't work since it is a varchar. Here is the ad-hoc query for what I want to do in an SP:
select * from Something where ID IN (0,1)
Thanks for any help....
|
|
|
|
|
When you are forming the string of ids itself try to include '' in that string.
"0,1" -> "'0', '1',...."
Gautham
|
|
|
|
|
Thanks for the response but that didn't work for me. I think it is becasuse theId param in my SP is a varchar? I dunno, maybe I should use a different datatype in that param?
I get this:
Conversion failed when converting the varchar value ''0','1'' to data type int.
|
|
|
|
|
Ah well...I'll just make two seperate SP's. One for a specific ID and the other for all of 'em.
|
|
|
|
|
Hi Everyone:
I have solved the problem of how to bind a DataSet to the BindingNavigator control thanks to those who guided me in the right direction.
I now have another problem that is causing me ulcers.
When I populate the DataSet with data I retrieved from some table. I have no trouble using the move next, move previous, move first and move last buttons on the BindingNavigator control. I can navigate from record to record with no problem.
When I try to add a new record into the dataset using the routine written below; two problems occur. 1 – The auto increment ID number goes from 5(the last record ID in the Dataset) to 7. What happened to 6? 2 – After I add the new record’s data; when I page back through the dataset’s records the new record I just added has the record ID of 7. When I move to the previous record 6 (the one that was skipped) I see the data for the first record in the dataset. Records 5 to 2 seem to be correct, but record 1 is empty.
Logic dictates when I add a new record to the dataset it is added at the end of the dataset. This doesn’t seem to be happening. What I plan on doing is after I update the Dataset I will use the DataAdapter to write the changes back to the SQL Sever database, but the dataset looks incorrect, so I won’t commit an update until I’m sure the dataset’s information is correct.
A corollary to this problem is when I try to add records to an empty dataset. I get the DBNull error.
Does anyone know how to add a new record to: 1) an existing dataset, or 2) an empty one?
Thanks,
Quecumber256
|
|
|
|
|
Private Sub New_Bindings(ByVal intOrdinal As Integer, _
ByVal strCode As String, _
ByVal strName As String)
Dim tblBindings As DataTable
tblBindings = MyDataSet.Tables("tblBindings")
Dim drCurrent As DataRow
drCurrent = tblBindings.NewRow
drCurrent("Ordinal") = intOrdinal
drCurrent("BindingCode") = strCode
drCurrent("BindingName") = strName
tblBindings.Rows.Add(drCurrent)
Call Clear_Form()
End Sub
Sorry,
Quecumber256
|
|
|
|
|
Instead of replying to your own post, use the modify option and do changes to your message if required!
Gautham
|
|
|
|
|
FYI - The code I added in the second post was the code I intended to place in the first post.
Obviously this board was the wrong place to ask my question. I'll repost it correctly on the VB.NET board.
Thank you,
Quecumber256
|
|
|
|
|
this is the script.
first table:
CREATE TABLE [ImePoste] (
[PostaId] [int] NULL ,
[imePoste] [varchar] (50) NULL
) ON [PRIMARY]
second table:
CREATE TABLE [Posta] (
[PostaId] [int] NULL ,
[Posta] [varchar] (25) ,
[Znesek] [varchar] (50)
) ON [PRIMARY]
GO
and view
CREATE VIEW dbo.View_posta
AS
SELECT dbo.ImePoste.imePoste
FROM dbo.ImePoste INNER JOIN
dbo.Posta ON dbo.ImePoste.PostaId = dbo.Posta.PostaId
How can i convert dbo.ImePoste.imePoste to int?
please help
|
|
|
|
|
You try a cast?
CREATE VIEW dbo.View_posta
AS
SELECT dbo.ImePoste.imePoste
FROM dbo.ImePoste INNER JOIN
dbo.Posta ON Cast (dbo.ImePoste.PostaId as int) = dbo.Posta.PostaId
Regards,
Hulicat
|
|
|
|
|
Hi,
I have dropdownlist named ddlapplication in my web form. When I click any item it insert
that record in database with primary key and show in gridview.
If user try to add the same record next time by clicking same item from the dropdown list
which already exist in database it should show error message that this
record alredy exist or in other words before inserting it should check wheather the record is already in database or not.
How can I do that. Here is my code
string str1 = this.ddlApplication.DataTextField;
string str2 = this.ddlApplication.SelectedValue;
string str3 = this.ddlPermission.SelectedItem.Value;
String constr = System.Configuration.ConfigurationManager.ConnectionStrings["twcsanConnectionString"].ConnectionString;
SqlConnection objConn = new SqlConnection(constr);
SqlCommand objCmd = new SqlCommand("INSERT INTO tbUsersAppRights(AppId, AppRightId, UserId) " +
"VALUES (@str1,@str2,@str3)", objConn);
objCmd.Parameters.AddWithValue("@str1", int.Parse(this.ddlApplication.SelectedValue));
objCmd.Parameters.AddWithValue("@str2", int.Parse(this.ddlPermission.SelectedValue));
objCmd.Parameters.AddWithValue("@str3", int.Parse(this.lblUserid11.Text));
objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
this.gdAddUser.DataBind();
seema
|
|
|
|
|
Seema,
Try to have a stored procedure, pass the same values to the stored procedure and then check your validations like this:
IF EXISTS(SELECT * FROM YourTableName WHERE YourCondition)
begin
insert query.....
end
else
begin
record exists
end
All you have to do is to display your error msg accordingly once the valdation and insertion is done in your database
Gautham
|
|
|
|
|
Is there any way to do this natively, or will I need to concoct a kluge to enforce this?
eg a using a string with this sort of format "keya124keyb464keyc45" as the primary key in link tables?
--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer
|
|
|
|
|
In the designer, use Ctrl+Click to select multiple columns, then right-click to choose "Primary Key".
|
|
|
|
|
hi all,
i have a sql table which contains multiple entries like 3 duplicate records.
now i have to delete 2 records out of 3 duplicates and keep only one unique record(last inserted).
can anybody pls help me in writing such a delete query...?
thanx in advance.
|
|
|
|
|
Which database product? For SQL-Server, I would use something like:
delete MyTable
from (
select DuplicateColName, Max(InsertDate) MaxInsertDate
from MyTable
group by DuplicateColName
having count(*) > 1
) as A
where MyTable.DuplicateColName = A.DuplicateColName
and MyTable.InsertDate <> A.MaxInsertDateThe inline select statement identifies the duplicate records that need to be kept.
PS: You probably want to try this on a test database first.
Regards
Andy
|
|
|
|
|
|
Hi All,
What is the maximum no. rows in MS-Access2000 and MS-Access XP
Thanx
|
|
|
|
|
The max size of a single MDB file is 2GB. There is no maximum number of rows.
If you're getting into data this large, Access is not your best choice for a database. SQL Server, even the Express Edition, will handle this far more efficiently than Access could ever hope to.
|
|
|
|
|
hi,
i have a coloumn in my database which display the data as '27/01/2007'(nvarchar) . now i wanna conver this in to a Date type in SQL please tell me how to do this.
Regards
ruwandi
rkherath
|
|
|
|
|
cast(column as datetime)
Wout Louwers
|
|
|
|
|
hi,
thax for replying but the date is not typed in the proper format
user types ------> 27/05/2007 (dd/mm/yyyy)
datetime ------> 05/27/2007 (mm/dd/yyyy)
when try to do above error comes
Arithmetic overflow error converting expression to data type datetime.
plz help
regards
rkherath
|
|
|
|
|
Do you want to convert nvarchar data to datetime or vice versa?
Gautham
|
|
|
|
|
in the sql table column is nvarchar , but the text goes like 15/05/2007. but i wanna compare and filter data accoarding to the current date. current date is like 05/17/2007. so i have a problem in writing the sql SP.
basically i wanna filter all the data in the table to see only the records before 2 day from today. but table contains a nvarchar type date entered.
plz tell me a solution
regards
ruwandi
rkherath
|
|
|
|
|
as far as i understand you have a column of type nvarchar with dates in it and you want to display records with dates between today and 2 days before???
then try this:
select * from Your_TableName WHERE Your_ColumnName
BETWEEN CONVERT(NVARCHAR, GETDATE(), 103)
AND
CONVERT(NVRCHAR, GETDATE()-2, 103)
Gautham
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
