Category Archives: SQL server

Create marquee news in front page and the news is read from the database with expired data,also included management page to insert news

My boss just saw other company website has a marquee new on the front page, so she asked me if I could make one and arrow her to insert news and the news will dispear at the expired data.

For achieve that task, first I create a table in my database table, here is my table:

breadNewTable

then I write a store proceedure, a little tip for fresh guy, alway use store preceedure to insert data for security reasons.

breadNewStoreProceedure

Now, We could start the front end job. I create a user control:marquee.ascx(so you could put it in anyway you like), to display the new, here is my html code:

frontUCmarquee

here is the backcod, I used ado.net to open the database and qurery what I need, and bind it to my BulledtedList:

databasereadertomarquee

See the code this if(!IsPostBack) is not neccessary, but I use web froms so it wil inherit maintain state so I don’t need retrieve database everytime postback.

here I am also happy to share the css file for style this unorder list to display inline:

MarqueeCssStyle

A tip for fresh guy, put this in the page your use control will stand it.

Now, you just need drag the usercontrol to any page you want it with this css style in top of the header it. You coud see it.

Then We could start create a page to arrow my boss to insert the bread news, I used boostrap format so it save my time to write css. here is the front page look like:

addNews

here is the html code:

htmlCodeAddnews

The form use jQury Validate, the jquery-ui.js, is for my boss click then will pop up the jQuery calender to arrow her to put the expired date. So simple isn’t, I am love JQure very much, save a lot of time. It remind me when I first time write javascript over 10 years ago, for every simply function I need write a lot codes.

Finally I just need write a event could be fired by this button.

storeIntodatabase

Ok, That’s all. Have fun!

Advertisements

A store procedure to insert values into multitable without duplicate records

The previous store procedure have records repeated and here is the improve one.

the table like this:

tables

 


  
CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]       
@parameterJobID varchar(30),  
@parameterLMStatus nvarchar(10),  
@parameterEntry_datetime datetime   
   
AS  
BEGIN  
declare   
 @username varchar(500),  
 @Job_Acceptance bit   
    SET @Job_Acceptance = '1'  
   
   
 IF NOT EXISTS(SELECT * FROM tblJob where JobID = @parameterJobID)    
 begin   
  insert into tblJob values(@parameterJobID,@Job_Acceptance);  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
 else  
 begin  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
   
END  

How to write store procedure to insert into multiple table at one time

Today just write a complex store procedures to retrieve data from one table, and insert  values into two tables

Here is my table: DataBase Table

My Task is write a store procedure to get username from tblUserAuthemtication And insert usename and other Values into tblLMStatus and tblJob table.

Here is my solution:


CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]
@parameterRef varchar(30),
@parameterLM_Booking_Ref varchar(30),
@parameterLMStatus nvarchar(10),
@parameterEntry_datetime datetime

AS
BEGIN
declare
@username varchar(500),
@JobID int
insert into tblJob values(@parameterRef,@parameterLM_Booking_Ref);
select @username = username from tblUserAuthentication
select @JobID = ID from tblJob
insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@JobID,@username)

END

In my next post I will write how to use this store procedure in my Application project
And my task is not just use this I need write a event to use this store procedure and
also that button trick Send email at the same time.