problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

Problem with Saving data in SQL Server

Hi,

I have a problem. The thing is that when I enter the values in a text boxes like evendate.text = "02/11/07" and desc.text="hello how r u" then I click on Save Button and its save in the SQL DATABASE but after this if I click on Refresh Button every time from the internet Explorer its automatically again save the data in the database although I have empty the textboxes.

The code is below:

<script runat="server">

sub SaveEvent(sender as object, e as EventArgs)

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")

dim cmd as new SQLCommand("insert into events values('" & desc.text & "','" & DateTime.Parse(eventdate.text) & "')",con)

con.open()

cmd.executenonquery()

con.close()
clearform()
display()

end sub

sub display()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from events",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()


if SDR.HasRows = true then
ViewEvents.DataSource = SDR
ViewEvents.DataBind()

end if

con.close()


end sub

sub clearform()
eventdate.text=""
desc.text=""

end sub

sub Reset(sender as object, e as EventArgs)
clearform()

end sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

<style type="text/css">

#display_prod{float:left;}

#prod{margin-top:0;}

</style>

</head>
<body>

<table border="1" align="center" cellpadding="5" cellspacing="0">
<tr>
<td valign="top">

<form id="prod" runat="server" enctype="multipart/form-data">
<table id="display_prod" width="554" border="1" cellspacing="0" cellpadding="0" align="left" height="400">
<tr valign="top" height="20">
<td> </td>
<td>
<table border="1" align="center" cellpadding="10" cellspacing="0">
<tr>
<td>PRODUCTS DESCRIPTION <asp:Label runat="server" ID="lbl"></asp:Label></td>
</tr>
</table>
</td>
</tr>

<tr valign="top">
<td>
<table align="left" id="menu" width="150" border="1" cellpadding="0" cellspacing="0">
<tr align="center">

<td><a href="http://links.10026.com/?link=home.aspx">Main</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=Events.aspx">Change Password</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=events.aspx">Events</a></td>
</tr>
</table>
</td>

<td align="left">
<table width="390" border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="100" >Enter Date :</td>
<td width="243" ><asp:TextBox runat="server" ID="eventdate" BackColor="#FFFFFF" Width="100"></asp:TextBox> mm/dd/yy</td>
</tr>
<tr>
<td>Description :</td>
<td><asp:TextBox runat="server" ID="desc" TextMode="MultiLine" Columns="20" Rows="3" BackColor="#FFFFFF" MaxLength="200"></asp:TextBox> max 200</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button runat="server" ID="btnsubmit" Text="Save" OnClick="SaveEvent"></asp:Button>
<asp:Button runat="server" ID="btnreset" Text="Reset"></asp:Button>
</td>
</tr>
</table>

<table width="390" border="1" align="left" cellpadding="0" cellspacing="0">
<tr>
<td>
<asp:DataGrid runat="server" ID="ViewEvents" AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td><asp:Label runat="server" ID="eventname" Text='<%#container.DataItem("event_name")%>' ></asp:Label></td>

<td><asp:Label runat="server" ID="pdesc" Text='<%# trim(DataBinder.Eval(Container.DataItem,"event_date","{0:MM/dd/yyyy}")) %>' ></asp:Label></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</td>
</tr>
</table>
</td>
</tr>
</table>

</form>

</td>
</tr>
</table>
</body>
</html>

it will happend.. on think you can do after successfully save the data you can redirect the page with same URL.

|||

Thanks its solved now!

Here is a structure of my table

create table events
(
event_id integer primary key identity(1,1),
event_name varchar(200),
event_date datetime,
)

Note: The event_id is a automacally field and we dont need to insert record in this.

for example

i have inserted 10 records in a table

then i deleted all records manually from sql server

but when again I save the record the event_id field shows started from 11 even the table was empty, it should be start from 1?

Thanks for the co-operation

Problem with saving Chinese.

Hi.. I'm trying to save text into SQL 2000 database.

When user enter text in text box , the text save into variable , and show it in confirm page , after save the text to database, all the text turned into "??"

I try to view the data in SQL enterprise Manager / Web Matrix / ASP web page gridview , all of them showing the text fields in "??"

Then I try to add record which come with SQL 2000 enterprise Manager.After save the record , the chinese also turned into "??"

Is there something I need to set for database or server?

Is your datatype VARCHAR, TEXT or CHAR as the datatype on the table columns?

If VARCHAR change to NVARCHAR. If TEXT change to NTEXT. If CHAR change to NCHAR.

If you are using stored procedures (and you should be), you will to amend the parameter declaration on them.

You might want to change the collating sequence on your database to one more appropriate to Chinese (if collating sequence is meaningful for that language), but otherwise you should not need to change anything at the database level.

If this reply provides the anser to your question, please mark as such.

|||all the text are stored in varchar... I'll try to change it to nvarchar|||

yea... it works when I change all the text field type to nnvarchar..

Problem with Saving a Stored Procedure

I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.

Below is my stored procedure.

USE [AdventureWorks]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[WDR_CountryAdd]

(

@.CountryName nvarchar(50),

@.CountryID int OUTPUT

)

AS

INSERT INTO Country

(

CountryName

)

VALUES

(

@.CountryName

)

SELECT

@.CountryID = @.@.Identity

When I hit the execute button, I get the following error.

Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24

Invalid object name 'WDR_CountryAdd'.

I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.

If it doesn't exists, you need to use a CREATE PROCEDURE statement instead of an ALTER PROCEDURE statement.|||

Arnie

I appreciate the fast response.

I used a create and it ran successfully. However, it still did not show up under the stored procedures. So if it does not show up in the list, how do I access it from my web application? With SQL 2000 I just did a right click create new, did the code and it saved it to the list and then I accessed it. I was planning on a code along the lines of WDR_CountryAdd("USA") (of course in the proper codes)

Now here is a beginners question, When I first execute this procedure, do I need to supply the parameters for it to successfully attach to the database?

Jerry

|||

Never Mind. I ran the CREATE again and it worked very well. Must of had a brain dead moment there.

Thanks for the help. I have learned a lot from this forum.

Jerry